Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Let me know the relationship of your fact table with DimCurrency & Dimlocation is one-to-one then i will let you know another solution
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
Hi,
you will try by using "Aliasing"
thanks,
krish
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
You can also use Generic Keys