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

Hi All

Is there a setting in CI Studio that will cause implicit pass-through in selection queries where it is possible?

1 ACCEPTED SOLUTION

Accepted Solutions
shill
SAS Employee

If you're using calculated items defined in your information map or in CI Studio, the tips from @LinusH are invaluable for setting up your environment (SQL_FUNCTIONS=ALL, making sure all your libnames have the exact same settings except SCHEMA=, etc.). And the option statement below, combined with capturing the query from your CI core log (on the mid tier) and running it in EG with a proc sql statement ("proc sql loops=1000 _method feedback;") will let you see if that calculated item is being passed or not:

 

ACCESS ENGINE:  SQL statement was not passed to the DBMS, SAS will do the processing

 

If SAS has to do the processing, it will download all the data, then do the calculation on the compute server.

 

If, on the other hand, your question is more about the queries that get generated from the information map for non-calculated fields, there is no switch to control that; it's always generated sql that's executed using the normal SAS/ACCESS & proc sql method


Register today and join us virtually on June 16!
sasglobalforum.com | #SASGF

View now: on-demand content for SAS users

View solution in original post

2 REPLIES 2
LinusH
Tourmaline | Level 20

I'm not familiar with CI Studio, but if you are using SAS/ACCESS libnames, implicit pass-through will occur by default. There are some settings that affect (mostly reduce) what's gets "pass-throughed".

 

See the specifics for you database. Here's the doc for Oracle:

Functions: https://support.sas.com/documentation/cdl/en/acreldb/69039/HTML/default/viewer.htm#p0f64yzzxbsg8un1u...

Joinshttps://support.sas.com/documentation/cdl/en/acreldb/69039/HTML/default/viewer.htm#p0idw091gkbf6hn17...

 

Also, to monitor pass through behaviour, add this to your SAS program/autoexec...:

options msglevel=i sastrace=',,,d' sastraceloc=saslog nostsuffix;

 

Data never sleeps
shill
SAS Employee

If you're using calculated items defined in your information map or in CI Studio, the tips from @LinusH are invaluable for setting up your environment (SQL_FUNCTIONS=ALL, making sure all your libnames have the exact same settings except SCHEMA=, etc.). And the option statement below, combined with capturing the query from your CI core log (on the mid tier) and running it in EG with a proc sql statement ("proc sql loops=1000 _method feedback;") will let you see if that calculated item is being passed or not:

 

ACCESS ENGINE:  SQL statement was not passed to the DBMS, SAS will do the processing

 

If SAS has to do the processing, it will download all the data, then do the calculation on the compute server.

 

If, on the other hand, your question is more about the queries that get generated from the information map for non-calculated fields, there is no switch to control that; it's always generated sql that's executed using the normal SAS/ACCESS & proc sql method


Register today and join us virtually on June 16!
sasglobalforum.com | #SASGF

View now: on-demand content for SAS users

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
  • 2 replies
  • 1421 views
  • 3 likes
  • 3 in conversation