Data Modelling Design Help

[revansh]

Hi,

I have 3 fact tables and 1 dimension table .

FCT1:  Country | Sales Amount

FCT2:  State      |  Sales Amount

FCT3:  City  | Sales Amount

DIM:  Country | State | City

How do i join above 4 tables to create reports to drill through from COUNTRY  to STATE to CITY.

Thanks

[dramus]

From the details you have given. How do you know which State and Country a City belongs to, or which State a Country is in?

Without that, you can't drill down.

[revansh]

Hi Dramus,

Country to STATE to city link is in dimension table.

for eg:

US   KY LOUISVILLE

US   KY LEXINGTON

US   KY FRANKFORT

Thanks

[dramus]

So at the City level, how do you tell Boston US, from Boston UK?

[revansh]

DATA IN FACT TABLE IS LIKE THIS.

FCT1: COUNTRY |  SALES AMT

FCT2: COUNTRY | STATE | SALES AMT

FCT3: STATE |  CITY  |  SALES AMT

Hope it is clear now.

Thanks

[dramus]

Okay...So Assuming that the City table has the full key (City/State/Country).

Power BI can only handle single field keys, so you will need to create some composite keys. (e.g. State-Key = [Country] & "|" & [State]) on the FCT2 and FCT3 tables. You will also need to create a city-key (Country/State/City) on the DIK and FCT3 tables