topic Re: Data Model issue with 3 Fact tables in App Development

Data Model issue with 3 Fact tables

SP_Qlik — Fri, 08 Jul 2022 07:11:42 GMT

Hi ,

Hope you are doing well!

I have 3 fact tables in model,

Table1: Act_ID, Ind_ID,Wkp_ID,Region;

Table2:Ind_ID, Region;

Table3:Wkp_ID,Region.

Here my requirement is like I have to show count(Act_ID) from Table1, count(Ind_ID) from Table2 and Count(Wkp_ID) from table3 but I have to secure my application based on Region(Section access: Data reduction based on Region) and also I want to show filters from all 3 tables and I cant able to take counts from Table1 as misssing some data from Table2 & Table3.

Here I can't able to concatenate these 3 tables because filters not working properly(Association is not happening properly). So I gone for Link Table but I am getting synthetic key.

%Key_Link1:Ind_ID&'|'&Region; (Table1 & Table2)

%Key_Link2:Wkp_ID&'|'&Region;(Table1 & Table3)

Please find the below model for the same .Can you please suggest best solution to remove synthetic key or any other solution apart from concatenate @ Link table.

Thanks in advance.

Best Regards,

SSPonnam.

Re: Data Model issue with 3 Fact tables

MayilVahanan — Fri, 08 Jul 2022 07:24:57 GMT

link between these 2 tables?
If you are creating the link tables for 3 tables. In that case, why we still has 2 key in this table also?

Re: Data Model issue with 3 Fact tables

SP_Qlik — Fri, 08 Jul 2022 11:58:56 GMT

Thanks for your response.

Here the relation between tables like Table1->Table2 & Table1->Table3.

So that's why I have created separate Key's for Table2 & Table3.

Thanks,

SSPonnam

Re: Data Model issue with 3 Fact tables

MartW — Fri, 08 Jul 2022 13:28:51 GMT

maybe to save a bit of hassle, if in the 3 fact tables most/all key fields are the same, is it an option to concatenate (Union) all the fact tables. This also makes it faster because it is optimized