在Qlikview中处理多个事实表

Question

我有一个包含各种教育数据的PostgreSQL数据库，例如学校水平的考试成绩和入学人数。我需要将入学人数与考试成绩分开，因为这些数据在不同的粒度上。尽管入学人数与考试成绩数据的粒度不同，但很多维度是相同的。例如，我拥有：

~ ---------------------------------------------------------------------------------~
| 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的响应是创建更多的合成键？

我对Qlikview还比较陌生，目前正在独自工作。如何处理具有共同维度的不同粒度的多个事实表？

编辑：

我提供了解决此问题的方案，在生产环境中已经运行了将近一年！请查看下面的答案...

- bdiamante

Answer 1

看到这个问题的流行度，我将添加我的实际解决方案，以便人们有一个例子可以参考，对于这样一个普遍的问题，找到例子却异常困难...

我继续创建了一个连接表。这个解决方案至今仍然感觉像是一种hack，因为它创建了一个包含所有事实表中每个键的笛卡尔积的巨大表格...但它确实起作用。

问题：您的数据库中有多个事实表；几乎每个数据库都会出现这种情况。其中一些（或全部）事实表共享相同的键字段；没有问题，对吧？错了。不幸的是，由于 Qlik 的关联性质，您的事实表不是与其查找表良好地链接，而是互相关联，破坏了您的数据模型；创建循环引用和不计其数的合成键。

解决方案：创建一个连接表。听起来很简单，对吧？好吧，确实是，但是它的文档非常不完善，没有初始说明很难理解。你可能会想...连接表是什么？它是从所有事实表中的所有键的笛卡尔积。这如何解决问题？它消除了所有不需要的事实表之间的关联，因为每个事实表现在只包含一个唯一的连接键。这些唯一的键将仅与连接表关联，该表包含所有唯一的连接键以及所有单独的键。连接表随后将与您的查找表相关联，一切都会好起来。

实施：

此实现将使用我上面问题中包含的两个表：test_scores_fact 和 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

正如你所看到的，这两张事实表存在重叠键，包括school_code、grade_id、gender_id和ethnicity_id。在关系模型中，每个键字段都有对应的表格，用于存储有关键的其他信息。然而，这种模型与 Qlikview 的关联性质不符，因为 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>
   

2. 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];
   

3. 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];
   

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

5. 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 数据模型将不包含任何合成键或循环引用。

如果您将来创建另一个事实表，只需再次按照步骤1和2操作，并向链接表添加任何新的单独键，以及将新的连接键也添加到链接表中。这样可以轻松扩展。

祝好运！

- bdiamante

---

+1 非常有帮助。小细节：短语“笛卡尔积”在这里不适用。您并没有生成多个集合的乘积。您只是连接列和连接行。 - MarredCheese

Answer 2

在QlikView中，处理多个事实表的数据建模有两种主要策略：

1. 将您的事实表附加到一个单一的事实表中 - 通常称为连接事实，因为QlikView将数据附加到表中的语法是使用CONCATENATE前缀（相当于SQL UNION操作）。

优点：

- 由于数据模型中大型表的数量减少，性能表现良好。 - 实现简单，只需将所有数据附加到一个通用事实表中，同时确保通用字段名引用共同的维度。

缺点：

- 不同的事实表之间没有直接关联。这个含义很重要。这意味着跨事实的交叉分析通常只能通过公共维度实现。任何特定于事实的维度都不会以任何方式连接到不引用这些维度的事实记录。复杂的“集合分析”语法在某种程度上可以缓解这个缺点，但如果您的核心需求是通过事实B的特定维度对事实A进行间接分析，则可能需要改回链接表模型。

2. 构建链接表（您已经做过的）对于大多数实施，选项1是适当的方法。连接事实的属性可以总结为：

优点：

- 由于数据模型中大型表的数量减少，性能表现良好。 - 实现简单，只需将所有数据附加到一个通用事实表中，同时确保通用字段名引用共同的维度。

缺点：

- 不同的事实表之间没有直接关联。这个含义很重要。这意味着跨事实的交叉分析通常只能通过公共维度实现。任何特定于事实的维度都不会以任何方式连接到不引用这些维度的事实记录。复杂的“集合分析”语法在某种程度上可以缓解这个缺点，但如果您的核心需求是通过事实B的特定维度对事实A进行间接分析，则可能需要改回链接表模型。

如何构建链接表是一个复杂的主题，但依赖于传统的数据库链接表设计技术。很容易出错，并产生看似在前端产生正确结果的链接表，但却过于庞大，消耗内存和CPU资源。

根据我的经验，QlikView建模不良是导致性能差的最常见原因。

希望这个简短而不全面的介绍对QlikView中的多事实建模有所帮助，并为您指明正确的方向。

- Jonas Valleskog

Answer 3

然而，每个事实表都有不同的“共享”字段子集，因此我无法正确地在我的事实表中键入。

您笛卡尔维度的输入之一将是对主题和测试代码的“N/A”（因为它不在注册表中）。

因此，当您按“性别”测量时，测试分数与具有有效主题和测试代码的维度记录相匹配，而注册与具有“N/A”主题和测试代码的记录相匹配。

然后，当您按性别卷起时，一切都非常顺利。

- Nick.McDermaid

Answer 4

我能想到的两种最快的方法是：

A) 您可以将事实表左连接到它们所在的相应表中。您只需要重命名字段以避免与其他表冲突即可。

B) 您可以重命名公共字段，方法如下：

1. 在加载事实表之前使用 QUALIFY，在加载事实表之后使用 UNQUALIFY。 2. 使用"[旧字段名称] as [新字段名称]"重命名字段。

假设事实表具有可链接到主表的唯一 id 字段名称，则您不必在主表中重命名任何内容。

我会选择 B-1，因为这似乎不那么麻烦。

QUALIFY
A,
B,
C,
ID;

FactTable1:
Load ID,
A,
B,
C,
From [FactTable1];

FactTable2:
Load ID,
A,
B,
C,
From [FactTable2];

UNQUALIFY
A,
B,
C,
ID;

编辑：如果你想从这些表中创建一个链接表，可以将事实表连接成一个表格，将所有列都放入其中（许多列将为空值，但QlikView处理空值很好）。

我通常会加载事实表并创建一个 id 字段（使用 RowNo() 或 autonumberhash128([唯一 ID 字段名称列表]）），然后在加载到链接表时，也将该 id 字段包含在链接表中。最后，我从事实表中删除所有公共字段，所以它们只存在于链接表中。

- JMon

---

首先，感谢您抽出时间回复。其次，我已经在我的主贴中编辑了一个更具体的例子。在我开始实现任何操作之前，我只想确保我理解自己在做什么。Qualify会将两部分名称添加到字段中，因此如果它是grade_id，现在它将是enrollment_fact.grade_id。Qlikview如何关联现在已经合格的字段？ - bdiamante

您实际上需要重命名新表中的字段才能连接到正确的列。因此，如果您想要enrollment_fact.grade_id，您需要重命名另一个表中的grade_id以便链接到它。 - JMon