在內部部署 Analysis Services 表格式模型中實作數據列層級安全性

  • 發行項

本教學課程會使用範例語意模型來處理下列步驟，示範如何在內部部署 Analysis Services 表格式模型中實作數據列層級安全性，並在 Power BI 報表中使用。

  • 在 AdventureworksDW2012 資料庫中建立新的安全性數據表
  • 使用必要的事實和維度數據表建置表格式模型
  • 定義使用者角色和許可權
  • 將模型部署到 Analysis Services 表格式 實例
  • 建置 Power BI Desktop 報表，顯示針對存取報表的使用者量身打造的數據
  • 將報表部署至 Power BI 服務
  • 根據報表建立新的儀錶板
  • 與同事共用儀錶板

本教學課程 需要 AdventureworksDW2012 資料庫。

工作 1：建立使用者安全性數據表並定義數據關聯性

您可以找到許多文章，說明如何使用 SQL Server Analysis Services （SSAS） 表格式模型來定義數據列層級動態安全性。

此處的步驟需要使用 AdventureworksDW2012 關係資料庫。

  1. 在 AdventureworksDW2012 中 DimUserSecurity ，建立數據表，如下所示。 您可以使用 SQL Server Management Studio （SSMS） 來建立數據表。

    建立 DimUserSecurity 數據表

  2. 建立並儲存數據表之後，您必須建立數據表數據SalesTerritoryID行與DimSalesTerritory數據表數據SalesTerritoryKey行之間的DimUserSecurity關聯性，如下所示。

    在 SSMS 中，以滑鼠右鍵按兩下 [DimUserSecurity]，然後選取 [ 設計]。 然後選取 [數據表設計工具>關聯性...]。完成後，請儲存數據表。

    外鍵關聯性

  3. 將使用者新增至數據表。 以滑鼠右鍵按兩下 [DimUserSecurity ]，然後選取 [ 編輯前 200 個數據列]。 新增用戶之後， DimUserSecurity 數據表看起來應該類似下列範例：

    具範例使用者的 DimUserSecurity 數據表

    您將會在即將執行的工作中看到這些使用者。

  4. 接下來，使用DimSalesTerritory數據表執行內部聯結，以顯示使用者相關聯的區域詳細數據。 這裡的 SQL 程式代碼會執行內部聯結，而影像會顯示數據表的顯示方式。

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

    聯結數據表會顯示誰負責每個銷售區域，這要歸功於在步驟 2 中建立的關聯性。 例如，您可以看到 麗塔·桑托斯 負責 澳大利亞。

工作 2：使用事實和維度數據表建立表格式模型

一旦關係型數據倉儲就緒，您必須定義表格式模型。 您可以使用 SQL Server Data Tools 建立模型（SSDT）。 如需詳細資訊，請參閱 建立新的表格式模型專案。

  1. 將所有必要的數據表匯入模型，如下所示。

    匯入的 SQL Server 與數據工具搭配使用

  2. 匯入必要的數據表之後，您必須使用讀取許可權來定義名為 SalesTerritoryUsers 的角色。 選取 SQL Server Data Tools 中的 [ 模型 ] 功能表，然後選取 [ 角色]。 在 [角色管理員] 中，選取 [ 新增]。

  3. 在 [角色管理員的成員] 底下，新增您在工作 1 中數據表中DimUserSecurity定義的使用者。

    在角色管理員中新增使用者

  4. 接下來，為 和 DimUserSecurity 數據表新增適當的函式DimSalesTerritory，如下所示，在 [數據列篩選] 索引卷標底下。

    將函式新增至數據列篩選

  5. 函 LOOKUPVALUE 式會傳回 Windows 用戶名稱符合 USERNAME 函式所傳回之數據行的值。 然後，您可以將查詢限制為傳 LOOKUPVALUE 回的值符合相同或相關數據表中的查詢。 在 [DAX 篩選] 數據行中，輸入下列公式：

        =DimSalesTerritory[SalesTerritoryKey]=LOOKUPVALUE(DimUserSecurity[SalesTerritoryID], DimUserSecurity[UserName], USERNAME(), DimUserSecurity[SalesTerritoryID], DimSalesTerritory[SalesTerritoryKey])

    在此公式中，函 LOOKUPVALUE 式會傳 DimUserSecurity[SalesTerritoryID] 回數據行的所有值，其中 DimUserSecurity[UserName] 與目前登入的 Windows 用戶名稱相同，且 DimUserSecurity[SalesTerritoryID] 與 DimSalesTerritory[SalesTerritoryKey]相同。

    重要

    使用數據列層級安全性時，不支援DAX函 式USERELATIONSHIP 。

    接著會使用 Sales SalesTerritoryKey的 LOOKUPVALUE 傳回集合來限制 中顯示的 DimSalesTerritory數據列。 只有值位於函式傳回之標識碼LOOKUPVALUE中的數據列SalesTerritoryKey才會顯示。

  6. DimUserSecurity針對數據表，在 DAX 篩選數據行中，新增下列公式：

        =FALSE()

    此公式會指定所有數據行解析為 false，這表示 DimUserSecurity 無法查詢數據表數據行。

現在您需要處理和部署模型。 如需詳細資訊，請參閱 部署。

工作 3：在內部部署數據閘道內新增數據源

部署表格式模型並準備好取用之後，您必須將數據源連線新增至內部部署 Analysis Services 表格式伺服器。

  1. 若要允許 Power BI 服務 存取內部部署分析服務，您需要在您的環境中安裝及設定內部部署數據閘道。

  2. 正確設定閘道之後，您必須為 Analysis Services 表格式實例建立數據源連線。 如需詳細資訊，請參閱 管理您的數據源 - Analysis Services。

    建立數據源聯機

完成此程式之後，閘道會設定並準備好與您的內部部署 Analysis Services 數據源互動。

工作 4：使用 Power BI Desktop 根據 Analysis Services 表格式模型建立報表

  1. 啟動 Power BI Desktop，然後選取 [取得數據>資料庫]。

  2. 從數據源清單中，選取 [SQL Server Analysis Services 資料庫]，然後選取 [連線]。

    連線 至 SQL Server Analysis Services 資料庫

  3. 填入 Analysis Services 表格式實例詳細數據，然後選取 [即時 連線]。 然後選取確定。

    Analysis Services 詳細數據

    使用 Power BI 時，動態安全性僅適用於即時連線。

  4. 您可以看到已部署的模型位於 Analysis Services 實例中。 選取個別的模型，然後選取 [ 確定]。

    Power BI Desktop 現在會在 [欄位] 窗格中的畫布右側顯示所有可用的欄位。

  5. 在 [欄位] 窗格中，從 FactInternetSales 數據表選取 SalesAmount 量值，並從 SalesTerritory 數據表選取 SalesTerritoryRegion 維度。

  6. 為了讓此報表保持簡單，我們現在不會再新增任何數據行。 若要擁有更有意義的數據表示法，請將視覺效果變更為 環圈圖。

    環圈圖視覺效果

  7. 報表準備就緒后，您可以直接將其發佈至 Power BI 入口網站。 從 Power BI Desktop 的 [首頁] 功能區中，選取 [發佈]。

工作 5：建立和共用儀錶板

您已建立報表，並將其發佈至 Power BI 服務。 現在，您可以使用先前步驟中建立的範例來示範模型安全性案例。

在 Sales Manager 角色中，使用者 Grace 可以看到來自所有不同銷售區域的數據。 Grace 會建立此報表，並將其發佈至 Power BI 服務。 此報告是在先前的工作中建立的。

一旦 Grace 發佈報表，下一個步驟就是根據該報表，在名為 TabularDynamicSec 的 Power BI 服務 中建立儀錶板。 在下圖中，請注意，Grace 可以看到對應至所有銷售區域的數據。

Power BI 服務 儀錶板

現在，Grace 會與負責澳大利亞地區銷售的同事 Rita 共用儀錶板。

共用Power BI儀錶板

當 Rita 登入 Power BI 服務 並檢視 Grace 建立的共用儀錶板時，只會顯示來自澳大利亞區域的銷售額。

恭喜！ Power BI 服務 會顯示內部部署 Analysis Services 表格式模型中定義的動態數據列層級安全性。 Power BI 會 EffectiveUserName 使用 屬性將目前的 Power BI 使用者認證傳送至內部部署數據源，以執行查詢。

工作 6：瞭解幕後會發生什麼事

此工作假設您已熟悉 SQL Server Profiler，因為您需要擷取內部部署 SSAS 表格式實例上的 SQL Server 分析工具追蹤。

一旦使用者 Rita 存取 Power BI 服務 中的儀錶板，會話就會立即初始化。 您可以看到 salesterritoryusers 角色會立即生效，且有效的使用者名稱為 <EffectiveUserName>rita@contoso.com</EffectiveUserName>

       <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>rita@contoso.com</EffectiveUserName></PropertyList>

Analysis Services 會根據有效的用戶名稱要求，在查詢本機 Active Directory 之後，將要求轉換為實際的 contoso\rita 認證。 Analysis Services 取得認證之後，Analysis Services 會傳回使用者有權檢視和存取的數據。

如果儀錶板發生更多活動，使用 SQL Profiler，您會看到特定查詢以 DAX 查詢的形式回到 Analysis Services 表格式模型。 例如，如果 Rita 從儀錶板移至基礎報表，就會發生下列查詢。

DAX 查詢回到 Analysis Services 模型

您也可以在 DAX 查詢下方看到執行以填入報表數據。

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>rita@contoso.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>

考量

  • Power BI 的內部部署數據列層級安全性僅適用於即時連線。

  • 處理模型之後，數據中的任何變更都會立即可供從 Power BI 服務 使用即時連線存取報表的使用者使用。