Home » BI Tools, Stored Processes

Quick Tip for Using OLAP Cube Results in Your Stored Process

Submitted by 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. [Here’s the video presentation.]

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”.

  1. Open the cube and create the view of the data that you want.
  2. Select the Slicer icon from the tool bar.  
  3. 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

Stored processes with OLAP Cubes Slicer Window

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. 

Stored processes with OLAP Cubes

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!

Stored processes with OLAP Cubes 4

 


Learn More about SAS Stored Processes and Prompts

You can learn more tips and tricks for creating, debugging, and using SAS stored processes in the 50 Keys to Learning SAS Stored Processes book. It's a complete guide to SAS stored processes. Check Amazon for best pricing and quick shipping!

The following two tabs change content below.
Tricia Aanderud is a SAS Business Intelligence and Visual Analytics consultant based in Raleigh, NC. She has written several books about SAS, presented papers at many SAS conferences, and has over 10 years of SAS programming experience. Contact her for assistance with your next project.

Tags: ,

2 Comments »

  • Good points Don. I have to agree I’m not the world’s biggest OLAP fan and I find MDX even less appealing. (Sorry Steve!) So I was excited to see that SAS EG would do some of the work for you.

    Obviously this was a simplified example. My real thought with this method is that you could then use the data with the BI Dashboard. You can also use a cube with an information map but sometimes you don’t need that overhead.

    Hope to see you at the SAS Global Forum – are you presenting any papers?

  • Nice tip about using EG to generate the MDX code. But frankly I don’t think this is a reasonable approach in general.

    Permit me to first admit that I am not a fan of OLAP cubes. But when I do use them my design approach is to first create and save an NWAY summary table with PROC SUMMARY and use that data to populate the cube.

    Historically, PROC SUMMARY did that initial aggregation orders of magnitude faster than PROC OLAP (something that SAS may have improved in PROC OLAP) and so create the cube ran much faster.

    And, since the same base NWAY aggregates exist is a SAS data file, those data can be easily used in any of the SAS PROCS – including, for example, the example in this blog posting.