在Qlikview中处理多个事实表 [英] Handling multiple fact tables in Qlikview

问题描述

我有一个PostgreSQL数据库，包含各种教育数据，如学校水平考试成绩和入学人数。我需要将注册和测试分数分开，因为数据是在不同的谷物。即使注册与测试分数据的粒度不同，但许多维度是相同的。例如，我有：

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

这种结构在后端很好，但是在Qlikview中，这会创建一个合成键。合成键的解决方案似乎通常通过Qlikview脚本替换它与链接表，这也是我的方法。但这似乎不成比例，因为当我添加第三个事实表（在另一个谷物）包含更多相同的维度，如果我创建另一个链接表，现在我的两个链接表开始关联，因为它们包含几个命名字段，Qlikview的响应是创建更多的合成键？

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?

我是Qlikview的新手，我自己工作。如何处理具有通用尺寸的不同谷物的多个事实？

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

编辑：

解决这个问题，已经在一个生产环境中工作一年左右！见下面的答案...

I've provided my solution to this problem which has been working in a production environment for just under a year! See my answer below...

推荐答案

看到这个问题的普及，我将把我的实际解决方案添加到混合，所以人们有一个工作的例子，这是出于某种原因很难找到这样的常见问题...

Seeing the popularity of this question, I'm going to add my actual solution to the mix so people have an example to work from, which for some reason is really hard to find for such a common problem...

我继续创建一个链接表。这个解决方案仍然到今天感觉像一个黑客，因为它创建一个巨大的表，包含所有的事实表中的每一个键的笛卡尔积产品...但它的工作。

I proceeded with creating a Link Table. This solution still to this day feels like a hack as it creates one huge table containing the Cartesian product of every one of your keys in all of your fact tables... but it does work.

问题：您的数据库中有多个事实表;几乎每个数据库中都发生了一次。这些事实表中的一些（或全部）共享相同的关键字段;没问题，对吧？错误。不幸的是，由于Qlik的关联性，而不是每个事实表与它们的查找表链接得很好，你的事实表现在相互关联，并且破坏了你的数据模型;创建循环引用和无数量的合成键。

The problem: You have multiple fact tables in your database; an occurrence in almost every database ever. Some (or all) of these fact tables share the same key fields; no problem, right? Wrong. Unfortunately, due to Qlik's associative nature, instead of each one of your fact tables linking nicely to their lookup tables, your fact tables are now associating with each other and wreaking havoc on your data model; creating circular references and untold amounts of synthetic keys.

解决方案：创建链接表。听起来很简单，对吧？好吧，它是，但它的文档很差，很难理解，没有一个初步的解释。你可能想知道...什么是链接表？它是所有事实表中所有键的笛卡尔积。这如何纠正这个问题？它删除事实表之间的所有不需要的关联，因为每个事实表现在只包含一个唯一的连接键。这些唯一键将仅与链接表相关联，其中包含所有唯一的连接键以及所有单个键。

The Solution: Create a Link Table. Sounds simple, right? Well, it is, but it's also very poorly documented and hard to understand without an initial explanation. You might be wondering... what's a Link Table? It is the Cartesian product of all keys from all of your fact tables. How does this correct the problem? It removes all of the unwanted associations between your fact tables as each will now only contain a single unique concatenated key. Those unique keys will associate with the Link Table only, which contains all your unique concatenated keys as well as all the individual keys. The Link Table will subsequently associate with your lookup tables and all will be well.

实施：

这个实现将使用我上面的问题中包含的两个表; test_scores_fact 和 enrollment_fact 。

This implementation will use the two tables contained in my question above; test_scores_fact and enrollment_fact.

test_scores_fact     |    enrollment_fact      |    school            |    gender         |   ...
----------------     |    ---------------      |    ------            |    ------         |   ---
school_code (FK)     |    school_code (FK)     |    school_code (PK)  |    gender_id (PK) |
test_code (FK)       |    grade_id (FK)        |    school_name (FK)  |    gender_desc    |
grade_id (FK)        |    ethnicity_id (FK)    |    address           |    ...            |
gender_id (FK)       |    gender_id (FK)       |    ...               |
ethnicity_id (FK)    |    number_enrolled (F)  | 
subject_id (FK)      |
test_score (F)       |

FK = Foreign Key
PK = Primary Key
F = Fact

b $ b

可以看到，两个事实表有重叠的键， school_code ， grade_id code> gender_id ，以及 ethnicity_id 。在关系模型中，每个关键字字段都有一个对应的表，其中包含关于该关键字的附加信息。这个模型不使用Qlikview的关联性质，因为Qlikview基于字段的名称关联表;即使你不想要它。您确实希望将命名字段与其查找表关联，但不希望事实表中的命名字段与之关联。不幸的是，你不能阻止这种行为。您必须实现链接表...

As you can see, the two fact tables have overlapping keys, school_code, grade_id, gender_id, and ethnicity_id. In a relational model, each key field has a corresponding table with additional information about the key. This model does not jive with Qlikview's associative nature as Qlikview associates tables based on the name of a field; even when you don't want it to. You do want like named fields to associate to their lookup tables, however you don't want like named fields in your fact tables to associate. Unfortunately you cannot stop this behavior. You must implement a Link Table...

1. 在您的Qlikview脚本中，创建一个临时事实表，从您的数据库表中：

1. In your Qlikview script, create a temporary fact table, which loads in all fields from your database table:

[temp_test_scores]:
LOAD school_code,
     test_code,
     grade_id,
     gender_id,
     ethnicity_id,
     subject_id,
     test_score;
SQL SELECT * FROM <database connection>

连结您的金钥并移除所有个别金钥：

Concatenate your keys and remove all individual keys:

[test_scores]:
LOAD school_code & '_' test_code & '_' grade_id & '_' gender_id & '_' ethnicity_id & '_' subject_id as test_key,
     test_score
RESIDENT [temp_test_scores];

重复步骤1&每个事实表的值为2：

Repeat Steps 1 & 2 for each fact table:

[temp_enrollment]:
LOAD school_code,
     grade_id,
     ethnicity_id,
     gender_id,
     number_enrolled;
SQL SELECT * FROM <database connection>

[enrollment]:
LOAD school_code & '_' & grade_id & '_' & ethnicity_id & '_' & gender_id as enrollment_key,
     number_enrolled
RESIDENT [temp_enrollment];

通过将各个键连接到单个表格中来创建链接表：

Create your Link Table by concatenating your individual keys into a single table:

[temp_link_table]:
LOAD DISTINCT
    school_code,
    test_code,
    grade_id,
    gender_id,
    ethnicity_id,
    subject_id
RESIDENT [temp_test_scores];

CONCATENATE ([temp_link_table])
LOAD DISTINCT
    school_code,
    grade_id,
    ethnicity_id,
    gender_id,
    number_enrolled
RESIDENT [temp_enrollment];

/**
 * The final Link Table will contain all of the individual keys one time as well as your concatenated keys
 */
[link_table]:
LOAD DISTINCT
    school_code,
    test_code,
    grade_id,
    gender_id,
    ethnicity_id,
    subject_id,
    school_code & '_' test_code & '_' grade_id & '_' gender_id & '_' ethnicity_id & '_' subject_id as test_key,
    school_code & '_' & grade_id & '_' & ethnicity_id & '_' & gender_id as enrollment_key
RESIDENT  [temp_link_table]

删除您的临时表，不出现在您的数据模型中：

Drop your temp tables so they do not appear in your data model:

DROP TABLE [temp_test_scores];
DROP TABLE [temp_enrollment];
DROP TABLE [temp_link_table];

这将删除事实表之间的所有关联因为它们之间现在没有公共字段名。每个事实表将通过创建的连接键链接到链接表。然后链接表将与每个单独的查找表相关联。您的Qlikview数据模型不会包含任何合成键或循环引用。

This will remove all associations between your fact tables as there now exist no common field names between them. Each fact table will link to the link table via the created concatenated key. The link table will then associate with each individual lookup table. Your Qlikview data model will not contain any synthetic keys or circular references.

如果您以后创建另一个事实表，请按照步骤1& 2，并将任何新的单个密钥添加到链接表，并将新的连接密钥添加到链接表。

If you create another fact table in the future, just follow steps 1 & 2 again, and add any new individual keys to the Link Table and also add the new concatenated key to the Link Table as well. It scales with little effort.

祝你好运！

这篇关于在Qlikview中处理多个事实表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！