Hi,
We are trying to export a campaign output using SAS CI of around 5 million rows. What we have seen is that if it is less number of rows, say around 1.5 mil, the campaign run finishes fast. But for larger number of rows, we see that it using a lot of SASWORK space. I suspect it is pulling all the data into SAS and trying to merge the data in SAS. We want to avoid SAS CI doing this and run the full query in the Database. Are there any options we can use to force SAS CI Export to avoid pulling the data into SAS? Any options that can be set in the Library to make it do this?
John
Hi,
Obviously, you do use functions in this query. At the first glance, I see two examples:
These functions may be coming either from the Calculated Items or straight from the information map.
Upcase and Trim are among the functions enabled for pass-through to Teradata: https://support.sas.com/documentation/cdl/en/acreldb/69580/HTML/default/viewer.htm#p0lpu4mxo0lkrcn17...
But Left and Put are not, they are preventing the query from passing through to the database.
The ways to remedy the situation depend on the column format in the database, how long is that number. You may be better exporting it "as-is" (as a number) and setting the export format as BEST32. in the export tab. If it happens that the number is extremely long (over 16 decimal digits), you must convert it to a string in-database before it is exported to SAS to avoid losing precision, and the way to do so will be on a Teradata view level (create a view, or modify an existing view if you are already using one).
Good luck!
Data pulls to SAS for processing are not a result of a bigger number of records. Most likely, same happens with 1.5 mln, just it finishes faster.
And there is no setting.
SAS always tries to push the data to the database for processing (including Communication nodes execution), unless there is a reason that prevents it from doing so.
You should carefully explore the SQL query (you can find it in CICore log, or if you generate Campaign Documents - Audit Log in the campaign) to look for the cues.
Most typical issues that prevent a query from being pushed down are the following (there are other situations as well, but they a rare)
The tricky part starts when an advanced function is identified as a cause, but users still need this function and can not avoid its usage:) This is where some fun begins... there are some tricks even for such situations though.
Hope it helps. Good luck!
Thanks Dmitry. We think it is point 4. There are no specific functions being used in the query. We will keep investigating.
This is actually quite unlikely for a Communication node. Try recreating (reuploading) temporary join tables to the dbtmplib library, and then try the following tests.
Thank you for that link of supported pass-through functions. Bookmarked...
In CIS you can "Create PDF Document" for a campaign and one of the options is to create an Audit Log. If you review it you should see the librefs/libnames being used and what queries were used to produce your data. It may help you determine if there are queries joining separate connections to backend database. If you could post the audit log you may get additional advice.
Below is the query.
/* clear syscc before new query */
%let syscc=0;
%let sysrc=0;
options Locale=en_AU;
LIBNAME TDBUSCMP TERADATA SERVER="voda2.vodafone.com.au" SCHEMA=AUPR_BUS_VIEW
USER=svc_pr_sasci_0010 PASSWORD=******** ;
libname MATables list;
PROC SQL;
Create table MATables.TEDAGM4IWAVGOTPUCjohnm as
SELECT DISTINCT
table2.SUBS_ID AS SUBS_ID LABEL='.Subscriber Id' FORMAT=F9.0,
( table3.Acct_Id ) AS ACCT_ID LABEL='Account Id' FORMAT=F9.0,
( table4.Acct_Num ) AS ACCT_NUM_1 LABEL='Account Number' FORMAT=$50.,
( table3.Accs_Num ) AS ACCS_NUM_3 LABEL='Access Number' FORMAT=$50.,
( table3.Email_Bill_Addr ) AS EMAIL_BILL_ADDR LABEL='Billing Email Address'
FORMAT=$100.,
( table3.Mktg_Prmssn_EMail_Ind ) AS MKTG_PRMSSN_EMAIL_IND_1 LABEL='Marketing
Permission Email Indicator' FORMAT=$1.,
( table3.Mktg_Prmssn_Acct_Ind ) AS MKTG_PRMSSN_ACCT_IND_1 LABEL='Account Marketing
Permission Indicator' FORMAT=$1.,
( table3.Mktg_Prmssn_Ind ) AS MKTG_PRMSSN_IND LABEL='Marketing Permission
Indicator' FORMAT=$1.,
( table3.Acct_Type_Cd ) AS ACCT_TYPE_CD LABEL='Account Type Code' FORMAT=$30.,
Page 4 of 7
( CASE WHEN table3.Accs_Num NOT LIKE '614%' THEN 'Fixed' WHEN table3.Plan_Type_Cd =
'MBB' THEN 'MBB' ELSE 'Voice' END ) AS SERVICE_TYPE_CODE LABEL='Service Type Code'
FORMAT=$30.,
( table5.Cntct_Title_Cd ) AS CNTCT_TITLE_CD_1 LABEL='Admin Contact Title'
FORMAT=$30.,
( CASE UPCASE(table5.Frst_Nm) WHEN 'N/A' THEN '' WHEN 'NA' THEN '' ELSE
table5.Frst_Nm END ) AS ADMIN_CONTACT_FIRST_NAME_2 LABEL='Admin Contact First Name (N/A
Removed)' FORMAT=$100.,
( table5.Last_Nm ) AS LAST_NM_1 LABEL='Admin Contact Last Name' FORMAT=$100.,
( table3.Addr_Ln_One_Txt ) AS ADDR_LN_ONE_TXT LABEL='Billing Account Address Line
One' FORMAT=$1000.,
( table3.Addr_Ln_Two_Txt ) AS ADDR_LN_TWO_TXT_2 LABEL='Billing Account Address Line
Two' FORMAT=$1000.,
( table3.Sbrb_Nm ) AS SBRB_NM LABEL='Billing Account Address Suburb' FORMAT=$100.,
( table3.Regn_Cd ) AS REGN_CD_3 LABEL='Billing Account Address State' FORMAT=$30.,
( table3.Pstl_Cd ) AS PSTL_CD LABEL='Billing Account Address Postcode' FORMAT=$30.,
( table3.Curr_Mnfctr_Nm ) AS CURR_MNFCTR_NM LABEL='In Use Device Manufacturer'
FORMAT=$1000.,
( table3.Curr_Dvc_Gnrtn ) AS CURR_DVC_GNRTN LABEL='Current Handset Type Code'
FORMAT=$30.,
( trim(left(put(table3.Prim_Prod_Key, best32.))) ) AS PRIMARY_PRODUCT_KEY
LABEL='Primary Product Key' FORMAT=$50.,
( table3.Curr_Dvc_Rollup_Nm ) AS CURR_DVC_ROLLUP_NM LABEL='In Use Device Model
Rollup' FORMAT=$1000.,
( table3.Plan_Cntrct_Tnr_Mnths ) AS PLAN_CNTRCT_TNR_MNTHS LABEL='Subscriber
Contract Tenure' FORMAT=BEST12.,
( table3.Subs_Tnr_Mnths ) AS SUBS_TNR_MNTHS_1 LABEL='Subscription Tenure (Month)'
FORMAT=F11.0
FROM
(
SELECT DISTINCT
( table0.Subs_Id ) AS SUBS_ID LABEL='Subscriber Id' FORMAT=F9.0
FROM
MATables.TGHGEU2VUBJBDUVX2johnm table1 Inner join TDBUSCMP.Cmpst_Actv_Subs_Curr
table0 on table1.SUBS_ID = table0.Subs_Id
) table2 Left join TDBUSCMP.Cmpst_Actv_Subs_Curr table3 on table2.SUBS_ID = table3.Subs_Id
Left join TDBUSCMP.Cmpst_Acct_Curr table4 on ( ( table3.Acct_Id =table4.Acct_Id ) ) Left
join TDBUSCMP.Dim_Party table5 on ( table5.Party_Id=table3.Acct_Party_Id ) ;
quit;
Hi,
Obviously, you do use functions in this query. At the first glance, I see two examples:
These functions may be coming either from the Calculated Items or straight from the information map.
Upcase and Trim are among the functions enabled for pass-through to Teradata: https://support.sas.com/documentation/cdl/en/acreldb/69580/HTML/default/viewer.htm#p0lpu4mxo0lkrcn17...
But Left and Put are not, they are preventing the query from passing through to the database.
The ways to remedy the situation depend on the column format in the database, how long is that number. You may be better exporting it "as-is" (as a number) and setting the export format as BEST32. in the export tab. If it happens that the number is extremely long (over 16 decimal digits), you must convert it to a string in-database before it is exported to SAS to avoid losing precision, and the way to do so will be on a Teradata view level (create a view, or modify an existing view if you are already using one).
Good luck!
Want to review SAS CI360? G2 is offering a gift card or charitable donation for each accepted review. Use this link to opt out of receiving anything of value for your review.
Listen to the Reimagine Marketing podcast
Assess your marketing efforts with a free tool
SAS Customer Intelligence Learning Subscription (login required)
Compatibility notice re: SAS 9.4M8 (TS1M8) or later
SAS' Peter Ansbacher shows you how to use the dashboard in SAS Customer Intelligence 360 for better results.
Find more tutorials on the SAS Users YouTube channel.
Want to review SAS CI360? G2 is offering a gift card or charitable donation for each accepted review. Use this link to opt out of receiving anything of value for your review.
Listen to the Reimagine Marketing podcast
Assess your marketing efforts with a free tool
SAS Customer Intelligence Learning Subscription (login required)
Compatibility notice re: SAS 9.4M8 (TS1M8) or later