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