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!

Tricia Aanderud is a SAS Business Intelligence and Visual Analytics consultant based in Raleigh, NC who works for Zencos Consulting. 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.

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.

Need SAS Admin Support – Hire Zencos

Join me in Dallas, Texas!

SAS Global Forum – I’m a Speaker!

3581 - Visualizing Your Big Data Whether you have a few variables to compare or billions of rows of data to explore, seeing the data in visual format can make all the difference in the insights you glean. More

3352 - Tactical Marketing with SAS® Visual Analytics Presented with Johann Pasion (89 Degrees) Marketers often face a cross-channel challenge in making sense of the behavior of web visitors who spend considerable time researching an item online, even putting the item in a wish list or checkout basket, but failing to follow up with an actual purchase online, instead opting to purchase the item in the store.
More

When working in SAS Enterprise Guide, you can access data from the metadata libraries. When you make changes to the datasets in the meta library, such as adding or modifying columns, or even deleted datasets, the metadata library does not have the data – it must be registered. Argh – so frustrating especially if you are use to working with the normal SAS libraries. However, you …

A nice feature of SAS Enterprise Guide is the “Files” folder. This provides direct access to the file system where the workspace server is running. You can take advantage of this by configuring a central location where users can store data, SAS code, CSV files, Excel files, or anything useful. If this is not configured it defaults to a user-specific area. In some cases this …

SAS Enterprise Guide allows you to change your output graphics device. The graphic device controls how your charts your look when used in web pages, PDFs, and RTFs. Here’s a quick tour of how each device displays the same chart. All of these are in SAS EG viewer and used the Statistical stylesheet.
Setting the Graphic Device in SAS Enterprise Guide
First, if you have never changed …

There are 2 ways to develop OLAP cubes in the SAS EBI platform: using SAS OLAP Cube Studio or by writing the PROC OLAP statement by hand and submitting the SAS program. There are pros and cons for both that I will go through in this post. Personally I prefer writing the PROC OLAP statement(s) myself but I will use SAS OLAP Cube Studio from …