Forum Discussion

DebbieE's avatar
DebbieE
Community Champion
5 years ago
Solved

Building a Power BI Model Tips for a star schema

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

 

 

1 Reply