В этом учебном руководстве описаны действия, необходимые для реализации безопасности на уровне строк в табличной модели Analysis Services, и показано, как использовать эти функции в отчете Power BI. Здесь приведены пошаговые инструкции, которые помогут вам познакомиться с последовательностью необходимых действий на примере набора данных.

Ниже перечислены действия, подробно описанные в этом руководстве, чтобы вы могли понять, как реализовать функции безопасности на уровне строк в табличной модели Analysis Services.

  • Создание таблицы безопасности в базе данных AdventureworksDW2012
  • Создание табличной модели с необходимыми таблицами фактов и измерений
  • Настройка ролей и разрешений для пользователей
  • Развертывание модели в табличном экземпляре Analysis Services
  • Создание в Power BI отчета, отображающего для пользователей данные с учетом их прав доступа
  • Развертывание отчета в службе Power BI
  • Создание панели мониторинга на основе отчета
  • Предоставление доступа к панели мониторинга коллегам.

Для выполнения действий, описанных в этом руководстве, понадобится база данных AdventureworksDW2012, которую можно скачать здесь.

Задача 1. Создание таблицы безопасности пользователей и определение связей между данными

Существует множество статей, в которых объясняется, как настроить динамическую безопасность на уровне строк в табличной модели SQL Server Analysis Services (SSAS). В нашем примере мы воспользуемся инструкциями из этой статьи. Ниже описаны действия для выполнения первой задачи, описанной в этом руководстве.

  1. В нашем примере используется реляционная база данных AdventureworksDW2012. В этой базе мы создадим таблицу DimUserSecurity, как показано на изображении ниже. В этом примере мы используем для создания таблицы приложение SQL Server Management Studio (SSMS).

  2. Создав и сохранив таблицу, мы должны установить связь между столбцом SalesTerritoryID таблицы DimUserSecurity и столбцом SalesTerritoryKey таблицы DimSalesTerritory, как показано на изображении ниже. В SSMS для этого можно щелкнуть таблицу DimUserSecurity правой кнопкой мыши и выбрать команду Изменить.

  3. Сохраним таблицу, а затем добавим в нее несколько строк с информацией о пользователях. Для этого снова щелкнем таблицу DimUserSecurity правой кнопкой мыши и выберем команду Изменить первые 200 строк. После добавления пользователей строки таблицы DimUserSecurity будут выглядеть так, как показано на следующем изображении:

    Мы вернемся к этим пользователям при выполнении следующих задач.

  4. Теперь нам нужно создать внутреннее соединение с таблицей DimSalesTerritory, которое будет отражать связь между сведениями о регионе и пользователем. Ниже приведен код, который создает такое внутреннее соединение, а на изображении ниже показано, как таблица выглядит после его успешного создания__.

    **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. Обратите внимание, что на приведенном выше изображении показаны сведения об ответственности определенных пользователей за тот или иной регион. Эти данные отображаются благодаря связи, которую мы создали на шаге 2. Кроме того, обратите внимание, что пользователь Jon Doe относится к региону продаж Australia. Мы вернемся к нему в следующих действиях и задачах.

Задача 2. Создание табличной модели с таблицами фактов и измерений

  1. Создав реляционное хранилище данных, мы можем настроить определение табличной модели. Для ее создания можно использовать инструментарий SQL Server Data Tools (SSDT). Сведения о том, как задать табличную модель, см. в этой статье.

  2. Импортируем в модель все необходимые таблицы, как показано ниже.

  3. После импорта таблиц нужно определить роль под названием SalesTerritoryUsers с разрешениями на чтение. Для этого нужно открыть меню Модель в инструментарии SQL Server Data Tools и выбрать пункт Роли. В диалоговом окне Диспетчер ролей щелкнем Создать.

  4. На вкладке Участники в диспетчере ролей добавим пользователей, созданных в таблице DimUserSecurity при выполнении задачи 1 (шаг 3).

  5. Теперь добавим необходимые функции для таблиц **DimSalesTerritory** и **DimUserSecurity**, как показано ниже на вкладке **Фильтры строк**.
    

  6. На этом этапе мы с помощью функции LOOKUPVALUE возвращаем значения для столбца, в котором имя пользователя Windows совпадает с именем, возвращаемым функцией USERNAME. Для запросов можно настроить ограничение, благодаря которому значения, возвращаемые функцией LOOKUPVALUE, будут совпадать со значениями в той же или связанной таблице. В столбце Фильтр DAX введем следующую формулу:

    =DimSalesTerritory[SalesTerritoryKey]=LOOKUPVALUE(DimUserSecurity[SalesTerritoryID], DimUserSecurity[UserName], USERNAME(), DimUserSecurity[SalesTerritoryID], DimSalesTerritory[SalesTerritoryKey])
    
  7. В этой формуле функция LOOKUPVALUE возвращает все значения из столбца DimUserSecurity[SalesTerritoryID], для которых DimUserSecurity[UserName] совпадает с именем текущего пользователя Windows, а параметр DimUserSecurity[SalesTerritoryID] совпадает с параметром DimSalesTerritory[SalesTerritoryKey].

    Набор значений SalesTerritoryKey, возвращаемых функцией LOOKUPVALUE, используется для фильтрации списка строк, отображаемых в таблице DimSalesTerritory. В ней показаны только строки, в которых значение SalesTerritoryKey входит в набор идентификаторов, возвращенных функцией LOOKUPVALUE.

  8. Для таблицы DimUserSecurity в столбце DAX Filter введите следующую формулу:

    =FALSE()
    
  9. Она указывает, что все столбцы возвращают логическое значение false, поэтому столбцы таблицы DimUserSecurity не используются в запросе.

  10. Теперь нужно обработать и развернуть модель. Сведения о том, как это сделать, см. в этой статье.

Задача 3. Добавление источников данных на локальном шлюзе

  1. После того как табличная модель развернута и готова к работе, необходимо добавить подключение к источнику данных на сервер табличного экземпляра Analysis Services на портале Power BI.

  2. Чтобы служба Power BI могла обращаться к локальной службе аналитики, в вашей среде должен быть установлен и настроен локальный шлюз данных.

  3. Настроив шлюз должным образом, необходимо создать подключение к источнику данных для экземпляра табличной модели Analysis Services. В этой статье описывается добавление источника данных на портале Power BI.

  4. После выполнения предыдущего шага шлюз настроен и готов к взаимодействию с локальным источником данных Analysis Services.

Задача 4. Создание отчета на базе табличной модели Analysis Services с помощью Power BI Desktop

  1. Запустите **Power BI Desktop** и выберите **Получение данных > База данных**.
    
  2. В списке источников данных выберите пункт База данных SQL Server Analysis Services и щелкните Подключиться.

  3. Введите данные своего экземпляра табличной модели Analysis Services и щелкните Подключение в реальном времени. Нажмите кнопку "ОК". В Power BI динамическая безопасность работает только при динамических подключениях.

  4. Вы увидите модель, развернутую в экземпляре Analysis Services. Выберите нужную модель и нажмите кнопку ОК.

  5. Power BI Desktop выведет все доступные поля справа от холста в области Поля.

  6. В области Поля справа выберите меру SalesAmount в таблице FactInternetSales и измерение SalesTerritoryRegion и таблице SalesTerritory.

  7. Этот отчет будет простым, поэтому мы не станем добавлять в него другие столбцы. Чтобы сделать данные более осмысленными, выберем режим визуализации Кольцевой график.

  8. Создав отчет, мы можем опубликовать его непосредственно на портале Power BI. На ленте Главная в Power BI Desktop выберем Опубликовать.

Задача 5. Создание и совместное использование панели мониторинга

  1. Мы создали отчет и воспользовались командой Опубликовать в Power BI Desktop, поэтому теперь он опубликован в службе Power BI. Теперь мы можем продемонстрировать работу нашей модели на основе примера, который создали на предыдущих этапах.

    Руководитель продаж Sumit видит данные из всех регионов. Он создает этот отчет (который мы сформировали на предыдущих этапах) и публикует его в службе Power BI.

    После публикации он создает на его основе в службе Power BI панель мониторинга и называет ее TabularDynamicSec. На изображении ниже показано, что руководитель продаж (Sumit) видит все данные для всех регионов продаж.

  2. Теперь он предоставляет доступ к панели своему коллеге, которого зовут Jon Doe и который отвечает за продажи в Австралии.

  3. Когда Jon Doe входит в службу Power BI и открывает общую панель мониторинга, созданную пользователем Sumit, он видит только показатели продаж в подотчетном ему регионе. Итак, Jon Doe входит, открывает панель мониторинга, доступ к которой ему предоставил пользователь Sumit, и видит только продажи в Австралии.

  4. Поздравляем! Параметры динамической безопасности на уровне строк, настроенные в локальной табличной модели Analysis Services, успешно отражены и соблюдаются в службе Power BI. Служба Power BI использует свойство effectiveusername для передачи учетных данных текущего пользователя Power BI в локальный источник данных при выполнении запросов.

Задача 6. Понимание происходящего

  1. При выполнении этой задачи предполагается, что вы знакомы с приложением SQL Profiler, так как вам нужно выполнить трассировку обмена данными с SQL Server в локальном экземпляре SSAS.

  2. Сеанс создается в момент, когда пользователь (в нашем случае — Jon Doe) обращается к панели мониторинга в службе Power BI. Мы видим, что роль salesterritoryusers сразу же применяется для действующего имени пользователя 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. После того как службы Analysis Services получают реальные учетные данные из Active Directory, они применяют права этого пользователя на доступ к соответствующим данным и ****возвращают только ту информацию, для работы с которой у него есть разрешения.

  4. Если на панели мониторинга будет выполнено еще какое-то действие (например, Jon Doe перейдет с панели на связанный с ней отчет), в SQL Profiler отразится соответствующий запрос, который направляется в табличную модель Analysis Services в виде запроса DAX.

  5. Ниже также показан запрос 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>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>
    

Примечания

При работе с безопасностью на уровне строк, SSAS и Power BI следует помнить о ряде моментов.

  1. Локальная безопасность на уровне строк при работе с Power BI действует только при динамических подключениях.

  2. Все изменения, вносимые в данных после обработки модели, становятся сразу же доступны пользователям, которые работают с отчетом через динамические подключения из службы Power BI.