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):
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:
Note: Information Map Studio 4.3 and below do not list member sets in OLAP cubes like Enterprise Guide:
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.
Latest posts by Steve Overton (see all)
- BulkLoading with PostgreSQL available in SAS 9.4 - 2013-10-22
- Everything Still Starts and Ends with Data in SAS Visual Analytics - 2013-07-30
- Describe Your Table in SAS to Write the SQL Code - 2013-05-22
- Update Table Metadata Using Base SAS Code - 2012-12-19
- SAS Administration: Fetch Data Faster across ODBC - 2012-11-01