BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
JM_VFAU
Obsidian | Level 7

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

1 ACCEPTED SOLUTION

Accepted Solutions
Dmitry_Alergant
Pyrite | Level 9

Hi,

 

Obviously, you do use functions in this query. At the first glance, I see two examples:

 

  • upcase
  • trim(left(put()))

 

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!

-------
Dmitriy Alergant, Tier One Analytics

View solution in original post

8 REPLIES 8
Dmitry_Alergant
Pyrite | Level 9

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)

 

  • A calculated item is exported that uses some advanced functions, beyond the very limited list of "supported" functions for push-down (http://documentation.sas.com/?docsetId=acreldb&docsetTarget=p0f64yzzxbsg8un1uwgstc6fivjd.htm&docsetV...)

  • Information map tables are stored in different schemas / SAS libraries, and these libraries are not configured in an exactly same way (same server, same connection credentials, same assignment options - everything should match except for the "schema" attribute)

  • Some query execution error happened upstream (you should find error messages in the log) in an attempt to upload temporary tables, or on the first query execution attempt. SAS now tries to "catch-up" for the error with non-DB execution.

  • SAS MA generated invalid SQL query due to a software bug (they fix them in newer versions, but there are still a few) that is not ANSI SQL compliant. Such SQL may not be pushed to a database, but may still be executed in SAS due to it being less strict in sql validation.

 

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!

-------
Dmitriy Alergant, Tier One Analytics
JM_VFAU
Obsidian | Level 7

Thanks Dmitry. We think it is point 4. There are no specific functions being used in the query. We will keep investigating.

Dmitry_Alergant
Pyrite | Level 9

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.

  • Repeat and run the same query in SAS Enterprise Guide (with libraries assigned etc) with sastrace option and see what it says...

  • Try running the same query directly in the database client, replacing SAS library names with appropriate DBMS schema names.
-------
Dmitriy Alergant, Tier One Analytics
pcapazzi
Pyrite | Level 9

Thank you for that link of supported pass-through functions. Bookmarked...

 

pcapazzi
Pyrite | Level 9

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.

 

audit.PNG

JM_VFAU
Obsidian | Level 7

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;

Dmitry_Alergant
Pyrite | Level 9

Hi,

 

Obviously, you do use functions in this query. At the first glance, I see two examples:

 

  • upcase
  • trim(left(put()))

 

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!

-------
Dmitriy Alergant, Tier One Analytics
JM_VFAU
Obsidian | Level 7
Thanks Dmitry. This worked.
How to improve email deliverability

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.

Discussion stats
  • 8 replies
  • 3762 views
  • 4 likes
  • 3 in conversation