在表之间建立关系时，幕后发生了什么？ [英] What is happening under the hood when a relationship is established between tables?

问题描述

此问题不仅限于Power BI，但可以帮助我解释我的问题。

This question is not limited to Power BI, but it will help me explain my problem.

如果Power BI中有多个表，则可以建立通过将一个列从一个表拖到另一个表之间来建立它们之间的关系，像这样：

If you have more than one table in Power BI, you can establish a relationship between them by dragging a column from one table to the other like this:

然后您可以通过单击出现的行来编辑该关系：

And you can edit that relationship by clicking the occuring line:

顺便说一下，这是两个表的结构：

And by the way, here are the structures of the two tables:

# Table1
A,B
1,abc
2,def
3,ghi
4,jkl

# Table2
A,C
1,abc
1,def
2,ghi
3,ghit

自从A列在表1由唯一值组成，可以用作主键。现在，您可以转到报告选项卡，设置两个表，并通过直接单击Table1中的A下方或引入一个切片器：

This works fine since column A in Table1 consists of unique values and can work as a primary key. And now you can head over to the Report tab, set up two tables, and slice and dice at your hearts desire either by clicking directly under A in Table1, or by introducing a slicer:

但是问题是可以在没有建立表之间关系的情况下 进行操作。删除关系下的relationshiop，然后返回到报告，然后选择首页>管理关系来了解我的意思：

But the thing is that you can do that without having established a relationship between the tables. Delete the relationshiop under Relationships and go back to Report and select Home > Manage Relationships to see what I mean:

如对话框中所示：尚未定义任何关系。 但是，您可以仍然将一个表子集化就像以前一样在其他选项中进行选择（编辑：）。我知道，您可以突出显示切片器并选择 Format>编辑交互并取消选择与切片器关联的表。但是我仍然对整个事情感到困惑。

As the dialog box says 'There are no relationships defined yet.' But you can still subset one table by making selections in the other just like before ( This statement has been proven wrong in the answer from RADO) . I do know that you can highlight the slicer and select Format > Edit Interactions and deselect the tables associated with the slicer. But I'm still puzzled by the whole thing.

那么，这里面有什么我不知道的事情发生了吗？或者表之间的关系真的是由表的内容定义的吗？因为表中相关值的存在与潜在主键（自然的或合成的）的存在使得可以使用SQL，dplyr动词或任何其他形式的查询技术来查询它们。并且您真的不需要显式定义的关系吗？

So is there something happening under the hood here that I'm not aware of? Or is the relationship between tables really defined by the very contents of the tables - in the sence that the existence of related values accross tables with the existence of a potential primary key (be it natural or synthetic) makes it possible to query them using SQL, dplyr verbs or any other form of querying techniques. And that you really do not need an explicitly defined relationship?

或者换句话说，建立Power BI表关系是否具有SQL等效项？也许像以下内容：

Or put in another way, does the establishment of a Power BI table relationship have a SQL equivalent? Perhaps like the following:

CREATE TABLE Persons (
    ID int NOT NULL,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Age int,
    PRIMARY KEY (ID)
);

对不起，如果我在这里闲逛，但我只是非常到目前为止，谷歌搜索只会加剧混乱。因此，谢谢您的任何见解！

I'm sorry If I'm rambling a bit here, but I'm just very confused. And googling has so far only added to the confusion. So thank you for any insights!

推荐答案

您的语句但是您仍然可以像在另一个表中进行选择那样对一个表进行子集化，就像之前是不正确。这是这里的关键问题。

Your statement "But you can still subset one table by making selections in the other just like before" is not correct. This is a key issue here.

关系使Power BI中的筛选器上下文得以传播。这是一个非常繁琐的短语，如果您打算使用Power BI，则必须了解它的含义。这是最重要的概念。

Relations enable propagation of filter context in Power BI. That's a very loaded phrase, and you will have to learn what it means if you plan to use Power BI. It's the most important concept to understand.

要理解我的意思，您将需要编写DAX度量并尝试使用表对其进行操作。有或没有关系时，您会立即看到差异。

To see what I mean, you will need to write DAX measures and try to manipulate them using your tables. You will immediately see the difference when you have or don't have relations.

整个系统的工作方式（简化）：
PowerBI包含一种称为 DAX的语言。您将在DAX中创建度量，然后PowerBI会将其转换为其内部语言xmSQL，这是SQL的一种特殊形式。在xmSQL中，常规连接被转换为LEFT OUTER JOIN，如下所示：

How the whole system works (simplified): PowerBI contains a language called "DAX". You will create measures in DAX, and PowerBI will then translate them into its internal language called xmSQL, which is a special flavor of SQL. In xmSQL, regular connection is translated into LEFT OUTER JOIN, like this:

SELECT SUM(Sales.Amount)
FROM Sales
LEFT OUTER JOIN Customer
ON Sales.Customer_Key = Customer.Customer_Key

双向关系较为复杂，但在概念上相似。

By-directional relations are a bit more complex, but conceptually similar.

总的来说，当您在表之间创建关系时，您在告诉PowerBI引擎如何联接表。然后，引擎还添加了一些优化措施以加快查询速度。
每次执行DAX度量时，单击切片器或视觉效果，PowerBI都会在后台生成多个xmSQL语句，执行它们，然后将其结果呈现为视觉效果。您可以使用DAX Studio等工具查看这些SQL查询。

Overall, when you create relations between tables, you are telling PowerBI engine how to join the tables. The engine then also adds some optimizations to speed up the queries. Every time you execute a DAX measure, click a slicer or a visual, PowerBI generates multiple xmSQL statements in the background, executes them, and then renders their results as visuals. You can see these SQL queries with some tools such as DAX Studio.

请注意，并非一定要在PowerBI中的表之间建立关系。您可以使用DAX（以编程方式）模仿相同的行为，但是这种虚拟关系更加复杂，并且速度可能会慢得多。

Note that it's not strictly necessary to establish relations between tables in PowerBI. You can imitate the same behavior using DAX (programmatically), but such "virtual" relations are more complex and can be substantially slower.

这篇关于在表之间建立关系时，幕后发生了什么？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！