Sunday, December 09, 2012

How to deal with Time Dimension in SQL Server 2008,2012 Business Intelligence

Here I'm not going to learn how to create a Time Dimension in business intelligence 2008 and 2012 as you may know it well or it could be found on the net, however there are some consideration in order to create the Time Dimension.
The key difference is that in one approach it will never show you the  Time Dimension during that wizard and you have to create it later by manual.

1. In first approach, we have already installed (or restored) our data mart database using SQL Server Management Studio, so we would create a cube at top of data mart. In this case we do the following steps briefly:
a. Using SSDT (in SQL Server 2012) or BIDS (in SQL Server 2008) to create a new Analysis Services project.
b. Create a new Data Source.
c. Create a new Data Source View.
d. Create a new Cube:
in this step while creating a new Cube, you asked to define the measures and dimensions except Time Dimension. Then we have to create our Time Dimension manually.

2. In second approach, we do not have the data mart yet, instead we are going to create our data mart using business intelligence tools. In this case we do the following steps briefly: 
a. Using SSDT (in SQL 2012) or BIDS (in SQL 2008) to create a new Analysis Services project.
b. Create a new cube:
in this step while creating a new Cube, you asked to define the Time Dimension along with the other measures and dimensions as well as at the end of the wizard you have to check the "Generate Schema" to create the data mart followed by the measures and dimensions which you have defined during that wizard as the schema. In this case we will asked to define a Data Source and Data Source View during the data mart creation (after click "Finish", it will open a new wizard page) and only if we did check the "Generate Schema".

As you may be noticed, in the second approach we followed the opposite direction in compare to the first approach, as well as we create the data mart on demand but asked for Time Dimension, in contrast at the second approach we have to create the Time Dimension manually but already have the data mart.

No comments:

There was an error in this gadget