By: Ray Barley | Comments (23) | Related: > Analysis Services Dimensions
Problem
We are building an Analysis Services cube and trying to understand how to create a time dimension and take advantage of the built-in time intelligence features that provide many useful calculations such as period-to-date, period-over-period growth, etc. Can you provide a detailed explanation of how it all works?
Solution
Getting the time dimension configured correctly is a necessary first step in order to take advantage of the Time Intelligence features in SSAS which support the various queries that you mention. Just about every data warehouse and cube has a time dimension which represents the hierarchies that you use to filter your queries such as year, month and quarter. The dimension names time, date, calendar, etc. are generally used interchangeably. My naming preference is calendar because that's really what you're modeling in most cases and the name easily supports multiple calendars like fiscal year, reporting year, etc.
Probably the best way to understand how the SSAS built-in features for the time dimension work is to walk through an example. When you create an SSAS cube you have the option of building it from an existing data source or without using a data source. In the latter case the SSAS Cube Wizard can generate the relational schema that you need to populate the cube. This option comes in handy when you're trying to design your cube as the to-be solution; i.e. this is what I want versus creating a cube from an existing data source; i.e. this is what I have and let's use it to create a cube. Another advantage of creating the cube without a data source is that the important properties for configuring the time dimension get set for you automatically.
Let's walk through the steps to build a cube that uses the built-in SSAS features for the time dimension and associated time intelligence. The starting point is to create an SSAS project in Business Intelligence Developer Studio (BIDS) or Visual Studio.
After creating the SSAS project, right click on Cubes in the Solution Explorer and select New Cube to launch the Cube Wizard. On the Select Build Method dialog select Build the cube without using a data source. This allows us to create a cube and generate the relational schema that will be used to populate the cube.
Define a couple of measures. Measures are typically the dollar amounts that are monitored to determine how your business is performing. When the relational schema is generated, a table is created for each Measure Group; a column in the table is created for each Measure Name.
The Define New Dimensions dialog allows us to define dimensions for the cube. Dimensions are the attributes that we use to slice and dice the data. For our example we'll just create a time dimension. Check the Time type and enter the name Calendar. When the relational schema is generated, a table called Calendar will be created.
After selecting a Time-type dimension, you define the date range and the time periods. When the relational schema is generated, the time dimension will be populated automatically based on the selections below. The time periods allow us to perform queries that filter the measures by year, quarter, month, week or day. You also have the option to define a Fiscal Calendar, Reporting Calendar, etc. (this step is skipped in our example).
The Define Dimension Usage dialog allows us to link the measures to the respective dimensions. When the relational schema is generated, there will be a foreign key in each measure group table enabling a join to the dimension table.
The final dialog in the Cube Wizard summarizes the selections made and allows you to name the cube. Click the Generate schema now checkbox; this generates the relational schema that will be used to populate the cube.
When you check Generate schema now the Schema Generation Wizard is launched to guide you through the steps to generate the relational schema. The Specify Target dialog is used to identify the database where the relational schema will be generated. The Data source identifies the database; the Data source view is used to specify the tables, views, etc. from one of more data sources that will be used to populate the cube. The cube is always built from the data source view. Prior to this step you may want to create a new database to use as the data source.
For the remainder of the steps in the Schema Generation Wizard just accept the defaults. After the schema is generated, the cube needs to be processed. Right click on the cube in the Solution Explorer and select Process.
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.
At this point we have created a cube with a properly configured time dimension. The next step is to add the built-in Time Intelligence which allows us to specify the types of time slicing we want to use in our queries. Click on the MSSQLTips cube in Solution Explorer then click Cube on the top-level menu and Add Business Intelligence to launch the Business Intelligence Wizard. Select Define time intelligence from the list of available enhancements. 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). The sample data used to populate the cube was a simple query that extracts data from the sample AdventureWorksDW database that comes with SQL Server 2005.
Next Steps
- Download a copy of the sample SSAS project here to experiment with the built-in Time Intelligence features in SSAS. Note that the project assumes you have an instance of SQL Server and Analysis Services running locally. Unzip the files into the folder "C:\MSSQLTips" to minimize changes to get the sample to run and follow the instructions in the README.txt file.
- While the Time Intelligence Wizard provides many calculation options, you may come up with others and you will have to implement these yourself. You can follow the pattern of the calculations created by the built-in Time Intelligence to do this.
- Before you run the Time Intelligence Wizard make sure that you have all of your measures defined in the cube. Each time you run the wizard it creates a whole new hierarchy in the time dimension for the calculations. You can manually edit the generated calculations from the Calculations tab in the cube designer if necessary.
About the author
Ray Barley is a Principal Architect at IT Resource Partners and a MSSQLTips.com BI Expert.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
