Working with DimensionSets

Overview

Dynamics 365 Business Central incorporates a ”Dimension Set” feature that is a unique combination of dimension values. The dimension set ID is used in many OData endpoints to define the dimensions of the data without listing the dimension(s) in the OData endpoint itself. Since the dimension set is just an ID and not the actual dimension codes, Solver has created a business rule to transform the data after it has been loaded into Solver so that the end result is the dimensional codes that define the data instead of the DimensionSetID.

The use of this business rule does require some preconfiguration and is described in this document.

Exposing the OData Endpoint

The first step is to expose the OData endpoint. This is required in order for the dimension set data to be loaded into Solver .

  1. Log in to D365 BC. https://businesscentral.dynamics.com/
  2. In the search bar, type: Web Services and click the result.
  3. Click Actions on the tool bar and then click New Document > Create Data Set.
  4.  Type a name and click the ellipsis … to look up the Data Source Id 479.
  6. Click Publish and then click Finish.

This completes the configuraiton of the DimensionSetID OData endpoint on the Business Central.

Configuring Your Integration

In this section we will detail steps needed on the Solver side. This section assume that user is familiar with Solver interface, in particular, the integration configuration. Please reference the Solver User Guide for addiitonal information. 

There are multiple steps required due to the way that Business Central presents the data. These steps are. 

  1. Load your DimensionSet information into a dimension. This dimension does not need to be mapped to the module in Module Schema
  2. Load your DimensionCodes
    1. This process will
  3. Load your Transactional information with only the DimensionSetID value mapped to an attribute
    1. Most users are loading only GL Detail into the data warehouse and using a business rule to create summary
  4. Run two business rule that transforms this data.  It is required that your configuration is completed as described above
    1. The first business rule will clean up your dimension. If you ran just your dimension load, you will notice that each dimension has a listing of all dimension codes. This business rule deletes unnecessary dimension from the resepctive dimension
    2. The second rule will replace the DimensionSetID that exists on your transaction table after import into the operational codes associated with the dimension set
    3. Run the third rule that will create GL Summary from GL Detail. 

DimensionSetID Task

The DimensoinSet ID task is required to load into Solver in order for our business rule to populate the respective dimensions of a particular dimension set ID. 

  1. Log in to Solver>> Data Warehouse >> Data >> Add New and select D365 Business Central.
  2. Type in your login credentials.
  3. Create a New Task.
  4. From source table find the DimensionSet endpoint that was created above.
  5. Under the TARGET TABLE choose New Dimension.
  6. Give this target table a name
  7. Copy the following text into the code field on the Target mapping: [Dimension_Set_ID]+[Dimension_Code]
  8. Drag Dimension_Set_ID from source to CLICK HERE. In data type field, choose Integer. 

    Drag Dimension_Code from source to CLICK HERE. In data type field, choose Text.
     
  9. Drag DimensionvalueCode from source to CLICK HERE. In data type field, choose Text.
  10. The page should look similar to the below image. 
  11. Click SAVE and publish the integration.
  12. Returning back to the integration page, you can now run this integration. This will load all dimensionsets into your database.  

Dimension Data Task

User will also need to load in the dimensional data for example Department, Area and Sales. Business Central stores all dimensional data in Dimensions (ID 560) endpoint. The user will need to expose this endpoint in D365 BC and load the dimensional data into each dimension within Solver. Note that all dimensional data across all dimensions will be imported into each Solver dimension. A business rule will be used to clean the dimension up. 

Please note: All dimension label names in Solver should match the original name in D365 BC. In the preview of dimensional data, the label name is in the Dimension_code column. The label name is not case sensitive but should not contain any special characters include space.
 

Load Dimensional Data

D365 BC has a limitation when connecting to the Dimensions table where it does not accept filters from Solver. As a result, all dimension codes are imported into every dimension. For example, Department codes in the Currency dimension. The following process will detail how to setup your dimension load from Business Central in order to utilize a business rule to remove unnecessary dimensions later. 

  1. Create an OData endpoint in D365 that contains your dimension codes. See Expose D365 BC OData Objects, earlier in this document for additional information.
  2. Using the Solver D365 BC connector, load data from the above-mentioned OData object into each dimension, i.e. Department in the example below.
  3. Drag-and-drop the Name from the source to the Code in the Data Warehouse target.
  4. In the target area, click to edit the mapping expression.
    • Append an underscore and <FirstCharacterOfDimensionName> to the Code. The formula to achieve this is: [Code]+’_’+left([Dimension_Code],1)

       NOTE: This is the first step to handle use cases where a code value can exist across multiple dimensions. A business rule is imported to complete the process.
  5. Create a dimension attribute that will be the same across all dimensions. In this case the attribute DimensionName is created.
  6. Map Dimension_Code to this attribute.
  7. Click Save 
  8. Repeat these steps for each dimension task.
  9. IMPORTANT: Multiple tasks can be defined in a single integration. However, best practice is to create a separate integration and task for your module/data load. Because the dimension codes are currently out-of-sync with your source data, a data load will fail until the final step: running a business rule, is completed.
  10. Navigate to Overview > Dimensions to review the results of your integration.

At this point you have a configured integration that is loading in dimension codes. Running the integration, you will see that data is coming in with “code_x”. For example, your departments include _D, while currency codes include _C.

To correct this, we have a business rule available on the support site called BI360DW_D365_DimUpdater. This rule will update the data properly, removing unnecessary codes and removing the “_<Character>” values.

 

Update Dimension Codes with a Rule

Follow the steps below to update your dimension codes. 

  1. To import the rule:
    • Download the business rule from the support site.
    • In Solver Cloud, navigate to Data Warehouse > Data > Rules. A listing of all imported rules will appear.
    • In the upper right-hand corner, click Import .
    • Select the previously downloaded rule and click Open. The rule should import successfully.
  2. Select the new rule in the list and click Run.
    • In the popup that appears, select the Attribute name that you created for each dimension.
    • The rule will process.
  3. Navigate back to Overview > Dimensions to review your dimension codes. Your data should look correct.

Be sure to run the rule after loading dimensional data. At this point, after running the rule you can start loading in the transactional data such as General Ledger Detail. 

 

Transactional Data Task

You are now ready to import transactional data. Unlike other integrations, D365 BC requires the module schema is configured before hand via the Module Schema page. These dimensions need to be created in Solver first either via the Data Warehouse >> Settings >> Dimensions page or via an integration as done above. 

If user choose to create the dimensions via the Dimensions menu, you must also add these dimensions to the Module via the Module Schema page. The next section will discuss loading in General Ledger information

  1. Configure a BC integration, this time choosing General Ledger and Module. 
  2. In TASK MANGER, if you did not previously configure the design of the General Ledger with all of the required dimensions via the Module Schema page, assign by clicking the Checkbox of the dimension(s) that should be associated to this module. click all dimensions from dimension set which needs to be associated with this module. And click Continue. 
  3. In task mapping, map only the Period, Account and any attribute information such as Amount into the respective target fields. 
  4. Drag Dimension_Set_ID from source field to be a New field, and data type choose integer. 
  5. Click SAVE,
    • We did not map any dimension fields as they will be populated by the business rule. 
  6. Publish this integration and run this integration.
  7. Check the Date explorer and you will see all dimension column are blank.

At this point we have the transaction data loaded in with only the dimension set ID. This ID is associated to a combination of dimensional information that we must assign to the transactions. This is where the business rule is needed.

Incorporate the DimensionSet Business Rule

The business rule is available for download on the Solver support site and is called DimensionSet. This rule will update the data properly.

  1. After downloading teh Business Rule, import the rule via Data Warehouse >> Data >> Rules.
  2. Run the rule.
    1. DimensionSet: Select the dimension that you defined as dimesnion set.
    2. Module: Select the module where the transactional information must be updated.
    3. Periods: select the periods that rule should be ran for.
    4. Click Run
    5. After the rule has been completed, check Data Explorer to validate all data. Click Lookup for each parameter. dimension Set and choose the dimension user import dimension set data in.
    6. The end result will be the dimensional data and the corresponding DimensionSetID. 

     

    You have now created your first integration to load Business Central data that contains a DimensionSetID value and have converted it into its operational codes within Solver.