SAS OLAP Cubes: Using PROC SQL and MDX to Query OLAP Cubes
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
Thoughts and Considerations
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…