Using Analysis Services to
Auto-create and Populate a
Date Dimension in the Relational Database
The goal with this exercise is to use the BI wizard in Analysis Services to do the heavy lifting in creating and populating a Date dimension with a large set of date-related attributes and as many dates (rows) as you need. This example assumes you have had some experience with Analysis Services – we don't provide complete step-by-step
instructions, and we don't explain why you need to do some of the things you do.
Define the Dimension in Analysis Services
First, create a new AS project.
Right-click on the Dimensions folder in the Solution Explorer and create a new date dimension using the Dimension Wizard:
Select Build the dimension without using a data source
check the 'use a dimension template' box,
select Date template in the drop-down box.
Next, define the time periods you would like in your dimension
(our example covers 12 years and includes Year-Quarter-Month-Week-Date attributes)
Next, select any additional calendar types you would like. This example includes a fiscal
year calendar starting July 1.
Complete the wizard by naming the dimension using the name box at the top of the next screen; the default is Time, but we use DateDimGen in this example. Don’t check the
Generate schema now box. We’ll do this next.
Take a look at the dimension in the dimension design tab. Notice the attributes and hierarchies.
Create the Date Dimension in the Relational Database
From the Database menu at the top of the Visual Studio window, select Generate Relational Schema…
This should open the Schema Generation wizard. The first screen will ask you to create a data source view. Name the dsv and create a data source by hitting the New… button which opens the Data Source wizard. Create an appropriate data source that points to the target relational database where you would like AS to create and populate the relational Date dimension table.
The next screen in the wizard requires an owning schema name for the table. The account you chose in the data source definition has to have permission to create tables under the schema name you enter here. You can leave all the options set at their default values.
Finish the wizard and it should generate and populate the table for you in the relational database. Take a look at it in SQL Server Management Studio.
Add the Surrogate Key Column
Next, we need to add the surrogate key column to the relational table. The default key, called PK_Date in the example, is a date type, which won't let you enter a non-date key (like a -1 for 'Missing Date').
In SQL Studio, right-click on the table and go to the Design pane. Insert a new integer column called Date_Key. Uncheck the Allow Nulls box and set its (Is Identity) property under the Identity Specification section to Yes. Drag this column up to the top of the list, right-click on it and select Set Primary Key. Now, check the Allow Nulls box on the former key column called PK_Date. You might also want to change the name of this column to just Date or SQL_Date. See the example figure below.
Save your changes which will create the identity column.
Refresh the table definition in the Object Explorer and take another look at the table in the SQL Studio. You should see the identity column called Date_Key, starting with number 1.
Redefine the Dimension Definition in Analysis Services
Now, go back to the BI Studio and fix up the dimension definition in Analysis Services so it will match the table.
First, go to the data source view, right-click and refresh the table definition.
Click on the dimension design tab and drag the new Date_Key column from the data source view into the dimension attributes list. Go to the Date_Key properties window and change its Usage property from Regular to Key. This will cause some problems.
Drag all the related attributes from the old Date key column to the new Date_Key column. Delete the Date_Key entry under the Date_Key attribute. Drag Month and Week back up to the old Date column. This should clear up the last warning.
Note that we've been doing all this definitional work in Analysis Services without actually having created an Analysis Services dimension. Try and process the dimension and see what happens. You should be able to browse the attributes and hierarchies.
Note that the relational table is now your master Date Dimension.
Add a Missing Date Row in the Relational Table
Finally, add a 'Missing Date' row to the relational table and reprocess the dimension. (See the example SQL script below for adding the row to the Date dimension described above. You will probably need to change the schema and table names, and add any additional columns you included.)
You should see an Unknown entry in each of the hierarchies.
Press on from here!
-- Script to INSERT a dimension row that corresponds to missing dates
SET IDENTITY_INSERT DWTest2.DateDimGen ON
(Date_Key, Date, Date_Name, Year, Year_Name, Quarter, Quarter_Name,
Month, Month_Name, Week, Week_Name, Day_Of_Year,
Day_Of_Year_Name, Day_Of_Quarter, Day_Of_Quarter_Name, Day_Of_Month,
Day_Of_Month_Name, Day_Of_Week, Day_Of_Week_Name,
Week_Of_Year, Week_Of_Year_Name, Month_Of_Year, Month_Of_Year_Name,
Month_Of_Quarter, Month_Of_Quarter_Name, Quarter_Of_Year,
Quarter_Of_Year_Name, Fiscal_Year, Fiscal_Year_Name, Fiscal_Quarter,
Fiscal_Quarter_Name, Fiscal_Month, Fiscal_Month_Name, Fiscal_Week,
Fiscal_Week_Name, Fiscal_Day, Fiscal_Day_Name, Fiscal_Day_Of_Year,
Fiscal_Day_Of_Month_Name, Fiscal_Day_Of_Week, Fiscal_Day_Of_Week_Name,
Fiscal_Week_Of_Year, Fiscal_Week_Of_Year_Name, Fiscal_Month_Of_Year,
(-1, NULL, 'Unknown Date', NULL, 'Unknown Year', NULL, 'Unknown
'Unknown Month', NULL, 'Unknown Week', NULL, 'Unknown Day of Year',
'Unknown Day of Quarter', NULL, 'Unknown Day of Month', NULL, 'Unknown
Day of Week',
NULL, 'Unknown Week of Year', NULL, 'Unknown Month of Year', NULL,
'Unknown Month of Quarter', NULL, 'Unknown Quarter of Year', NULL,
'Unknown Fiscal Year', NULL, 'Unknown Fiscal Quarter', NULL, 'Unknown Fiscal Month',
NULL, 'Unknown Fiscal Week', NULL, 'Unknown Fiscal Day', NULL, 'Unknown Fiscal Day of Year', NULL, 'Unknown Fiscal Day of Quarter', NULL,
'Unknown Fiscal Day of Month', NULL, 'Unknown Fiscal Day of Week', NULL, 'Unknown Fiscal Week of Year', NULL, 'Unknown Fiscal Month of Year', NULL,
'Unknown Fiscal Month of Quarter', NULL, 'Unknown Fiscal Quarter of Year')
SET IDENTITY_INSERT DWTest2.DateDimGen OFF