Data Warehouse
One of the main projects I have been involved with since the summer of 2006 is the design and development of a campus Data Warehouse for tracking institutional data and performing strategic analysis to assist administration with decision making and planning.
The DataMart is comprised of many suporting components:
Fact Qualifier Matrix (FQM)
Once the Strategic plan is in place, the FQM is used to bring particular measures into focus by identifying the specific items that collectively indicate a strategic measure. These items are then defined and base level data is identified to support the defined items. FQM’s for the bases for KPI’s (Key Performance Indicators).
Data Modeling
Data modeling is basically architecting a logical DataMart on paper (using common business terms) that can answer the questions from the FQM by extracting data from heterogeneous sources and transforming the data into a new holistic homogenous data the source. The Entity Relationship Diagram (ERD) is used to architect the table and field level relationships.
Analytics
Once the DataMart schema is constructed and populated, business analytic reports can be created. We have used Microsoft Power BI for most of the campus reporting needs. Data is separated into fact tables and dimension tables. The dimensions are then linked to facts and all distinct combinations are queried and stored in the reports analytic model. The data set can also be accessed as a pivot table or pivot chart, where one can drill into or drill through various dimensions and compare the underlying facts based on the various dimension values. Some data savvy users opt to import the data into various tools such as Excel or statistical packages.
SaaS/PaaS (The Cloud)
In 2021 we completed a full migration of the data warehouse to the Azure cloud. This involved transposing databases to schemas and moving from an enterprise instance of SQL Server to a single database in Azure. The results were notably positive, spring boarding Power BI reporting and broadening the reach of analytics on campus.
Challenges
One of the main challenges came out of a request to ensure reports would be consistent, independent of when the report is being viewed. To achieve this base line snapshot type functionality, a process was developed to store history at a per-day level. Simply put this would allow us to retain the last record entry on a given day regardless of action type (add, insert, update, or delete).