Data Model: 2 or more fact tables

[#1]

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

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

[jagan]

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]

here is some reading for you

http://community.qlik.com/blogs/qlikviewdesignblog/2013/05/17/concatenate-vs-link-table

Fact Table with Mixed Granularity

Generic keys

Merging Data from Multiple Sources

[Anonymous]

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]

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]

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

[#8]

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]

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?

[#10]

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