I have an application where the source database contains multiple fact tables that share common dimensions. For example:
Facts:
Task(DateKey,ProjectKey,TaskEstimate)
Test(DateKey,ProjectKey,TestDate)
Dimensions:
Dates(DateKey), Projects(ProjectKey)
When loading these into QV, the fact table dimension fields with common names are associated automatically, which is not the desired affect, and seems to generate synthetic keys. If the fact table dimension fields are renamed to prevent association, they no longer associated to the dimension tables.
Can you suggest how best to resolve this so that each fact table is correctly associated with just the relevant dimension table?
This is a "classic" QlikView Data Modeling issue. The two traditional approaches include:
1. Building a "Link Table" that holds all the keys and links through a composite key. This is commonly called a "Link Table" solution.
2. Concatenating all the Fact tables into a single Fact table, to avoid multiple linking and synthetic keys. This solution is commonly called "Concatenation".
Both approaches have been heavily discussed on this forum and in the Wiki. Please look it up. Those approaches are also discussed in detail as part of the standard training class "Developer II". Look it up under Services/Training on this website. There should be a class coming up in a location near you.
