BI Tools

Tips and tricks for building information maps, OLAP cubes, reports, and dashboards

BI Admin

Learn your way around a SAS BI installation.

Visual Analytics

Learn your way around the SAS Visual Analytics tool

Coding & Data

Extract, transform, and load your data into the SAS BI toolset

Stored Processes

Create and design stored processes like a rock star

Home » Enterprise Guide

Best Practices: Letting SAS Enterprise Guide Write MDX Code

Submitted by on 2012-01-04 – 8:00 AM

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:

Creating a New Custom Measure

Create a time series measure:

First Step of Creating a Time Series Measures

Enter the parameters:

Parameters for Rolling 3 Month Measure

Specify the format:

Formatting the Custom Measure

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

Editing OLAP Cube with MDX Editor

This is the output you should see in the MDX Editor:

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 for custom members and sets.

On a more general note, if you want to learn MDX, you can use SAS Enterprise Guide to learn basic MDX syntax by manipulating a cube and viewing the MDX code generated.  If you need some help getting started with OLAP – in the To Write or not Write OLAP Code  post I talk more about writing OLAP cubes.

Never miss a BI Notes post!

Click here for free subscription. Once you subscribe you'll be asked to confirm your subscription through your email account. You email address is kept private and you can unsubscribe anytime.
The following two tabs change content below.
Spread the love


No Comment »

2 Pingbacks »