Using Microsoft Analysis Services (MSAS) 2005, you can design a basic cube with relative ease by defining the data source(s) and data source views, then adding dimension and fact tables. Once basic structures like dimensions, facts and measures are in place, you may want to extend your application's analytical functionality by adding calculated members, named sets, key performance indicators and actions. This is where the art of cube design becomes more interesting.
Most data warehouses benefit from time-related dimensions; rarely do you see a business with no interest in comparing revenues, costs, sales or some other numeric value across a range of time periods. Most businesses need reports that contrast the performance of the current year with that of the previous year. Many also need quarter-to-quarter and month-to-month analyses. In manufacturing environments, it is common to find reports comparing a number of defective units from one shift to the next or perhaps even by hour. The bottom line is that in addition to creating the basic cube structure you will probably be required to add time-related calculations to your cubes.
In MSAS versions prior to 2005, you had to add such calculations by writing complex MDX formulas manually. FORTUNATELY, MSAS 2005 simplifies the process of adding time intelligence to your cubes. Let me show you how.
How to add time intelligence to cubes
For demonstration purposes let's open a cube using Business Intelligence Development Studio (BIDS); you will see the icon for adding business intelligence in the upper left corner, which looks like this:
Clicking this icon activates the Business Intelligence Wizard. Alternatively you can right-click on the cube in the Solution Explorer window and choose "Add Business Intelligence" to invoke the same wizard. After the introductory screen, the wizard allows you to choose the type of business intelligence enhancement you need for your cubes. Your choices include:
- Define time intelligence
- Define account intelligence
- Define dimension intelligence
- Specify a unary operator
- Create a custom member formula
- Specify attribute ordering
- Enable dimension write-back
- Define semi-additive behavior
- Define currency conversion
The wizard also supplies a brief description for each of these options. In this tip, I'll only discuss the time intelligence but you'll see how this wizard makes the cube architect's job considerably easier than before.
After you choose to add the time intelligence you must specify the date and time-related dimension and hierarchy to enhance. As I mentioned earlier, every data warehouse is likely to have a time dimension, but keep in mind that your warehouse could have multiple time dimensions.
For example, a retail application may have three time dimensions -- one for purchase date, one for billing date and another for shipping date. All of these dimensions could be based on a single-dimension table but represented as three different MSAS dimensions, each with a different number of hierarchies: The purchase date may include year, month, date and even hour. On the other hand, the billing date dimension hierarchy may include only the billing month and year because aggregating data by bill date or bill hour might not be practical. Also note that MSAS 2005 supports multiple hierarchies within a single dimension. Each hierarchy is essentially treated as a separate dimension as you will see from the example later in this tip.
Once you choose the appropriate hierarchy to enhance you can choose the type of time intelligence enhancements to add, as shown in the screenshot below:
Figure 1: Time intelligence enhancements
The wizard gives you a multitude of options for time-related analyses, including:
- Year to date
- Quarter to date
- Twelve months to date
- Twelve month moving average
- Six month moving average
- Three month moving average
- Year over year growth
- Year over year growth percentage
- Quarter over quarter growth
- Quarter over quarter growth percentage
- Month over month growth
- Month over month growth percentage
- Day over day growth
- Day over day growth percentage
These calculations will be sufficient for most of your time analyses, but what if you must use another calculation specific to your business in all of your cubes? For example, you may want to compare a number of defective units from one shift to the next. What if the default formulas for the built-in calculations don't meet your needs? You'll be glad to learn that all formulas are stored in an XML file that you can easily modify to include custom calculations that you can reuse for all future cube development efforts. You can also customize the built-in calculations by editing the same file. Time intelligence calculations are found in C:\Program Files\Microsoft SQL Server\90\Tools\Templates\olap\1033\TimeIntelligence.xml file. Keep in mind that editing this file will affect how the Business Intelligence Wizard writes MDX for your cubes, so be sure to back up this file before making any changes.
Once you have specified the desired time calculations, you also need to define the scope -- or the measures for which you want to add business intelligence. The new calculations will only be created for the measures you choose; for all other measures, the new calculations will have a value of "NA."
The final screen lets you review the MDX that the wizard is about to write for you. Once you click "Finish," your cube will have new time calculations similar to the following:
Note that the wizard created a new hierarchy within the [DimTime] time dimension called [Calendar DimTime Calculations]. This new hierarchy will have one member for each calculation you choose to add -- month to date, quarter to date and year to date. In addition, this hierarchy will have a member called "Current DimTime;" and as the name indicates, it references the current member of the time dimension.
You can edit the time calculations created by the wizard as needed under the calculations' tab within BIDS. Notice the usage of Aggregate, PeriodsToDate, CurrentMember and DefaultMember MDX functions. Be sure to understand what these functions do before you make any changes to the XML file or to any calculations within the MDX script.
Once you process the cube you can view the new calculations in cube browser within SQL Server Management Studio. You can also reference them in your MDX queries. Since the newly created hierarchy is treated as a separate dimension, you can retrieve the default hierarchy of the time dimension on rows and the calculations' hierarchy on columns with the query shown below:
