教程︰通过 Analysis Services 表格模型实现动态行级别安全性Tutorial: Dynamic row level security with Analysis services tabular model

本教程演示在 Analysis Services 表格模型中实现行级别安全性而需执行的步骤,以及如何将其用于 Power BI 报表中。This tutorial demonstrates the steps necessary to implement row level security within your Analysis Services Tabular Model, and shows how to use it in in a Power BI report. 本教程中的步骤在示例数据集上完成,旨在让您了解必需的步骤。The steps in this tutorial are designed to let you follow along and learn the steps necessary by completing on a sample dataset.

在本教程中,详细描述了以下步骤,帮助您了解在Analysis Services 表格模型中实现动态行级别安全性而需执行的步骤︰During this tutorial, the following steps are described in detail, helping you understand what you need to do to implement dynamic row level security with Analysis Services tabular model:

  • AdventureworksDW2012 数据库中创建一个新安全表Create a new security table in the AdventureworksDW2012 database
  • 生成含有所需事实数据表和维度表的表格模型Build the tabular model with necessary fact and dimension tables
  • 定义用户的角色和权限Define the roles and permissions for the users
  • 将模型部署到 Analysis Services 表格实例Deploy the model to an Analysis Services tabular instance
  • 使用 Power BI Desktop 生成报表以显示对应于正在访问此报表的用户的数据Use Power BI Desktop to build a report that displays the data corresponding to the user accessing the report
  • 将报表部署到 Power BI 服务。Deploy the report to Power BI service
  • 最后,基于报表创建新仪表板Create a new dashboard based on the report, and finally,
  • 与您的同事共享仪表板Share the dashboard with your coworkers

要执行本教程中的步骤,您需要使用 AdventureworksDW2012 数据库(可以在 此处下载)。To follow the steps in this tutorial you need the AdventureworksDW2012 database, which you can download here.

任务 1︰创建用户安全表并定义数据关系Task 1: Create the user security table and define data relationship

很多已发布的文章都介绍了如何通过 SQL Server Analysis Services (SSAS) 表格模型定义行级别动态安全。There are many published articles describing how to define row level dynamic security with SQL Server Analysis Services (SSAS) tabular model. 在本示例中,我们将遵循此篇文章。For our sample we follow this article. 以下步骤引导您完成本教程的第一项任务。The following steps walk you through the first task in this tutorial.

  1. 本示例中,我们采用 AdventureworksDW2012 关系数据库。For our sample, we're using AdventureworksDW2012 relational database. 在该数据库中创建 DimUserSecurity 表,如下图所示。In that database, create the DimUserSecurity table, as shown in the following image. 本示例中,我们使用 SQL Server Management Studio (SSMS) 来创建表。For this sample, we're using SQL Server Management Studio (SSMS) to create the table.

  2. 一旦创建并保存表,我们需要创建 DimUserSecurity 表的 SalesTerritoryID 列和 DimSalesTerritory 表的 SalesTerritoryKey 列之间的关系,如下图所示。Once the table is created and saved, we need to create the relationship between the DimUserSecurity table's SalesTerritoryID column and DimSalesTerritory table's SalesTerritoryKey column, as shown in the following image. 这可以从 SSMS 中完成,方法是右键单击 DimUserSecurity 表并选择编辑This can be done from SSMS by right-clicking on the DimUserSecurity table, and selecting Edit.

  3. 保存表,然后向该表中添加几行用户信息,为此,可再次右键单击 DimUserSecurity 表然后选择 编辑前 200 行Save the table, then add few rows of user information in to the table by again right clicking on the DimUserSecurity table and then selecting Edit top 200 rows. 在添加这些用户后,DimUserSecurity 表的行类似于下图中所示︰Once you’ve added those users, the rows of the DimUserSecurity table look like they do in the following image:

    我们会在即将开始的任务中重新使用这些用户。We’ll come back to these users in upcoming tasks.

  4. 接下来,我们在 DimSalesTerritory 表中执行内部联接,其中显示了与用户关联的区域详细信息。Next we do an inner join with the DimSalesTerritory table, which shows the region details associated with the user. 下面的代码可执行内部联接,之后的图显示了在内部联接成功后的表显示方式。The following code performs the inner join, and the image that follows shows how the table appears once the inner join is successful.

    **select b.SalesTerritoryCountry, b.SalesTerritoryRegion, a.EmployeeKey, a.FirstName, a.LastName, a.UserName from [dbo].[DimUserSecurity] as a join  [dbo].[DimSalesTerritory] as b on a.[SalesTerritoryKey] = b.[SalesTerritoryKey]**
    

  5. 请注意,上图显示了诸如销售区域的负责用户等信息。Notice that the above image shows information such as which user is responsible for which sales region. 由于我们已在步骤 2 中创建关系,所以会显示此数据。That data is displayed because of the relationship that we created in Step 2. 另请注意,用户 Jon Doe 属于澳大利亚销售区域Also, note that the user Jon Doe is part of the Australia sales region. 我们会在即将执行的步骤和任务中再度讨论 John Doe。We’ll revisit John Doe in upcoming steps and tasks.

任务 2︰创建含事实数据表和维度表的表格模型Task 2: Create the tabular model with facts and dimension tables

  1. 在准备好关系数据仓库后,即可定义您的表格模型。Once your relational data warehouse is in place, it’s time to define your tabular model. 可以使用 SQL Server Data Tools (SSDT) 创建该模型。The model can be created using SQL Server Data Tools (SSDT). 有关如何定义表格模型的详细信息,请参阅此篇文章To get more information about how to define a tabular model, please refer this article.
  2. 将所有必需表导入模型中,如下所示。Import all the necessary tables in to the model as shown below.

  3. 在导入必需的表之后,您需要定义一个名为 SalesTerritoryUsers的具有读取权限的角色。Once you’ve imported the necessary tables, you need to define a role called SalesTerritoryUsers with Read permission. 这可以通过在 SQL Server Data Tools 中单击模型菜单然后单击角色来实现。This can be achieved by clicking on the Model menu in SQL Server Data Tools, and then clicking Roles. 角色管理器对话框中,单击新建In the Role Manager dialog box, click New.
  4. 角色管理器中的成员选项卡下,添加我们已在任务 1-第 3 步中在 DimUserSecurity 表中定义的用户。Under Members tab in the Role Manager, add the users that we defined in the DimUserSecurity table in Task 1 - step 3.

  5. 接下来,为 DimSalesTerritoryDimUserSecurity 表添加适当的函数,如下面的行筛选器选项卡下所示。Next, add the proper functions for both DimSalesTerritory and DimUserSecurity tables, as shown below under Row Filters tab.

  6. 在此步骤中,我们使用 LOOKUPVALUE 函数返回列的值,在其中 Windows 用户名与 USERNAME 函数所返回的用户名相同。In this step, we use the LOOKUPVALUE function to return values for a column in which the Windows user name is the same as the user name returned by the USERNAME function. 然后,可将查询限制为满足以下条件的情况:LOOKUPVALUE 返回的值与相同或相关表中的值相匹配。Queries can then be restricted where the values returned by LOOKUPVALUE match values in the same or related table. DAX 筛选器列中,键入以下公式︰In the DAX Filter column, type the following formula:

    =DimSalesTerritory[SalesTerritoryKey]=LOOKUPVALUE(DimUserSecurity[SalesTerritoryID], DimUserSecurity[UserName], USERNAME(), DimUserSecurity[SalesTerritoryID], DimSalesTerritory[SalesTerritoryKey])
    
  7. 在此公式中,LOOKUPVALUE 函数将返回 DimUserSecurity[SalesTerritoryID] 列的所有值,其中,DimUserSecurity[UserName] 与当前登录的 Windows 用户名相同,DimUserSecurity[SalesTerritoryID]DimSalesTerritory[SalesTerritoryKey] 相同。In this formula, the LOOKUPVALUE function returns all values for the DimUserSecurity[SalesTerritoryID] column, where the DimUserSecurity[UserName] is the same as the current logged on Windows user name, and DimUserSecurity[SalesTerritoryID] is the same as the DimSalesTerritory[SalesTerritoryKey].

    接着,LOOKUPVALUE 返回的 Sales SalesTerritoryKey 集将用于限制 DimSalesTerritory 中所示的行。The set of Sales SalesTerritoryKey's returned by LOOKUPVALUE is then used to restrict the rows shown in the DimSalesTerritory. 将仅显示 SalesTerritoryKey 属于 LOOKUPVALUE 函数所返回的 ID 集的行。Only rows where the SalesTerritoryKey for the row is in the set of IDs returned by the LOOKUPVALUE function are displayed.

  8. 针对 DimUserSecurity 表,在“DAX 筛选器”列中键入以下公式。For the DimUserSecurity table, in the DAX Filter column, type the following formula.

    =FALSE()
    
  9. 此公式指定所有列均解析为 false 布尔条件;因此,DimUserSecurity 表没有可查询的列。This formula specifies that all columns resolve to the false Boolean condition; therefore, no columns for the DimUserSecurity table can be queried.
  10. 现在,我们需要处理并部署该模型。Now we need to process and deploy the model. 你可以参考此文,以便在部署该模型时获得帮助。You can refer this article for assistance in deploying the model.

任务 3:在本地数据网关中添加数据源Task 3: Adding Data Sources within your on-premises data gateway

  1. 在部署表格模型并使其可供使用后,需要向 Power BI 门户中的本地 Analysis Services 表格服务器添加数据源连接。Once your tabular model is deployed and ready for consumption, you need to add a data source connection to your on-premises Analysis Services tabular server with in your Power BI portal.
  2. 若要允许 Power BI 服务访问本地分析服务,你需要在环境中安装并配置本地数据网关To allow the Power BI service access your on-premises analysis service, you need to have an on-premises data gateway installed and configured in your environment.
  3. 正确配置本地数据网关后,需要为 Analysis Services 表格实例创建一个数据源连接。Once the gateway is correctly configured, you need to create a data source connection for your Analysis Services tabular instance. 本文将帮助你在 Power BI 门户中添加数据源This article will help you with adding data source within the Power BI portal.

  4. 完成上一个步骤后,网关便已配置完成,并且可与本地 Analysis Services 数据源交互。With the previous step complete, the gateway is configured and ready interact with your on-premises Analysis Services data source.

任务 4:使用 Power BI Desktop 基于 Analysis Services 表格模型创建报表Task 4: Creating report based on analysis services tabular model using Power BI desktop

  1. 启动 Power BI Desktop 并选择“获取数据”>“数据库”。Launch Power BI Desktop and select Get Data > Database.
  2. 从数据源列表中选择“SQL Server Analysis Services 数据库”,然后选择“连接”。From the list of data sources, select the SQL Server Analysis Services Database and select connect.

  3. 填写 Analysis Services 表格实例详细信息,然后选择“实时连接”。Fill in your Analysis Services tabular instance details and select Connect Live. 选择“确定”。Select OK. 使用 Power BI 时,动态安全性仅适用于实时连接With Power BI, dynamic security works only with Live connection.

  4. 你会发现模型已部署在 Analysis Services 实例中。You'll see that the model that was deployed in the Analysis Services instance. 选择相应的模型并选择“确定”。Select the respective model and select OK.

  5. Power BI Desktop 现在在画布右侧的“字段”窗格中显示所有可用字段。Power BI Desktop now displays all the available fields, to the right of the canvas in the Fields pane.
  6. 在右侧的“字段”窗格中,从 FactInternetSales 表中选择 SalesAmount 度量值,从 SalesTerritory 表中选择 SalesTerritoryRegion 维度。In the Fields pane on the right, select the SalesAmount measure from FactInternetSales table and SalesTerritoryRegion dimension from SalesTerritory table.
  7. 我们将让此报表看上去简单明了,因此现在不会再添加任何列。We’ll keep this report simple, so right now we won’t add any more columns. 为了让数据的表示形式更有意义,我们将可视化效果更改为“环形图”。To have more meaningful representation of the data, we'll change the visualization to Donut chart.

  8. 等报表准备就绪后,你就可以将它直接发布到 Power BI 门户。Once your report is ready, you can directly publish it to the Power BI portal. Power BI Desktop 的“主页”功能区中选择“发布”。From the Home ribbon in Power BI Desktop, select Publish.

任务 5:创建和共享仪表板Task 5: Creating and sharing a dashboard

  1. 你已经在 Power BI Desktop 中创建了报表,并且单击了“发布”,因此,该报表将发布到 Power BI 服务中。You’ve created the report and clicked Publish in Power BI Desktop, so the report is published to the Power BI service. 由于它已在该服务中,因此,可以使用在前述步骤中创建的示例来演示我们的模型安全性方案。Now that it’s in the service, our model security scenario can be demonstrated by using the example we created in the previous steps.

    销售经理 Sumit 的角色中,他可以看到所有不同销售区域的数据。In his role, Sales Manager - Sumit can see data from all the different sales regions. 因此,他创建此报表(在前面的任务步骤中创建的报表),并将其发布到 Power BI 服务。So he creates this report (the report created in the previous task steps) and publishes it to the Power BI service.

    发布报表后,他便在 Power BI 服务中基于该报表创建一个名为 TabularDynamicSec 的仪表板。Once he publishes the report, he creates a dashboard in the Power BI service called TabularDynamicSec based on that report. 在下图中,请注意销售经理 Sumit 可以看到所有销售区域的对应数据。In the following image, notice that the sales Manager (Sumit) is able to see the data corresponding to all the sales region.

  2. 现在,Sumit 与他的同事 Jon Doe 共享此仪表板,Jon Doe 负责澳大利亚地区的销售。Now Sumit shares the dashboard with his colleague, Jon Doe, who is responsible for sales in Australia region.

  3. 当 Jon Doe 登录到 Power BI 服务并查看 Sumit 创建的共享仪表板时,他应该只能看到自己所负责的区域的销售额。When Jon Doe logs in to the Power BI service and views the shared dashboard that Sumit created, Jon Doe should see only the sales from his region for which he is responsible. 因此,当 Jon Doe 登录并访问 Sumit 共享给他的仪表板时,他只能看到澳大利亚地区的销售额。So Jon Doe logs in, accesses the dashboard that Sumit shared with him, and Jon Doe sees only the sales from the Australia region.

  4. 祝贺你!Congratulations! Power BI 服务中已成功反映并显示本地 Analysis Services 表格模型中定义的动态行级别安全性。The dynamic row level security that was defined in the on-premises Analysis Services tabular model has been successfully reflected and observed in the Power BI service. Power BI 使用 effectiveusername 属性将当前 Power BI 用户凭据发送到本地数据源,以运行查询。Power BI uses the effectiveusername property to send the current Power BI user credential to the on-premises data source to run the queries.

任务 6:了解幕后发生了什么Task 6: Understanding what happens behind the scenes

  1. 此任务假定你熟悉 SQL 事件探查器,因为你需要捕获本地 SSAS 表格实例上的 SQL Server 事件探查器踪迹。This task assumes you're familiar with SQL Profiler, since you need to capture a SQL Server profiler trace on your on-premises SSAS tabular instance.
  2. 只要用户(本例中为 Jon Doe)访问 Power BI 服务中的仪表板,会话就会初始化。The session gets initialized as soon as the user (Jon Doe, in this case) accesses the dashboard in the Power BI service. 你会发现,salesterritoryusers 角色立即生效,有效用户名为 jondoe@moonneo.comYou can see that the salesterritoryusers role takes an immediate effect with the effective user name as jondoe@moonneo.com

    <PropertyList><Catalog>DefinedSalesTabular</Catalog><Timeout>600</Timeout><Content>SchemaData</Content><Format>Tabular</Format><AxisFormat>TupleFormat</AxisFormat><BeginRange>-1</BeginRange><EndRange>-1</EndRange><ShowHiddenCubes>false</ShowHiddenCubes><VisualMode>0</VisualMode><DbpropMsmdFlattened2>true</DbpropMsmdFlattened2><SspropInitAppName>PowerBI</SspropInitAppName><SecuredCellValue>0</SecuredCellValue><ImpactAnalysis>false</ImpactAnalysis><SQLQueryMode>Calculated</SQLQueryMode><ClientProcessID>6408</ClientProcessID><Cube>Model</Cube><ReturnCellProperties>true</ReturnCellProperties><CommitTimeout>0</CommitTimeout><ForceCommitTimeout>0</ForceCommitTimeout><ExecutionMode>Execute</ExecutionMode><RealTimeOlap>false</RealTimeOlap><MdxMissingMemberMode>Default</MdxMissingMemberMode><DisablePrefetchFacts>false</DisablePrefetchFacts><UpdateIsolationLevel>2</UpdateIsolationLevel><DbpropMsmdOptimizeResponse>0</DbpropMsmdOptimizeResponse><ResponseEncoding>Default</ResponseEncoding><DirectQueryMode>Default</DirectQueryMode><DbpropMsmdActivityID>4ea2a372-dd2f-4edd-a8ca-1b909b4165b5</DbpropMsmdActivityID><DbpropMsmdRequestID>2313cf77-b881-015d-e6da-eda9846d42db</DbpropMsmdRequestID><LocaleIdentifier>1033</LocaleIdentifier><EffectiveUserName>jondoe@moonneo.com</EffectiveUserName></PropertyList>
    
  3. 基于有效用户名请求,Analysis Services 在查询本地 Active Directory 后将请求转换为真实的 moonneo\jondoe 凭据。Based on the effective user name request, Analysis Services converts the request to the actual moonneo\jondoe credential after querying the local Active Directory. Analysis Services 从 Active Directory 获取真实凭据后,就会基于用户对数据的访问权限仅返回用户对其具有权限的数据。Once Analysis Services gets the actual credential from Active Directory, then based on the access the user has permissions for on the data, Analysis Services returns the only the data for which he or she has permission.
  4. 如果使用仪表板执行更多活动,例如,如果 Jon Doe 从仪表板转到基础报表,则可以在 SQL 事件探查器中看到作为 DAX 查询返回到 Analysis Services 表格模型的特定查询。If more activity occurs with the dashboard, for example, if Jon Doe goes from the dashboard to the underlying report, with SQL Profiler you would see a specific query coming back to the Analysis Services tabular model as a DAX query.

  5. 你还可以从下面看到正在执行该 DAX 查询以填充报表数据。You can also see below the DAX query that is getting executed to populate the data for the report.

    EVALUATE
      ROW(
        "SumEmployeeKey", CALCULATE(SUM(Employee[EmployeeKey]))
      )
    
    <PropertyList xmlns="urn:schemas-microsoft-com:xml-analysis">``
              <Catalog>DefinedSalesTabular</Catalog>
              <Cube>Model</Cube>
              <SspropInitAppName>PowerBI</SspropInitAppName>
              <EffectiveUserName>jondoe@moonneo.com</EffectiveUserName>
              <LocaleIdentifier>1033</LocaleIdentifier>
              <ClientProcessID>6408</ClientProcessID>
              <Format>Tabular</Format>
              <Content>SchemaData</Content>
              <Timeout>600</Timeout>
              <DbpropMsmdRequestID>8510d758-f07b-a025-8fb3-a0540189ff79</DbpropMsmdRequestID>
              <DbPropMsmdActivityID>f2dbe8a3-ef51-4d70-a879-5f02a502b2c3</DbPropMsmdActivityID>
              <ReturnCellProperties>true</ReturnCellProperties>
              <DbpropMsmdFlattened2>true</DbpropMsmdFlattened2>
              <DbpropMsmdActivityID>f2dbe8a3-ef51-4d70-a879-5f02a502b2c3</DbpropMsmdActivityID>
            </PropertyList>
    

注意事项Considerations

使用行级别安全性、SSAS 和 Power BI 时需要牢记几个注意事项。There are a few considerations to keep in mind when working with row level security, SSAS and Power BI.

  1. Power BI 的本地行级别安全性只能用于实时连接。On-premises row level security with Power BI is only available with Live Connection.
  2. 基于 Power BI 服务中的实时连接访问报表的用户可以立即获得处理模型后的任何数据更改。Any changes in the data after processing the model would be immediately available for the users who are accessing the report is based on live connection from the Power BI service.