The Smart Method Logo

Publishers of the world’s most comprehensive and up-to-date Excel tutorials

OLAP Star Schema

Understand OLAP database Design

This lesson is excerpted from the Expert Skills book

This lesson is excerpted form the book: Learn Excel 365 Expert Skills with The Smart Method: Fourth Edition: updated for the Jul 2020 Semi-Annual version 2002. 

This lesson is contained in the session: Create Get & Transform Queries.Power Pivot, Data Modeling, OLAP and Business Intelligence.

OLAP databases and OLTP databases are designed differently

OLTP design criteria

In the article Understand OLTP database design you learned that an OLTP (On Line Transaction Processing) database is designed around the need to quickly perform four actions: Create, Retrieve, Update and Delete.

Well-designed OLTP databases must also conform to Third Normal Form (3NF) and are said to contain Normalized Data. A normalized database reduces the risk of data corruption during transactional processing. The Expert Skills book describes 3NF in: Lesson 11 31: Understand normal and de-normalized data.

OLTP databases can contain one-to-many and many-to-many relationships. The Expert Skills book describes the different relationship types in: Lesson 12 10: Understand many-to-many relationships.

OLAP design criteria

The data model that you create in Power Pivot is an OLAP (On Line Analytical Processing) database. An OLAP database is read-only and has no need to Create, Retrieve, Update and Delete records. Instead it is designed around the need to quickly analyze data. 

Data analysis is most easily performed using a special table and relationship arrangement called a Star or Snowflake schema that has a central Fact table surrounded by several Dimension tables. 

OLAP databases used with Power Pivot cannot contain many-to-many relationships.

Because OLAP databases do not process transactions they have no need to conform to third normal form. For this reason several dimension tables are often de-normalized into a single dimension table.

Snowflake schema

OLAP Snowflake Schema Example

A Snowflake schema has a central Fact table surrounded by several Dimension tables. 

Notice that some of the dimension tables have sub-dimensions giving the schema the appearance of a snowflake.

You can think of the dimension tables as filters that enable aggregations of the values in the fact table.

Star schema (the best choice for OLAP)

OLAP Star Schema Example

In the above diagram, you can see that a star schema has only one level of dimension tables, giving the schema the appearance of a star.

While Power Pivot will work with either a snowflake or star schema, Power Pivot works faster and better with a star schema. Star schemas are also easier for users to work with.

For these reasons the star schema is the recommended choice when working with Power Pivot.

The best-practice rule:
Use a star schema (rather than a snowflake schema) when constructing data models.
… is included in the Expert Skills book in: Appendix A: Power Pivot Rules.

Additional information (shown in the sidebar of the Expert Skills book)

Fact and Dimension tables

A perfect OLAP data model contains one Fact table and several Dimension tables.

Fact table 

The Fact table is the central table that (ideally) contains nothing but values and foreign keys (that are used to define relationships to the dimension tables).

The values in the fact table can be aggregated into the values displayed in the body of pivot tables.

The best-practice rule:
Only store numerical data and foreign keys in the fact table.
… is included in the Expert Skills book in: Appendix A: Power Pivot Rules.

A perfect OLAP data model contains one Fact table and several Dimension tables.

Fact table

The Fact table is the central table that (ideally) contains nothing but values and foreign keys (that are used to define relationships to the dimension tables).

The values in the fact table can be aggregated into the values displayed in the body of pivot tables.

The best-practice rule:

Only store numerical data and foreign keys in the fact table.

… is included in: Appendix A: Power Pivot Rules.

Dimension tables

Dimensions can be thought of as filters.  They contain fields that will be used for the row, column, filter and slicer names in pivot tables.

Data models with many dimensions add more flexibility but increase the data model size

There is always a trade-off between reporting flexibility and the data model size. 

Dimension tables
Dimensions can be thought of as filters. They contain fields that will be used for the row, column, filter and slicer names in pivot tables.

Data models with many dimensions add more flexibility but increase the data model size

There is always a trade-off between reporting flexibility and the data model size.

Other names for dimension and fact tables

Some users prefer the term Lookup table (for dimension tables) and Data table (for fact tables).

This lesson as shown in the Expert Skills e-book Kindle reader for PC

Lesson 12-12 Understand OLAP Database Design

Available for Excel 365, 2019 and 2016 as both an e-book and paper printed book.

Share this article

Share on facebook
Share on twitter
Share on linkedin
Share on stumbleupon
Share on email

Related Articles

Understand modern data analysis

Understand modern data analysis

This article describes a new way of working with Excel called “modern data analysis” enabled by the new Get & Transform and Power Pivot tools.

OLTP database example

Understand OLTP database design

A Power Pivot data model is an OLAP database. Business databases are OLTP databases. This article discusses how OLTP databases are structured.

Leave a Reply

Your email address will not be published.

10 + 7 =