Star Schema Model Suggestions

[Anonymous]

Hi there. 

I'm fairly new to modelling in PowerBI but have a large amount of experience in your typical normalised ER data modelling for RDBMS. 

I have a table of data which shows outputs between an importing country and exporting country for a given year. The importing country side of the equation has values like population and GDP while the exporting side has just marketshare. 

Normally when I'm building an RDBMs I'd probably normalise this date into the following table structure.

However, from what I read, this is probably not the most ideal structure for PowerBI and that I should attempt a more denomarlised 'star schema' approach with facts and dimensions.

However, even after doing quite a bit of reading on this, I'm not sure if I'm designing my star schema correct. My fact table ends up looking somewhat like this: 

....which looks much like my original data table. Is this the correct structure for mdoelling in PowerBI? The database guy in me recoils at the duplication of the import country data. 

Or is there potentially another way. Perhaps two fact tables - one for export countries, one for import countries? OR are the attributes of populations, year and GDP actually attributes of a dimensions table?

Any help or suggestions much much appreciated!

Cheers

DM

[PaulDBrown]

@Anonymous 

What is the structure of the source tables?

[HotChilli · Accepted solution]

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]

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. 

[HotChilli]

"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

[Anonymous]

I posted a reply before Paul but it just randomly disappeared.

It's just a flat table like in the image, but I guess thats probably not the best way to show it.

I can't post the table here, but here's a link to the data in a google document.

Thanks very much.

DM