Building a Power BI Model Tips for a star schema

[DebbieE]

I am trying to build a star schema model for some surveys data and I am struggling with the logic because there are a few options you can choose that are 1 to many. For example

Dim.Survey

SurveyKey   Surveydate    SurveyName

1                01/01/2021  Survey on project

The above survey is be attached to all the below choices

Dim.Choice1

ChoiceKey    ChoiceDescription

1                   I enjoyed working on the project

2                   he project was harder than expected

Dim.Choice2

Choice2Key    ChoiceDescription

1                     Azure Devops

2                    SQL database

3                     Storage Account

Dim.Choice3

Choice3Key    ChoiceDescription

1                     Working in a team

2                    Working with 2 other people

Dim.Choice4

Choice4Key    ChoiceDescription

1                    Full Time

2                    Part time

However the choices are only relevant to the survey ID and not to each other. With this example how would you try and work out a star schema in Power BI

I thought of (Creating a junk dimension where every option is set against every option)

Creating bridge tables but this then isnt a star schema

Creating seperate models for each option

Choice1 Model

SurveyKey  ChoiceKey 

1                  1

1                  2

1                  3

And then in Power BI You could set the metric as Distinct Count of the SurveyKey.

Im trying to get the 1 to many relationships in place but obviously the choices are causing issues here. Im sure there is a proper way of dealing with this so any suggestions would be great

[v-angzheng-msft · Accepted solution]

Hi, @DebbieE 

Here are some references that may be helpful

https://radacad.com/power-bi-basics-of-modeling-star-schema-and-how-to-build-it

https://www.skylinetechnologies.com/Blog/Skyline-Blog/February_2021/power-bi-star-schema-dimensional...

https://www.sqlshack.com/implementing-star-schemas-in-power-bi-desktop/

https://databear.com/data-preparation-part-6-star-schema-power-bi/

Hope this helps.

Best Regards,
Community Support Team _ Zeon Zheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.