关系Relationships

适用于: SQL Server Analysis Services Azure Analysis Services Power BI Premium

在表格模型中,关系是两个数据表之间的连接。In tabular models, a relationship is a connection between two tables of data. 该关系确立两个表中的数据应该如何相关。The relationship establishes how the data in the two tables should be correlated. 例如,Customers 表和 Orders 表可以彼此相关,以便显示与每个订单关联的客户名称。For example, a Customers table and an Orders table can be related in order to show the customer name that is associated with each order.

从相同数据源导入时,在您选择导入的数据源) 中已存在的表中 (的关系将在模型中重新创建。When importing from the same data source, relationships that already exist in tables (at the data source) that you choose to import will be re-created in the model. 通过使用关系图视图中的模型设计器或使用“管理关系”对话框,可以查看检测到的关系和自动重新创建的关系。You can view relationships that were detected and re-created automatically by using the model designer in Diagram View or by using the Manage Relationships dialog box. 还可以通过使用关系图视图中的模型设计器或使用“创建关系”或“管理关系”对话框,手动创建表之间的新关系。You can also create new relationships between tables manually by using the model designer in Diagram View or by using the Create Relationship or Manage Relationships dialog box.

在定义了两个表之间的关系(在导入过程中自动创建或手动创建)之后,便能够使用相关列筛选数据以及在相关表中查找值。After relationships between tables have been defined, either automatically during import or created manually, you will be able to filter data by using related columns and look up values in related tables.

提示

如果您的模型包含多个关系,则关系图视图可以更好地展现表之间的关系和创建新关系。If your model contains many relationships, Diagram View can better help you better visualize and create new relationships between tables.

优点Benefits

关系是两个数据表之间的连接,它基于每个表中的一列或多列。A relationship is a connection between two tables of data, based on one or more columns in each table. 要理解关系为何有用,可以想像一下在业务中跟踪客户订单数据。To see why relationships are useful, imagine that you track data for customer orders in your business. 可以在具有以下结构的一个表中跟踪所有数据:You could track all the data in a single table that has a structure like the following:

CustomerIDCustomerID “属性”Name EMailEMail DiscountRateDiscountRate OrderIDOrderID OrderDateOrderDate 产品Product 数量Quantity
11 AshtonAshton chris.ashton@contoso.com .05.05 256256 2010-01-072010-01-07 Compact DigitalCompact Digital 1111
11 AshtonAshton chris.ashton@contoso.com .05.05 255255 2010-01-032010-01-03 SLR CameraSLR Camera 1515
22 JaworskiJaworski michal.jaworski@contoso.com .10.10 254254 2010-01-032010-01-03 Budget Movie-MakerBudget Movie-Maker 2727

这种方法可以用,但会存储大量冗余数据,如每个订单的客户电子邮件地址。This approach can work, but it involves storing a lot of redundant data, such as the customer e-mail address for every order. 存储成本低廉,但如果电子邮件地址发生更改,就必须确保更新该客户的每一行数据。Storage is cheap, but you have to make sure you update every row for that customer if the e-mail address changes. 针对这一问题,一种解决方法是将数据拆分到多个表中,然后在这些表之间定义关系。One solution to this problem is to split the data into multiple tables and define relationships between those tables. 这是 关系数据库 (如 SQL Server)中使用的方法。This is the approach used in relational databases like SQL Server. 例如,导入模型的某个数据库可以使用三个相关表来表示订单数据:For example, a database that you import into a model might represent order data by using three related tables:

客户Customers

[CustomerID][CustomerID] 名称Name 电子邮件Email
11 AshtonAshton chris.ashton@contoso.com
22 JaworskiJaworski michal.jaworski@contoso.com

CustomerDiscountsCustomerDiscounts

[CustomerID][CustomerID] DiscountRateDiscountRate
11 .05.05
22 .10.10

OrdersOrders

[CustomerID][CustomerID] OrderIDOrderID OrderDateOrderDate 产品Product 数量Quantity
11 256256 2010-01-072010-01-07 Compact DigitalCompact Digital 1111
11 255255 2010-01-032010-01-03 SLR CameraSLR Camera 1515
22 254254 2010-01-032010-01-03 Budget Movie-MakerBudget Movie-Maker 2727

如果从同一数据库导入这些表,则导入可以根据 [方括号] 中的列来检测表之间的关系,并可以在模型设计器中再现这些关系。If you import these tables from the same database, Import can detect the relationships between the tables based on the columns that are in [brackets], and can reproduce these relationships in the model designer. 有关详细信息,请参阅本文中的关系的自动检测和推理部分。For more information, see Automatic Detection and Inference of Relationships section in this article. 如果从多个源导入表,则可以手动创建关系,如 创建两个表之间的关系中所述。If you import tables from multiple sources, you can manually create relationships as described in Create a Relationship Between Two Tables.

列和键Columns and keys

关系基于每个表中包含相同数据的列。Relationships are based on columns in each table that contain the same data. 例如,Customers 和 Orders 表可以彼此相关,因为它们都包含存储客户 ID 的列。For example, the Customers and Orders tables can be related to each other because they both contain a column that stores a customer ID. 在本示例中,列名称相同,但这不是必需的。In the example, the column names are the same, but this is not a requirement. 只要 Orders 表的所有行都包含也存储在 Customers 表中的 ID,一列可以是 CustomerID,另一列可以是 CustomerNumber。One could be CustomerID and another CustomerNumber, as long as all of the rows in the Orders table contain an ID that is also stored in the Customers table.

在关系数据库中,有几种类型的键 **,键通常是具有指定属性的列。In a relational database, there are several types of keys, which are typically just columns with special properties. 在关系数据库中,可以使用以下四种类型的键:The following four types of keys can be used in relational databases:

  • 主键:唯一标识表中的一行,如 Customers 表中的 CustomerID。Primary key: uniquely identifies a row in a table, such as CustomerID in the Customers table.

  • 备用键 (或 候选键):主键之外的唯一列。Alternate key (or candidate key): a column other than the primary key that is unique. 例如,Employees 表可能存储雇员 ID 和社会保障号,这两者都是唯一的。For example, an Employees table might store an employee ID and a social security number, both of which are unique.

  • 外键:引用另一表中唯一列的列,如 Orders 表中的 CustomerID(可引用 Customers 表中的 CustomerID)。Foreign key: a column that refers to a unique column in another table, such as CustomerID in the Orders table, which refers to CustomerID in the Customers table.

  • 组合键**:由多列组成的键。Composite key: a key that is composed of more than one column. 表格模型中不支持组合键。Composite keys are not supported in tabular models. 有关详细信息,请参阅本文中的组合键和查找列部分。For more information, see Composite Keys and Lookup Columns section in this article.

在表格模型中,主键或备用键称为“相关查找列” ** 或“查找列” **。In tabular models, the primary key or alternate key is referred to as the related lookup column, or just lookup column. 如果表既有主键又有备用键,则主键和备用键都可作为查找列。If a table has both a primary and alternate key, you can use either as the lookup column. 外键称为“源列” ** 或简单地称为“列” **。The foreign key is referred to as the source column or just column. 在我们的示例中,将在 Orders 表的 CustomerID(列)和 Customers 表的 CustomerID(查找列)之间定义关系。In our example, a relationship would be defined between CustomerID in the Orders table (the column) and CustomerID (the lookup column) in the Customers table. 如果从关系数据库导入数据,默认情况下,模型设计器会从一个表中选择外键,从另一个表中选择相应的主键。If you import data from a relational database, by default the model designer chooses the foreign key from one table and the corresponding primary key from the other table. 但是,您可以将具有唯一值的任意列用作查找列。However, you can use any column that has unique values for the lookup column.

关系类型Types of relationships

Customers 与 Orders 之间的关系是“一对多关系”**。The relationship between Customers and Orders is a one-to-many relationship. 每个客户都可以有多个订单,但一个订单不能有多个客户。Every customer can have multiple orders, but an order cannot have multiple customers. 其他关系类型还有“一对一”** 和“多对多”**。The other types of relationships are one-to-one and many-to-many. 为每个客户定义一个折扣率的 CustomerDiscounts 表与 Customers 表具有一对一关系。The CustomerDiscounts table, which defines a single discount rate for each customer, is in a one-to-one relationship with the Customers table. Products 和 Customers 之间的直接关系就是多对多关系的一个示例,在这种关系中,一个客户可以购买多种产品,同一种产品可由很多客户购买。An example of a many-to-many relationship is a direct relationship between Products and Customers, in which a customer can buy many products and the same product can be bought by many customers. 在用户界面中,模型设计器不支持多对多关系。The model designer does not support many-to-many relationships in the user interface. 有关详细信息,请参阅本文中的多对多关系部分。For more information, see Many-to-Many Relationships section in this article.

下表显示了三个表之间的关系:The following table shows the relationships between the three tables:

关系Relationship 类型Type 查找列Lookup Column Column
Customers-CustomerDiscountsCustomers-CustomerDiscounts 一对一one-to-one Customers.CustomerIDCustomers.CustomerID CustomerDiscounts.CustomerIDCustomerDiscounts.CustomerID
Customers-OrdersCustomers-Orders 一对多one-to-many Customers.CustomerIDCustomers.CustomerID Orders.CustomerIDOrders.CustomerID

关系和性能Relationships and performance

在创建任何关系之后,对于任何公式,如果它们使用新创建的关系中所涉及的各表中的列,模型设计器通常必须对其进行重新计算。After any relationship has been created, the model designer typically must recalculate any formulas that use columns from tables in the newly created relationship. 处理可能需要一些时间,这取决于数据量和关系的复杂度。Processing can take some time depending on the amount of data and the complexity of the relationships.

Requirements for relationshipsRequirements for relationships

创建关系时,模型设计器必须遵守几项要求:The model designer has several requirements that must be followed when creating relationships:

表之间的单个活动关系Single Active Relationship between tables

多个关系会导致表之间存在不明确的依赖关系。Multiple relationships could result in ambiguous dependencies between tables. 若要创建准确的计算,需要从一个表到下一个表的单一路径。To create accurate calculations, you need a single path from one table to the next. 因此,每对表之间只能存在一个活动关系。Therefore, there can be only one active relationship between each pair of tables. 例如,在 AdventureWorks DW 2012 中,表 DimDate 包含一个列 DateKey,该列与表 FactInternetSales 中的以下三个不同列相关:OrderDate、DueDate 和 ShipDate。For example, in AdventureWorks DW 2012, the table, DimDate, contains a column, DateKey, that is related to three different columns in the table, FactInternetSales: OrderDate, DueDate, and ShipDate. 如果您试图导入这些表,则会成功创建第一个关系,但是在创建涉及相同列的后续关系时会接收到下面的错误:If you attempt to import these tables, the first relationship is created successfully, but you will receive the following error on successive relationships that involve the same column:

* 关系:表 [列 1]-> 表 [列 2]-状态:错误-原因:在表和之间无法创建关系 <table 1> <table 2> 。* Relationship: table[column 1]-> table[column 2] - Status: error - Reason: A relationship cannot be created between tables <table 1> and <table 2>. 在两个表之间只能存在一个直接或间接关系。Only one direct or indirect relationship can exist between two tables.

如果您有两个表并且这两个表之间存在多个关系,则需要导入包含查找列的表的多个副本,并在每对表之间创建一个关系。If you have two tables and multiple relationships between them, then you will need to import multiple copies of the table that contains the lookup column, and create one relationship between each pair of tables.

表之间可以有许多非活动关系。There can be many inactive relationships between tables. 表之间要使用的路径在查询时由报表客户端指定。The path to use between tables is specified by the reporting client at query time.

每个源列具有一个关系One relationship for each source column

源列无法具有多个关系。A source column cannot participate in multiple relationships. 如果您已在一个关系中将某列用作源列,但希望使用该列连接到其他表中的另一个相关的查找列,则可以创建该列的副本并使用该列创建新的关系。If you have used a column as a source column in one relationship already, but want to use that column to connect to another related lookup column in a different table, you can create a copy of the column, and use that column for the new relationship.

可使用计算列中的 DAX 公式轻松创建具有完全相同值的列的副本。It is easy to create a copy of a column that has the exact same values, by using a DAX formula in a calculated column. 有关详细信息,请参阅 创建计算列For more information, see Create a Calculated Column.

每个表的唯一标识符Unique identifier for each table

每个表都必须具有一个单独的列,用于唯一标识该表中的每一行。Each table must have a single column that uniquely identifies each row in that table. 该列通常称为主键。This column is often referred to as the primary key.

唯一查找列Unique lookup columns

查找列中的数据值必须是唯一的。The data values in the lookup column must be unique. 也就是说,该列不能包含重复值。In other words, the column cannot contain duplicates. 在表格模型中,Null 和空字符串等效于空白,而空白是一种独特的数据值。In Tabular models, nulls and empty strings are equivalent to a blank, which is a distinct data value. 这意味着查找列中不能包含多个 Null 值。This means that you cannot have multiple nulls in the lookup column.

兼容的数据类型Compatible data types

源列和查找列中的数据类型必须兼容。The data types in the source column and lookup column must be compatible. 有关数据类型的详细信息,请参阅 支持的数据类型For more information about data types, see Data Types Supported.

组合键和查找列Composite keys and lookup columns

不能在表格模型中使用组合键;必须始终有一列来唯一标识表中的每一行。You cannot use composite keys in a tabular model; you must always have one column that uniquely identifies each row in the table. 如果尝试导入基于组合键的现有关系的表,则 Import 将忽略该关系,因为不能在表格模型中创建它。If you try to import tables that have an existing relationship based on a composite key, Import will ignore that relationship because it cannot be created in the tabular model.

如果要在模型设计器中创建两个表之间的关系,并且存在多个定义主键和外键的列,必须先组合这些值创建一个键列,然后才能创建关系。If you want to create a relationship between two tables in the model designer, and there are multiple columns defining the primary and foreign keys, you must combine the values to create a single key column before creating the relationship. 您可以在导入数据之前执行此操作,也可以在模型设计器中通过创建计算列来执行此操作。You can do this before you import the data, or you can do this in the model designer by creating a calculated column.

多对多关系Many-to-many relationships

1500和更高兼容级别的表格模型部署到 Azure Analysis Services、SQL Server 2019 Analysis Services 和 Power BI Premium 支持多对多关系。Tabular models at the 1500 and higher compatibility level deployed to Azure Analysis Services, SQL Server 2019 Analysis Services, and Power BI Premium support many-to-many relationships.

多对多关系是两个列都不唯一的表之间的关系。Many-to-many relationships are those between tables where both columns are non-unique. 可以在维度和事实表之间以高于维度的键列的粒度定义关系。A relationship can be defined between a dimension and fact table at a granularity higher than the key column of the dimension. 这样避免了对维度表进行标准化并且可以改善用户体验,因为生成的模型具有较少带有逻辑分组列的表。This avoids having to normalize dimension tables and can improve the user experience because the resulting model has a smaller number of tables with logically grouped columns.

使用 Visual Studio 2019 与 Analysis Services 项目、表格对象模型 (TOM) API、表格模型脚本语言 (TMSL) 和开源表格编辑器工具创建多对多关系。Create many-to-many relationships by using Visual Studio 2019 with Analysis Services projects, the Tabular Object Model (TOM) API, Tabular Model Scripting Language (TMSL), and the open-source Tabular Editor tool.

1400和更低兼容级别的表格模型不支持多对多关系,并且不能在模型设计器中添加 联接表Tabular models at the 1400 and lower compatibility levels do not support many-to-many relationships, and you cannot add junction tables in the model designer. 但可以使用 DAX 函数为多对多关系建模。However, you can use DAX functions to model many-to-many relationships. 你还可以尝试设置双向交叉筛选器以查看它是否实现同一目的。You can also try setting up a bi-directional cross filter to see if it achieves the same purpose. 有时,通过跨多个表关系保持筛选器上下文的交叉筛选器,可以满足多对多关系的要求。Sometimes the requirement of many-to-many relationship can be satisfied through cross filters that persist a filter context across multiple table relationships. 有关详细信息,请参阅 表格模型中的双向交叉筛选器See Bi-directional cross filters in tabular models for details.

自联接和循环Self-joins and loops

表格模型表中不允许使用自联接。Self-joins are not permitted in tabular model tables. 自联接是一个表与其自身之间的递归关系。A self-join is a recursive relationship between a table and itself. 自联接通常用于定义父子层次结构。Self-joins are often used to define parent-child hierarchies. 例如,可以将 Employees 表联接到其自身,从而生成显示业务管理链的层次结构。For example, you could join an Employees table to itself to produce a hierarchy that shows the management chain at a business.

模型设计器不允许在模型中的关系之间创建循环。The model designer does not allow loops to be created among relationships in a model. 换言之,禁止使用下面这组关系。In other words, the following set of relationships is prohibited.

表 1、列 a 到表 2、列 fTable 1, column a to Table 2, column f

表 2、列 f 到表 3、列 nTable 2, column f to Table 3, column n

表 3、列 n 到表 1、列 aTable 3, column n to Table 1, column a

如果您试图创建的关系会导致创建循环,则会生成错误。If you try to create a relationship that would result in a loop being created, an error is generated.

Inference of relationshipsInference of relationships

在某些情况下,表之间的关系会自动链接。In some cases, relationships between tables are automatically chained. 例如,如果在以下前两组表之间创建关系,则会推断出其他两个表之间存在一个关系,进而自动建立一个关系。For example, if you create a relationship between the first two sets of tables below, a relationship is inferred to exist between the other two tables, and a relationship is automatically established.

Products 和 Category -- 手动创建Products and Category -- created manually

Category 和 SubCategory -- 手动创建Category and SubCategory -- created manually

Products 和 SubCategory -- 推断出关系Products and SubCategory -- relationship is inferred

为使关系自动链接,关系方向必须相同,如上所示。In order for relationships to be automatically chained, the relationships must go in one direction, as shown above. 例如,如果初始关系是在 Sales 和 Products 以及 Sales 和 Customers 之间,则不会推断出关系。If the initial relationships were between, for example, Sales and Products, and Sales and Customers, a relationship is not inferred. 这是因为 Products 和 Customers 之间的关系是多对多关系。This is because the relationship between Products and Customers is a many-to-many relationship.

Detection of relationships when importing dataDetection of relationships when importing data

从关系数据源表中导入时,将基于源架构数据在表之间检测现有关系。When you import from a relational data source table, existing relationships are detected between tables based on the source schema data. 如果导入相关的表,则将在模型中复制这些关系。If related tables are imported, those relationships will be replicated in the model.

Manually create relationshipsManually create relationships

尽管单个关系数据源中的表之间的大多数关系将会被自动检测到并且在表格模型中创建,但还有许多必须手动创建模型表之间的关系的情况。While most relationships between tables in a single relational data source will be detected automatically, and created in the tabular model, there are also many instances where you must manually create relationships between model tables.

如果您的模型中包含来自多个数据源的数据,则可能需要手动关系。If your model contains data from multiple sources, you will likely have to manually create relationships. 例如,您可以从关系数据源导入 Customers、CustomerDiscounts 和 Orders 表。For example, you may import Customers, CustomerDiscounts, and Orders tables from a relational data source. 在源中的这些表之间存在的关系将在模型中自动创建。Relationships existing between those tables at the source are automatically created in the model. 然后,您可以添加来自不同源的其他表,例如,从 Microsoft Excel 工作簿中的 Geography 表导入区域数据。You may then add another table from a different source, for example, you import region data from a Geography table in a Microsoft Excel workbook. 然后,您可以手动在 Customers 表中的某一列和 Geography 表中的某一列之间创建关系。You can then manually create a relationship between a column in the Customers table and a column in the Geography table.

若要手动在表格模型中创建关系,您可以使用关系图视图中的模型设计器或使用“管理关系”对话框。To manually create relationships in a tabular model, you can use the model designer in Diagram View or by using the Manage Relationships dialog box. 关系图视图以图形格式显示表以及表之间的关系。The diagram view displays tables, with relationships between them, in a graphical format. 您可以单击一个表中的某一列,将该列拖放到其他表中以便轻松地在两个表之间以正确顺序创建关系。You can click a column in one table and drag the cursor to another table to easily create a relationship, in the correct order, between the tables. “管理关系”对话框会以简单的表格式显示表之间的关系。The Manage Relationships dialog box displays relationships between tables in a simple table format. 若要了解如何手动创建关系,请参阅 创建两个表之间的关系To learn how to manually create relationships, see Create a Relationship Between Two Tables.

Duplicate values and other errorsDuplicate values and other errors

如果选择了在关系中不能使用的列,该列旁边将出现一个红色的 X。If you choose a column that cannot be used in the relationship, a red X appears next to the column. 您可以将光标暂停在错误图标之上,以查看提供有关该问题的详细信息的消息。You can pause the cursor over the error icon to view a message that provides more information about the problem. 导致无法在所选列之间创建关系的问题包括:Problems that can make it impossible to create a relationship between the selected columns include the following:

问题或消息Problem or message 解决方法Resolution
无法创建关系,因为这两个选定的列包含重复值。The relationship cannot be created because both columns selected contain duplicate values. 若要创建有效的关系,您所选的一对列中应至少有一列必须包含唯一值。To create a valid relationship, at least one column of the pair that you select must contain only unique values.

您可以编辑列来删除重复值,也可以反转列的顺序,以便将包含唯一值的列用作 “相关查找列”You can either edit the columns to remove duplicates, or you can reverse the order of the columns so that the column that contains the unique values is used as the Related Lookup Column.
该列包含 Null 值或空值。The column contains a null or empty value. 对于 Null 值,无法将数据列相互联接。Data columns cannot be joined to each other on a null value. 对于每一行,关系中所用的两列都必须具有值。For every row, there must be a value in both of the columns that are used in a relationship.

另请参阅See also

项目Article 说明Description
创建两个表之间的关系Create a Relationship Between Two Tables 介绍如何手动创建两个表之间的关系。Describes how to manually create a relationship between two tables.
删除关系Delete Relationships 描述如何删除关系和删除关系带来的后果。Describes how to delete a relationship and the ramifications of deleting relationships.
双向交叉筛选器Bi-directional cross filters 描述相关表的双向交叉筛选。Describes two-way cross filtering for related tables. 如果两个表是相关的,且定义了双向交叉筛选器,则当跨第二个表关系查询时,可以使用第一个表关系的筛选上下文。A filter context of one table relationship can be used when querying across a second table relationship if tables are related and bi-directional cross filters are define.