Data Warehouse Dimensional Modeling and Conformed Dimensions

by Pearlin Devasahayam

Fact Diagram

The key concept of any Data Warehouse is to show a single version of truth across an organization to help in decision making and forecasting. In order to support robust analytics it is important to put emphasis on data modeling.  The Enterprise Data and Business Intelligence (EDBI) team uses Kimball’s dimensional modeling approach, which represents the data in a star schema structure using dimension and fact tables. It is called a star schema, as the diagram resembles a star with the fact table in the center and points of the star are the dimension tables.

Fact tables are the primary tables that store measures and metrics of a business process. Examples of measures in an Admission fact table are: count of applications, total amount, etc.  The dimensions provide descriptive details about the measures in the fact tables like, Who is the applicant? What is the degree applied? Where is that applicant from? What is the applicant’s ethnicity? The Admissions star schema will support analysis such as the number of applicants within a term, by degree, by location, by test score.

When building dimensions in a warehouse, it is key to build conformed dimensions that can be reused across multiple data models and integration projects. Conformed dimensions help to standardize the descriptive data across multiple facts and ensure consistent reporting across the organization.  The EDBI team has started building some of these conformed dimensions such as Date, Gender, Ethnicity, Academic Term, Geography etc.  These dimensions will be used for all future data warehouse integration projects, data mart, and multiple domains across the university

Student Application Details

 

Still need help?