Segurança em nível de linha dinâmica com o modelo de tabela do Analysis ServicesDynamic row level security with Analysis services tabular model

Este tutorial apresenta as etapas necessárias para implementar a segurança em nível de linha no Modelo de Tabela do Analysis Services e mostra como usá-la em um relatório do 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 a Power BI report. As etapas deste tutorial foram projetadas para permitir que você acompanhe e conheça as etapas necessárias preenchendo um conjunto de dados de exemplo.The steps in this tutorial are designed to let you follow along and learn the steps necessary by completing on a sample dataset.

Durante este tutorial, as seguintes etapas são descritas detalhadamente, ajudando a entender o que você precisa fazer para implementar a segurança dinâmica em nível de linha com o modelo de tabela do 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:

  • Criar uma nova tabela de segurança no banco de dados AdventureworksDW2012Create a new security table in the AdventureworksDW2012 database
  • Criar o modelo de tabela com as tabelas de fatos e dimensões necessáriasBuild the tabular model with necessary fact and dimension tables
  • Definir as funções e permissões para os usuáriosDefine the roles and permissions for the users
  • Implantar o modelo em uma instância da tabela do Analysis ServicesDeploy the model to an Analysis Services tabular instance
  • Usar o Power BI Desktop para criar um relatório que exibe os dados correspondentes ao usuário que está acessando o relatórioUse Power BI Desktop to build a report that displays the data corresponding to the user accessing the report
  • Implantar o relatório no serviço do Power BIDeploy the report to Power BI service
  • Criar um novo dashboard baseado no relatório e, por último,Create a new dashboard based on the report, and finally,
  • Compartilhar o dashboard com seus colegasShare the dashboard with your coworkers

Para seguir as etapas deste tutorial, você precisa do banco de dados AdventureworksDW2012, que pode ser baixado no repositório.To follow the steps in this tutorial you need the AdventureworksDW2012 database, which you can download from the repository.

Tarefa 1: Criar a tabela de segurança de usuário e definir a relação de dadosTask 1: Create the user security table and define data relationship

Há vários artigos publicados que descrevem como definir a segurança dinâmica em nível de linha com o modelo de tabela do SSAS (SQL Server Analysis Services).There are many published articles describing how to define row level dynamic security with SQL Server Analysis Services (SSAS) tabular model. Para nossa amostra, seguimos o artigo Implementar a segurança dinâmica usando filtros de linha.For our sample, we follow the article Implement Dynamic Security by Using Row Filters. As etapas a seguir mostrarão a primeira tarefa deste tutorial:The following steps walk you through the first task in this tutorial:

  1. No nosso exemplo, usaremos o banco de dados relacional AdventureworksDW2012.For our sample, we're using AdventureworksDW2012 relational database. Nesse banco de dados, crie a tabela DimUserSecurity, conforme mostrado na imagem a seguir.In that database, create the DimUserSecurity table, as shown in the following image. Para esta amostra, estamos usando o SSMS (SQL Server Management Studio) para criar a tabela.For this sample, we're using SQL Server Management Studio (SSMS) to create the table.

  2. Depois que a tabela for criada e salva, será necessário criar a relação entre a coluna SalesTerritoryID da tabela DimUserSecurity e a coluna SalesTerritoryKey da tabela DimSalesTerritory, como mostrado na imagem a seguir.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. Faça isso no SSMS clicando com o botão direito do mouse na tabela DimUserSecurity e selecionando Projetar.This can be done from SSMS by right-clicking on the DimUserSecurity table, and selecting Design. Em seguida, selecione Designer de Tabela -> Relações... no menu.Then select Table Designer -> Relationships... from the menu.

  3. Salve a tabela e adicione algumas linhas de informações do usuário à tabela novamente clicando com o botão direito do mouse na tabela DimUserSecurity e selecionando Editar as 200 Primeiras Linhas.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. Depois de adicionar os usuários, as linhas da tabela DimUserSecurity serão parecidas com a seguinte imagem:Once you’ve added those users, the rows of the DimUserSecurity table look like they do in the following image:

    Voltaremos a esses usuários em tarefas futuras.We’ll come back to these users in upcoming tasks.

  4. Em seguida, fazemos uma junção interna com a tabela DimSalesTerritory, que mostra os detalhes da região associados ao usuário.Next we do an inner join with the DimSalesTerritory table, which shows the region details associated with the user. O código a seguir executa a junção interna, e a imagem a seguir mostra como a tabela aparece depois que a junção interna é bem-sucedida.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.EmployeeID, a.FirstName, a.LastName, a.UserName from [dbo].[DimUserSecurity] as a join  [dbo].[DimSalesTerritory] as b on a.[SalesTerritoryKey] = b.[SalesTerritoryID]
    

  5. Observe que a imagem acima mostra informações, como qual usuário é responsável por qual região de vendas.Notice that the above image shows information such as which user is responsible for which sales region. Esses dados são exibidos devido à relação que criamos na Etapa 2.That data is displayed because of the relationship that we created in Step 2. Além disso, observe que o usuário Carlos Silva faz parte da região de vendas Austrália.Also, note that the user Jon Doe is part of the Australia sales region. Voltaremos a Carlos Silva em tarefas e etapas futuras.We’ll revisit John Doe in upcoming steps and tasks.

Tarefa 2: Criar o modelo de tabela com tabelas de fatos e dimensãoTask 2: Create the tabular model with facts and dimension tables

  1. Depois de implementar o data warehouse relacional, é hora de definir o modelo de tabela.Once your relational data warehouse is in place, it’s time to define your tabular model. O modelo pode ser criado usando o SSDT (SQL Server Data Tools).The model can be created using SQL Server Data Tools (SSDT). Para obter mais informações sobre como definir um modelo de tabela, consulte Criar um novo projeto de modelo de tabela.To get more information about how to define a tabular model, please refer to Create a New Tabular Model Project.

  2. Importe todas as tabelas necessárias no modelo, conforme mostrado abaixo.Import all the necessary tables into the model as shown below.

  3. Depois de importar as tabelas necessárias, você precisa definir uma função chamada SalesTerritoryUsers com a permissão Leitura.Once you’ve imported the necessary tables, you need to define a role called SalesTerritoryUsers with Read permission. Faça isto clicando no menu Modelo no SQL Server Data Tools e clicando em Funções.This can be achieved by clicking on the Model menu in SQL Server Data Tools, and then clicking Roles. Na caixa de diálogo Gerenciador de Funções, clique em Novo.In the Role Manager dialog box, click New.

  4. Na guia Membros do Gerenciador de Funções, adicione os usuários que definimos na tabela DimUserSecurity, na Tarefa 1 – etapa 3.Under Members tab in the Role Manager, add the users that we defined in the DimUserSecurity table in Task 1 - step 3.

  5. Em seguida, adicione as funções apropriadas para as tabelas DimSalesTerritory e DimUserSecurity, conforme mostrado abaixo na guia Filtros de Linha.Next, add the proper functions for both DimSalesTerritory and DimUserSecurity tables, as shown below under Row Filters tab.

  6. Nesta etapa, usamos a função LOOKUPVALUE para retornar valores de uma coluna na qual o nome de usuário do Windows é o mesmo que o nome de usuário retornado pela função 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. As consultas poderão ser restringidas nos casos em que os valores retornados por LOOKUPVALUE corresponderem aos valores na mesma tabela ou em uma tabela relacionada.Queries can then be restricted where the values returned by LOOKUPVALUE match values in the same or related table. Na coluna Filtro DAX, digite a seguinte fórmula:In the DAX Filter column, type the following formula:

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

    Nesta fórmula, a função LOOKUPVALUE retorna todos os valores da coluna DimUserSecurity[SalesTerritoryID], em que o DimUserSecurity[UserName] é o mesmo nome de usuário atual do Windows conectado, e a DimUserSecurity[SalesTerritoryID] é igual a 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].

    O conjunto de SalesTerritoryKeys de Vendas retornado por LOOKUPVALUE é usado para restringir as linhas mostradas em DimSalesTerritory.The set of Sales SalesTerritoryKey's returned by LOOKUPVALUE is then used to restrict the rows shown in the DimSalesTerritory. Somente as linhas em que SalesTerritoryKey da linha estiver no conjunto de IDs retornadas pela função LOOKUPVALUE são exibidas.Only rows where the SalesTerritoryKey for the row is in the set of IDs returned by the LOOKUPVALUE function are displayed.

  7. Para a tabela DimUserSecurity, na coluna Filtro DAX, digite a fórmula a seguir:For the DimUserSecurity table, in the DAX Filter column, type the following formula:

    =FALSE()
    

    Esta fórmula especifica que todas as colunas são resolvidas para a condição booliana false; portanto, nenhuma coluna da tabela DimUserSecurity pode ser consultada.This formula specifies that all columns resolve to the false Boolean condition; therefore, no columns for the DimUserSecurity table can be queried.

  8. Agora, precisamos processar e implantar o modelo.Now we need to process and deploy the model. Confira o artigo Implantar para obter assistência na implantação do modelo.You can refer to the Deploy article for assistance in deploying the model.

Tarefa 3: adicionando fontes de dados ao Gateway de dados localTask 3: Adding Data Sources within your On-premises data gateway

  1. Depois que o modelo de tabela for implantado e estiver pronto para consumo, você precisará adicionar uma conexão de fonte de dados ao servidor de tabela do Analysis Services local no portal do 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 within your Power BI portal.

  2. Para permitir que o serviço do Power BI acesse o serviço de análise local, é necessário ter um Gateway de dados local instalado e configurado no seu ambiente.To allow the Power BI service access to your on-premises analysis service, you need to have an On-premises data gateway installed and configured in your environment.

  3. Depois de configurar o gateway corretamente, é necessário criar uma conexão de fonte de dados à instância de tabela do Analysis Services.Once the gateway is correctly configured, you need to create a data source connection for your Analysis Services tabular instance. Este artigo o ajudará a adicionar uma fonte de dados no portal do Power BI.This article will help you with adding a data source within the Power BI portal.

  4. Após a conclusão da etapa anterior, o gateway está configurado e pronto para interagir com sua fonte de dados do Analysis Services local.With the previous step complete, the gateway is configured and ready to interact with your on-premises Analysis Services data source.

Tarefa 4: Criando relatórios baseados no modelo de tabela do Analysis Services usando o Power BI DesktopTask 4: Creating report based on analysis services tabular model using Power BI desktop

  1. Inicie o Power BI Desktop e selecione Obter Dados > Banco de Dados.Launch Power BI Desktop and select Get Data > Database.

  2. Na lista de fontes de dados, selecione o Banco de Dados do SQL Server Analysis Services e selecione Conectar.From the list of data sources, select the SQL Server Analysis Services Database and select Connect.

  3. Preencha os detalhes da instância de tabela do Analysis Services e selecione Conexão Dinâmica.Fill in your Analysis Services tabular instance details and select Connect Live. Selecione OK.Select OK. Com o Power BI, a segurança dinâmica funciona apenas com a Conexão dinâmica.With Power BI, dynamic security works only with Live connection.

  4. Você verá que o modelo implantado está na instância do Analysis Services.You'll see that the model that was deployed is in the Analysis Services instance. Selecione o respectivo modelo e selecione OK.Select the respective model and select OK.

  5. Agora, o Power BI Desktop exibe todos os campos disponíveis à direita da tela no painel Campos.Power BI Desktop now displays all the available fields, to the right of the canvas in the Fields pane.

  6. No painel Campos à direita, selecione a medida SalesAmount na tabela FactInternetSales e a dimensão SalesTerritoryRegion na tabela SalesTerritory.In the Fields pane on the right, select the SalesAmount measure from the FactInternetSales table and the SalesTerritoryRegion dimension from the SalesTerritory table.

  7. Vamos manter esse relatório simples e, por isso, não adicionamos mais nenhuma coluna.We’ll keep this report simple, so right now we won’t add any more columns. Para ter uma representação mais significativa dos dados, vamos alterar a visualização para Gráfico de rosca.To have a more meaningful representation of the data, we'll change the visualization to Donut chart.

  8. Quando o relatório estiver pronto, você poderá publicá-lo diretamente no portal do Power BI.Once your report is ready, you can directly publish it to the Power BI portal. Na faixa de opções Página Inicial do Power BI Desktop, selecione Publicar.From the Home ribbon in Power BI Desktop, select Publish.

Tarefa 5: Criando e compartilhando um dashboardTask 5: Creating and sharing a dashboard

  1. Você criou o relatório e clicou em Publicar no Power BI Desktop; portanto, o relatório foi publicado no serviço do Power BI.You’ve created the report and clicked Publish in Power BI Desktop, so the report is published to the Power BI service. Agora que ele está no serviço, nosso cenário de segurança do modelo pode ser demonstrado com o exemplo criado nas etapas anteriores.Now that it’s in the service, our model security scenario can be demonstrated by using the example we created in the previous steps.

    Em sua função, o Gerente de vendas – Pedro pode ver os dados de todas as diferentes regiões de vendas.In his role, Sales Manager - Sumit can see data from all the different sales regions. Portanto, ele cria esse relatório (o relatório criado nas etapas da tarefa anterior) e o publica no serviço do Power BI.So he creates this report (the report created in the previous task steps) and publishes it to the Power BI service.

    Depois de publicar o relatório, ele cria um dashboard no serviço do Power BI chamado TabularDynamicSec com base no relatório.Once he publishes the report, he creates a dashboard in the Power BI service called TabularDynamicSec based on that report. Na imagem a seguir, observe que o Gerente de Vendas (Pedro) consegue ver os dados correspondentes a todas as regiões de vendas.In the following image, notice that the Sales Manager (Sumit) is able to see the data corresponding to all the sales region.

  2. Agora, Pedro compartilha o dashboard com seu colega, Carlos Silva, responsável pelas vendas na região da Austrália.Now Sumit shares the dashboard with his colleague, Jon Doe, who is responsible for sales in the Australia region.

  3. Quando Carlos Silva fizer logon no serviço do Power BI e exibir o dashboard compartilhado criado por Pedro, Carlos Silva deverá ver somente as vendas da região pela qual é responsável.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. Portanto, Carlos Silva faz logon, acessa o dashboard que Pedro compartilhou com ele e Carlos Silva vê somente as vendas da região da Austrália.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. Parabéns!Congratulations! A segurança dinâmica em nível de linha definida no modelo de tabela do Analysis Services local foi refletida com êxito e observada no serviço do 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. O Power BI usa a propriedade effectiveusername para enviar as atuais credenciais de usuário do Power BI à fonte de dados local para executar as consultas.Power BI uses the effectiveusername property to send the current Power BI user credential to the on-premises data source to run the queries.

Tarefa 6: Entendendo o que acontece nos bastidoresTask 6: Understanding what happens behind the scenes

  1. Esta tarefa pressupõe que você esteja familiarizado com o SQL Profiler, já que você precisa capturar um rastreamento do criador de perfil do SQL Server na instância de tabela do SSAS local.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. A sessão é inicializada assim que o usuário (Carlos Silva, neste caso) acessa o dashboard no serviço do Power BI.The session gets initialized as soon as the user (Jon Doe, in this case) accesses the dashboard in the Power BI service. Você pode ver que a função salesterritoryusers funciona imediatamente com o nome de usuário efetivo jondoe@moonneo.comYou 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. Com base na solicitação de nome de usuário efetivo, o Analysis Services converte a solicitação para a credencial real moonneo\carlossilva depois de consultar o Active Directory local.Based on the effective user name request, Analysis Services converts the request to the actual moonneo\jondoe credential after querying the local Active Directory. Depois que o Analysis Services obtém a credencial real do Active Directory, em seguida, com base no acesso e nas permissões que o usuário tem para os dados, o Analysis Services retorna somente os dados para os quais ele tem permissão.Once Analysis Services gets the actual credential from Active Directory, then based on the access and permissions the user has for the data, Analysis Services returns only the data for which he or she has permission.

  4. Se outras atividades forem realizadas no dashboard, por exemplo, se Carlos Silva acessar o dashboard e depois o relatório subjacente, com o SQL Profiler, você verá uma consulta específica retornando para o modelo de tabela do Analysis Services como uma consulta 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. Você também pode ver abaixo a consulta DAX sendo executada para popular os dados do relatório.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>
    

ConsideraçõesConsiderations

Há algumas considerações para ter em mente ao trabalhar com a segurança em nível de linha, o SSAS e o Power BI:There are a few considerations to keep in mind when working with row level security, SSAS, and Power BI:

  1. A segurança em nível de linha local com o Power BI só está disponível com a Conexão Dinâmica.On-premises row level security with Power BI is only available with Live Connection.
  2. Quaisquer alterações nos dados após o processamento do modelo seriam imediatamente disponibilizadas para os usuários (que estão acessando o relatório com a Conexão Dinâmica) por meio do serviço do Power BI.Any changes in the data after processing the model would be immediately available for the users (who are accessing the report with Live Connection) from the Power BI service.