⚠️ This page has been archived

✅ New page with updated info: ssw.com.au

Home > Archive > SSW Standards > Rules > Creating a Time Dimension in 10 Easy Steps

Creating a Time Dimension in 10 Easy Steps

  1. 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.

  2. 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.

  3. 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.

  4. 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.

  5. 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.

  6. 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.

  7. 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.

  8. 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.

  9. 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

  10. 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.

Acknowledgements

Adam Cogan
Grant Paisley
Michael Mileos