If you have never worked with OLAP code – then you might be curious how it works and what it looks like. Some developers use SAS OLAP Cube Studio to start their cube building process since it saves some of the typing required. Let’s review some of the code. [Adv Users: check out Steve’s posts about OLAP Code] Exporting PROC OLAP Code There are two methods to export your cube to a SAS program from SAS OLAP Cube Studio. Use Method 1 after you have added custom measurements. Click cube name > Export Code. The Export Code window appears. Use Method 2 after you have completed the Cube Designer wizard. Click the Export Code button. The Export Code window appears. From the Export Code window you can select if you want standard (long) OLAP procedure code or the shorten code. Either way – just add the path and program name. [More at SAS Support about when to use short/long form. ] Under the Hood After opening the BUILD_GA_BLOGSITE.sas program in Enterprise Guide, you see how the OLAP procedure code appears. You can learn to code your own cubes, just by reviewing how the OLAP wizard wrote the code. For instance, […]
Tag: OLAP
Excel and OLAP Viewer: Not enough room for the Cube!
When you use the OLAP Viewer in Microsoft Excel you may have run into the window borders – literally. Your cube and analysis needs more space! Scroll, Scroll, and Scroll to See the Cube Data When you open the OLAP cube in the MS Excel OLAP Viewer the default cube size dictates the area used. You may get 20 rows by 20 columns or you might get 2 rows by 2 columns. So when you want to do some analysis, you expand a dimension and scroll, scroll this way and then scroll, scroll that way to see the coveted data! We need space to do proper analysis!! Oh have the data gods forsaken us?!? Answer to Your OLAP Prayers Obviously I would not bring up this point if I did not have an answer. So here’s how you work yourself out of this tight spot. Trick is to turn on the MS Excel Developer Ribbon. It may already be turned on – for instance, in the figure below you can see the Developer menu between the View and Excellent Analytics menus in my MS Excel menu. If you have the Developer menu – then skip to the next step. Otherwise, […]
To be or not to be, testing your cube’s existence
Unlike BASE SAS tables, OLAP cubes must exist in within the metadata in order to access from any of the OLAP Viewers. In addition to having some metadata OLAP cubes have a physical file structure presence (at least for MOLAP/HOLAP because it’s a differ…
Skipping through OLAP to find your value
One of the cool things with SAS OLAP Viewer in Add-in to Microsoft Office is your ability to skip right into a specific value. Out of the box, viewing OLAP cubes can lead you to believe that in order to view a specific value you need to click-thru a hi…
Best practices for refreshing production OLAP cubes
Unlike prior versions of SAS OLAP technology, 9.2 provides more options for maintaining and refreshing OLAP cubes. With this comes some discussion about what each one does alone, and how pairing these techniques really provides SAS OLAP Server Administ…
SAS OLAP Cubes: Using PROC SQL and MDX to Query OLAP Cubes
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…
[[ This is a content summary only. Visit my webs…
Best Practices: Letting SAS Enterprise Guide Write MDX Code
SAS Enterprise Guide is a VERY useful client tool for SAS development and data analysis. One the best features is the ability to see the SAS code generated from different tasks. In addition to viewing the Base SAS code used to perform different tasks, you can also view MDX code written to create certain measures, members, and sets in OLAP Cubes. Adding Simple Measures from SAS Enterprise Guide Let’s start by creating a simple measure for a Rolling 3 Months of Sales using my SGF2011 cube: Create a time series measure: Enter the parameters: Specify the format: The next screen summarizes the parameters entered into this wizard. You can see the MDX written here as well. Click ‘Finish’ to define the measure. You should see the measure in your cube output. Right click anywhere on the grid output to ‘Edit View’ > ‘Edit with MDX Editor’. This is the output you should see in the MDX Editor: The measure can then be defined in the cube globally by putting the MDX code in the SAS program which builds the cube (if applicable) or through SAS OLAP Cube Studio. This same method can be used to view the MDX code generated […]
OLAP users can fall into the abyss of detail data
OLAP cube developers might have noticed that the amount of time required to generate any aggregation (including the NWAY) can negatively impact their productivity. Developers remove the NWAY aggregation (following the sample screens in a prior post) to…