cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
andershs
Frequent Visitor

Modelling a hierachy

I have a data modelling question, which I hope you will help me figure out. I am new to data modelling in BI, so please consider this.
I have a feeling this might be relatively straight-forward for more experienced users.

 

I have a database with a record of dishes in table Dish. Each dish has some characteristics: name, price, amount of pasta etc.
They also have a Pasta Type and a Pasta Type Group. The relationships between these seem to be circular.

 

The thinking is, that each dish can have a certain Pasta Type that it has to be made with. If no Pasta Type is present, there may be a Pasta Type Group, that the dish has to be made with.
Both Pasta Type and Pasta Type Group are nullable in the Dish table. They don't have to have a value.

If a dish entry has both Pasta Type and Pasta Type Group set (to not null), the most detailed information, Pasta Type, will win.

 

Pasta Types could be: 'Fettucine', 'Spaghetti', 'Tagliatelle' etc. Pasta Type Groups could be 'Tubes', 'Ribbons', 'Shapes', but also 'Whole-grain', 'Gluten-free' etc.

There exists a many-to-many relationship between tables Pasta Type and Pasta Type Group.

 

Below is an overview of the database. I have inserted a value 'None' for each dish if both Pasta Type and Pasta Type Group is null:

andershs_0-1659012357135.png


I have modelled this currently with a fact table covering the dishes made on a certain date.
Each dish has both a pastaTypeId and a pastaTypeGroupId. Both can be null/not defined.
Like this:

andershs_1-1659012401365.png


I would like a hierachy based on Pasta Type Group and Pasta Type, such that I can make a visual like this:

andershs_2-1659012439005.png


Since each Pasta Type can be a member of many Pasta Type Groups, I am struggling with creating a circular relationship like in the database above.

This however, gives me None in Pasta Type:

andershs_3-1659012522084.png


Moreover, I have added reference table and a duplicate Pasta Type table to the Pasta Type Group table like this:

andershs_4-1659012627525.png


But this means, that my summations of amount of pasta is not correct for the Pasta Types:

andershs_5-1659012659917.png

 

Any help on how to model this hierachy is appreciated.

1 ACCEPTED SOLUTION
lbendlin
Super User
Super User

It sounds like Pasta Type Group should be a parent dimension of Pasta.  Are there instances of Pasta that are not part of a group?

 

Moreover it sounds like dish should be a standalone table, not linked to either Pasta or Pasta Group, since there is no strict dependency.

View solution in original post

2 REPLIES 2
Ashish_Mathur
Super User
Super User

Hi,

Share some data and show the expected result.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
lbendlin
Super User
Super User

It sounds like Pasta Type Group should be a parent dimension of Pasta.  Are there instances of Pasta that are not part of a group?

 

Moreover it sounds like dish should be a standalone table, not linked to either Pasta or Pasta Group, since there is no strict dependency.

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

Thank you 2022 Review

2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.