Use OLAP data sources from SAS Stored Processes

So are you now dreaming big? Coming up with other ideas for how SAS Stored Processes can be leveraged in your installation of SAS?

As I mentioned in last week's post, you could use OLAP cubes as a data source for your SAS Stored Process. Here is how I would suggest getting started.

Step 1. Open and slice the OLAP Cube in SAS Enterprise Guide

This allows you to navigate through the cube and generate the tedious MDX code for that specific view and limit all of your typing.

Step 2. Select the menu item Edit View -> Edit in MDX Viewer


Now you can view all of the MDX code associated with this view. Copy this entire code for the next step.

Step 3. Place the MDX code in a PROC SQL statement

proc sql;
connect to olap (host="machine" port=5451
    user="username"
        pass="pwd"
);

CREATE TABLE stp_datasource as 
select * from connection to olap 
(
/*********************PUT PASTED MDX CODE HERE**********************/

SELECT { 
 [Measures].[UnitsSUM] } PROPERTIES MEMBER_CAPTION, PARENT_UNIQUE_NAME
ON COLUMNS, { 
 [DateDimension].[Fiscal_Month_Num].Members } 
                         PROPERTIES MEMBER_CAPTION, PARENT_UNIQUE_NAME
ON ROWS
FROM [SalesSummary]
WHERE (
         [ProductDimension].[All ProductDimension].[Wholesale].[Carob N Almonds])

/************STOP THE PASTING OF MDX CODE BY THIS POINT****************/
);
quit;

Step 4. Build cascading filters for the where clause

I created a cascading filter, one for TYPE and one for PRODUCT and replaced the where clause with the following code:

WHERE (
        [ProductDimension].[All ProductDimension].[&type].[&product])

Step 5. Register and test the SAS Stored Process

Whalaa! You have successfully queried an OLAP cube for use in your stored process! Now that you have the basic steps, what dreaming are you doing? How will you use OLAP cube data for stored processes?

tags: OLAP, Stored Processes

5 Comments

  1. Posted June 22, 2012 at 8:09 am | Permalink

    Great post - gotta love MDX/STP/OLAP!

    I've always wanted to test this as a way to enable report linking to OLAP-based web reports in WRS. Have you extended it this far?

    One big note is to avoid putting dimensions on columns in the MDX statement as member names can change which would change the column names in the output table (stp_datasource in this post). Not fun when your column names are changing dynamically!

    • Angela Hall Angela Hall
      Posted June 22, 2012 at 8:42 am | Permalink

      This is exactly how I do report linking for SAS Stored Processes, but I havent attempted linking into WRS/OLAP reports.
      Guess that'll be another blog post! You should post that one! :)

  2. Chris Hemedinger Chris Hemedinger
    Posted July 16, 2012 at 2:33 pm | Permalink

    In SAS Enterprise Guide 4.3, when you select Create Slice from your OLAP view it generates a "task" that includes the PROC SQL and MDX code, ready to use (or to copy/paste for use in another stored process).

  3. Bob Whitehead
    Posted September 13, 2012 at 12:11 am | Permalink

    Thanks Angela.

    I used a similar technique some years ago for generating thousands of web pages from an OLAP cube (don't ask why).

    The approach of using Enterprise Guide to generate the first MDX slice was extremely useful as Enterprise Guide allows you to see the report/data layout as a preview. Then just added macro references as required to generate the reports for various slices.

    NB: we needed to do this in a traditional SAS batch environment at the time.
    As always, SAS offers so much flexibility in providing solutions.

Post a Comment

Your email is never published nor shared. Required fields are marked *

*
*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <p> <pre lang="" line="" escaped=""> <q cite=""> <strike> <strong>