Migrate to generic key model...

[#1]

We have a model where two fact tables which are related to each other by country.  Fact1 is a straight connection.  Each country has a set of facts.  Fact2 measures are attached to one/more vendors in a country.  We moved the vendor country to the Fact2 table and duplicated the country key (%Fact2 Country) giving us the following diagram.  What we get on the load are fact2 rows duplicated for each occurence of a fact1 row.  All facts in our model have a country.  This give us what we need (all facts related directly/indirectly to the country).  

We're not sure this is the most efficient design.  We've been reviewing generic keys example‌ but we get a checkerboarding effect where dimensions specific to one fact table will not show the other fact table.

Table layout

Script

[Fact1]:

LOAD * INLINE [

%Fact1Key,Measure1,Meausure2,%Country,%Category

];

[Country]:

LOAD * INLINE [

%Country,%Fact2 Country, Country Name,Region

];

[Category]:

LOAD * INLINE [

%Category,Category,Category Description

];

[Fact2]:

LOAD * INLINE [

%Fact2Key,Measure3,Measure4,%Vendor,%Fact2 Country,%Status

];

[Fact2 Log]:

LOAD * INLINE [

%Fact2Key, First Log Date, Last Date

];

[Vendor]:

LOAD * INLINE [

%Vendor,Vendor ID,Vendor Name

];

[Status]:

LOAD * INLINE [

%Status, Status, Status Description

];

[marcus_sommer]

If it worked in this way like you and your users expected it I wouldn't change it and are the response-times are too slow I would concatenate the fact-tables but only then.

- Marcus

[Digvijay_Singh]

I may not be correct on this, since you have two country fields in the Country table(%Country, %Fact2 Country), if you expect different values in these two fields in one record then Country Name will be a confusing field. You may need two country name fields. OR if you expect both fields to be always have same value in one record then not sure why we should keep them separate.

[#4]

At first, we had the tables concatenated but the filters (e.g. Category) were only showing one fact table.  If we wanted a star schema, are generic keys the solution?

[marcus_sommer]

I don't think that generic keys would be the best solution for you. Like above mentioned without a serious problem I wouldn't change it - if you concatenate the tables and one or both of the fact-tables are missing some dimension-fields like Category you could add them. I would very probably in this case load the Country-CategoryID as mapping-table before loading the fact-tables and would then add these information per applymap.

This meant you might need some intermediate-step to create your datamodel.

- Marcus