Forum Discussion
Star Schema Model Suggestions
I don't think you should worry about it too much. As long as you know how to deal with repeated values, it'll be ok. Just don't SUM them, there are plenty of aggregate functions in DAX to return what you want in any visualisation.
Also, unless file size and performance is critical, I wouldn't use surrogate keys for the country. It makes it easier when inspecting the data in Data View. You'll still want a country dimension (or two, import and export).
There are design theories in data warehousing that deal with this stuff, however powerbi can cope with lots of different designs. So I say keep it simple - you can always redesign if required.
Anonymous
What is the structure of the source tables?
I don't think you should worry about it too much. As long as you know how to deal with repeated values, it'll be ok. Just don't SUM them, there are plenty of aggregate functions in DAX to return what you want in any visualisation.
Also, unless file size and performance is critical, I wouldn't use surrogate keys for the country. It makes it easier when inspecting the data in Data View. You'll still want a country dimension (or two, import and export).
There are design theories in data warehousing that deal with this stuff, however powerbi can cope with lots of different designs. So I say keep it simple - you can always redesign if required.
Hi Hot Chilli. Thanks for the reply. Issue is that I do need to use some of those repeated data in calculations and visualisations - which seem to default aggregate them with 'sum'. I'm a newbie to DAX, any clues on creating a measure that doesn't aggregate the repeated values?
I think I've found a better way to model it. Create a key for each ImportCountryCode and Year combo and then link that candidate key to the export country data.
"any clues on creating a measure that doesn't aggregate the repeated values" -
a) https://docs.microsoft.com/en-us/power-bi/create-reports/service-aggregates
b) write explicit measures