Data reconciliation is a verification process to ensure that the data-migration or ETL process has transferred the data correctly from source to target by comparing the target data against the original source.
This as we know can be a very challenging process as most data migrations imply a transformation of data as it is moved from source to target system. In short data are not the same!
documentHowever, at key critical entities, the project should be able to account for how data has been transformed ant to duccument that all has been moved correctly (as expected).
Typically separate independent processes are designed to facilitate such reconciliation.
Power BI can ensure this process is done reliably and effectively in three main ways.
The first is Automation, Power BI has its own generic ETL tool, called Power Query.
Here data can be triggered to load both from the source system and the target system. This provides automation so the process can be done frequently, quickly reliably.
The second would be producing a finalised report.
Here one set of results could be produced so this can be reviewed jointly. This will highlight any differences in the data source system and the target system This report can also account for validation rules or rules incorporated that are not visible in the source but are in the target.
For example, in the source, we may see a Product Type which is categorised as "Accounts" with one record associated with this Product Type but the target Product Type has been aggregated into just "Products" and therefore we may see "Accounts" along with "Cards" and "Customers" under Products.
The finalised report could be developed to take this event into consideration, therefore highlighting where in the migration process this has happened.
Sufficient data granularity.
As we know many organisations accumulate millions of records over time, as such Power BI has the capability to handle such loads. Here we are not forced to work with aggregated data which ultimately makes the reconciliation process much more reliable.