Hi Friends,
This is a second part in the series “SQL Server Analysis Services Time Dimension Consideration”. In the Part 1, we have briefly discussed on the two types of the Time Dimension Structures i.e. Regular time dimension and Server time dimension. We have also started with the first out of the three creation methods which is “Use an existing table”. In this post we will be discussing on the next options which enables generating a time table if it is not already available in the data warehouse.
Generate a time table – When no time table is available in the source database; Dimension Wizard in BIDS provides an option to create a time dimension using one of the following options.
- Generate a time table in the data source – This option creates a time dimension table in the underlying data source and populates the same with data based on the inputs provided. Off course this option requires to have “Create Object” permission in the underlying data source. The wizard then creates the time dimension from this time table.
Steps
- Define Time Periods – Here we have to define the data our time dimension should hold. Specify the first date, last date, first day of the week and the time periods the hierarchy should include.
- Select Calendars – This will define the hierarchies to be created for the time dimension. Here I have selected Regular and Fiscal calendar. In addition to this, Reporting, Manufacturing and ISO 8601 calendars are also available.
- Completing the Wizard – Review the attributes and Name the dimension. Notice the “Generate schema now” option, which is selected. This initiates the “Schema Generation Wizard”.
- Specify Target (Schema Generation Wizard) – Specify here the new Data Source View (DSV) to be created or select an existing DSV.
- Subject area Database Schema Options – This screen provides various schema generation options like, Create Primary Keys on the table, Create Indexes, Enforce Referential Integrity, and Preserve Data on Generation. You also have an option to populate the table with data at this stage.
- Specify Naming Convention – Available options can be seen in the below screenshot.
- Completing the Wizard – Review the settings and finally the time dimension gets created. Query the underlying database of the specified data source and you will find the table Time with the data populated.
Generate a time table on the server – This option generates and stores the time dimension directly on the “Analysis Server” instead of in the data source and hence known as “Server Time Dimension”. This eliminates the need to create and maintain a separate time table in the Data Warehouse database. This option may be selected in case…
- The user creating the table does not have enough permission to create objects in the underlying data source.
- A real time cube is created based on operational database.
- The user does not want to alter the Data Warehouse and save the storage space and the maintenance efforts
- And So on …
Once created, the next step is defining the relationship between this dimension and the corresponding measure group in the Dimension Usage tab of the cube designer.
Steps
The Wizard steps for this option are same as the first three steps in the previous option except the “Generate Schema Now” in step 3. Once the solution is deployed, the dimension gets directly created in the deployed SSAS database.
Hope it’s now easy for you to build a time dimension with these available options. One thing I would like to mention here is the advantage of generating a time table through Dimension Wizard. The time dimension thus created is preconfigured with correct properties which enables it to take the advantage of the built in time intelligence in SSAS. This built in time intelligence support queries like Period-to-date, Moving averages, Growth over a period of time etc.
Regards
Amit Karkhanis
Like us on FaceBook | Follow us on Twitter
Join the fastest growing SQL Server group on FaceBook
