Creating a Time Dimension

In my last blog entry I explained how to add time intelligence to your PerformancePoint dashboard. That blog was based on the assumption that you already had a time dimension created in your SQL Server Analysis Services cube. In this blog entry I will go over the different options you have to adding this time dimension to your cube. In addition to this, I will provide some design tips with time dimensions in general, and tell you how your decisions made during the design phase can affect how you can use the time dimension in your dashboard. There are two options available for adding a time dimension: Creating the time dimension manually, or creating the time dimension automatically with Business Intelligence Development Studio (BIDS). While the second option looks a lot more tempting there are pros and cons to both.

As you will see, allowing BIDS to create the time dimension, while providing you with essentials, also adds a lot of fluff to the underlying time table. It adds a lot of seemingly redundant columns that may fall under the category of “too much information” for your situation, and what you end up with is a very bloated table. The other downside is that you have very little control over the naming conventions of the different subdivisions. For example, the default naming convention for a fiscal year is of the form “Fiscal Calendar <year>”. If this doesn’t suit your needs, your only option is to create a script that manually changes all of the values in your time table. So while doing this through BIDS automates the process, you have little to no customizability short of scripting the changes in the table after the fact. On the other hand, creating the time dimension using this method takes a matter of minutes as opposed to hours, so it is definitely the user-friendly option for people who want to get a time dimension up and running quickly.

In the first section, I will go over the process of designing the underlying time table yourself in Microsoft SQL Server. In the second section, I will go over the process of setting up the time dimension in BIDS after you’ve created the time table in your database. In the final section, I will briefly go over the wizard that BIDS provides for automatically creating the time dimension.

Designing a Business Intelligence Time Table

The basic idea of a business intelligence time table is to provide a row for every date within the time period that you want to cover on your dashboard. For example, if you have been collecting data since 2004, then you need to have a row in your time table for every day since 2004 plus additional rows for every day that you want to collect data for in the future. A good range for the above example would be to have a row for every day between January 1st, 2004 and December 31st, 2020. This range will be the window of time that you will be able see data for in your time-aware dashboard, so make it big enough so that you don’t have to extend it too often.

Besides having the actual dates in your time table you will want to have columns that will allow you to split the dates in to their respective months and years. The time intelligence in your dashboard is only as smart as the data you give it, so don’t be afraid to add as many subdivisions of time as possible. For example, if your company’s fiscal year doesn’t start on the same date as a normal calendar year then you can add columns to indicate which fiscal year the date is part of. The same goes for the quarters and semesters of fiscal years. A good minimum for subdivisions would be to have the date, followed by its month, followed by its year.

In order to have a useable time table it is highly recommended to have both a numerical key as well as a textual description for each of the subdivisions. By this I mean that in addition to having a column for the number of the month, we should have another column that gives the textual description of that month. For example, in the row for March 5th, 2010 we would have a column that indicates that this date lies in the 3rd month of the year, as well as another column that indicates that the month is called “March”. This example is shown in the image below.

Time Table Row

Having your time table setup like this will allow SQL Analysis Services to know what order to put your months in, and will also allow you to put a label on the month instead of just a simple integer.

Your time table can have very simple subdivisions (e.g. day, month, year, etc.) or it can have very complicated subdivisions (e.g. day, month, quarter, semester, fiscal year, etc.). It all depends on how you want to slice your data on your dashboard. An example of a complicated time table can be found in the Adventure Works database. The UML diagram for their table can be found below.

Time Table UML

As you can see their table even includes localization information for each of their subdivisions. Another thing to notice is that there is even a subdivision for weeks. This is a weird subdivision because there is no way to know whether your month or year subdivisions will start at the beginning of a week or somewhere in the middle of a week. This means that the subdivision for weeks will not fit in to a rigid calendar structure, and needs to be considered on its own. You’ll see this in the next step when we add the time dimension to the Analysis Services cube.

Creating the Time Dimension

The first thing you need to do in order to create your time dimension with BIDS is to add the time table to the data source view in your Business Intelligence project. To do this, you simply need to right-click the background of your data source view, click “Add/Remove Tables”, and move the time table over to the right side. Once you’ve done this, your next step should be to create some relationships between your time table and your fact tables. This is required for time intelligence to work at all with your dashboard, so make sure you perform this step. Any relationships that you create in your data source view will be carried over to your cube when we add the time dimension, so this is a good time to make decisions about how your facts relate to time.

Once all of your relationships are defined it is time to add the time dimension to your Business Intelligence project. To begin, right-click the “Dimensions” folder and click “New Dimension”. In the dialog that comes up, you need to choose to “Use an existing table” and then pick the time table that you added in the previous step. If you have your time table setup with a date key and a date description, then change the “Name Column” field to the date description column. Otherwise, the defaults should be OK. In the next step you will be asked to choose all of the attributes for your dimension. It is recommended that you leave out any description columns in this step, as they will be added later as name columns to their key counterpart. For example, if you have a “MonthNumber” column and a “MonthDescription” column, only choose the “MonthNumber” column to add at this point. You can also optionally indicate the type of data that each attribute holds. An example of how this window should look after everything is selected is shown in the image below.

Calendar Attribute Setup

The next step of the process is to pick the name columns for the “key” attributes that you picked in the previous step. In order to do this, you simply need to click on the attribute in the dimension designer and modify the “NameColumn” property to point to the column that contains the textual description of the key column. This name column will be the label displayed to the user when they browse your calendar hierarchy.

In a rigid calendar structure it is important that you define how the different subdivisions relate to each other. This can be accomplished by defining the proper key columns for your attributes as well as defining the attribute relationships in the time dimension. When defining the key columns of your attributes you need to define them in a way that they can be uniquely identified in your time table. For example, for the “Month” attribute, it is not enough to have your key defined by the month number. This is due to the fact that the month number repeats itself every year, so your “key” to the month is not actually unique. The solution to this problem would be to add the “Year” attribute as a secondary key. Since a particular month will only show up once per year, this key definition is satisfactory for uniquely identifying each month in the time table. Another example for a key definition is found in the image below. This is the key definition for the “Calendar Quarter” attribute. You could read this definition as follows: “The calendar quarter only comes around once per year”. Once you have defined a key as a collection of multiple columns, you will also need to set the name column for that attribute. This can simply be the source column of the attribute if you don’t have a description column defined for it. After this is done you are then ready to start creating the attribute relationships.

Key Columns

To define the attribute relationships you need to go to the “Attribute Relationships” tab of your time dimension. By default you will see all of the attributes in your time dimension related directly to the primary key of your time table. This should be changed to represent the natural hierarchy of a calendar. To add a relationship, simply right-click on the attribute that you want for the left side of the relationship and click “Add New Relationship”. Then, for the right side of the relationship, you will choose the attribute that represents the next subdivision in your calendar hierarchy. For example, if you’re adding a relationship to the Date attribute, then you would want to pick the Month attribute as the right side of the relationship. Once the two attributes are selected, change the relationship type from “flexible” to “rigid”. A rigid relationship is one that never changes throughout your time dimension. For example, by defining a rigid relationship between the “Month” attribute and the “Quarter” attribute we are saying that a month will always be a part of one quarter, not multiple quarters. In other words, January always falls completely under the first quarter of the year, never the second, third, or fourth quarters. A good way to do this would be to start with the “Date –> Month” relationship, followed by the “Month –> Quarter” relationship, followed by the “Quarter –> Semester” relationship, and so on. Once you’ve defined all of your attribute relationships your diagram should look something like the one below.

Attribute Relationships

The final step of creating your time dimension is to create the user-defined hierarchies. Creating these hierarchies is pretty self-explanatory. You simply need to drag and drop your attributes one-by-one in to logical hierarchies. As a rule of thumb you should always follow the same structure that you have in your attribute relationships. These hierarchies will be the source of all your time intelligence on your dashboard so make sure that they have enough levels to give the users complete flexibility when they hook up the hierarchies to time period filters on their dashboard. The image below shows an example of how your time dimension should look after everything is done.

Final Time Dimension

Using the Time Dimension Wizard

The more user-friendly way to create your time dimension is to use the built-in wizard in BIDS. Using the time dimension wizard is a pretty straight-forward process so I will simply go through each of the windows to describe what your options are in each step. To start the wizard, right-click the “Dimensions” folder and click “New Dimension”. In the window that comes up you have two options available to create your time dimension: “Generate a time table in the data source” and “Generate a time table on the server”. Choosing the former will generate the time table in your existing data source, and will also give you the option of generating the underlying table in your database. Choosing the latter will create a time dimension on the same server as your business intelligence project without touching your underlying data source. Once you have chosen a method, you can move on to the next step.

Subdivision Selection

In this step you need to define exactly how much time your time dimension will cover. As I mentioned before it is very important to pick a range that goes well beyond the present date of your data. This will ensure that the time dimension will stay valid for a long time after you’ve deployed your dashboard. On top of this you need to tell the wizard which time subdivisions you would like your time dimension to have. You should always pick ALL of the subdivisions that apply to your corporation’s calendar. But, as a bare minimum, you should pick the Date, Month, and Year subdivisions. Finally, in this step, you can choose the language that your calendar will be in. Once this is done you are ready to move on.

Calendar Selection

In the next step you have the option of choosing some additional calendars to create in your time dimension. Only add additional calendars as needed, otherwise you will simply clutter your table with unnecessary information. Once you’ve selected your additional calendars you can move on to the final step. In this final step you are simply asked to name your time dimension and to review the attributes and hierarchies that will be created. Additionally, if you chose to create your time dimension in the existing data source, check the “Generate schema now” checkbox to automatically add the time table to your backend database. Once everything is finished, you should have a time dimension that looks like the figure below.

Final Generated Time Dimension

As you can see, there are a lot of excess attributes that are created when automating the creation of your time dimension. So while this is not the cleanest way to do things, it is quick and it will work out-of-the-box.

Once you’ve created your time dimension (either manually or through the BIDS wizard), the last thing you need to do is to add the time dimension to your SQL Server Analysis Services cube. After this is done, you are ready to integrate time intelligence to your dashboard!

Useful Links

To learn how to use this time dimension in your PerformancePoint dashboard, please read my previous post:


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: