当表之间建立关系时，幕后发生了什么? [英] 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

这很好用，因为 Table1 中的 A 列包含唯一值并且可以用作主键.现在您可以前往 Report 选项卡，设置两个表格，然后通过直接单击 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:

但问题是您可以在不建立表之间关系的情况下 做到这一点.删除Relationships下的relationshipop，回到Report，选择Home>；管理关系 看看我的意思:

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:

正如对话框所说 '尚未定义任何关系.' 但是您可以仍然像以前一样通过在另一个表中进行选择来对一个表进行子集( RADO 的回答已证明此陈述是错误的).我确实知道您可以突出显示切片器并选择 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屋！