topic SQL Pass-through in SAS Customer Intelligence

SQL Pass-through

HeidiDT — Wed, 22 Jun 2016 09:24:42 GMT

Hi All

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

Re: SQL Pass-through

LinusH — Wed, 22 Jun 2016 11:05:47 GMT

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#p0f64yzzxbsg8un1uwgstc6fivjd.htm

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

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

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

Re: SQL Pass-through

shill — Wed, 22 Jun 2016 13:29:44 GMT

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