Create a Time dimension with SSAS (DimTime) - 1st part

The dimension Time (DimTime) plays a very important role in a decision-making system, its presence is obligatory in the tables of facts which characterizes a decision-making system compared to a transactional system.

In the rest of this article, we will create the table DimTime using an SSAS project wizard.

  • At first, open BIDS and create a new Analysis Services project that you can name SSASTime.
  • Create a new data source, by entering the name of your SQL Server instance, as well as the name of the data warehouse.

Now that the data source is created, we will create a dimension DimTime in the Analysis Services project.

  • Create a new dimension.


  • A new assistant opens. In the window select the option Generate a time table in the data source.


  • Select the period that interests you: in our case, we have chosen a range from 1er in January 2013 31 December 2016.


  • Select the type of calendar supported by your Time axis.

In our case, we will need the regular calendar.

(Each service has its own calendar, for example the tax calendar can begin 1er September and finish the 31 August of the following year)


  • Check the option Generate schema now so that the wizard creates the structure and data and change the name of the new dimension to DimTime. Finally click on the Finish button.


  • A new wizard appears, click Next.
  • Select option Create a new data source view.


  • Select option Populate to generate the data from the table DimTime.


  • The wizard displays a setting screen for naming conventions. Specify the value none in the option Separator.


  • Click Finish to start the schema generation and close the wizard.

In SQL Server Management Studio, you can see that the table DimTime has just been added.


And you can check the structure of the table as well as the rows of this table.


In the 2th part of this article, we will make some improvements on our dimension.


Leave a comment

Leave a Reply

Your email address Will not be published.

This site uses Akismet to reduce spam. Learn how your comment is processed.