Our Customer, a Fortune 500 medical solutions company, uses a scorecard with various KPIs to report and evaluate the Purchase-to-Pay process performance in the organization. Created on a monthly basis, it contains data from various sources. The process of gathering data for the scorecard preparation was entirely manual and the reports were produced in Microsoft Excel. This was a highly inefficient process, prone to errors and manipulations.
Our Customer made the decision to automate the existing process with a new Business Intelligence (BI) reporting solution. The solution had to utilize the existing data warehouse and Microsoft BI infrastructure.
What We Did:
IntelliCo Solutions was initially requested to perform data source analysis and high level BI solution design, based on the existing manual scorecard business process requirements. Satisfied with the initial deliverables, our Customer decided to extend the task, and we were asked to implement the already designed solution. This included building the ETL processes and the reporting layer.
In the initial phase of the project, a senior consultant with software architecture experience created the high level solution design. It was partially based on already existing business requirements, however, these did not cover 100% of the manual scorecard solution, and to get the full picture we had to analyze the source data, review the manual dashboard process, and conduct interviews with key stakeholders. Based on this analytical work, we created a high level design document which covered the proposed architecture (ETL, data warehouse and a reporting layer).
In the second phase, we secured an experienced Microsoft BI (MS SQL Integration, Analytics and Reporting Services) consultant to implement the designed solution. This included the creation of two databases – a staging one and a data warehouse. When the databases were created, we developed ETL (extract, transform and load) jobs in Microsoft SQL Server Integration Services to populate both databases. We used Microsoft SQL Server Reporting Services to create the required reports and sub-reports, integrated the reports with the rest of the solution, and developed advanced filtering for a better user experience. Finally, we developed the scorecard report and performed integration and user acceptance tests on every component of the solution.
We also prepared the technical and user documentation which was handed over to the Customer. We conducted complete training with relevant knowledge transfer, and provided ad-hoc support during a hyper-care period of 2 months after the solution went live.
The Result:
The final solution met the expectations of the company and is now the primary tool for Purchase-to-Pay performance reporting, providing correct data in an efficient manner. The Customer also considers extending this solution to other business regions, potentially on a global scale.