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.
It is strongly recommended that only users with an advanced knowledge of SQL attempt the steps contained in this article. If this customization needs to be made to your project but you feel you cannot complete the below steps without assistance, please contact your BI Consultant or firstname.lastname@example.org.
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.
If you are not using NAV and/or you do not have an Accounting Period table available, you will need to create something similar either in SQL or Excel. The process described here relies on the specific month and year start dates to be in place in the Accounting Period table (or similar).
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:
Fiscal Year Week Number
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:
The step numbers listed below correlate to the numbers within the image below.
- 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.You will need to set the two-part Dimension Level Keys for each of your new Fiscal date dimension levels before proceeding.
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.
The last step in updating your Date dimension requires you to assign the Dimension Level Relations . To do so, right-click on your Date dimension and select Advanced > Dimension Level Relations
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)If you only want the 4-4-5 Calendar displayed, delete the standard 'Date YQMD' hierarchy and fix every time dependent measure by selecting the appropriate hierarchy.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.This process will need to be completed for each time-sensitive measure that exists within your cubes. It is recommended that you name your Script Commands in such a way that you can identify them easily (i.e. Scope_INSERTMEASURENAME).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.
The following sample code will need to be adjusted if used for any measures other than 'Sales Amount YTD' or any hierarchies named differently than 'Fiscal YQMWD'.
Sample Scope Statement for Sales Amount YTD
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.