概念8: 多事实表 Multiple Data Tables-(读书笔记23)

目前为止，我们只使用过一个数据表即Sales表，实际工作中你很可能需要在数据模型中使用多个数据表。当把第二个数据表引入Power BI时，我们可能首先想到要把新的数据表连接到原来的数据表，但这样做是不正确的。将第二个数据表连接到数据模型的正确方法与第一个数据表建模时采用相同的方法，就是数据表应该连接到维度表。

为了更好地理解如何处理多个数据表，让我们看一个常见的业务场景，在实际工作中，假如公司希望加载一个Budget表和一个Sales表。这种情况下的麻烦是：预算表和销售表通常显示的颗粒度(数据明细级别)是不同的。例如，销售表要体现每个单独产品每天的销售情况，但是预算表仅每个产品类别设置月级别的颗粒度。

在我们逐步对这些数据建模之前，先解释一个的问题。

一、多对多关系 Many-to-Many Relationships

预算表的数据如下所示，Category列和Period列都有重复值，这两列是新事实表中用于连接到模型其余部分，Category列需要连接到Products表，Period列需要连接到Calendar表。

下图显示了Products表的一部分数据(左)和Calendar表的一部分数据(右)，仔细看的话，发现有什么问题吗?

有两个主要问题:

•Budget[Category]列需要连接到Product[Category]，但两列包含重复的值。

• Budget[Period]列需要连接到'Calendar'[Period]列，但这两列包含重复的值。

Power BI针对一对多关系进行了优化，“一端”(维度表)应该有一列唯一值(一个主键)，而“多端”(数据表)可以有重复值(一个外键)。Power BI就是按照这种方式构建和优化模型的。如果尝试连接两个在两列中都有重复值的表，PBI会发出警告，提示这样做有风险。

在这个对话框的底部的这个警告信息是有原因的，在Power BI中创建多对多关系是非常危险的，特别是当您不知道自己在做什么的时候，就是说你这样建立关系是为了什么。尽管在Power BI中，它可以在某些条件下完成我们的需要(Power Pivot for Excel中无法做到这一点)。

讨论这个问题，超出了本书的范围，但是有一种方法可以在不需要多对多关系的情况下对数据建模:使用桥接表。

二、如何把 Budget表做成一个桥接表  Here’s How: Bridging a Budget Table

请按以下步骤导入预算表、加载所需的桥接表以及为预算编写度量。

1.在Power BI中，单击获取数据(Get Data) 选择 Excel并找到在第2章中使用的Excel文件。

2. 从导航列表中选择Budget和BudgetPeriod表，如下所示。在本例中，Budget表是数据表，而BudgetPeriod表是桥接表。

3. 单击加载。如下所示，预算表对每个类别都有每月的销售预算，期间列的格式为YYYYMM，表示年和月。

BudgetPeriod表既是一个桥接表，也是一种日历表，但是它与我们使用过的日期表有些不同。从技术上讲，它仍然是一个日历表，但它是一个月度日期表，而不是一个日日期表，与Budget表一样，它包含格式为YYYYMM的Period列，如下所示。

4.您还需要一个ProductCategory桥接表，但是数据源没有，因此需要使用DAX单击New table按钮并键入如下所示的公式来创建一个。

正如您在上面看到的，新的ProductCategory表有一个包含四种产品类别的列表。

注意:为什么建立这两个桥接表，以及它们的使用方式很快就会揭晓。

5.切换到模型视图

重新排列表如下所示:将BudgetPeriod表(见下面#1)放在Calendar表的上面，将Budget表(#2)放在Sales表的旁边。将ProductCategory表(#3)放在Products表的上方，如所示。

6. 要了解为什么需要桥接表，请尝试将Budget表连接到Calendar表。

          当您尝试在Budget[Period]列和Calendar[Period]列之间创建一个新的关系时，PBI会弹出警告消息。

7. 单击“取消”关闭“创建关系”窗口。

Calendar表是每日日历，而Budget表是每月预算(非常常见的业务场景)。Calendar[Peroid]列每个月有28到31个条目，Budget[Peroid]列也有重复的值。从技术上讲，你可以在Power BI中创建多对多关系，但正如前面提到的，这样的关系可能是低效的，也是危险的，本书中不过多介绍这种方法。

要解决这种多对多的数据建模问题，可以使用桥接表，以便仅使用一对多关系连接模型中的表。BudgetPeriod表是一个桥接表，它有一个包含唯一值(一个主键)的Period列。因此，你可以使用一对多的关系将Budget表连接到BudgetPeriod表。事实上，这个关系(见下面#1)是在加载数据时自动创建的。

8. 通过将'Calendar'[Period]列拖到BudgetPeriod[Period]列(参见下面#2)，将Calendar表连接到BudgetPeriod表。

现在您需要将ProductCategory表(它也是一个桥接表)连接到Budget表。如果您尝试将“Budget[Category]”列加入“Product[Category]”列，也会出现刚才的警告信息。

9.  如果要将Budget表连接到ProductCategory桥接表，单击并拖动Budget[Category]列到ProductCategory[Category]。

10. 要将“Product”表连接到“ProductCategory”表，单击“Product”表并拖动“[Category]”列到“ProductCategory”表的“[Category]”列。

完成以上操作后，你应该得到如下布局的模型。注意，当数据模型中有很多表时，跟踪所有的关系变得很困难，这就是我推荐使用Collie布局方法来安排表格的原因之一，如下所示。

正如你在上图中看到的，关系中“多”端的表格在下面，关系中“一”端的表格在上面。筛选总是自上而下，这种布局使我们更容易理解表之间的交互方式。如果你在BudgetPeriod表上放置一个筛选器(参见上面的#1)，这个表将通过直接关系直接筛选Budget表(#4)。此外，BudgetPeriod表(#1)直接筛选Calendar表(#2)，Calendar表(#2)筛选Sales表(#3)。最终的结果应该是，应用到BudgetPeriod表(#1)的任何筛选器都筛选了Sales表(#3)和Budget表(#4)。同样的概念也适用于ProductCategory表(#5)。

在处理不同颗粒度的数据表时(如本例)，在矩阵筛选器中使用正确的表和列非常重要。因此，当在这样的模型中同时使用销售表和预算表时，你应该在视觉效果中使用BudgetPeriod表和ProductCategory表中的列;Calendar表和Products表中的列将不能正确地筛选Sales和Budget表。

三、每个对象使用同一张表 Using One Table for Each Object

我在第二章中介绍的一个通用规则是，你应该针对每个视觉对象使用同一张表(例如，Calendar, Product)。这样做的一个原因是，它能让使用数据模型的人更容易找到他们正在找的东西。上一节也提到过，加载桥接表时，必须在可视化对象中使用桥接表的列，而不是原来的模型中的维度表中的列。

只有桥接表列可以同时筛选Budget表(直接)和Sales表(间接地，通过维度表)。这可能会让创建新视觉对象的用户感到困惑，我们来看Fields窗格中的表。

有两个日期表(calendar和BudgetPeriod)和两个产品表(Products和ProductCategory)。如果用户使用了错误的表列，得到的结果也将是错误的。在下面的图像中，Category列来自Products表，而不是ProductCategory表，因此没有筛选Budget表。

此外，在Calendar表中还有一些其它列(例如，Year)，你可能希望在报表中使用桥接表中不存在的列，这是在数据模型中使用双向交叉筛选的的少数几种情况之一。

四、使用双向交叉筛选 Enter Bidirectional Cross-Filtering

一般来说，我们都要使用单向筛选;也就是说，维度表应该筛选数据表，而不是相反。但在特殊情况下，建议使用双向交叉筛选，其中每个表都可以筛选另一个表，因为它解决了上面提到的两个问题: 

1.它印证了为每个视觉对象使用一个表的概念——至少在最终用户面前是这样)

2.它开放了维度表中其他列的使用权，可以将数据汇总到更高级别的聚合。

❇️ 如何使用双向交叉筛选

1.切换到模型视图。

2. 双击Calendar表和BudgetPeriod表之间的关系连线(如下所示)。

3.将交叉筛选方向设置从单一更改为两者都，如下所示，并单击确定。

4.对ProductCategory表和Products表之间的关系重复此过程。下图显示了这些更改之后的模型视图。

注意:现在的关系表明筛选器是双向的。其含义是，如果使用Calendar表中的列，它将直接筛选Sales表(与更改交叉过滤方向之前相同)，但是Calendar表现在也将筛选BudgetPeriod桥接表。BudgetPeriod表直接筛选Budget表，所以现在Calendar表将同时筛选Sales和Budget表，Products表也是如此。

5.通过单击显示隐藏图标，隐藏BudgetPeriod桥接表，如下所示。

6.对另一个桥接表ProductCategory表重复上述步骤。

做了这些操作之后，一切就显得像是正常的样子了。用户现在可以使用Product[Category]列了(见下面#1)，用户在Fields窗格中也看不到其它捣乱的桥接表了，TOTAL BUDGET显示正常了(#2)。此外，用户现在可以使用Calendar表而不是BudgetPeriod桥接表来生成可视化报表了。

五、向上聚合，不能向下聚合(从颗粒度级别角度) Aggregating Rolls Up, Not Down

需要注意的是，预算数据是在月份和产品类别级别上的，这是预算数据表中包含的最低级别的颗粒度。最低级别的颗粒度数据可以很容易地向上聚合(例如，可以向上聚合月份以创建年度数据)，但数据不能自动创建更细级别的粒度(例如，类别不能自动分解为子类别)。

下图在矩阵中显示了“日历”[年份]和产品[子类别]。

矩阵中的年度列能够正确地把预算从月加到年(即，预算数字在矩阵中按行加起来)。SubCategory(矩阵上的行字段)都显示相同的预算总额(矩阵中的年度TOTAL BUDGET列)。从技术上讲，可以通过使用销售历史数据编写一个预算度量值，将类别预算分配到子类别级别。你可以自己试着写写这个公式。可以试试以下步骤:

1. 为如何解决问题制定一个计划，可以先用Excel做。

2. 利用你学到的使用ALL()函数计算列的百分比(参见第14章)来计算每个子类别的历史销售百分比。

3.使用变量将问题分解，逐个解决问题的每个部分，在进行下一步之前把之前的变量结果放在可视化对象中检查。

六、多数据表实战练习 Practice Exercises: Multiple Data Tables

在预算和销售两个数据表上练习编写新的DAX公式，

首先，创建一个新矩阵。

然后把Product[Category]放在行上，把Calendar[Peroid]放在行上，把[TOTAL SALES]放在值上。当你的矩阵设置好后，点击钻取图标中的扩展到所有级别，如下所示，展开矩阵中的所有级别。

我们编写以下几个度量值，放到矩阵

Total Budget = SUM(Budget[Budget])

Change in Sales vs. Budget = [Total Sales] - [Total Budget]

% Change in Sales vs. Budget =DIVIDE([Change in Sales vs. Budget], [Total Budget])