Advice of type of schema

I am attaching a pic here… i just need to confirm one thing from your expert advise,

i.e in a star schema or snowflake schema… we can have only one fact table and multiple dimension tables…this what i have studied so far.



In the diagram attached…i have a data model …which has two fact tables and few common dimension tables. Looking at the diagram can i say it has 2 snowflake schemas.

any advise will be appreciated.

regards

*** READ FIRST ***

Before you send your question.
Make sure that all details relevant to your question is complete:

  • Your current work-in-progress PBIX file - VERY IMPORTANT
  • A clear explanation of the problem you are experiencing
  • A mockup of the results you want to achieve
  • Your underlying data file

Check out this thread on Tools and Techniques for Providing PBIX Files with Your Forum Questions

Also make sure that your data file contains no confidential information. If it does, click the link above.

*** DELETE THIS MESSAGE IF YOU ARE SURE ALL YOUR DETAILS ARE COMPLETE OR IF THE ABOVE INFORMATION IS NOT APPLICABLE TO YOUR QUESTION.***

@praveer82 ,

When it comes to Power BI, my advice is always to shoot for the star schema if at all possible. Here’s a slide I prepared for the Accelerator course week #2 on Data Modeling. Without knowing more about the specifics of your data, my inclination would be to de-normalize Dim1 and Dim2 down to a single dimension to make it star schema compliant.

I hope this is helpful.

– Brian

Yes sir you are correct… i always create star schemas… but if some one asks what kind of schema is that? in that case can i call them it is having 2 snowflake schemas?

regards

@praveer82
Great Question.
Simple answer is it is a Galaxy Schema.

In the examples below you can see the difference between the 3 schemas discussed.
Note:
The examples are laid out using the Collie Method.
This helps you to quickly and easily understand the filter propagation in Power BI.
Using the Collie Method, the filters will always propagate downhill.

Star


Defining Features:
Single connection between Fact Table and Dimension Tables

SnowFlake


Defining Features:
Daisy Chain from outermost Dimension table to Fact Table

Galaxy


Defining Features:
This model can involve more than one fact table and many dimension tables

1 Like

Thanks sir…for reply, actually it does not seems a galaxy schema as galaxy schema consists of multiple star schemas… but in my case its forming snowflake…please suggest…can i say its combination of two snowflakes?
regards

@praveer82
It all depends on the filter propagation.
If your schema laid out using the Collie Method looks like this, then it is a SnowFlake

If it looks like this it’s a Galaxy
Galaxy2

Hope this helps

Thanks again sir… i am re


uploading along with directions…can you please give idea now what it will be called?
regards

Sir in this galaxy diagram dim1 is not directly connected to fact tables so dont they will form snowflake?

Your diagram is a galaxy.

Essentially, a Galaxy schema can be defined as a collection of star schemas interlinked and completely normalized, to avoid redundancy and inaccuracy of data. It can also be a meaningful association of a Snowflake schema with a Star schema, etc, where the fact tables of both schemas can be linked by common dimensions.

Your Dim1 table makes your Galaxy a combination of 2 snowflake schemas

3 Likes

Thanks a lot sir.

regards