SAS Support Communities

In some cases, especially when analysing performance issues, it might be helpful to trace SQL queries when publishing a campaign to the Common Data Model.   To enable tracing, follow these steps:   1. Add the following options statement to the MarketingAutomation_autoexec_usermods.sas file that resides in \SAS-configuration-directory\Lev1\Applications\SASCustomerIntelligence:   options sastrace=',,,ds' sastraceloc=saslog nostsuffix sql_ip_trace=(note,source) msglevel=i fullstimer;   2. Restart the SAS Object Spawner.   3. Execute a campaign in SAS Customer Intelligence Studio and the following messages are shown in the SASCustIntelCore6.x and Stored Process Server logs:    MPRINT(MAUPLOAD): ; OLEDB: AUTOCOMMIT turned ON for connection id 7 OLEDB: *-*-*-*-*-*-* COMMIT *-*-*-*-*-*-* on connection 7 OLEDB: AUTOCOMMIT turned OFF for connection id 7 MPRINT(MAUPLOAD):   libname dbtmplib OLEDB DIRECT_EXE=DELETE SQL_FUNCTIONS=ALL UTILCONN_TRANSIENT=YES PROPERTIES=("Initial Catalog" = CDM) DATASOURCE=SAS PROMPT=NO PROVIDER=SQLNCLI10 USER=sasdemo PASSWORD=********; NOTE: Libref DBTMPLIB was successfully assigned as follows:      Engine:       OLEDB      Physical Name: SQLNCLI10 MPRINT(MAUPLOAD):   option nodbidirectexec; OLEDB: AUTOCOMMIT turned ON for connection id 8 OLEDB: *-*-*-*-*-*-* COMMIT *-*-*-*-*-*-* on connection 8 OLEDB: AUTOCOMMIT turned OFF for connection id 8 OLEDB: AUTOCOMMIT turned ON for connection id 8 OLEDB: *-*-*-*-*-*-* COMMIT *-*-*-*-*-*-* on connection 8   Summary Statistics for OLEDB are: Total SQL execution seconds were:                   0.004799 Total seconds used by the OLEDB ACCESS engine were     0.005096   DEBUG: SQL Implicit Passthru stmt has been prepared successfully. SQL_IP_TRACE: The SELECT statement was passed to the DBMS.   The example above demonstrates the message when SAS/Access to OLEDB is used, but this works for all other SAS/Access engines as well.   As you can see, it will show a Summary Statistic about the execution time and that the SQL statement is properly sent to the DBMS.   Please note: Because publishing to the Common Data Model is done by a Stored Process, you will see these messages in both log files.                 ... View more

Did you know how Cell Nodes affect query results in SAS Customer Intelligence?   First let me explain some Key Terms:   Subject List: A list of subjects. There is no context associated with this list.  All we know is whether or not a given Subject_ID is in the list.   Select and Cell Nodes: A Customer Intelligence Selection Node that represents a Subject List.  Nodes downstream from a Cell Node see only the Subject List. They don't know why the Subjects are in that list.   MATable: The Table that Customer Intelligence creates containing the physical Subject List.  Represented by a Cell Node in the Campaign.   Example Data:   Diagram Use Case: Select Node(Type='VISA') ---> AND(meets all) Select Node(Balance>=1000)   Question being asked: Which Subjects have 'VISA' accounts with a Balance >= 1000. The result of this query is:     SUBJECT_ID = { 2 } because only SUBJECT_ID 2 has a VISA account with a Balance >= 1000:   Diagram Use Case: Select Node(Type='VISA') --> Cell --> AND(meets all) Select Node(Balance>=1000)   Question being asked: Which Subjects that have 'VISA' accounts also have any other account with a Balance > = 1000.   The result table and the Use Case have been highlighted in yellow to show the source of the final result. This case is the same as the previous case, except that there is now a Cell between Select Node(Type='VISA') and the AND node. The Cell creates a MATable that contains this list of subjects: { 1, 2 } because both SUBJECT_IDs 1 and 2 have VISA accounts.   The AND node then combines this list of SUBJECT_IDs with the criteria Balance>=1000. The result of the AND query is now:     SUBJECT_IDs = { 1, 2 } because both subjects were in the Cell's List of SUBJECT_IDs and they had an account with a Balance >= 1000.   Summary: Dropping Cells into a diagram can change the question that's being asked. When formulating the Natural Language Question that represents the 'AND' result, one must remember that the AND node does not know why SUBJECTs are in the list. It only knows that the list exists and that the result needs to consider membership in this list as one of its criteria. ... View more

To extract Metadata for the SAS® Customer Intelligence objects, like for example the export definitions, you can use the SAS® Customer Intelligence 6.x Integration Utilities. These utilities are typically located on your Compute Tier at the following location: <SASHome>\SASMarketingAutomationIntegrationUtilities\6.x   1) In my example I would like to extract the export definition with name “FiServ - Export to SMS” stored in folder “Definitions\Export Definitions”     2) As a first step you need to create a request xml file. In my example the request xml file would look like:   <?xml version="1.0"?> <MAExtractRequest> <ExportDefinitionDO detail="All">                 <Folder operator="=">                 <Name operator="=">Definitions\Export Definitions</Name>                 </Folder>                 <Name operator="=">FiServ - Export to SMS</Name> </ExportDefinitionDO> </MAExtractRequest>   Saved as “export_definitions_extract.xml”.   Note: Requesting an export definition with detail set to "ALL" and removing the tag <Name operator="=">FiServ - Export to SMS</Name> will result in one or more complete definitions being extracted.   3) The command line to extract the definition would look like: <SASHome>\SASMarketingAutomationIntegrationUtilities\6.x  sasmaextract.exe sasdemo password DefaultAuth "Business Context Name" export_definitions_extract.xml out_export_definition.xml   4) The output file “out_export_definition.xml” does contain the tags for <FieldName>:   <FieldName>Individual ID</FieldName> <FieldName>trackingCode</FieldName> <FieldName>First Name</FieldName> <FieldName>Surname</FieldName> <FieldName>Mobile Number</FieldName> <FieldName>Age</FieldName> <FieldName>Gender</FieldName> <FieldName>Income</FieldName> <FieldName>Marital Status</FieldName> <FieldName>Challenger</FieldName>   Which correspond to the data items selected in your export definition:     Note: Modified field names in the “Selected:” Field section are not extracted.   If you need more information have a look at the “SAS® Customer Intelligence 6.x Integration Utilities User’s Guide” located at:  http://support.sas.com/documentation/solutions/ci/6.5/en/PDF/ciintutilug.pdf where 6.x should be modified with the version you are using.       ... View more