3 min read

Learning the Star Schema

2021-05-18

Dimensional Modeling Design with the Star Schema

The purpose of this post is to quickly give the resources for learning the star schema. I have produced a star schema in the data warehouse from staged data, built a Sql Server Analysis Services (SSAS) tabular model, and created reports in Power BI (see Understand star schema and the importance for Power BI). Going through this process, the resources below were invaluable.

Dimensional modeling with a star schema was developed by Ralph Kimball. Although, the Kimball group is no longer active, their website has many key posts that will help you with the patterns and principles. Putting the Star Schema (Adamson) book in your library is the first step along the path. If you don’t have time to read it through, read Part I Fundamentals before you make any design decisions.

When your eyes get tired of reading, there are some must-watch, free video resources available:

  1. Free training SQLBI, [Introduction to Data Modeling for Power BI Video Course] (https://www.sqlbi.com/p/introduction-to-data-modeling-for-power-bi-video-course/).
  2. Three part series on YouTube, [ETL Architecture In-Depth - Dimensional Modelling 101] (https://www.youtube.com/watch?v=DspXXZrSVRk).
  3. Implementing Data Warehouse Patterns with Microsoft BI Tools

After about 40 hours of learning, you should have a foundation on which to start modeling. About 75% of the modeling will implement the fact table to dimension table relationship that is many-to-one. The other 25% of the model will be where you will spend your most time – remember to keep it simple and use the established pattern and practices, The 10 Essential Rules of Dimensional Modeling. The design patterns can be viewed as tools in your tool belt – you pull the correct one out and apply it.

The key to acquiring the correct tool when needed is to have some familiarity with what the tools are called so you can search and find the pattern needed. The resources above should provide you with most of this knowledge. However, a list here may be helpful:

  • Common Dimension (same dimension across facts, allows drilling across fact measures)
  • Conformed Dimension (translate and conform your data into one dimension table from multiple sources)
  • Degenerate Dimension (sometimes it is useful to have a single dimensional attribute or two in a fact table but this pattern doesn’t translate well into Power BI)
  • Slowing Changing Dimension & Type (handling data that changes over time)
  • Junk Dimension (gather a few unrelated attributes)
  • Outrigger Dimension (only utilize when dimension table to too large and cannot be done with a typical star schema pattern)
  • Role-Playing Dimension (multiple fact keys to the same dimension)
  • Bridge Table (pattern for many-to-many relationship)
  • Factless Fact Table (tallies – # of instances of dimension attribute values)
  • Transaction Fact Table
  • Periodic Snapshot Fact Table (measures that cannot always be rolled up)
  • Accumulating Snapshot Fact Table