SAS OLAP Cubes: Using PROC SQL and MDX to Query OLAP Cubes

Submitted by Steve Overton on January 11, 2012 – 5:09 am

SAS PROC SQL has a lot of neat ways you can extract and manipulate data. In my experience, SAS users typically write PROC SQL to query SAS datasets or relational tables in databases. You can also write multi-dimensional queries against OLAP cubes using the MDX expression language. This makes you REALLY think outside the box when you have to construct what measures to aggregate and how they will be sliced on rows and columns. A BIG requirement to using this technique is the ability to write and understand MDX expressions. If you do not understand MDX, turn back now!

Benefits of Proc SQL with SAS OLAP

Managers want to minimize data infrastructure for environments with a lot of OLAP technology already in place. Rather than create more SAS datasets or RDBMS tables, which represent summary data, SAS developers can query OLAP cubes on-the-fly for better performance on high volumes of data. Or a summary dataset can be produced using a more modular approach rather than going through the same aggregation a SAS program would go through to aggregate data.
Programmers want to use some of the advanced capabilities in the MDX language like time-series, trending, and forecasting functions.
Query performance is a must have at run time. I have used PROC SQL against an OLAP cube in SAS Stored Process and SAS BI Dashboard applications where run time performance needed to be very fast. These queries can also be parameterized using Macro variables, but I will save that for another discussion…

Basic Skeleton Structure of MDX Query

This code uses the SAS pass-through facility to connect to a specified OLAP Server and execute an MDX query against a cube.

Here is an example query using my cube from SAS Global Forum 2011:

You can download the code yourself and run against the sample cube I have provided on my website.

Output

This is the output you should see in the resulting SAS dataset output, which matches what is in the OLAP cube:

Thoughts and Considerations

You can see a lot of static references to members in the MDX query. This can create headaches when dealing with reports or other outputs which require dynamic results that change over time. If this is the case, I would use pre-processing steps in the same SAS program to write macro variables that are dynamic. These macro variables can then be used to references pieces of the MDX query. I will go into this topic a little later in a future post.

Also be careful how you structure the MDX query. I would recommend keeping the abstract dimensions in the ROWS. If you include dimensions in the columns, the member names will become the output column names. These column names can change over time as new members appear which would change the structure of your resulting dataset.

Final Thoughts

I have described a trick you can use to let Enterprise Guide write the MDX code for you! So rather than loose sleep over how to structure an MDX query, take the easier route and let SAS Enterprise Guide put you to sleep better at night!

Check out SAS Support for more documentation and technical considerations on using PROC SQL to query an OLAP cube.

You could also use PROC SQL to do a number of other interesting things like create macro variables and lists for advanced programming procedures. I will go into more fun PROC SQL tricks in future blog posts…