The Problem
Eldercare uses their data warehouse to create reports that will enable them to measure certain KPIs such as staff, budget and bed occupancy at each of their sites. They already have an existing ETL process in place, however, their current process of loading the data from various sources is very complex and involves approximately 5-6 complex SSIS packages. Queue complexity to modify and A LOT of time.
The goal of simplifying the ingestion of the data into the data warehouse (including making the process more efficient), meant that an entirely new process needed to be created to ensure it was scalable, time-efficient and future-proof.
The solution also needed to be dynamic or flexible enough to support any future optimisations and extensions. Moreover, it needed to be easier to maintain and require less debugging efforts if issues were to arise in the future.
The Solution
exposé was engaged to design and develop a new ETL process that was simpler and more robust but still had the same functionality as their current process. exposé worked closely with the client’s data engineer and data analyst to determine the initial scope and requirements of the project.
Some of the database tables were identified to be part of the initial development but along the way, exposé found that additional tables needed to be added to supplement the information from the tables that were part of the original scope.
exposé made sure that historical data was handled properly by the new solution since the client will also build future reports that will use it.
exposé leveraged Microsoft SQL Server Integration Services and Microsoft SQL Server to build the necessary packages and database objects to cleanse, transform and load the customer’s data into their data warehouse. Certain rules and naming conventions were also followed to maintain consistency and transparency.
The Business Benefits
The work which exposé has undertaken helped Eldercare manage their data loads with minimal time and fewer resources. It will also require less effort from a maintenance perspective since it will be much simpler to debug or trace any issues that may happen in the future.
The design of the ETL process meant that future optimisations and extensions are fully supported and would be much simpler to implement.
Additionally, the solution will also improve data quality and will eliminate risks of data inaccuracy and incompleteness. The surface area of risk for any given load is reduced since it will add or modify less data.
Moreover, historical data will now be preserved by only loading new and changed data to the solution, and the data in the destination database will stay intact.
To Download a copy of this Case Study Click HERE