The 4-4-5 calendar is primarily used for the analysis across accounting periods equally. The same applies to the other well-known 4-5-4 and 5-4-4 calendar variants.
Step 1: Add the Accounting Period Table
For the purposes of this example, we will be using the Accounting Period table from a NAV 2015 database.
Once added, locate the newly added table in your Staging database. Right-click on the table and select Deploy and Execute to continue.
Step 2: Modify View Query as Needed
Next, you will need to open up the file named SQLQuery that is contained within the attachment at the bottom of this KB article. Once opened, modify the query to fit your specific fiscal calendar and ensure that the naming conventions used in the query match your local environment (i.e. Database names, table names, etc.).
To verify that the query will work within your environment, you can comment out the first line of the query by placing two dashes -- in front of the 'CREATE VIEW' statement. Once the 'CREATE' portion is commented out, you will be able to execute the query locally.
Step 3: Create View
Once you've verified that the SQLQuery file will work in your environment, the next step is to add the query into the project as a View. To do so, right-click on the 'Views' node in your data warehouse and select 'Add Custom View'
In the window that opens, you will paste the code from the SQLQuery file into the entry section. For the Name as in script , make sure to name it the same as the View creation title. Click OK to proceed.
Once created, the next step is to deploy the view. To do so, right-click on the view you just created and select Deploy . In the window that opens, simply click Start to begin the deployment process and then click Close when the process finishes.
Once deployed, you will need to right-click on the view once more and this time select Read View Fields .
Step 4: Integrate View with Date Table
With the view created and deployed, the next step is to begin bringing the view fields into your Date table as lookups.
Each of these lookups will use the following as the join condition:
1) FiscalStartDate is less or equal to Date
2) FiscalEndDate is greater or equal to Date .
After adding all of the view fields to your date table, the next step is to create a new custom field on the date table. To do so, right-click on your Date table and select Add Field .
In the window that opens, give the new field a name such as FiscalWeekNumber. You will also need to set the Data Type to Text , the Text Length to 5 , and check the Unicode box. Click OK to proceed.
Once created, right-click on your new field and select Field Transformations .
In the Field Transformation pane that appears on the right-hand side, leave the Operator set to Custom and then click Add .
In the window that opens, you will need to paste the following code into the entry area. If your fields are named differently, you will need to adjust the SQL code accordingly. Click OK to proceed.
RIGHT('0' + CAST(DATEDIFF(d, [FiscalYearStartDate], [Date]) / 7 + 1 as VARCHAR), 2)
Once the FiscalWeekNumber field has been created, the next step is to create two additional custom fields. This will essentially repeat the process from the FiscalWeekNumber creation.
To start, right-click on the Date table and select Add New Field . For the two custom fields, one should be named FiscalYearWeekNumber and the other should be named FiscalYearWeekName . The settings for these fields should be as shown below.
For each of these fields, you will be applying the following transformations:
CAST([FiscalYear] AS varchar(4)) + CAST([FiscalWeekNumber] as varchar(2))
CAST([FiscalYear] AS varchar(4)) + ' Wk ' + CAST([FiscalWeekNumber] as varchar(2))
With all of the modifications to the Date table complete, right-click on the Date table and select Deploy and Execute
Step 5: Create Additional Dimension Levels
The rest of this process will be conducted within the Cubes tab of the Jet Data Manager. To add the new Fiscal elements to your Date dimension, right-click on the Date dimension and select Add Dimension Level .
You will be creating seven additional dimension levels. Use the images below as a template.
Step 6: Set Dimension Level Types
For each of these 7 dimension levels that you just created, you will need to set the Type of the dimension level so that the Hierarchy will behave correctly. To do so, right-click on any of the Fiscal Dimension Levels just created and select Edit Dimension Level .
From within the window that opens, complete the following steps:
- Click Advanced
- Click the ellipses button '...'
- Select the appropriate Dimension Level Type (listed below)
- Click OK
- Click OK
- Click OK
Correct Dimension Level Types :
- Fiscal Year
- Fiscal Quarter & Fiscal Year Quarter
- Fiscal Month & Fiscal Year Month
- Fiscal Week & Fiscal Year Week
Step 7: Assign Dimension Level Keys
The next step is to go back through the dimension levels once more and assign a two-part Dimension Level Key .
To start, right-click on one of your Dimension Levels and select Edit Dimension Level
In the window that opens, first click the ellipses button '...' which will open up the multi-part Dimension Level Keys selector. Once opened, scroll down until you locate Fiscal Year and click the box next to it. Click OK within both opened windows to proceed.
Step 8: Create Dimension Hierarchy
Now that you've created and setup the additional dimension levels for your Fiscal calendar, the next step is to create a hierarchy for these values. To do so, right-click on your Date dimension once more and select Add Hierarchy .
In the Hierarchy window, start by giving your new hierarchy a name. For this example, our new hierarchy will be named 'Fiscal YQMWD' (YQMWD = Year/Quarter/Month/Week/Day)
After naming your hierarchy, the next step is to select the dimension levels that you want contained within the hierarchy. Upon clicking on a dimension level, it will be added to the hierarchy list in the right-hand pane.
Once you've added all five dimension levels as shown below, click OK to proceed.
The order in which the dimension levels appear within your hierarchy is important. If the ordering of yours differs from that above, simply click-and-drag the level into the appropriate location.
Once in the Dimension Level Relations window, you will simply click-and-drag items and drop them onto their child element.
To start, click and drag Fiscal Month and drop it onto the Fiscal Week element. Continue this process by dragging Fiscal Quarter onto Fiscal Month , and finally Fiscal Year onto Fiscal Quarter . When finished, click OK to proceed.
Step 9: Add Scope Statement (Optional)
The last step in implementing your 4-4-5 Calendar deals with displaying any time sensitive measures (i.e. YTD, MTD, etc.) correctly when you have your new Fiscal hierarchy in use.
To accomplish this, we need to create a Scope statement that will tell Analysis Services to calculate the measures differently anytime the Fiscal hierarchy is in use.
To start, expand the desired cube and right-click on Script Commands and select Add Script Command .
In the Add Script Command window, assign an identifiable name for the scope statement and then paste the following code into the script command window. Click OK to proceed.
Scope(Descendants([Posting Date].[Fiscal YQMWD],,After));
[Measures].[Sales Amount YTD] = Sum(Ytd([Posting Date].[Fiscal YQMWD].CurrentMember), [Measures].[Sales Amount]);
Step 10: Use your new 4-4-5 Calendar
The last step is to use your new Fiscal Dates and Hierarchy in a PivotTable.