Advanced Dimensions (NAV 2009 or Older) in the JDM

Overview

This article explains how to add Advanced Dimensions from NAV 2009 and older to a project using the Jet Data Manager.

Create Dimension Tables

First we need to create a new dimension table in the Data Warehouse database that we will use later on to link to our fact tables. Second we need to create a new dimension in OLAP database. You need to repeat this part of the process only one time for each NAV dimension you would like to add to your project (no matter how many cubes you then link the dimension to).

1. Right-click Tables within Data Warehouse and select Add Table.



2. Give it a name (example: Dim AREA) and click OK to proceed.

   

3. Locate [NAV_dbo_Dimension Value] table in the stage database, and drag the following fields up to the newly created table: [DW_Account], [Dimension Code], [Code], and [Name].
   • If [Dimension Code] is not available within your Staging [Dimension Value] table, you will need to add it from the data source.

   

4. Rename [DW_Account] field to [Company].

   

   

   Right click your table (DimArea) and select Add Data Selection Rule.

   

   1. Click [Dimension Code] in Data Selection window.

   2. From the Operator drop-down menu select 'Equal'.

   3. In the value input box type the name of your NAV dimension code (example: AREA).

   4. Click Add.

      

   1. Click the Cubes tab.

   2. Expand the OLAP server

   3. Right click Dimensions and select Add Dimension.

      

   Type in a dimension name (example: Dim AREA) and click OK.

   

   1. Type in a dimension level name (example: Dim AREA).

   2. From the Key Table dropdown menu select the newly created table (example: Dim AREA).

   3. From the Key Column dropdown menu select [Code].

   4. Keep the Layout as name only.

   5. From the Name Table dropdown menu select the newly created table.

   6. From the Name Column dropdown menu select [Name].

   7. Click OK.

      

   Click OK to confirm that you want to set the level as key level.

   

   With this last step, you have now created the dimension aspect for your NAV Advanced Dimension. This process needs to be repeated for each additional Advanced Dimension you want to add to your Jet Enterprise project.

Add correlationg fields to Fact Tables

Aside from creating the dimension, you will also need to add the correlating field(s) to your fact tables as well.  For this, you will need to pick the appropriate “dimension linking” table in NAV, which depends on what fact table you want to link the dimension to. If you want to link it to an entry table (such as GL Entry, Value Entry), you need to use Ledger Entry Dimension table. If you want to link it to a posted document table (such as Sales Invoice Line, Purchase Credit Memo Line), you need to use the Posted Document Dimension table.

1. Expand Data Sources and click your NAV adapter (default: NAV).

   

2. In the table list on the right hand side, pick your "dimension linking" table (example: Ledger Entry Dimension) and select the fields you will need (in most cases, that will be all of them).

   

3. To speed up the execution process, you can add a Data Selection Rule on this table (right-click table and select Add Data Selection Rule) to only load data for specific tables (for example, you can limit the Ledger Entry Dimension table to only load data from [Table ID] = 17, which is [GL Entry]).

   

   

4. In your Staging database, move the "dimension linking" table above your fact table (example: [GL Entry]).

   

5. Drag the [Dimension Value Code] field from your "dimension linking" table down to your fact table.

   

6. Rename the newly created lookup field to something more appropriate (example: [Dim AREA]).

   

   

7. Expand the lookup field to add joins (these depend on what "dimension linking" table you picked) In this example for the [GL Entry] table, the join conditions are [DW_Account] = [DW_Account], [Entry No.] = [Entry No.], [Dimension Code] = <YOUR DIMENSION CODE> (example: [Dimension Code] = AREA), [Table ID] = <YOUR TABLE ID> (example: [Table ID] = 17)

   

   

   Copy the new lookup field up to your fact table in the Data Warehouse database (example: [Finance Transactions]).

   

   1. Click the Cubes tab.
   2. Expand your OLAP server
   3. Expand the 'Cubes'
   4. Expand the desired Cube (example: Finance)
   5. Right-click 'Dimensions' and select 'Add Dimension(s) to Cube'.

   

8. Select the dimension created previously and click OK.

   

9. Expand the cube, locate your dimension under the Dimensions node, right-click it and select 'Dimension Relations -> All Fact Tables'.

   

10. Match the Code key column to your lookup field in the fact table (example: Dim AREA).

    

11. Click OK and Deploy and Execute the project.

Related Articles

• NAV Advanced Dimension Editor