Built a data warehouse platform to support future growth

Built a data warehouse and BI reporting platform to support future growth

 

Customer Challenge

A Washington state university needed support to build a data warehouse platform to support the university; the solution needed to be scalable to support future growth. Additionally, they wanted to create BI reporting platform that could be leveraged by the executive board of the university. Stability, agility, and flexibility were critical to the success of the project.

 

Logic20/20 solution:

Logic20/20 performed an in-depth assessment to learn about the current state of the new data warehousing platform, including current setup and functionality, pain points, and important metrics. The team worked with the internal IT department to make development recommendations, create an environment of continuous integration, and create and deploy the business intelligence dashboards.

 

Solution breakdown: the challenges and interesting bits:

Logic20/20’s solution can be broken down into several core components – here’s how we tackled the challenge.

 

Challenge 1: Update the data warehouse without breaking reporting

To minimize reporting components that could be affected by changes to the EDW, the team defined the exact set of reporting tables and tested those tables for changes every time new code was added to the database. Tests prevented disruption to the end users by ensuring that breaking changes would not be released to reporting tables unless explicitly approved.

 

Challenge 2: Create a continuous integration environment

Increased speed of deployment and development by creating a continuous integration environment.

Generated automatic tests to run after each deployment to make sure that new code is not breaking the reporting platform.

Automated end-to-end data warehouse deployment, which enabled the ability to completely remap backend dependencies without negatively affecting customers.

Leveraged GitLab CI functionality that listens for new commits and automatically deploys the latest code into a test environment.

 

Challenge 3: Design, develop, and deploy BI dashboards on a short development cycle

Identified the top data models that had the highest value across the university (ie – used by the greatest number of departments).

Simplified the data models by combining similar tables and removing intermediate datasets from the user-facing data model. Data model simplifications decreased maintenance and training costs.

Incrementally added features to BI dashboards on a weekly sprint cycle with weekly client check-ins to assess progress and functionality and guide changes to the data model.

 

Technologies used:

Power BI

SQL server

GitLab Continuous Integrations