I have an issue where I have several Fact tables and some Dimension Tables.
Some Dimensions should be linked to some of the fact tables, but not to all.
I have read about this matter here. Generic keys
But still I need a concrete example for me to be able to implement what this article suggests.
Does anyone have an example qvf file in which there are:
Much appreciation for any help.
I have tried to reduce my model to make it relatively simple (My original data is much larger and more complex...).
Here is the qvf.
What I would like to do is create for each dimension a Dimensional Link Table
Then, create a Master Link Table which links the Dimensional Link tables to the 2 Fact Tables (Labor and P&L)
This is based on the way suggested in the attached PDF
Message was edited by: Sandra Pinto
below a simple Generic Load Script
LOAD id,'data'&AutoNumber(RowNo(),id) as D1,data Inline [
I saw 2 syn tables in your data model.
to avoid it you should create only one fact table or create a link table.
why do you think you need a generic keys approach ?
Yes. you are indeed right. There are 2 syn tables.
I forgot to explain in my initial question that in my original model the 2 fact tables are actually concatenated and so there is only 1 syn table there - and that is because of the interval match. and it looks like an OK syn...
What I would like is - to create the model in such a way that the 2 fact tables are Not concatenated .
Instead, I would rather the 2 fact tables to get connected to the Dimensions via 2 "Bridges" - One bridge of the Dimensional Link Table and the other is the Master Link table.
This thought of mine is based on the model below - suggested by HIC in his article. (PDF attached)
Problem is I am not sure how to write the script to get there ...
Here is the model from the article:
- are you sure you want Amount threated as a part of a synthetic key? What is a relation of Labour and P&L?
- is there a possibility to clean PLHeaders a bit?
-Relation of P&L and Labor is the AccountID
-PLHeaders are for the purpose of allowing a sort of P&L Layout in the UI. What do you mean clean a bit?
Syn key because of the PLHearders is OK and doesn't compromise the model. but the syn table 2 is a problem - one that would be resolved by the master link table, i believe..
did you create a link table before?
to do it i suggest you a script similar as below:
DROP Fields sharedDim1, sharedDim2, ....From FacTable1, FacTable2;
Where the two %KEY are concatenated string as ( sharedDim1 & '|' & sharedDim2 & '|' & ...) as %KEY_FacTable that you have to create before in each FacTable.
while sharedDim1, sharedDim2, etc... are all common dimensions on both FacTable.
Hope it may help you.