Generic Keys

[Anonymous]

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:

• Multiple Fact tables,
• Dimension Tables,
• Dimension Link table and
• One Master Link table?

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

[tomasz_tru]

Can You give us sample data?

Tomasz

[antoniotiman]

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

[Anonymous]

Hi I have edited my original message.

See qvf there.

Thanks!

[Anonymous]

Hi,

I am not sure a simple load would help here. See my edited message. I have attached a qvf there.

Thanks !

[agigliotti]

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 ?

[Anonymous]

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:

[tomasz_tru]

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?

[Anonymous]

-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.. 

[agigliotti]

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