Dimensional modeling

Now that we have provided sufficient background as to why for the purposes of our analytics, a cloud data warehouse is necessary, let us look at how data is organized in that warehouse.

Data in the warehouse must be structured in such a way that it can be used efficiently for the purpose of analytics. If you implement best practices around dimensional modeling and star schema, your data will be better organized, easier to understand, and easier to work with when creating your reports and dashboards.

Let us start our discussion of dimensional modeling by reminding ourselves of our definition:

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.

The techniques that comprise dimensional modeling exist to measure business processes. For us in K12 education this can refer to student assessment, attendance, enrollment, discipline, grades, and so on. When data is grouped under specific business processes, we call those data marts. For each process, there are two components: measurements and additional context related to those things being measured. These two things are our facts and dimensions. When you apply dimensional modeling to a data warehouse such as BigQuery, you have implemented a star schema.

It is called a star schema because when you have a fact surrounded by dimensions and well, it looks like a star.

In the proceeding course, you will run code that extracts data from your Ed-Fi API, loads it into your data lake (more on that in the next course), and transforms it into various data marts all with their respective facts and dimensions under it. Let us look at a few examples of the facts and dims you will create and explain why they are structured in the way that they are.