Building OLAP Date Dimensions
Posted by Kevin S. Goff on 04/24/08 | Business Intelligence
Welcome to I-Data Weekly, a technical blog dedicated to Business Intelligence with the Microsoft SQL Server family of products. I-Data stands for Intelligent Data, reflecting the goal of presenting the type of content you'll find in SetFocus' Master's Program - Business Intelligence Track .
Each week, we'll present a different technical piece from our Business Intelligence Courseware - covering items such as data patterns with SSIS packages, building hierarchical OLAP cube dimensions, custom MDX code, advanced reporting with SQL Server Reporting Services, dashboard construction with PerformancePoint Server, and SharePoint integration. As Microsoft is scheduling SQL Server 2008 for release later this year, we'll also include some coverage of the new BI features that SQL 2008 has to offer.
This week's installment of I-Data Weekly focuses on building a custom OLAP Date Dimension. Virtually all businesses need to summarize data by date range - therefore, it's critical to represent a company's business calendar in the database system. You need to have a date system in place, to be able to query and summarize data for important date groupings. To do this, we'll take a look at the following:
- Defining our end result: an OLAP date dimension hierarchy
- Starting off by creating a base Date Calendar of date definitions in a SQL Server table
- Creating an SSAS project that uses the Date Calendar table as a Data Source View, and adds some additional definitions to the Calendar
- Building the OLAP Date Dimension, and defining some properties for the dimension
- Writing some queries against an OLAP sales database to summarize data by month, quarter, etc.
1 - Defining the end result: an OLAP Date Dimension Hierarchy
The SetFocus BI training methodology involves starting at the end, to visualize the final result, and then working backwards (step by step) to build the results.
Our goal here is to build an OLAP date dimension hierarchy that will allow us to do the following:
- Summarize sales data by Year, by Quarter, by Month, or by individual day
- Establish a fiscal calendar, where sales months may be different than calendar months (for instance, the last few days of March might actually be part of a business month for April)
- Build definitions for Month Names and Quarter Names so that you can sort them chronologically on something other than the display name (for instance, display months as "Jan 2006", "Feb 2006", etc. while internally storing the key values as sortable values, such as "200601", "200602", etc.)
- Establish a Date Hierarchy, so that you can query and return sales data in a tree-like format (Year, Quarter, Month, etc.)
Here is our "end result", a Date dimension called Date Calendar, with dimension attributes for Year (BzYear), Quarter (BzQuarter), Month (BzMonth), and Actual Date (Full Date). The Dimension also contains a hierarchy called Bz Calendar Tree, for drilldown capabilities.
(Special note: many of these images have been resized - if you want to see the entire original image, simply right-click on the image and take the "Save Picture As..." option.)
2 - Creating a base Date Calendar of Date Definitions
So how do we go about building such a Dimension? There are several steps, but fortunately each step isn't that difficult. The very first thing you need to do is establish a database table that contains all calendar dates, along with the associated Business Month, Business Quarter, and Business Year for each date. Here we have a table called DateCalendar, with some example entries:
While the example above is a very simple one (each Business Month/Quarter/Year is based on the calendar value), you could just as easily define the entries based on the company business month/quarter/year. Additionally, you might have two sets of date attributes, one for the actual calendar dates and one for the business dates.
You can build this calendar with date values going back to your business' "beginning of time", and stretch it out as far in advance as you wish.
The next step is to read this data into Analysis Services, so that we can build an OLAP dimension.
3 - Create an SSAS project that uses the Date Calendar table as a Data Source View, and adds some additional definitions to the Calendar
Your next step would be to create a SQL Server 2005 Analysis Services project that uses the database (to which the calendar belongs) as the data source.
Once you create a data source view and include the DateCalendar table from the first step, you'll want to include some additional calculated expression columns to represent the "English-like" display for month and quarter. Let's assume you've agreed that you want to show months in the form "MMM YY", and you want to show quarters in the form "Q1 YY". So you'll need to add two columns expressions for those. Additionally, you'll want to include two expressions to drive the sort order for the Month and Quarter, since we can't directly sort on the display values (at least not if we want the sort order to be correct!). So that means we need to add a total of four column expressions.
Our "end goal" for this step is the following two images, which show the DateCalendar table in an SSAS Data Source view, along with the four new columns (our new display columns, MonthDesc and QuarterDesc, along with their sort values, MonthValue and QuarterValue).
To create the actual calculated expressions, you can right-click on the table in the Data Source View, and take the option to add a new calculated expression. For example, the expression for the Month Description would use the T-SQL SUBSTRING, DATENAME, AND CAST functions to build a display string.
Similarly, you can use T-SQL to create the calculated expression for MonthValue (which we'll use as the sort key for Month Desc).
One final note: you could just as easily have built these four calculated expressions as part of the root DateCalendar table.
4 - Build the OLAP Date Dimension, and define some properties for the dimension
After establishing all of the columns in the Data Source view (the base date and numeric month/quarter/year columns) and the calculated display expressions, you can then turn your attention towards building the Date Calendar Dimension.
The next figure represents the SSAS 2005 Date Dimension designer. Note from the figure that not all of the columns from the Data Source View (on the right) appear as attributes (on the left). The attributes represent the columns that you'll use to "slice and dice" the sales data (in this case, by Year, Quarter, or Month). While the calculated columns for MonthValue and QuarterValue don't appear as attributes, we still need to use them as the actual key values for their description counterparts. As you can see in the figure below, you can right-click on an attribute (e.g. Month Desc), pull up the property sheet, and set the Key Column to MonthValue. (You'll then repeat the process for the QuarterDesc/QuarterValue combination).
Finally, to create the hierarchy, you can drag the dimension attributes into the hierarchy design area. Also note that we've renamed the attribute columns (this is optional, and simply based on your naming convention preferences).
Note: The SetFocus BI Master's Program devotes an entire week to SQL Server 2005 Analysis Services, including dimension/hierarchy design.
5 - Writing some queries against an OLAP sales database to summarize data by month, quarter, etc.
After you've processed the OLAP cube, you can start writing MDX queries to summarize data by any of the dimension attributes in the Date Calendar. For example, here's a query to sort dollar sales by BzMonth, in descending order.
Note that the query uses [BzMonth].CurrentMember.Properties('Key') for the second parameter of the Sort function. This is necessary because we established the key property for BzMonth as the calculated expression for MonthValue.
Similarly, if you wanted to sort the sales dollars by Quarter in descending order, you'd use the following query:
Finally, if you want to use the hierarchy to display sales in hierarchy order (Year, Quarter, Month, Date), you can use the MDX DESCENDANTS function, as follows:
Note: The SetFocus BI Master's Program devotes an entire week to MDX Programming.
Next week: One of the most powerful tools in the Microsoft BI stack is SQL Server 2005 Integration Services. SSIS 2005 is a complete replacement for DTS and provides tremendous functionality for ETL (extract, transformation, and load) operations. SSIS 2005 is so powerful, that new users often need time to grasp just how powerful SSIS tasks can be. Next week we'll take our first look at how we can use SSIS 2005 to perform ETL processing.
Final thoughts:
This week's article provided a basic walkthrough for creating a date calendar dimension and hierarchy, along with a few MDX examples for summarizing data by date dimension attributes. We could have done other things as well, such as implementing multiple sets of dates, adding additional attributes (week number, half-year, etc.). Additionally, we could have added other attributes to account for seasonality , as some companies perform historical trend analysis based on events that occur on different dates every year (e.g. Easter). Finally, we could have implemented some MDX named sets, to retrieve information such as the last 12 months or last 4 quarters of available data. We'll explore those at a later date.
About the author: