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 » BI Tools

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

Submitted by on 2011-12-07 – 6:43 AM

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

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 Expression Editor

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 olap cube does not show member sets

SAS Information Map Studio – Missing the Member Sets

 

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

Learn all about SAS BI

You can learn more about SAS Business Intelligence from the "SAS BI Bible." Take a peek inside the Building Business Intelligience with SAS book.

 

The following two tabs change content below.

Tags: , ,