Add a Week Level to a Date Dimension

Overview

The Jet Analytics standard project contains a Date dimension that contains the following attributes:

• Year
• Quarter
• Month
• Day

In order to incorporate a Week level into the date dimension we have to modify the project.

Modify the Date Table

1. In the staging database, right-click Date table and select Add a Custom Field. The Add Field window is displayed.

   

   Enter the Field Name as Year Week Name.

   

2. Add a custom transformation.

   

3. Add the WeekKey and the "Year Week Name" fields to the date table in the data warehouse.

   

4. Deploy and execute the date dimension

Modify Date Dimension

1. Locate the Date dimension, right click any attribute and click Add Quick Levels.

   

2. Select the WeekKey attribute. Click OK

   

3. Right click and edit the newly added dimension attribute. Perform the following:

   • Rename the attribute to "Year Week"
   • Untick the box for Visible
   • Change the Lay-out to Name
   • Change the Name Column to Year Week Name
   • Click Advanced... in the Type parameter select Weeks.

   

   

   Click OK to exit the Advanced window and OK to exit the Dimension Level window.

4. Right click the user defined hierarchy Date YQMD and click Edit Hierarchy.

   

   Rename the hierarchy to Date YQMWD and add the Year Week below Month (Year Month). Click OK.

   

   Lastly rename the Year Week hierarchy level to Week.

   

   

5. Right click the Date dimension, Advanced, Dimension Level Relations.

   

   Drag the Year Month on top of the Year Week. The result should look like this:

   

6. Right click the OLAP Servers node and click Deploy and Execute

The final result in Excel will now look like this: