Part 11: Galaxy Schema

A Galaxy Schema is like a Star or Snowflake schema a data model which contains fact tables and dimension tables. Compared to Star and Snowflake schema, a Galaxy Schema has always at least two fact tables and dimension tables. The fact tables are connected to the same dimension tables and are so called “conformed dimensions”. This way of data modeling brings a big plus when you want to create new measures and key performance indicators (KPI), but cannot be done with only one Star or Snowflake schema.

Example

A good example is the shown Galaxy Schema below which contains a fact table with visits of customers in shops. Before the second fact table, one specific KPI could be built:

  • Average OrderValue in Stores
Galaxay Schema For Sales

Galaxay Schema For Sales

What you can build with the second fact table is a new KPI with a different view on sales:

  • Average OrderValue in Stores per Visit

This kind of new measure and then KPI can bring absolutely new insights for the business. So you can calculate how many visits you need to get a specific revenue or you can compare the sales strategy of different stores when the KPI has a big deviation.

What is not possible is to analyze visits on products and that has to be understood by the users. The data of “visits” is measured for example by a sensor at the door of a store and at the end of the day every second count will be deleted because one visitor a day gives a signal when entering and leaving the store. But there are more than this way to count the visitors (for example via smartphone connection..)

Benefits:

  • The usage of conformed dimensions which allows to reduced data redundancy and data misunderstandings
  • More possibilities to build KPIs on additional measures

Disadvantage:

  • Higher complexity in data model administration
  • Higher complexity in data Analysis

 

About Tobias Riedner
Tobias Riedner foundet WYCDWD.com in 2015. He works and worked as innovator, consultant, analyst and educator in the fields of business intelligence and data warehousing. He learned a lot from the best consultants, managers und educators in the past and shares his knowledge worldwide. He works for a steady growing traditional company which is a leader in industry 4.0.