Creating Time Dimension

Let's review a few key points about the time dimension that was automatically created for us.  Double click Calendar.dim under Dimensions in the Solution Explorer to review the dimension structure which shows the attributes and hierarchies.  Two hierarchies have been created with the levels as shown.  These hierarchies allow us to summarize and drill through the measures in the cube.

Click on Calendar (first entry under Attributes above) to display the dimension properties.  The Type property (in the Basic group in the Properties window) has the value of Time which designates it as a Time dimension.  If you click on the dropdown for the Type attribute, you will see that there are many other types of dimensions that have some sort of built-in support in SSAS.

For an individual attribute, there are several properties that must be set correctly as well.  As an example click on the Year attribute to view its pertinent properties.  The Type property must be set to Years.  The Name column specifies the column in the generated relational schema that is used as the display name for the attribute ; note that it is a WChar type which is a string.  The ValueColumn represents the underlying value of the attribute and it is a Date type.  You can browse the other attributes and note their property settings.  A key advantage to generating the time dimension in the Cube Wizard is that all of the important properties get set correctly for you automatically.

Click on the Browser tab for Calendar.dim to view the actual data in the dimension.  This step shows the hierarchies in the time dimension, based on the time periods selected in the Cube Wizard.  The hierarchy  shown below allows us to query at the year, quarter, and month levels. 

The prerequisites for adding Time Intelligence are a time dimension with one or more hierarchies that is linked to one or more measures.

Click on the Available time calculations that you want to add to the cube.  A brief description of each is displayed as you click or scroll through the list. 

Select one or more measures to implement the calculations you selected in the previous dialog.  This is the final step in the wizard.

The Business Intelligence Wizard creates a number of calculated members in the cube which you can view on the Calculations tab of the cube.  The wizard creates an additional hierarchy in the Calendar dimension.  The hierarchy name is derived from the hierarchy that you pick in the Choose Target Hierarchy and Calculations step of the wizard.  In this example the hierarchy name is Year - Quarter - Month - Date Calendar Calculations.  Right click the MSSQLTips cube in the Solution Explorer and select Process to update the cube with the Time Intelligence calculations.
Our final step is to browse the cube and take advantage of the Time Intelligence to perform some queries.  Double click the MSSQLTips cube in the Solution Explorer to display the cube designer then click the Browser tab.  The Browser tab user interface is essentially the pivot table that you are familiar with from Excel or the Office Web Components.  Note the instructions on where to drop column fields to browse the cube.

Perform the following steps to see an example of the Time Intelligence calculations:

• Drag and drop Sales and SalesCost from Measures onto the Drop Totals or Detail Fields here area
• Drag and drop Year from the Calendar dimension onto the Drop Row Fields here area
• Drag and drop Year - Quarter - Month - Date Calendar Calculations from the Calendar dimension onto the Drop Column Fields here area
• Click Year - Quarter - Month - Date Calendar Calculations in the Drop Column Fields here area and click Current Calendar, Year over Year Growth % and Year Over Year Growth
• Click Year on the Drop Row Fields here area and click the years 2006, 2007 and 2008

You should now have a pivot table that looks like this:

The above pivot table shows Sales and SalesCost for the years 2006 through 2008, as well as the Year Over Year Growth % and Year Over Year Growth (in dollars).