Unifying Group MI Reporting
Mission Statement
Our client is an investor services group and grew rapidly by acquiring various small organisations.
The Group then naturally wanted to consolidate financial management reporting across the various organisations to provide a complete picture for Management Information purposes. As each organisation had different business systems, procedures and datasets, their challenge was for each of the organisations to report in a common framework.
Initially our client attempted to do this by issuing the same standard Excel sheet to each organisation, who would then complete the information in a uniformed way. However, they experienced difficulties with this solution, as each organisation had differing ways of understanding, preparing and reporting their information, leading to inaccurate and incorrect data being reported, thus leading to a lack of confidence in the data.
Our goal was to implement a process that would allow each organisation to report their information in their own way, then combine the information from the different organisations into one central location and common reporting framework - thus allowing the Group Management to access one accurate set of reports.
Tools Used
Alteryx
- Alteryx Server
- Alteryx Designer
Detailed Solution
Firstly we asked each organisation to download their raw system reports from their business systems without any adjustments or changes. These reports, called “Templates”, were the starting point.
We then worked with the client to understand how we can translate each of the individual organisation’s Templates into a common framework, so that the data can be sent to a Data Warehouse in the same format and structure for each organisation. This meant that each organisation did not have to make any adjustments to their downloaded Template reports.
Once we were confident that we had mapped each of the organisations Templates to a common framework, we then ran each of the Templates through an Alteryx workflow that we developed. This workflow performed several actions:
Firstly, the workflow has a built-in submission validation which checked for two problems:
Are all the field names the same and in the correct place? This ensures no adjustments have been made which would then create errors in the following calculations. If an error occurred, the workflow would stop immediately and inform the user of the error(s).
A data check to make sure that dates were in Date Fields, numbers were in Numeric Fields and text were in Text Fields. As above, this ensures no errors further down the line. If any fields contained incorrect data types, this would create an exception report for the user to clean up and run the workflow again.
Secondly, the workflow performed necessary changes and calculations to translate the data from the Template to the Common Framework, ready for the Data Warehouse to accept the information.
At the end of the workflow, there was a check to ensure this data was not being stored in duplicate. If the data from this organisation had already been sent, then that data was overwritten by the new report.
Below you can see a diagram of the process, which is run for each individual organisation within the larger group.
Detailed Solution
Once the data had been run through the Alteryx workflow, it was then loaded into the Group’s Data Warehouse in order for it to be combined with reports from other organisations within the Group.
There is then a final check to ensure all organisations have submitted their data to ensure a full dataset. If this was not the case, the relevant person received an automated email reminding them to complete their reporting.
This information was then output into a Power BI dashboard for the Group’s Management team to give them a true picture of how the individual businesses were performing and allow business decisions to be made on validated and correct reporting data.