Handling multiple fact tables...

[bdiamante]

I have a PostgreSQL database containing various education data such school-level test scores and enrollment figures. I need to separate enrollment from test scores because the data is on different grains. Even though enrollment is on a different granularity from the test-score data, many of the dimensions are the same. For example, I have:

    ~ ---------------------------------------------------------------------------------~
    | Test Scores Fact                                                                 |
    |-------------|-----------|----------|-----------|--------------|------------|-----|
    | school_code | test_code | grade_id | gender_id | ethnicity_id | subject_id | ... |
    |-------------|-----------|----------|-----------|--------------|------------|-----|

    ~ --------------------------------------------------------~
    | Enrollment Fact                                         |
    |-------------|----------|-----------|--------------|-----|
    | school_code | grade_id | gender_id | ethnicity_id | ... |
    |-------------|----------|-----------|--------------|-----|
    |-------------|----------|-----------|--------------|-----|

This structure is fine on the backend, but in Qlikview, this creates a synthetic key. The solution for synthetic keys seems to usually be replacing it with a link table via Qlikview scripting, which has been my approach as well. But this does not seem to scale, as when I add a third fact table (on yet another grain) that contains more of the same dimensions, if I create another link table, now my two link tables start to associate as they contain several commonly named fields, and Qlikview's response is to create more synthetic keys?

I'm relatively new to Qlikview and am working by myself. How are multiple facts of different grains with common dimensions usually handled?

[Jason_Michaelides]

Hi Brian,

This is a very common question and you should find lots of similar threads in here.  The usual solution is to concatenate the fact tables together (i.e. stack them on top of one another in a single fact table). You would most likely want to add a field called something like Fact_Type, or Source or something.

Hope this helps,

Jason