Use an OLAP Cube with Your SAS Stored Process

Quick Tip for Using OLAP Cube Results in Your Stored Process

Submitted by Tricia Aanderud on March 4, 2013 – 7:12 am 2 Comments

Sometimes the data you want for your stored process is already neatly located and summarized – but it is in an OLAP cube. While you can probably recreate the data with some of your fancy coding, it does take time, testing and later creates maintenance issues. If the data you want is already available in the OLAP cube, why not just use it? Oh you say you don’t know any MDX, I don’t either and it’s why I always turn to SAS Enterprise Guide for help.

Angela and I will be presenting a paper called “Stop Your Wine-ing – Use a Stored Process” on advanced stored process techniques at the SAS Global Forum this year. [Register early - it will be more fun than a barrel of monkeys!]

Anyway – one of the techniques we plan to show is using OLAP cube data with your stored process for quick results. We will walk through you the entire process for pulling the data from the cube and some other cool tricks. In this blog post, I’ll offer a quick overview of the process.

Use SAS Enterprise Guide to Create a Slice

SAS Enterprise Guide makes is easy to get a slice of the cube that you can use in your stored process. For my report, I just want to show the “Average Ratings” for the Napa Valley Cabernet Sauvignon wines. SAS Enterprise Guide allows you to create a slice that is “stored process ready”.

Open the cube and create the view of the data that you want.
Select the Slicer icon from the tool bar.
In the Create Slice window you can set the options for the slice. For Slice Type, select All rows except measures (stored process compatible).
Note: You may also want to change the Output Table to something more friendly, such as OLAP2STP

SAS Enterprise Guide places the slice in the Process Flow under the cube name. Here’s the dataset that was output.

Stored processes with OLAP Cubes

Examine the code to make sure the ”Connect to OLAP” code is present. This statement allows the stored process to connect to the OLAP server and get your precious data. This is the code you need to use in your stored process. Wasn’t that painless – no MDX required!

Always check with your SAS administrator to see if you need more information, such as user ID and password to connect to the OLAP cube.

Write Your Epic Stored Process

With your new data set and code – you can create the mostest bestest stored process ever conceived! Copy the code into your stored process and use the data set, which I called OLAP2STP, as you please. For this example, I am just using a PROC PRINT to display the results. Thanks SAS Enterprise Guide for making that super easy!