Is joining twice to a fact table will cause any effect...??

[vishalgoud]

Hi , we are using below sql code in our exctractor app.

select * from
FROM  DSS..FACT_CONSUMPTION  FACT
JOIN  DSS..DIM_DATE DD ON FACT.USAGE_EVENT_DATE_KEY = DD.DATE_KEY
LEFT JOIN  DSS..DIM_MOBILE_OPERATOR DMO ON FACT.CALLINGROAMNETWORKCODE = DMO.MCCMNC
LEFT JOIN  DSS..DIM_COUNTRY DC on  DC.COUNTRY_KEY = FACT.CALLEDHOMECOUNTRYCODE
LEFT JOIN  DSS..DIM_COUNTRY DC1 on  DC1.COUNTRY_KEY = FACT.CALLINGROAMCOUNTRYCODE.

and using above we are generating the Fact qvd called Consumption, then for the sake of star schema all the above dim tables will be loaded into the final app and also making defualt connection(as per qlikview default connection is called left outer join).

So is my data modelling is right or am i making any mistakes by joining the same dim tables to it 2 times to the fact table. first time while creating a fact qvd and second time in the final app..

Please send your thoughts on this, Thanks in advance.

[YoussefBelloum]

EDITED

Hi,

Here on this SQL query above, you are filtering the rows of the fact table depending on  rows of all the dimension table, before joining the fact to the dimensions on the model.

which makes no sense for me. because on the dimension table you will always have all your values.

I'll try to explain with an example:

Filter your fact table (where you have 10 rows) using the rows of the dimension (where you have a total of 100 rows, including the 10 rows of the fact table)===> you will have the exact 10 original rows.

I don't see the utility to do this.

I'll start by doing this:

• load * your fact table (FACT_CONSUMPTION) and rename the joining key

          rename Usage_event_date_key to Date_key

          rename callingRoamNetwork_code to MCCMNC

          rename CallHomeCountryCode to Country Key

• load your dimensions (don't need to rename the Keys)

you will have your star schema.

now you can decide to filter the fact table depending on the rows you have on the dimension table with a RIGHT JOIN fro example.

But all this is just to test the modeling part.. when everything is ok, I'll suggest you to follow this approach:

Three Tier Architecture

One last thing.

these two last lines:

LEFT JOIN  DSS..DIM_COUNTRY DC on  DC.COUNTRY_KEY = FACT.CALLEDHOMECOUNTRYCODE

LEFT JOIN  DSS..DIM_COUNTRY DC1 on  DC1.COUNTRY_KEY = FACT.CALLINGROAMCOUNTRYCODE.

the primary key on the Dim_country table is Country key

on the Fact table, is CALLEDHOMECOUNTRYCODE = CALLINGROAMCOUNTRYCODE. ??