New Data Vault Architecture for Large State Agency Creates Central Repository and Enhanced Reporting
As part of a modernization effort, a large state agency sought to upgrade their infrastructure, streamline their business processes and enhance their reporting capabilities. While part of this effort entailed replacing their data architecture, they also sought a short-term solution to establish a BI platform and data model which would be scalable and adjust with their future needs. UDig’s Data Enablement team partnered with the agency over the course of several months to establish a data vault architecture and business intelligence reporting tool to meet their needs and position them for success as their requirements evolved.
STRATEGIC SNAPSHOT
Challenge
Legacy systems facing deprecation in the near future are located in multiple locations which makes it difficult for the agency to easily pull critical reports.
Strategy
Create current data warehouse structure, improve usability for business users and improve flexibility and scalability as future data plans evolve.
Outcome
Establish data vault architecture to function as central repository and enhance reporting capabilities through a simple BI platform to streamline information gathering process.
The engagement improves operational efficiency and reduces costs through eliminating previously manual reporting processes.
Challenge
The agency’s existing reporting structure pulled from multiple source systems and was mainframe focused. In addition to being decentralized, the five separate systems being utilized were slated for deprecation in the near future. Reports lacked one source of truth and integration, and were difficult to access. Users were required to submit requests to a team who would then manually build the reports with the information desired. This was both a time-consuming and cumbersome process and, in combination with the inflexible data warehouse in place, limited the agency’s ability to easily consume the information they sought in their reports.
Outcome
Following an agile methodology, we articulated requirements of the architecture design through developing user and non-user stories. We then completed data profiling on the source tables to become familiar with the data itself and designed a flexible architecture to meet the immediate needs of the agency while also accommodating any future modifications associated with their overall modernization effort.
We sought to consolidate the multiple systems being used and establish one source of truth for the agency’s reporting and as such, created a centralized data repository. We established a data vault architecture to replace the existing traditional reporting repository structure and in doing so, provided a more flexible and extensible system with improved ease of maintenance. The data vault was designed in a way that allows for easy modification to data as needs change over time. BI reporting capabilities through SQL became more efficient, as establishing one central repository for the data streamlined the overall reporting process. Users gained the ability to generate reports autonomously and without the need to request information from their support team.
The engagement improves operational efficiency and reduces costs through eliminating previously manual reporting processes. The enhanced reporting also provides increased transparency within the organization and enables better decision making with improved outcomes. Creating a view of the new source systems within the centralized repository improves the quality and overall integrity of the data itself, and the new architecture provides extensibility for the agency.
How We Did It
Tech Stack
- Tableau
- SQL Server
- SSIS
- T-SQL
- Apex SQL Search
- Visual Studio (SSDT)
