After running the integration successfully, the user found that the integration did not update existing transactions, but rather created duplicates.
In the Solver Data Warehouse, data must be unique in order to be stored in the database. The application defines the uniqueness of these transactions based on a system column named UniqueDims.
The UniqueDims is a constraint that is a calculated column, it gets populated when a record is inserted. This column contains hashed values that are computed based on the dimensionalities associated with each transaction. The hash is a combination of all of dimension values plus the Transaction ID in a module. Each dimension is a part of the UniqueDims, therefore, editing / adding / or removing any dimensions that is associated to the existing transactions will cause the application to generate new transactions.
Note: Changes or adjustments made in the source (ERP) will either (1) update the data in the DW, or (2) create new transactions depending on if the change is a dimension value or a module attribute:
- Dimension: will create new transactions (aka “duplicates” in the DW)
- Module Attribute: will update existing transactions and replace the existing value with the updated value
Using the below example, you can see that the dimension Account, Servizi, and Tipo Contractt have updated values in each transaction. The application will create new transactions based on these changes in dimensionalities.
If you find duplicate transactions in your report in drill down or Data Explorer, compare the dimensions of these duplicates. You should find at least one dimension of these transactions are different, either with different values, or one of the transactions has a blank value for this particular dimension.
If these transactions are brought in via integrations, likely the integration has been modified and one or more of the dimensions in integration mapping is either removed or updated.
Once you delete the unwanted transactions in Data Explorer and fix the integration, you can re-run the integration to bring in the correct transactions. You can also add the Delete Data business rule as the first step of your scheduled job to ensure "old" transactions are cleared out prior to each scheduled run.
If you are unable to find the problemed area, please reach out to Support.