Реализация безопасности на уровне строк в локальной табличной модели служб 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 ).

    Create DimUserSecurity table

  2. После создания и сохранения таблицы необходимо установить связь между DimUserSecurity столбцом таблицы SalesTerritoryID и DimSalesTerritory столбцом таблицы SalesTerritoryKey , как показано ниже.

    В SSMS щелкните правой кнопкой мыши DimUserSecurity и выберите "Конструктор". Затем выберите "Связи конструктора>таблиц...". По завершении сохраните таблицу.

    Foreign Key Relationships

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

    DimUserSecurity table with example users

    Эти пользователи будут отображаться в предстоящих задачах.

  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. Импортируйте все необходимые таблицы в модель, как показано ниже.

    Imported SQL Server for use with data tools

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

  3. В разделе "Участники" в диспетчере ролей добавьте пользователей, определенных в DimUserSecurity таблице в задаче 1.

    Add users in Role Manager

  4. Затем добавьте соответствующие функции для обеих DimSalesTerritory таблиц и DimUserSecurity таблиц, как показано ниже на вкладке "Фильтры строк".

    Add functions to Row Filters

  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]именем пользователя Windows.

    Важно!

    При использовании безопасности на уровне строк функция DAX USERELATIONSHIP не поддерживается.

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

  6. В таблице в столбце DimUserSecurityфильтра DAX добавьте следующую формулу:

        =FALSE()
    

    Эта формула указывает, что все столбцы разрешаются falseв; то есть DimUserSecurity столбцы таблицы не могут запрашиваться.

Теперь необходимо обработать и развернуть модель. Дополнительные сведения см. в разделе "Развертывание".

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

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

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

  2. После правильной настройки шлюза необходимо создать подключение к источнику данных для табличного экземпляра служб Analysis Services . Дополнительные сведения см. в статье "Управление источником данных " Службы Analysis Services".

    Create data source connection

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

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

  1. Запустите Power BI Desktop и выберите "Получить базу данных".>

  2. В списке источников данных выберите базу данных SQL Server Analysis Services и выберите Подключение.

    Connect to SQL Server Analysis Services Database

  3. Заполните сведения о табличном экземпляре служб Analysis Services и выберите Подключение трансляции. Затем выберите OK.

    Analysis Services details

    С Помощью Power BI динамическая безопасность работает только с динамическим подключением.

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

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

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

  6. Чтобы сделать отчет простым, мы не добавим больше столбцов. Чтобы иметь более понятное представление данных, измените визуализацию на диаграмму Donut.

    Donut chart visualization

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

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

Вы создали отчет и опубликовали его в службе Power BI . Теперь можно использовать пример, созданный на предыдущих шагах, чтобы продемонстрировать сценарий безопасности модели.

В роли диспетчера продаж пользователь Grace может просматривать данные из всех разных регионов продаж. Грейс создает этот отчет и публикует его в служба Power BI. Этот отчет был создан в предыдущих задачах.

После публикации отчета грейс необходимо создать панель мониторинга в служба Power BI с именем TabularDynamicSec на основе этого отчета. На следующем рисунке обратите внимание, что Grace может видеть данные, соответствующие всем регионам продаж.

Power BI service dashboard

Теперь Грейс делится панелью мониторинга с коллегой, Ритой, которая отвечает за продажи региона Австралии.

Share a Power BI dashboard

Когда Рита входит в служба Power BI и просматривает общую панель мониторинга, созданную Grace, отображаются только продажи из региона Австралии.

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

Задача 6. Понимание того, что происходит за кулисами

Эта задача предполагает, что вы знакомы с SQL Server Profiler, так как необходимо записать трассировку профилировщика SQL Server в локальном табличном экземпляре SSAS.

Сеанс инициализируется, как только пользователь, Рита, обращается к панели мониторинга в служба 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 преобразует запрос в фактические contoso\rita учетные данные после запроса локального Active Directory. После получения учетных данных службы Analysis Services возвращают данные, которые пользователь имеет разрешение на просмотр и доступ.

Если с панелью мониторинга возникает больше действий, с профилировщиком SQL вы увидите конкретный запрос, возвращающийся в табличную модель служб Analysis Services в виде запроса DAX. Например, если Рита переходит с панели мониторинга в базовый отчет, происходит следующий запрос.

DAX query comes back to Analysis Services model

Вы также можете увидеть под запросом 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.