Учебник. Динамическая безопасность на уровне строк при использовании табличной модели служб Analysis ServicesTutorial: 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:

  • Создание таблицы безопасности в базе данных AdventureworksDW2012Create 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 ServicesDeploy the model to an Analysis Services tabular instance
  • Создание в Power BI отчета, отображающего для пользователей данные с учетом их прав доступаUse Power BI Desktop to build a report that displays the data corresponding to the user accessing the report
  • Развертывание отчета в службе Power BIDeploy 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. Создав и сохранив таблицу, мы должны установить связь между столбцом SalesTerritoryID таблицы DimUserSecurity и столбцом SalesTerritoryKey таблицы DimSalesTerritory, как показано на изображении ниже.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 относится к региону продаж Australia.Also, note that the user Jon Doe is part of the Australia sales region. Мы вернемся к нему в следующих действиях и задачах.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. На вкладке Участники в диспетчере ролей добавим пользователей, созданных в таблице DimUserSecurity при выполнении задачи 1 (шаг 3).Under Members tab in the Role Manager, add the users that we defined in the DimUserSecurity table in Task 1 - step 3.

  5. Теперь добавим необходимые функции для таблиц DimSalesTerritory и DimUserSecurity, как показано ниже на вкладке Фильтры строк.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 Filter введите следующую формулу: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. После того как табличная модель развернута и готова к работе, необходимо добавить подключение к источнику данных на сервер табличного экземпляра Analysis Services на портале Power BI.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. Создание отчета на базе табличной модели Analysis Services с помощью Power BI DesktopTask 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. В области Поля справа выберите меру SalesAmount в таблице FactInternetSales и измерение SalesTerritoryRegion и таблице SalesTerritory.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. Теперь он предоставляет доступ к панели своему коллеге, которого зовут 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! Параметры динамической безопасности на уровне строк, настроенные в локальной табличной модели 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, так как вам нужно выполнить трассировку обмена данными с SQL Server в локальном экземпляре SSAS.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.com.You 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 преобразуют его в учетные данные moonneo\jondoe при отправке запроса в каталог Active Directory.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 Profiler отразится соответствующий запрос, который направляется в табличную модель Analysis Services в виде запроса DAX.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.