Overview
Starting with NAV 2013, the advanced dimension structure in the database was drastically changed to be easier to use and take up much less disk space. This article explains how to add Advanced Dimensions from NAV 2013 to a project using the Jet Data Manager.
Process
Unlike earlier versions, the Advanced Dimensions entries in NAV 2013 and higher are not stored in a number of disparate tables. Rather, they are all stored in a table called the Dimension Set Entry table.
Instead of storing a different set of values for every entry transaction that takes place (as was the case in prior versions of NAV), many transactions can share a single Dimension Set ID that uniquely identifies the dimension values for all transactions that share the same Dimension Set ID.
The table below contains a sample set of dimension values for all transactions with a Dimension Set ID of 3:
| Dimension Set ID | Dimension Code | Dimension Value Code |
|---|---|---|
| 3 | AREA | 30 |
| 3 | CUSTOMERGROUP | MEDIUM |
| 3 | DEPARTMENT | SALES |
So all transactions that have a Dimension Set ID = 3 correspond with the AREA of 30, the CUSTOMERGROUP of MEDIUM, and the DEPARTMENT of SALES. This is true for all transaction tables within NAV for that company.
The general steps to add advanced dimensions to a NAV 2013 project are:
- Bring "Dimension Value Code" field from Dimension Set Entry table to the desired transaction table in the staging database
- Bring this field from the transaction table in the staging database to the desired fact table in the data warehouse
- Create a dimension table in the data warehouse that holds all of the dimension values for the dimension
- Create a new dimension in the OLAP structure
- Add this new dimension to the desired cube and join it based on the field that was added to the fact table
Since the Dimension Set Entry table is the foundation of the Advanced Dimension structure in NAV 2013 (and higher), you will add the following fields from this table:
- DW_Account
- Dimension Set ID
- Dimension Code
- Dimension Value Code
-
You will want to bring the Dimension Set Entry table near the top of the staging database so that it gets processed prior to any of the fact tables that you will be creating lookups to.
-
Once the Dimension Set Entry table has been added, the next step is to drag the Dimension Value Code field from the Dimension Set Entry table and drop it on the transaction table that you wish to bring the advanced dimension value into.
For this example, we will be adding the Department dimension to the G/L Entry table. You will also need to add the Dimension Set ID field to the transaction table that you are bringing the advanced dimension into.
Below is an example of the lookup field that has been added to the G/L Entry table.
-
The joins used for all advanced dimension lookups, regardless of the table, will be:
- DW_Account = DW_Account
- Dimension Set ID = Dimension Set ID
- Dimension Code = <DIMENSION>
The <DIMENSION> variable is a fixed value that represents the desired dimension name in NAV.
-
Once the advanced dimension is on the transaction table in the staging database it can then be added to the fact table in the data warehouse. For this example we will add the DEPARTMENT Dimension field from the G/L Entry table in the staging database to the Finance Transactions table in the data warehouse.
-
You will then need to have a dimension table in the data warehouse that includes all of the values for the department dimension. You will first want to make sure that the following fields are in the Dimension Value table in the staging database:
- DW_Account
- Dimension Code
- Code
- Name
-
This table will be the source table for the Department dimension that we will create in the cubes. To facilitate this, drag the Dimension Value table from the staging database and drop it as a new table on the data warehouse. When prompted click "Add as new table":
-
The table and fields can then be renamed based on your preference:
-
This will bring in all dimension values, however, and this table should only contain dimension values for the desired dimension.
To limit the values that are pulled in to the dimension table you will right-click the table name in the data warehouse and click Add Data Selection Rule
-
You will then click the Dimension Code field in the window on the right side of the Jet Data Manager, change the operator to Equal, and type in the name of the NAV dimension that this table will represent and then click Add
-
The next step is to add a new dimension to the OLAP database to represent the dimension table that was just created.
The dimension name in this case will be Department:
-
You can then add a dimension level to set the display format to the desired layout.
The example below will display with the key and name of the dimension code in the front-end in the format of Key - Name
-
The Department dimension will then be dragged and dropped onto the desired cube (in this case the Finance cube). The Department dimension associated with the Finance cube is currently a shell dimension and needs to be properly associated.
This can be accomplished by right-clicking on the Department dimension in the Finance cube and selecting Dimension Relations -> All Fact Tables
-
You will then link the dimension to the correct advanced dimension field in the fact table that was added in a prior step.
-
You can then deploy and execute the necessary objects to finalize the process.
The end result is the ability to analyze data based on this advanced dimension directly from the cubes:
Comments