The Challenge:
The Client was in need of modernizing their data management infrastructure to be as efficient as possible. Reporting and analytics was a time intensive, manual process that required people to source data from multiple sources including Sharepoint, external partners, spreadsheets, etc. There was no integrated reporting available. Operational data was separate from financial data which was separate from their Quality data and all of these various data sources existed in silos without a natural way to tie the data all together.
Our Solution:
Our team implemented a data warehouse/BI environment in Azure using Azure Data Factory, Azure SQL, and Power BI. Analytical content in the form of reports and dashboards were made available internally and externally outside the organization via delivering Power BI content into a Sharepoint portal. We are using Power Automate to refresh the existing reports which provides a “no code” solution to address the well known limitations of scheduling in Power BI. We automated the collection and processing of data across the various source systems and combined that data into a set of reusable data assets that can be used to produce the necessary reports.
Result/Deliverables:
WD created 36 dashboards (including sub recipients with a multi-tenant security model to control data access) and 5 internal reports to replace the existing Excel based reporting. These new reports have rolling numbers and the ability to drill down into details to ensure that the data is automatically updated. We created a transparent data encryption (TDE) at the database level and then stitched together the encounter level data in a de-identified way to prevent any leakage of PII. Currently, this new system is processing over 15,000 encounters per month and the solution is set to gracefully scale to meet anticipated future demand.
Result/What the Client Says:
“The consultants from Waterloo Data delivered a reporting and data management solution that consolidates our encounter level data so we have a complete 360 degree view of our sub-grantees and their clinical and financial performance, and in doing so provides an easy way for us to do integrated reporting across our organization. Waterloo Data has set the standard for what we expect from our consulting vendors.” Erica Solis, Director of Service Operations, Every Body Texas
