Star Schema basics

Star Schema is the "go to" modelling approach used in Power BI. This article covers:

1) What is a Star Schema

2) Bus Matrix and Star Schema

3) Key Observations

---

What is a Star Schema

A Star Schema is the application of dimensional modelling techniques to relational databases. This covers a wide range of techniques which, in a basic form, can be made to look like a star:

There is a Fact table is in the middle surrounded by Dimension tables where:

• Facts contain the observations and events (measurements)

• Dimensions contain the information used to explain those facts (context)

Rarely you'll see a star like this in practice and which has led many to claiming that star schema is not relevant; they are wrong. Dimensional modelling is more than just its most basic form and covers the full bredth of techniques such as Snowflaking, Degenerate Dimensions and Role Playing Dimenesions. As such it is relevant in most cases and when they're not being adhered to, it's generally a case of bad practice.

---

Bus Matrix and Star Schema

The below is a Bus Matrix example from the Functional Requirements article:

Hospital Bus Matrix:

Process	Measure	Hospital	Department	Date
Patients	In (Arriving)	X	X	X
Patients	Out (Leaving)	X	X	X

Context Detail:

Context	Attribute	Example
Hospital	Hospital ID	100010
Hospital	Hospital Name	Kingston-Upon-Tames
Hospital	Hospital Longitude	51.41478942284898
Hospital	Hospital Latitude	-0.28272796710927767
Department	Department ID	70020
Department	Department Name	Maternity
Date	Date	23rd Feb 2022
Date	Month	February
Date	Year	2022

From the above we can derive our Star Schema:

Hospital Star Schema

The fact table gets its structure from the bus matrix:

• One column for each context columns: - date, hospital and department

• One column for each measure row - In (Arriving) & Out (Leaving)

There is a separate dimension for each context - date, hospital and department - and they get their columns from the associated "attribute" in the "context detail" table.

---

Key Observations

It doesn't look like a star

In practice, a basic Star Schema rarely look like a star with 5 dimensions neatly surrounding our fact. Regardless of how your model looks, if you employee dimensional modelling techniques in Power BI you're still adopting star schema principles.

One to Many

There's a single record for each dimension, but this record can appear multiple times in the fact. For instances, the department dimension features one record for maternity (70200), but this appears 4 times on the fact table. For this reason on the Fact tables (those on the many side of the relationship) tend:

To be deep in terms of the number of rows, but narrow in terms of the number of columns

Kimball and Ross the Data Warehouse toolkit 3rd edition

In contrast, Dimensions (on the one side of the relationship) tend :

To have fewer rows than fact tables, but can be wide with many larger text columns

Kimball and Ross the Data Warehouse toolkit 3rd edition

The reason for many columns in the dimension is because there can be many descriptive attributes of each dimension. For instance, the date dimension could also contain attributes such as Quarter, Comparison Date for Last Year or a Current Date Flag.

Filter from one side to the many

When providing context to a measure, the filter is applied from the one-side of the relationship. For instance, when "23rd Feb 2022" is filtered on the one-side, all assosiated records in the fact table that contain that date are filtered:

Multiple filters can be applied simultaneously

As well as filtering on "23rd Feb 2022" , filters can be applied on department "Maternity":

No built in bias

There are no pre-computed answers in the fact table; the user can filter from any direction and use as many (or as few) as they desire. This gives the flexibility of the model. Therefore, we say that say that:

The dimensional model has no built-in bias regarding expected query patterns

Kimball and Ross the Data Warehouse toolkit 3rd edition

Not Always obvious what's a Fact and what's a Dimension

In the Example, it is clear-cut how we should categorise. However, say we wanted to consider the "Number of private rooms in a department". This could mean:

1. It is part of the department dimension. For instance, we want to measure the number of in and out patients in departments that have no private rooms vs those that have 1-5 private rooms.

2. It is a new measure - we want to track the number of private rooms across time

Thereford, it is important to be very specific with users when understanding thier requirements and seel clarification wherever necessary.

Very easy to understand

No Ambiguity

Very fast

Numbers in Fact and strings in dimensions

Relationships indicate business rules

Number of patients arriving can be explained by a single hospital.

---

Conclusion

We've covered the fundamental building blocks of Star Schema. These remain relevant regardless of the process you're trying to model. However, the problems you encounter are likely to be significantly more complex. As such, you'll need to supplement your understanding of basic Star Schema modelling with range of more advanced concepts. Once understood, you then are able to apply the relevant concepts to the desired business process.