We leverage dimensional modeling to design our data warehouse. This is what allows us to build a data warehouse that integrates data from multiple sources and provides a single source of truth for the organization. Here is a definition we like for dimensional modeling:

Dimensional modeling is a design approach optimized for analytic systems. A dimensional model captures how a process is measured. Data elements that represent measurements are called facts. Data elements that provide context for measurements are called dimensions. These elements are grouped into dimension tables and fact tables. Implemented in a relational database, the design is called a star schema.

— Christopher Adamson: Star Schema The Complete Reference

We can connect this to K12 data by using student school attendance as an example. Below you will see fct_student_school_attendance at the center. This is a fact table with one row per student per school year per instructional day enrolled. Each row for a student is tracking their day attendance status for a specific day. The fact table is surrounded by dimension tables that provide context for the attendance data. For example, the dim_student table provides information about the student such as their name, and whether or not they are an english language learner.

Attendance

Some BI platforms such as Microsoft Power BI and Lightdash work best with star schemas, and we’d recommend you connect these tables to them. The next section will talk about wide tables which tend to work best for BI platforms such as Google Looker Studio and Tableau.

FAQsWide Tables