Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Data Model: 2 or more fact tables

Hi all,

I'm not that used to the star data model yet and kindly ask for your advice on the following matter:

  • I have two (later more) fact tables A and B.
  • The two fact tables can be linked by concatenating Document ID & Company Code
  • USER ID is a common dimension but can have different values in each of the tables.

  • Merging the two fact tables into one is not an option because granularity of one of them could become different at a later stage. An additional reason is that we will likely deal with up to 10 fact tables later which would make the one fact table very crowded and hard to maintain.
  • Giving the tables unique column names is not an option either because the selection of one of the dimensions (e.g. Company Code) should matter for the second table as well.

Right now I think a link table would be the best option. But how would such a table look like exactly? And what would I do with the User ID dimension?

Thank you for your help!

David

2factTables.png

10 Replies
Gysbert_Wassenaar

A link table might work, but if you add more fact tables you may run into trouble again. With regards to the User ID dimension consider adding the user name to the fact tables and leaving out the ID entirely. I think it's either that or load the User dimension twice.


talk is cheap, supply exceeds demand
jagan
Luminary Alumni
Luminary Alumni

Hi,

Try implementing the Linktable concept or else try concatenating the Fact Table A and Fact Table B.

Hope this helps you.

Regards,

Jagan.

Anonymous
Not applicable
Author

HI,

You have to comment the field Document ID and Company Code from the two tables and create a KEY with field Document ID & Company Code => [Document ID]&' - '&[Company Code] as KEY

Then you can create a new table LINK_TABLE which will be:

LINK_TABLE:

LOAD Distinct

KEY,

subfield(KEY,' - ',1) as [Document ID],

subfield(KEY,' - ',2) as [Company Code]

RESIDENT

TABLE1;

LINK_TABLE:

LOAD Distinct

KEY,

subfield(KEY,' - ',1) as [Document ID],

subfield(KEY,' - ',2) as [Company Code]

RESIDENT

TABLE2;

For the field User ID, you have to rename it [User ID.Table1] and [User ID.Table2]

Then the better choice is to make a left join Table 1 et 2 with the table 'User DImension' to get the field User Name into Table 1 and Into Table 2 as [User Name.Table1] and [User Name.Table2]

Anonymous
Not applicable
Author

We actually have 3 fact tables in our QVW.  We are a health care organization.  One table has member information, one table has eligibility information and one has claims information.  No way to combine any of the fact tables.   So we created a concatenated field made up of 4 common fields in each table in order to have a unique key if you will.  It has its challenges when trying to create objects.  I have had to employ island tables on occasion in order to create expressions from fields in multiple fact tables.  I would be happy to share more if you want to connect with me directly.

Anonymous
Not applicable
Author

oh man, try to put CMS metrics, financials and HR metrics in one QV dashboard and all of that coming from 3 different EHR systems that would be a nightmare with QlikView

Not applicable
Author

I think that a link table data model should work.  Remember your primary keys are surrogate keys and your attributes are used for the filters.  You can read more about the link tables in the "Qlikview for Developers Cookbook", chapter 6.

Anonymous
Not applicable
Author

Ok. I've read chapter You recommended. But in my case i have 3 fact tables with common fields:

Table1: f1,f2,f3,f4

Table2: f1,f2,f3,f4

Table3: f1

So that should i create link table from f1,f2,f3,f4? What about Table3? f1 field is most inportant. Should i create 2 different link tables?

Not applicable
Author

Can you provide some sample data for each table so I can do some modeling of the potential solution?