Blogs

Blogs

Utilizing SQL pass thru within SAS Information Maps to improve Web Report Studio performance

6
By

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

Tags Access Engine business intelligence Information Maps
Share

About Author

Angela Hall

Senior Technical Architect

Angela offers tips on using the SAS Business Intelligence solutions. She manages a team of SAS Fraud Framework implementers within the SAS Solutions On-Demand organization. Angela also has co-written two books, 'Building BI using SAS, Content Development Examples' & 'The 50 Keys to Learning SAS Stored Processes'.

6 Comments

  1. Beni WIdjonarko on

    Please give me a sample for sas web report studio, SAS Protal report, Dashboard and SAS Information Delivery Portal. Thankyou

  2. Pingback: Do not doubt the power of SAS Information Maps

  3. Angela Hall
    Angela Hall on

    New comment from prior blog post location:
    Hi ,
    if i define the filter in the .sas STP code "a macro...&filter_var", how is the value passed from WRS to .sas code without defining it in the IMap.

    i have a requirement where the user will select from multiple / single value from one field and single or no value from other field. Base on these filter condition the a dataset should be created.

    This dataset should be again used information map studio to calculate a custom data element.
    the value of this custom data element with others will be used to render a graph.
    was wondering if all these can be done ?

    can you suggest any approach ?

    Thanks
    KD

    ***************
    My response:
    Check out the sample program from SAS at: http://support.sas.com/kb/33/146.html

    This gives you an idea of how a stored process maintains the prompts for WRS users to enter - and then derives data for the information map to make available to the web report.

  4. Angela Hall on

    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.

  5. Anonymous on

    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 applied?

    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?

Back to Top