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

January 11, 2012
By

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!  icon cool SAS OLAP Cubes: Using PROC SQL and MDX to Query OLAP Cubes

 

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

PROC SQL MDX query skeleton SAS OLAP Cubes: Using PROC SQL and MDX to Query OLAP Cubes
 
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:
 
PROC SQL MDX query SAS OLAP Cubes: Using PROC SQL and MDX to Query OLAP Cubes
 
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:
 
PROC SQL MDX query output SAS OLAP Cubes: Using PROC SQL and MDX to Query OLAP Cubes

 

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…

Related content:

  1. SAS OLAP Cubes: Tips for Building OLAP Cube Aggregations Effectively
  2. SAS OLAP Cubes: Tailing Time-Based Data for Dynamic Results
  3. SAS OLAP Cubes: Percent of Total MDX Calculation
  4. Best Practices: Letting SAS Enterprise Guide Write MDX Code
  5. Building Business Intelligence Using SAS Book
SAS OLAP Cubes: Using PROC SQL and MDX to Query OLAP Cubes Steve Overton  (6 Posts)

Passionate about productivity, quality, strong leadership, and intelligent decisions. Expert in Business Intelligence and Data Warehouse solutions. Primarily works with SAS 9 Enterprise Business Intelligence platform. Loving life and enjoying working for Zencos Consulting (http://www.zencos.com). Enjoy the knowledge!

Website SAS OLAP Cubes: Using PROC SQL and MDX to Query OLAP CubesTwitter SAS OLAP Cubes: Using PROC SQL and MDX to Query OLAP CubesGoogle Plus SAS OLAP Cubes: Using PROC SQL and MDX to Query OLAP CubesLinkedin SAS OLAP Cubes: Using PROC SQL and MDX to Query OLAP Cubes

If you enjoyed this post, make sure you get a free subscription to Notes on SAS BI Software Usage by Email. I respect your privacy!
Know someone who would like this post? Share it:
facebook SAS OLAP Cubes: Using PROC SQL and MDX to Query OLAP Cubes facebook   twitter SAS OLAP Cubes: Using PROC SQL and MDX to Query OLAP Cubes twitter   email SAS OLAP Cubes: Using PROC SQL and MDX to Query OLAP Cubes email   linkedin SAS OLAP Cubes: Using PROC SQL and MDX to Query OLAP Cubes linkedin   stumbleupon SAS OLAP Cubes: Using PROC SQL and MDX to Query OLAP Cubes stumbleupon  

Tags: , , , ,

This entry was posted on January 11, 2012 at 5:09 am and is filed under Building Business Intelligence, SAS OLAP Cubes. You can follow any responses to this entry through the RSS 2.0 feed.



SAS OLAP Cubes

Links

Archives

Pages

Categories

Resources


x
Loading...