Star schema and Snowflake schema in SSAS

In this article, we will show you the basic differences between the Star schema and Snowflake schema in SSAS

  • Star Schema: Every dimension present in the Data Source View (DSV) is directly linked or related to the Fact or measures table.
  • Snowflake Schema: Some dimensions present in the Data Source View (DSV) are linked directly to the fact table. And some dimensions are indirectly related to fact tables (with the help of middle dimensions). For instance, in [Adventure Works DW 2014], [Dim Product sub category] is indirectly related to [fact Internet Sales] with the help of [Dim Products].

Star Schema Vs Snowflake Schema

The below table will show the difference between the Star Schema and Snowflake Schema or star schema vs snowflake schema in SSAS.

STAR Schema Snowflake Schema
Centrally located fact table surrounded by DE normalized dimension tableCentrally located fact table surrounded by the normalized dimension table
In Star Schema, All dimensions will be linked directly with fact tableIn Snowflake Schema, some dimensions linked directly to the fact table and some dimensions are indirectly linked to fact tables (with the help of middle dimensions)
It is easy to understand the designIt is difficult to understand
Increase the query performance because we can extract the data with less number of joinsWe have to join more tables to extract data so more joins
We mostly see the STAR Schemas in a small database. Or we say small companies.Most of the Big Companies or any big database will always belong to Snowflake Schema.
By DE normalizing the database, we can convert the Snowflake Schema to star schema.By normalizing the database means dividing the table’s data further using primary and foreign keys, we can transform the star schema to Snowflake Schema quickly.

STAR FLAKE: A hybrid structure that contains a mixture of star schema (DE normalized data) and snowflake schema (normalized data).

STAR SCHEMA in SSAS EXAMPLE

We can see from the below figure [Dim Production], [Dim Customer], [Dim Product], [Dim Date], [Dim Sales Territory] tables are directly attached to [Fact Internet Sales]. So, this is the perfect example for the star schema in SSAS

Star schema and Snowflake schema in SSAS 1

SNOW FLAKE SCHEMA in SSAS EXAMPLE

We can see from the below SSAS figure [Dim Currency], [Dim Customer], [Dim Date], [Dim Product] tables are directly attached to [Fact Internet Sales]. But [Dim Product Sub Category] and [Dim Product Category] are indirectly connected.

Star schema and Snowflake schema in SSAS 2