教學課程:動態資料列層級安全性與 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. 這可以藉由在 DimUserSecurity 資料表上按一下滑鼠右鍵,然後選取 [編輯],以從 SSMS 完成。This can be done from SSMS by right-clicking on the DimUserSecurity table, and selecting Edit.

  3. 儲存資料表,然後藉由再次以滑鼠右鍵按一下 DimUserSecurity 資料表,然後選取 [Edit top 200 rows] (編輯前 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. 我們會在接下來的步驟和工作中,將焦點重新轉回 Jon 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 資料工具] 中的 [模型] 功能表,然後按一下 [角色]。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 - 步驟 3DimUserSecurity 資料表所定義的使用者。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].

    SalesTerritoryKey 接著會使用透過 LOOKUPVALUE 所傳回的銷售集,限制在 DimSalesTerritory 中顯示的資料列。The set of Sales SalesTerritoryKey's returned by LOOKUPVALUE is then used to restrict the rows shown in the DimSalesTerritory. 只有當資料列的 SalesTerritoryKey 位於由 LOOKUPVALUE 函數所傳回的識別碼集內,該資料列才會顯示。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.

  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 (負責澳洲地區的銷售) 共用儀表板。Now Sumit shares the dashboard with his colleague, Jon Doe, who is responsible for sales in Australia region.

  3. 當 Jon Doe 登入 Power BI 服務並檢視 Sumit 建立的共用儀表板時,Jon Doe 應該會看到他職責區域的銷售狀況。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 和他共用的儀表板時,Jon Doe 能看見澳洲地區的銷售狀況。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! 在內部部署 Analysis Services 表格式模型中定義的動態資料列層級安全性已成功地反映,並可在 Power BI 服務中觀察到。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 Profiler,因為您需要擷取內部部署 SSAS 表格式執行個體上的 SQL Server Profiler 追蹤。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

  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 中取得實際的認證後,根據使用者在資料上擁有的存取權限,Analysis Services 只會傳回他 (或她) 有權限的資料。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 Profiler 看到特定查詢會作為 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.

        "SumEmployeeKey", CALCULATE(SUM(Employee[EmployeeKey]))
    <PropertyList xmlns="urn:schemas-microsoft-com:xml-analysis">``


當使用資料列層級安全性、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.