Design Overview of Dimension handling in Navision 2016

Hold on,

Way of handling Dimension have changed from Navision 2009 not a new concept. Till now everyone is aware of this technical change, but still to keep handy reference today I decided to share the same with community, which can help for new comers in this industry.

Detailed technical insight into the concepts and principles that are used to redesign the dimension entry storing and posting feature in Microsoft Dynamics NAV 2016. Helpful when upgrading from earlier version to 2009.

Dimension Sets

A dimension set is a unique combination of dimension values. It is stored as dimension set entries in the database. Each dimension set entry represents a single dimension value.

The dimension set is identified by a common dimension set ID that is assigned to each dimension set entry that belongs to the dimension set.

Dimension Set Entries

Dimension sets are stored in the Dimension Set Entry table as dimension set entries with the same dimension set ID.

When you add, edit and close the Edit Dimension Set Entries window, a check is performed to see whether the combination of dimension values exists as a dimension set in the table.

If the combination occurs in the table, then the corresponding dimension set ID is assigned to the journal line, document header, or document line.

Otherwise, a new dimension set is added to the table, and the new dimension set ID is assigned to the journal line, document header, or document line.
Performance Improvement
By storing dimension sets once in the database, database space is preserved, and overall performance is improved.

Searching for Dimension Combinations

Building Search Tree
Table 481 Dimension Set Tree Node is used when Microsoft Dynamics NAV evaluates whether a set of dimensions already exists in table 480 Dimension Set Entry table.

The evaluation is performed by recursively traversing the search tree starting at the top level numbered 0.

The top level 0 represents a dimension set with no dimension set entries. The children of this dimension set represent dimension sets with only one dimension set entry.

The children of these dimension sets represent dimension sets with two children, and so on.

Finding Dimension Set ID

At a conceptual level, Parent ID, Dimension, and Dimension Value, in the search tree, are combined and used as the primary key because Microsoft Dynamics NAV traverses the tree in the same order as the dimension entries.

The GET function (record) is used to search for dimension set ID.

DimSet.”Parent ID” := 0;  // ‘root’

IF UserDim.FINDSET THEN

REPEAT

DimSet.GET(DimSet.”Parent ID”,UserDim.DimCode,UserDim.DimValueCode);

UNTIL UserDim.NEXT = 0;

EXIT(DimSet.ID);

However, to preserve the ability of Microsoft Dynamics NAV to rename a dimension and dimension value, table 348 Dimension Value is extended with an integer field of Dimension Value ID.

This table converts the field pair Dimension and Dimension Value to an integer value.

When you rename the dimension and dimension value, the integer value is not changed.

DimSet.”Parent ID” := 0;  // ‘root’IF UserDim.FINDSET THEN  REPEAT      DimSet.GET(DimSet.ParentID,UserDim.”Dimension Value ID”);  UNTIL UserDim.NEXT = 0;EXIT(DimSet.ID);

Table Structure

New Tables

Three new tables have been designed to manage dimension set entries.

Table 480 Dimension Set Entry
Table 480 Dimension Set Entry is a new table. You cannot change this table. After data has been written to the table, you cannot delete or edit it.

Deleting data requires that you check against all occurrences of the dimension set ID in the entire database, including partner solutions.

Field No.	Field Name	Data Type	Comment
1	ID	Integer	>0.0 is reserved for the empty dimension set. References field 3 in table 481.
2	Dimension Code	Code 20	Table relation to table 348.
3	Dimension Value Code	Code 20	Table relation to table 349.
4	Dimension Value ID	Integer	References field 12 in table 349. It is the secondary key that is used when traversing table 481.
5	Dimension Name	Text 30	CalcField. Lookup to table 348.
6	Dimension Value Name	Text 30	CalcField. Lookup to table 349.

Table 481 Dimension Set Tree Node

Table 481 Dimension Set Tree Node is a new table. You cannot change this table.

It is used to search for a dimension set. If the dimension set is not found, a new set is created.

Field No.	Field Name	Data Type	Comment
1	Parent Dimension Set ID	Integer	0 for top level node.
2	Dimension Value ID	Integer	Table relation to field 12 in table 349.
3	Dimension Set ID	Integer	AutoIncrement. Used in field 1 in table 480.
4	In Use	Boolean	False if not in use.

Table 482 Reclas. Dimension Set Buffer
Table 482 Reclas. Dimension Set Buffer is a new table.

The table is used to edit a dimension set ID.

It is required when you edit a dimension value code and a new dimension value code, for example, in the Item Reclas. Journal table.

Field No.	Field Name	Data Type	Comment
1	Dimension Code	Code 20	Table relation to table 348.
2	Dimension Value Code	Code 20	Table relation to table 349.
3	Dimension Value ID	Integer	References field 12 in table 349.
4	New Dimension Value Code	Code 20	Table relation to table 349.
5	New Dimension Value ID	Integer	References field 12 in table 349.
6	Dimension Name	Text 30	CalcField. Lookup to table 348.
7	Dimension Value Name	Text 30	CalcField. Lookup to table 349.
8	New Dimension Value Name	Text 30	CalcField. Lookup to table 349.

Modified Tables
All transaction and budget tables have been modified to manage dimension set entries.

Changes to Transaction and Budget Tables
A new field has been added to all transaction and budget tables.

Field No.	Field Name	Data Type	Comment
480	Dimension Set ID	Integer	References field 1 in table 480.

Changes to Table 83 Item Journal Line
Two new fields have been added to table 83 Item Journal Line.

Field No.	Field Name	Data Type	Comment
480	Dimension Set ID	Integer	References field 1 in table 480.
481	New Dimension Set ID	Integer	References field 1 in table 480.

Changes to Table 349 Dimension Value
A new field has been added to table 349 Dimension Value.

Field No.	Field Name	Data Type	Comment
12	Dimension Value ID	Integer	AutoIncrement. Used for references in table 480 and table 481.

Tables That Get New Field 480 Dimension Set ID
A new field, 480 Dimension Set ID, has been added to the following tables.

For the tables that store posted data, the field only provides a non-editable display of dimensions, which is marked as Drill-down.

For the tables that store working documents, the field is editable. The buffer tables that are used internally do not need editable or non-editable capabilities.

The 480 field is non-editable in the following tables

Table No.	Table Name
17	G/L Entry
21	Cust. Ledger Entry
25	Vendor Ledger Entry
32	Item Ledger Entry
110	Sales Shipment Header
111	Sales Shipment Line
112	Sales Invoice Header
113	Sales Invoice Line
114	Sales Cr.Memo Header
115	Sales Cr.Memo Line
120	Purch. Rcpt. Header
121	Purch. Rcpt. Line
122	Purch. Inv. Header
123	Purch. Inv. Line
124	Purch. Cr. Memo Hdr.
125	Purch. Cr. Memo Line
169	Job Ledger Entry
203	Res. Ledger Entry
271	Bank Account Ledger Entry
281	Phys. Inventory Ledger Entry
297	Issued Reminder Header
304	Issued Fin. Charge Memo Header
5107	Sales Header Archive
5108	Sales Line Archive
5109	Purchase Header Archive
5110	Purchase Line Archive
5601	FA Ledger Entry
5625	Maintenance Ledger Entry
5629	Ins. Coverage Ledger Entry
5744	Transfer Shipment Header
5745	Transfer Shipment Line
5746	Transfer Receipt Header
5747	Transfer Receipt Line
5802	Value Entry
5832	Capacity Ledger Entry
5907	Service Ledger Entry
5908	Service Header
5933	Service Order Posting Buffer
5970	Filed Service Contract Header
5990	Service Shipment Header
5991	Service Shipment Line
5992	Service Invoice Header
5993	Service Invoice Line
5994	Service Cr. Memo Header
5995	Service Cr. Memo Line
6650	Return Shipment Header
6651	Return Shipment Line
6660	Return Receipt Header
6661	Return Receipt Line

The 480 field is editable in the following tables

Table No.	Table Name
36	Sales Header
37	Sales Line
38	Purchase Header
39	Purchase Line
81	Gen. Journal Line
83	Item Journal Line
89	BOM Journal Line
96	G/L Budget Entry
207	Res. Journal Line
210	Job Journal Line
221	Gen. Jnl. Allocation
246	Requisition Line
295	Reminder Header
302	Finance Charge Memo Header
5405	Production Order
5406	Prod. Order Line
5407	Prod. Order Component
5615	FA Allocation
5621	FA Journal Line
5635	Insurance Journal Line
5740	Transfer Header
5741	Transfer Line
5900	Service Header
5901	Service Item Line
5902	Service Line
5965	Service Contract Header
5997	Standard Service Line
7134	Item Budget Entry
99000829	Planning Component

The 480 field has been added to the following buffer tables.

Table No.	Table Name
49	Invoice Post. Buffer
212	Job Posting Buffer
372	Payment Buffer
382	CV Ledger Entry Buffer
461	Prepayment Inv. Line Buffer
5637	FA G/L Posting Buffer
7136	Item Budget Buffer

Codeunit 408 Dimension Management
Codeunit 408 Dimension Management is a function library that handles common tasks that are related to dimensions, such as copying from one table to another or from one document to another.

Many functions are deleted because there is no need for copying between dimension tables at the other hand many functions are modified.

Modified Functions

Function Name	Modification Description
CheckDimSetIDComb	New function that substitutes the other check functions and takes a Dimension Set ID as an argument instead of a dimension table.
CheckDimSetIDComb CheckDocDimComb CheckServContractDimComb CheckDimBuffer CheckDimComb CheckDimValueComb	Delete. All usage should be changed to CheckDimSetIDComb.
GetDefaultDim	Modify to return an integer Dimension Set ID instead of a set of records.
CopyJnlLineDimToICJnlDim CopyICJnlDimToJnlLineDim CopyDocDimtoICDocDim CopyICDocDimtoICDocDim	Modify to work with DimSetID -> ICJnlLineDim

Deleted Functions

Functions that are deleted from codeunit 408 in connection with the Dimension Set Entries feature are listed below.

During the upgrade of application code from Microsoft Dynamics NAV 2009 or earlier versions to Microsoft Dynamics NAV 2016, the following functions are not available in Microsoft Dynamics NAV 2016.

If you have customizations that use one or more of the functions, you must upgrade that code accordingly.

InsertJnlLineDim

UpdateJnlLineDefaultDim

GetJnlLineDefaultDim

GetPreviousDocDefaultDim

GetPreviousProdDocDefaultDim

InsertDocDim

UpdateDocDefaultDim

ExtractDocDefaultDim

InsertProdDocDim

UpdateProdDocDefaultDim

InsertServContractDim

UpdateServcontractDim

UpdateDefaultDimNewDimValue

GetDocDim

GetProdDocDim

TypeToTableID1

TypeToTableID2

TypeToTableID3

TypeToTableID4

TypeToTableID5

DeleteJnlLineDim

DeleteDocDim

DeletePostedDocDim

DeleteProdDocDim

DeleteServContractDim

ShowJnlLineDim

SaveJnlLineDim

ShowJnlLineNewDim

SaveJnlLineNewDim

ShowDocDim

SaveDocDim

ShowProdDocDim

SaveProdDocDim

ShowTempDim

SaveTempDim

ShowTempNewDim

SaveTempNewDim

SaveServContractDim

MoveJnlLineDimToLedgEntryDim

MoveDocDimToPostedDocDim

MoveOneDocDimToPostedDocDim

MoveLedgEntryDimToJnlLineDim

MoveDimBufToJnlLineDim

MoveDimBufToLedgEntryDim

MoveDimBufToPostedDocDim

MoveDimBufToGLBudgetDim

CopyJnlLineDimToJnlLineDim

CopyLedgEntryDimToJnlLineDim

CopyDocDimToDocDim

CopyPostedDocDimToPostedDocDim

CopyDocDimToJnlLineDim

CopyDimBufToJnlLineDim

CopyDimBufToDocDim

CopySCDimToDocDim

MoveDocDimToLedgEntryDim

MoveDocDimToDocDim

MoveDocDimArchvToDocDim

MoveLedgEntryDimToDocDim

MoveProdDocDimToProdDocDim

MoveJnlLineDimToProdDocDim

MoveJnlLineDimToDocDim

MoveJnlLineDimToJnlLineDim

CopyLedgEntryDimToLedgEntryDim

MoveTempFromDimToTempToDim

TransferTempToDimToDocDim

MoveJnlLineDimToBuf

CopyICJnlDimToICJnlDim

TestDimValue

TestNewDimValue

MoveDimBufToItemBudgetDim. (Delete because the ItemBudgetDim Table is deleted.

GetServContractDim

MoveTempDimToBuf

UpdateSCInvLineDim

CopyJnlLineDimToBuffer

UpdateDocDefaultDim2

Code Examples of Changed Patterns in Modifications

Posting a Journal Line

Key changes are listed as follows:

• Journal line dimension tables are removed.
• A dimension set ID is created in the Dimension Set ID field.

Old Code

ResJnlLine.”Qty. per Unit of Measure” :=  SalesLine.”Qty. per Unit of Measure”;

TempJnlLineDim.DELETEALL;

TempDocDim.RESET;

TempDocDim.SETRANGE( “Table ID”,DATABASE::”Sales Line”);

TempDocDim.SETRANGE( “Line No.”,SalesLine.”Line No.”);

DimMgt.CopyDocDimToJnlLineDim( TempDocDim,TempJnlLineDim);

ResJnlPostLine.RunWithCheck( ResJnlLine,TempJnlLineDim);

New Code

ResJnlLine.”Qty. per Unit of Measure” := SalesLine.”Qty. per Unit of Measure”;

ResJnlLine.”Dimension Set ID” := SalesLine.” Dimension Set ID “;

ResJnlPostLine.Run(ResJnlLine);
Posting a Document
When you post a document in Microsoft Dynamics NAV 2016, you no longer have to copy the document dimensions.

Old Code

DimMgt.MoveOneDocDimToPostedDocDim(

TempDocDim,DATABASE::”Sales Line”,

“Document Type”,

“No.”,

SalesShptLine.”Line No.”,

DATABASE::”Sales Shipment Line”,

SalesShptHeader.”No.”);

New Code

SalesShptLine.”Dimension Set ID”  := SalesLine.”Dimension Set ID”
Editing Dimensions from a Document
You can edit dimensions from a document. For example, you can edit a sales order line.

Old Code

Table 37, function ShowDimensions:

TESTFIELD(“Document No.”);

TESTFIELD(“Line No.”);

DocDim.SETRANGE(“Table ID”,DATABASE::”Sales Line”);

DocDim.SETRANGE(“Document Type”,”Document Type”);

DocDim.SETRANGE(“Document No.”,”Document No.”);

DocDim.SETRANGE(“Line No.”,”Line No.”);

DocDimensions.SETTABLEVIEW(DocDim);

DocDimensions.RUNMODAL;

New Code

Table 37, function ShowDimensions:

“Dimension ID” := DimSetEntry.EditDimensionSet( “Dimension ID”);
Showing Dimensions from Posted Entries
You can show dimensions from posted entries, such as sales shipment lines.

Old Code

Table 111, function ShowDimensions:

TESTFIELD(“No.”);

TESTFIELD(“Line No.”);

PostedDocDim.SETRANGE(“Table ID”,DATABASE::”Sales Shipment Line”);

PostedDocDim.SETRANGE(“Document No.”,”Document No.”);

PostedDocDim.SETRANGE(“Line No.”,”Line No.”);

PostedDocDimensions.SETTABLEVIEW(PostedDocDim);

PostedDocDimensions.RUNMODAL;

New Code

Table 111, function ShowDimensions:

DimSetEntry.ShowDimensionSet(“Dimension ID”);
Getting Default Dimensions for a Document
You can get default dimensions for a document, such as a sales order line.

Old Code

Table 37, function CreateDim()

SourceCodeSetup.GET;

TableID[1] := Type1;

No[1] := No1;

TableID[2] := Type2;

No[2] := No2;

TableID[3] := Type3;

No[3] := No3;

“Shortcut Dimension 1 Code” := ”;

“Shortcut Dimension 2 Code” := ”;

DimMgt.GetPreviousDocDefaultDim( DATABASE::”Sales Header”,”Document Type”,

“Document No.”,0, DATABASE::Customer, “Shortcut Dimension 1 Code”,

“Shortcut Dimension 2 Code”);

DimMgt.GetDefaultDim(TableID,No,SourceCodeSetup.Sales,

“Shortcut Dimension 1 Code”, “Shortcut Dimension 2 Code”);

IF “Line No.” <> 0 THEN

DimMgt.UpdateDocDefaultDim( DATABASE::”Sales Line”,”Document Type”,

“Document No.”,”Line No.”, “Shortcut Dimension 1 Code”, “Shortcut Dimension 2 Code”);

New Code

Table 37, function CreateDim()

SourceCodeSetup.GET;

TableID[1] := Type1;

No[1] := No1;

TableID[2] := Type2;

No[2] := No2;

TableID[3] := Type3;

No[3] := No3;

“Shortcut Dimension 1 Code” := ”;

“Shortcut Dimension 2 Code” := ”;

GetSalesHeader;

“Dimension ID” :=  DimMgt.GetDefaultDimID(

TableID,No,SourceCodeSetup.Sales, “Shortcut Dimension 1 Code”,

“Shortcut Dimension 2 Code”, SalesHeader.”Dimension ID”, DATABASE::”Sales Header”);