SAS OLAP Cubes: Tailing Time-Based Data for Dynamic Results

SAS OLAP Cubes are a great way to quickly aggregate and summarize data that can be used in the other SAS Business Intelligence tools.  Learn more about creating relative time based dimensions today.

Adding Relative Time Dimensions to OLAP Cubes

Dimensions and measures are the two major components of a cube.  To effectively report time-based data from a cube, it is beneficial to report measures using relative time dimensions.  SAS Information Maps provide this functionality but in some situations a relative time set makes more sense defined in the cube itself.  Defining custom time sets directly in the cube provides a centralized location for key slices of data so that SAS Enterprise Guide or SAS Add-In for Microsoft Excel users can quickly report on relative time such as the past 12 months or the past 4 weeks without having to drill or manipulate the cube.  These same custom time members can be used in a SAS information maps as well.

 Here’s the MDX code:

DEFINE SET ‘[SGF].[Last 6 Quarters]‘ as “Tail([Time].[YQM].[Quarter].AllMembers ,6)”;

DEFINE SET ‘[SGF].[Rolling 6 Months]‘ as “Tail([Time].[YM].[MONTH].AllMembers ,6)”;

DEFINE SET ‘[SGF].[Rolling 13 Months]‘ as “Tail([Time].[YM].[MONTH].AllMembers ,13)”;

 Here’s how the output appears in SAS Enterprise Guide 4.3 (latest date in this example data is October 2002):

SAS Enterprise Guide - OLAP Cube Output

 Using the Member in SAS Information Map Studio

To reference a custom member set in Information Map Studio, create a new data item.  Then add the member set name in the Expression Text field:

SAS Information Map - Add a New Data Item

 Note: Information Map Studio 4.3 and below do not list member sets in OLAP cubes like Enterprise Guide:

SAS Information Map Studio - Missing the Member Sets

 For source code which demonstrates this and other OLAP techniques: http://www.stephenoverton.net/SASCode/SGF2011

Wait There’s More

In the upcoming Building Business Intelligence with SAS book, you can learn more about using SAS OLAP Cube Studio, SAS Stored Process, and even building dashboards using the SAS BI Dashboard. Sign up to have SAS Press send you an email when it’s available! 

Steve Overton  (12 Posts)

Passionate about productivity, quality, strong leadership, and intelligent decisions. Expert in Business Intelligence and Data Warehousing solutions. Primarily work with SAS 9 Enterprise Business Intelligence platform. Loving life and enjoying working for Zencos Consulting (http://www.zencos.com). Enjoy the knowledge!

Tags: Add-In for MS Office, Information Map Studio, mdx, olap, OLAP Cube Studio

This entry was posted on December 7, 2011 at 6:43 am and is filed under Building Business Intelligence, SAS OLAP Cubes. You can follow any responses to this entry through the RSS 2.0 feed.