Synthetic Key Issue

[#1]

Happy Friday Everyone!! I've got a right issue here and I can't work it out. Basically I want the two fact tables to relate to the two dimension tables independently and I don't know how to do it whilst keeping the field names the same. How do I avoid creating the synthetic key whilst keeping things quick....there is over 30 million rows in the back end of this!

Kind Regards,

Miles

[arsal_90]

Let me know the relationship of your fact table with DimCurrency & Dimlocation is one-to-one then i will let you know another solution

[simenkg]

You concatenate your two fact tables and have them share the keys for country and currency.  Instead of qualifying your fields like

FKFact_SalesTakenAt and FKFact_ParlSales

Call them both Sales and add another field to your tables called FactType or similar with the values 'TakenAt' and 'Park' respectively.

Fact:

Load FKDim_Location as %LocationKey,

           FKDim_Currency as %CurrencyKey

           FKFact_ParkSales as Sales,

  .

.

.

           'Park' as FactType

from ParkFacts.....;

Concatenate(Fact)

Load FKDim_Location as %LocationKey,

           FKDim_Curency as %CurrencyKey

          FKFact_SalesTakenAt as Sales

.

.

.

.      'TakenAt' as FactType

from TakenAtFacts.....;

DimLocations:
Load FK_DimLocation as %LocationKey

.

.

.

.

.

From DimLocations ....;

You get the picture.

When your model is like this then expressions become:

Sum({<FactType={Park}>} Sales)

and

sum([<FactType= {TakenAt}>} Sales)

Selecting a coutntry will link to both the row with factType Park and the one with factType TakenAt

[#4]

Hi,

you will try by using "Aliasing"

thanks,

krish

[fred_s]

Hi Miles,

Is it possible to share your file with some data?

Or create 4 excel or CSV files with some data (but containing all fields).

Make sure your sample data makes sense..

What (field) is the relation between FactParkSales and FactParkSalesTakenAt?

Grtz. Fred

[Anonymous]

You can also use Generic Keys