SAS OLAP Cubes: Using PROC SQL and MDX to Query OLAP Cubes
Submitted by Steve Overton on 2012-01-11 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.