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?
5 Comments
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.
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).
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!
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! :)
I will put it on the TODO list!