Friday, June 12, 2009

Utilizing SQL Pass Thru within SAS Information Maps to Improve Web Report Studio Performance

When accessing an external data source from Oracle, Teradata, SQL, etc with SAS it is widely accepted that the SAS program should use the SQL Pass-Thru functionality rather than a libname reference. However when linking to a defined external data source in the SAS Metadata from SAS Information Maps, the information map ONLY uses the libname mechanism. Therefore to improve the performance you will need to create a SQL pass-thru query in SAS, save as a Stored Process, and link to the SAS Information Map. Included are the steps to perform this linkage:
  1. Define sample table via sql passthru and store in a BASE SAS Library location ~Note: This table can have 0 records. It is required only to aid in defining the metadata correctly in step 2.
  2. Import the table into Metadata (via SAS Management Console, or proc metalib statement) ~Note: The table libref must be the same in SAS Management Console as the libref in the SAS Stored Process
  3. Modify the sample table creation program as mentioned below: ~NOTES:
    • Must have *ProcessBody; in the top of the program (This is created by Enterprise Guide automatically)
    • Must reassign the table location libref to libname BIOUT (work);
    • Must be the same libref of the Metadata Library used in Info Map
  4. Define the program as a Stored Process in Metadata
  5. Assign parameters for query ~Note: Parameters to pass into the query MUST be defined at the STP rather than within the Information Map.
  6. Start Info Map Studio and include the sample table (with 0 records) created and defined in steps 1-2 above.
  7. Include the Stored Process in the Information Map (Tools -> Stored Processes)
  8. Run an Information Map Test ~Note: The Stored Process Parameter will appear for the test.
  9. Stored Process Parameters of DATE type will not appear as calendars in Web Report Studio.
Reference Material which include screenshots & more descriptions can be found @:
~ http://support.sas.com/kb/26/175.html
~ http://support.sas.com/kb/33/146.html
~ http://www2.sas.com/proceedings/forum2008/049-2008.pdf
Posted by Angela Hall at 6/12/2009
Labels: ,

2 comments:

Anonymous said...

Hi,

So do you need to pre-define every possible filter in the STP? i.e what happens when a user chooses to filter on a field through WRS, where is the filter spplied?

Does the pass-through query always join every table in the STP? What happens when a user chooses fields from just 2 of the tables in the underlying data, are all tables queried or just the 2 the user has selected from?

June 16, 2009
Angela Hall said...

Filters that will be applied to the SQL PassThru Query must be defined and coded in the STP area of this interaction. I would recommend that no filters be defined in the InfoMap.

The pass-thru query is coded in the .sas STP code, so each time the InfoMap is called from WRS, the full query code will be run independant of fields selected by the WRS report author.

June 18, 2009

Post a Comment

Subscribe to: Post Comments (Atom)