Skip to main content

CONN Cloud Getting Started: Business Central v1

Cassie from Solver Support
  • Created

  • Updated

Summary:

This article details the Solver Cloud Business Central (BC) v1 Connector that is using ODatav4. For Business Central connector v2, please refer to this KB article: CONN CLoud Getting Started: Business Central v2 Connector. Dynamics 365 Business Central incorporates a ”Dimension Set” feature that is a unique combination of dimension values. The DimensionSetID 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 rather than the DimensionSetID. The use of this business rule requires pre-configuration and is described in this document.

Article Sections:

More Information:

Business Rules for D365BC Implementation 

The following rules can be found in the bottom attachment section in this KB:

DimensionSet_WithExceptions - This rule is for D365BC customers to populate dimension codes based on DimensionSetID.

CreateGLData - This is rule will create GL Summary data based off of GL Detail data.

Exposing the OData Endpoint

The first step is to expose the OData endpoint for the Dimension Set Entries object. 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 toolbar, then click New Document > Create Data Set.
  4.  Type a name and click the ellipsis (…) to look up the Data Source Id 479.
  5. Click Publish, then click Finish.

This completes the configuration of the Dimension Set Entries OData endpoint in Business Central.

Configuring Your Integration

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

Standard Setup for Business Central Integrations

There are multiple steps required due to the way Business Central presents the data in its endpoints. Our recommended setup will help create a smooth BC implementation.

  1. Load in Dimension Set information into a staging dimension. This dimension does not need to be mapped to any modules in Module Schema
  2. Load in Dimension Codes
    • This process will utilize Task Filtering to parse out BC dimension codes to their respective Solver dimensions
  3. Load in Transactional information with only the DimensionSetID value mapped to an attribute
    • Most users are loading only GL Detail data into the Data Warehouse and using a business rule to create GL Summary data
  4. Run the Solver_DimensionSet_WithException business rule to replace the DimensionSetID in the transaction table with operational codes associated with the DimensionSetID
  5. Run the createGLData business rule to create GL Summary data from GL Detail data

DimensionSetID Integration

The DimensoinSetID is required to load into Solver in order for our business rule to populate the respective dimensions based off of the DimensionSetID. 

  1. Log in to Solver > Data Warehouse > Data > Add New > select D365 Business Central
  2. Type in the login credentials, then click Save
  3. Create a New Task
  4. From Source Table find the DimensionSet endpoint that was created above
  5. Under Target Table, choose New Dimension
  6. Provide a friendly name for the staging dimension
  7. Copy the following text into the Code field in Target Mapping:
    [Dimension_Set_ID]+[Dimension_Code], this is to ensure each entry is unique
  8. Drag Dimension_Set_ID from Source to Click Here. In the Data Type field, choose Integer. 
  9. Drag Dimension_Code from source to CLICK HERE. In data type field, choose Text.
  10. Drag DimensionvalueCode from source to CLICK HERE. In data type field, choose Text.
  11. The page should look similar to the below image. 
  12. Click SAVE and publish the integration.
  13. 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 the Dimensions (Object id 560) endpoint. The user will need to expose this endpoint in D365BC and load the dimensional data into each dimension within Solver. Below is an example of the data contains in the 560 endpoint.
mceclip0.png

As you can see from the above screenshot, all dimension codes are stored in the same endpoint. The following process will detail how to utilize the Task Filtering feature to set up your dimension load from Business Central in order to remove unnecessary dimensions codes. 

  1. Create an OData endpoint in D365BC that contains your dimension codes. See Expose D365BC OData Objects, earlier in this document for additional information.
  2. Using the Solver D365BC connector, set up dimension integrations to load data from the above-mentioned OData object for each dimension. We will use DEPARTMENT as an example.
  3. Drag and drop Code and Name from source to map in Code and Description in target in the integration mapping page. Click Continue to proceed to the Task Filtering page.
    mceclip1.png
  4. Click Continue. You will be directed to the Task Filtering page. 
  5. Drag in Dimension_Code from Source (left) to Target (right). Input the dimension code you are looking to filter. We are using DEPARTMENT as an example.
    mceclip1.png
  6. By clicking the Preview button on the top right corner, the application will return the filtered result.mceclip0.png
  7. Click Save to proceed and save the integration.
  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.

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 MANAGER, 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 DimensionSet_WithExceptions business rule is available for download in the bottom attachment section in this KB. This rule will populate the transactional data with dimension values based on the associated DimensionSetID.

  1. After downloading the business rule from this KB, import the rule via Data Warehouse > Data > Rules.
  2. Run the rule.
    • DimensionSet: Select the staging dimension you defined as dimension set.
    • Module: Select the module where the transactional information must be updated.
    • Periods: Select the periods that rule should be ran for.
    • Scenario: Select the scenario the transaction information belongs to.
    • Excluded Dimensions: Select all dimensions that is not part of the DimensionSetID, with the exception of Staging Dimension, Account, Scenario, Period.
      mceclip2.png
  3. Click Run.
  4. When the rule process completes, check Data Explorer to validate all data. You should now see your transactional data has dimensional values that corresponds to its 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.

Login Credential for Business Central

This section details the required field needed to create a Solver Cloud integration using the D365BC connector. 

NOTE: Solver recommends creating a web service user for integration use. Also, Multi-factor authentication (MFA) is not support for logins. 

Follow the below guidelines to input values for each field. 

  • OData URL: This is the URL found on the Web Services page. Solver Cloud only needs up to the ...ODatav4/, so remove anything after the forward slash. It should look something like this: (https://api.businesscentral.dynamics.com/v2.0//ODataV4/)
  • Username: You only need the username, do not add the domain.
  • Web Service Access Key: This is the key generated from Web Service Access Key Section in the Users page under your web service user's profile.
  • Company Name: This is the name of the company for which data should be brought in. This is a case sensitive field.

mceclip0.png

mceclip0.pngmceclip2.png

Note: D365BC has a 30 characters limit for company name. Any name exceeds 30 characters will result in the below error.

Exporting a List of OData Objects to Excel

  1. Log on to D365 BC, in the search bar, type: Web Services and click the result.
  2. Click Actions on the tool bar and then click New Document Create Data Set.
  3. The Reporting Data Setup wizard should appear, click Next 
  4. Choose Create a new data set, click Next
  5. Type a name and click the ellipsis … to look up the Data Source Id.
  6. Click Page at the top-left and click Open in Excel. This will allow you to export all endpoints into Excel.

Properties:

Cloud


Related articles

Powered by Zendesk