Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
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.
Thank you,
Sandra
=================================================== Edit:
Hi Tomasz,
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
Thanks
Message was edited by: Sandra Pinto
Can You give us sample data?
Tomasz
Hi Sandra,
below a simple Generic Load Script
Generic
LOAD id,'data'&AutoNumber(RowNo(),id) as D1,data Inline [
id, data
1, 01/02/2016
1, 03/02/2016
1, 05/02/2016
2, 11/02/2016
2, 21/02/2016
2, 22/02/2016
];
Regards,
Antonio
Hi I have edited my original message.
See qvf there.
Thanks!
Hi,
I am not sure a simple load would help here. See my edited message. I have attached a qvf there.
Thanks !
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 ?
Hi Andrea,
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:
Thank you.
Off topic:
- 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..
Hello Sandra,
did you create a link table before?
to do it i suggest you a script similar as below:
[Link Table]:
LOAD Distinct
%KEY_FacTable1,
sharedDim1,
sharedDim2,
...
FROM FacTable1;
concatenate
LOAD Distinct
%KEY_FacTable2,
sharedDim1,
sharedDim2,
...
FROM FacTable2;
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.
BR
Andrea