- Creating a new dimension:
(We recommend that you have your Data Source created and ready
to go.)
To create a new dimension, right click the dimensions folder of your Analysis Services project and select "Add new dimension" as shown in the diagram below.
Figure: New Dimension.
-
Selecting the Time By Day template:
We are aware of at least 3 different ways to create the Time
Dimension Table, but this is by far the quickest and the easiest.
You can also opt to not use a template. This gives you access to
creating a Server-Time table which is not actually added to your
Database.
Figure: Select the Time By Day template.
-
Definining Time Periods:
Select the time periods that you want your Time Dimension table
to be populated between and don't be afraid to change the first day of the week to Monday. Also, check
any Time Periods that you want to be able to use later on in your cube (think
Reporting).
Figure: Define Time Periods.
Selecting Calendars::
Select any calendars you may wish to use in your cube, IE. if you want to analyze data by Year, then tick Fiscal calendar.
If you do select Fiscal calendar, make sure you change the Start
Month to July.
Figure: Choosing your calendars.
-
Finishing Up the Dimension
This is where we name our dimension. As you can see, the wizard automatically calls your dimension "Time", but be careful, because this will be the name of your table when we add it to your database. The preferred name
for a Time Dimension table should be "DimTime". Also, before you
click finish, ensure that "Generate schema now" is ticked so that we
can get right into adding and populating the Time Dimension table to
the database.
Figure: Finishing Up the Dimension.
-
Specifying Target:
This is where we choose which Data Source to add our Time Dimension table to. Select your pre-existing data source and create a new Data Source View (DSV) or select your pre-existing DSV and the wizard will add the Time Dim table to it at the end.
Figure: Specify Target.
-
Selecting Schema Options:
Name your owning schema and select any options you wish for your Time Dimension table. Keep in mind that the Owning Schema will prefix your Table name in your database. In our example,
the table we are adding to the database will be called
[DimTimeDemo].[Time].
Also, be aware that you can choose to populate the table, leave it empty, or populate empty rows only. The last option could be useful in updating your table if you exceed the dates set forth when we created our Time dimension earlier.
Figure: Schema Options.
-
Specifying Naming Conventions:
On this screen we can select the naming conventions for our columns. I think it is pretty straight forward.
After we click next, the wizard will have all the information needed
to generate and populate the Time Dimension table.
Figure: Specify Naming Conventions.
-
Generating the Data:
Here we see the wizard generate the schema and populate the table
with the date data. If something is going to go wrong, this is the
place where it will happen; however, as you can see in the picture
below, the "Generation Completed Successfully."
Figure: Generating the Data
-
And we are good to go...
Well, this isn't really a step, but 10 sounds like a nice round number, and I get to show off the data that we generated in the picture below.
Happy Time Dimension Generation!
Figure: The finished product.