By default, the Jet Data Warehouse and the corresponding OLAP cubes operate off of the traditional calendar year. However, in certain business case scenarios, there is the need to have these calendars reflect the calendar in which the business operates, such as a 4-5-4, 4-4-5, or any other type of calendar. If this calendar has been setup in your 'Accounting Period' table within NAV, you should be able to follow the steps below and begin reporting off of that calendar.
Out of the box, our standard NAV projects don't import the Accounting Period table. To correct this, scroll down to your 'Data Sources' section in the JDM and click on your 'NAV' data source. This will bring up a list on the right-hand side of the Jet Data Manager (JDM) that includes all of the available tables and columns within your data source. Scroll down to 'Accounting Period' and click the check-box.
Once your 'Accounting Period' table has been selected, it will automatically get added to your 'Staging' database, but will be in red text. Right-click on this table in your staging database, named 'NAV_dbo_Accounting Period', and select 'Deploy and Execute'. This will bring your 'Accounting Period' table from NAV and make the data available for use within your 'Staging' database.
Now we need to create a view in order to be able to use this table. The reason we need this view is because the 'Accounting Period' table in NAV only includes the date in which each fiscal year/month starts. The SQL query to create this view creates an ending point for each month, and then appends on your new fiscal month, quarter, and year information. A sample query that will work with most standard NAV environments can be found here: Custom Accounting Period View
Once you have this 'create view' query ready, right-click on the 'Views' branch of your 'Staging Database' tree and select 'Add Custom View'
This will then open up an 'Add Custom View' window for you to input your SQL query. In order for this to work smoothly, the 'Name as in script' field needs to match the name you used in the view query.
Once you've completed this, go ahead and click 'OK'. You'll now see your newly created view under the 'Views' branch, again with red text. You're now going to do a series of three right-click steps. First: right-click and select 'Deploy' and then 'Start'; This will write the view to your database. Second: right-click and select 'Read View Fields'; This will populate the columns within the JDM. Third: right-click and select 'Preview View'; This will show you the contents of your view. During this preview step, just take a quick look to make sure it's populating data correctly. Once you've confirmed that the data looks correct, click 'Close'.
The next step, now that we have a view populated with the 'Accounting Period' calendar, is to drag-and-drop that into your Data Warehouse. To do so, simply click-and-hold on your view and drag it up to the 'Tables' branch of your Data Warehouse as shown below. This will popup a two-option window in which you'll select 'Add as new table'.
Your view is now a table within the Data Warehouse. However, the text is red again, so we'll need to right-click on your new table name (it will be at the bottom of the table list) and select 'Deploy and Execute'. Once you've completed the 'Deploy and Execute' phase, we need to move your new table to the top of the 'Tables' hierarchy. As before, simply click-and-hold on the table name and drag it to the top, releasing it just above your topmost table, 'Date'.
We now need to bring some fields from your new 'Accounting Period' table into your 'Date' table. First, click-and-drag 'Fiscal Month Number' onto the 'Date' table name. It will then show up as a field in the 'Date' table, but the 'Joins' will be empty. In the screenshot below, you'll see that an example has been provided for the 'Fiscal Month Number' field joins. Once you've reproduced the joins for 'Fiscal Month Number', repeat this same process for 'Fiscal Month Name', 'Fiscal Year', and 'Fiscal Quarter'.
To finalize your new 'Fiscal' fields in your 'Date' table, you'll need to populate a new field to hold your 'Fiscal Quarter Name. To do this, right-click on your 'Date' table name and select 'Add Field'. Name the field 'Fiscal Quarter Name' with a data type of 'Text' and a text length of '2'. Add 4 'Field Transformations' to this new field; these will be 'Fixed' with values of 'Q1' through 'Q4'. Then for each transformation, add a condition that looks back to your existing 'Fiscal Quarter' field. When finished, this field should look like the following.
The last four fields that we need to account for are the 'Fiscal Year Quarter' name and number as well as the 'Fiscal Year Month' name and number. To do this, you will right-click on the 'Date' table and select 'Add Field'. Supply the field with a name, select the relevant data type, and click 'OK'. Once created, right-click on the new field and select 'Field Transformations'. This will then open up a 'Field Transformation' dialogue on the right hand side of your screen. Leave the 'Operator' set to 'Custom' and click 'Add'. This will then open up a 'Transformation Custom SQL' window, with a workspace for you to input some custom code. The necessary code that you will input here is shown in the below screenshot for each of these last four fields.
You now have your 'Accounting Period' calendar as part of your 'Date' table. You will be able to reference these fields from any Jet Essentials formulas, but to utilize these fields within any Pivot Tables or Pivot Charts you will have to go a little further. However, what you do from this point will vary depending on the needs of your business.
Accounting Period as Sole/Primary Calendar
Follow these steps if you want the 'Accounting Period' dates to trump the traditional calendar dates. Navigate to the 'Cubes' tab of your JDM and drill-down to the 'Date' dimension. Right-click on 'Month' and select 'Edit Dimension Level'. You will need to change the 'Key Column' and 'Name Column' to reflect your newly created 'Fiscal' counterparts instead. For instance, wherever it refers to 'Month Number', you'll need to use 'Fiscal Month Number'. You will need to make these same type of changes for every other dimension level here.
Accounting Period as Additional/Secondary Calendar
The rest of these steps are used to bring your 'Accounting Period' calendar in alongside the existing standard calendar. Navigate to the 'Cubes' tab of your JDM and drill-down to the 'Date' dimension. Right-click on the 'Date' dimension and select 'Add Dimension Level'. This will open up a window with a series of option fields. In the screenshot below, an example setup is provided for the 'Fiscal Month' dimension level. Once you have the 'Fiscal Month' dimension level created, you will simply repeat this process for the 'Fiscal Year Month', 'Fiscal Year Quarter', 'Fiscal Quarter' and 'Fiscal Year' levels as well. If needed, you can right-click and edit the standard levels for reference of what fields you'll need to use on your 'Fiscal' counterpart.
Next you'll need to create an additional hierarchy, much like the existing 'Date YQMD' hierarchy which is built off the standard calendar. To do so, right-click on the 'Date' dimension and select 'Add Hierarchy'. This hierarchy will use the following fields, in order from top to bottom: 'Fiscal Year', 'Fiscal Year Quarter', 'Fiscal Year Month', and 'Day'.
Once you have your 'Fiscal' hierarchy constructed, we need to create 'Fiscal' entities for the 'Date Calculation' dimension. To do so, right-click on the 'Date Calculation' dimension and select 'Add Dimension Level'. We're going to create one for 'Fiscal Aggregation' and 'Fiscal Comparison', essentially mirroring their traditional counterparts. The settings for these two new dimension levels should look as follows:
In order to get the 'Date Calculation' function to work for both calendars within a Pivot Table, we now need to import some 'Business Functions'. Do this by clicking over to your 'Tools' ribbon, clicking on 'Import Business Functions', and then navigating to the 'Business Function Library' xml file. This file will be contained in your Jet Enterprise Installation folder that was downloaded, or you can get a copy of just the business functions here: Business Function Library v3.1 . Upon selecting the xml file for import, you'll be presented with a window asking which functions to import. For the sake of ease, click 'Select All' and then 'OK'.
Once you have the business functions imported, we're going to add a function to one of your cubes. Drill into the cube that you want to add the calculation, right-click on the 'Script Commands', and then select 'Add Business Function' -> 'Script Commands' -> 'Date Calculation'.
Note: This step will have to be repeated for every cube that you want to use the 'Comparison' and 'Aggregation' views against the 'Fiscal' hierarchy.
Upon selecting 'Date Calculation' a new window will popup. If your window doesn't look like the below, click 'Show Parameters'. Then, using the image below as a guideline, select your relevant fields to use within the 'Object Name/Value' column of the Business Function. The last two fields will need to be manually typed in. For the 'Variant' column, use whatever the 'Description' column says to use, as shown below.
And that's it! You will probably need to Deploy and Execute your entire project, at the very least your OLAP Servers, but then you will have access to both calendars from within your Data Warehouse and OLAP Cubes.