Quick Tip for Using OLAP Cube Results in Your Stored Process
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.
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.
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!
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!
Latest posts by Tricia Aanderud (see all)
- Administration: What Demons Are at Your Command? - October 28, 2014
- SAS Visual Analytics: Keeping it Clean for Users - May 27, 2014
- Working with Large Prompt Population Lists - April 21, 2014
- Which population method should I use for my SAS prompt? - April 13, 2014
- SAS Office Analytics: Working with Your Inner SAS Programmer - March 12, 2014