By default, the extensions in the Data Model Gallery come with the first two Global Dimensions enabled. It is possible to have additional dimensions configured in Business Central, however, and to leverage these they will need to be added into the Data Model since they differ for each customer and are based on what you have named the dimensions. This article will cover how to add these additional dimensions into your data model.
Add the Dimension Set Table and Fields in the Staging Area
The Dimension Set Entry table in Business Central contains all the dimension values attached to a transaction. If this table hasn't been added yet, you will want to add it.
To add the table, navigate to the Staging section in Data Management, search for the Dimension Set Entry table in the "Select Tables" section, and check the box. You will then expand the Dimension Set Entry table in the "Select Fields" section and check the boxes for the following fields:
- Dimension Code
- Dimension Set ID
- Dimension Value Code
Once the fields have been brought in from the Dimension Set Entry table you will want to go into any of the transaction tables that you would like to see the additional dimensions for and make sure the Dimension Set ID field has been added to each one. In the example below we will be adding the Area dimension to the G/L Entry table.
Add Lookups to Bring the Dimension Values into the Transaction Tables
Once the Dimension Set Entry table has been added, along with the Dimension Set ID field in all the relevant transaction tables, you can add a lookup to bring in the dimension value (e.g. the Area code associated with the transaction).
To add the lookup, navigate to the Transformation section and select the table that you want to add the dimension code to. In this example we will use the G/L Entry table. Click the "+" to the right of the table name and select Lookup.
This will create a field named "Lookup" in the table. Click on the field name and then click on the pencil icon to the right of the name to rename it.
Next click the pencil icon next to the Target: No Selection to pick the field you want to bring into the table.
You will then navigate to the Dimension Set Entry table and select the Dimension Value Code field.
You will then add two lookup mappings for:
- Dimension Set ID = Dimension Set ID
- Company = Company
This will match up the Dimension Set ID fields and the Company field between the two tables.
Lastly, you will define a hard-coded filter for the dimension that you want to bring in. In our example, this dimension code is named "AREA", but you will want to type in the name of the dimension in your Business Central tenant that you are looking to return. To add this, leave the left column blank, select the Dimension Code field in the right column, and then type in the dimension name on the left (in this example, "AREA").
Add the Dimension Fields into the Data Model
Now that the lookup transformations have been added, the last step before checking in your changes is to add the field into the Data Model for the users. To do this, navigate to the Data Model section, find the table you want to add the new dimension field to (in our example, the GL Transactions table), and click the "+" button to the right of the table name to add a new field.
Give the field a name, select the appropriate data type, and click Create.
Lastly, find the field in the mapping pane in the right, click the empty section, and select the lookup field added in the previous step (in our example it is the Area Dimension field).
Check in the Changes and Publish/Run the Pipeline