Solver Integrations for Additional Fields
The Solver Data Warehouse is a Microsoft Azure SQL database that stores data for reporting and planning. It is composed of modules, like General Ledger and Personnel, and dimensions, like Accounts and Employees. Your source systems provide the data. However, there are times when you need more data than your source system provides. This article is going to discuss how to handle that situation in the Data Warehouse.
One advantage of the Data Warehouse is the ability to add value to your data. For example, your chart of accounts may have an Account Category column. Values you might see in that column are Cash and Equivalents, Sales, and Cost of Goods Sold. However, you may need to have different groupings for different reports. You may have reports for internal use, banks, and auditors. In each case, the groupings of accounts could be different. Enter the Data Warehouse!
With the Data Warehouse, you can add additional fields in your account dimension that don’t come from your source system. Here’s an example of an account dimension that pulls data from Dynamics 365.
Notice that the External Reporting column is not stored in Dynamics 365 and is only maintained in Solver. To update the data in Solver, you can import a spreadsheet with values as one option. However, you don’t want to worry about your scheduled integration overwriting that field.
The good news is it won’t!
When you create an integration in Solver, you only map the fields that you want to update, as shown in this next image.
See the External Reporting field on the bottom right? There’s nothing in the Mapping Expression. The absence of an expression means the integration from Dynamics 365 will not change the values in the accounts.
You could have two integrations:
- An account integration from Dynamics 365 that populates fields available in 365
- An external reporting integration from a spreadsheet that populates the External Reporting field
When creating integrations, only map the fields you want to update. The integration, when run, will only update those mapped fields and won’t update the other fields.
For additional assistance, reach out to your BKD Trusted Advisor™ or complete the Contact Us form below.