The standard project contains a Date dimension that contains the following attributes:
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 add a custom field called "Year Week Name".
Add a custom transformation.
Add the WeekKey and the "Year Week Name" fields to the date table in the data warehouse.
Deploy and execute the date dimension
Modify Date Dimension
Locate the Date dimension, right click any attribute and click Add Quick Levels.
Select the WeekKey attribute. Click OK.
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.
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.
Right click on the dimension, Advanced, Dimension Level Relations.
Drag the Year Month on top of the Year Week. The result should look like this:
Right click the OLAP Servers node and click Deploy and Execute.
The final result in Excel will now look like this: