Esercitazione: Sicurezza dinamica a livello di righe con Analysis Services in modalità tabulareTutorial: Dynamic row level security with Analysis services tabular model

Questa esercitazione illustra i passaggi necessari per implementare la sicurezza a livello di riga all'interno di un modello tabulare di Analysis Services e mostra come usarlo in un report di 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. I passaggi descritti in questa esercitazione sono progettati per poter proseguire e apprendere i passaggi necessari per completare un set di dati di esempio.The steps in this tutorial are designed to let you follow along and learn the steps necessary by completing on a sample dataset.

Durante questa esercitazione viene fornita una descrizione dettagliata dei passaggi seguenti, utile per comprendere quali azioni occorre eseguire per implementare la sicurezza dinamica a livello di riga con un modello tabulare di 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:

  • Creare una nuova tabella di sicurezza nel database AdventureworksDW2012Create a new security table in the AdventureworksDW2012 database
  • Compilare il modello tabulare con tabelle dei fatti e delle dimensioniBuild the tabular model with necessary fact and dimension tables
  • Definire i ruoli e le autorizzazioni per gli utentiDefine the roles and permissions for the users
  • Distribuire il modello in un'istanza tabulare di Analysis ServicesDeploy the model to an Analysis Services tabular instance
  • Usare Power BI Desktop per creare un report che visualizzi i dati corrispondenti all'utente che accede al reportUse Power BI Desktop to build a report that displays the data corresponding to the user accessing the report
  • Distribuire il report nel servizio Power BIDeploy the report to Power BI service
  • Creare un nuovo dashboard in base al report e, infine,Create a new dashboard based on the report, and finally,
  • Condividere il dashboard con i colleghiShare the dashboard with your coworkers

Per seguire i passaggi in questa esercitazione è necessario il database AdventureworksDW2012, che è possibile scaricare qui.To follow the steps in this tutorial you need the AdventureworksDW2012 database, which you can download here.

Attività 1: Creare la tabella di sicurezza utente e definire le relazioni di datiTask 1: Create the user security table and define data relationship

Sono disponibili molti articoli pubblicati che descrivono come definire la sicurezza dinamica livello di riga con il modello tabulare 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. Per questo esempio si seguirà questo articolo.For our sample we follow this article. I passaggi seguenti consentono di eseguire la prima attività dell'esercitazione.The following steps walk you through the first task in this tutorial.

  1. Per l'esempio si userà il database relazionale AdventureworksDW2012.For our sample, we're using AdventureworksDW2012 relational database. Nel database creare la tabella DimUserSecurity, come illustrato nell'immagine seguente.In that database, create the DimUserSecurity table, as shown in the following image. Per questo esempio verrà usato SQL Server Management Studio (SSMS) per creare la tabella.For this sample, we're using SQL Server Management Studio (SSMS) to create the table.

  2. Dopo aver creato e salvato la tabella, è necessario creare la relazione tra la colonna SalesTerritoryID della tabella DimUserSecurity e la colonna SalesTerritoryKey della tabella DimSalesTerritorycolonna, come illustrato nell'immagine seguente.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. Questa operazione può essere eseguita da SSMS facendo clic con il pulsante destro del mouse sulla tabella DimUserSecurity e scegliendo Modifica.This can be done from SSMS by right-clicking on the DimUserSecurity table, and selecting Edit.

  3. Salvare la tabella, quindi aggiungere alcune righe di informazioni utente nella tabella facendo nuovamente clic con il pulsante destro del mouse sulla tabella DimUserSecurity e quindi selezionando Modifica le prime 200 righe.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. Dopo aver aggiunto gli utenti, le righe della tabella DimUserSecurity avranno un aspetto simile a quello dell'immagine seguente:Once you’ve added those users, the rows of the DimUserSecurity table look like they do in the following image:

    Si tornerà a tali utenti nelle prossime attività.We’ll come back to these users in upcoming tasks.

  4. Successivamente, viene eseguito un inner join con la tabella DimSalesTerritory che mostra i dettagli dell'area associati all'utente.Next we do an inner join with the DimSalesTerritory table, which shows the region details associated with the user. Il codice seguente esegue l'inner join e l'immagine che segue illustra come viene visualizzata la tabella quando l' inner join viene eseguito correttamente.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. Si noti che l'immagine precedente mostra informazioni quali l'identità degli utenti responsabili di specifiche aree di vendita.Notice that the above image shows information such as which user is responsible for which sales region. Tali dati vengono visualizzati per effetto della relazione creata nel passaggio 2.That data is displayed because of the relationship that we created in Step 2. Si noti anche che l'utente Jon Doe fa parte dell'area di vendita Australia.Also, note that the user Jon Doe is part of the Australia sales region. Gli aspetti correlati a Jon Doe saranno trattati nelle attività e nei passaggi che verranno eseguiti successivamente.We’ll revisit John Doe in upcoming steps and tasks.

Attività 2: Compilare il modello tabulare con tabelle dei fatti e delle dimensioniTask 2: Create the tabular model with facts and dimension tables

  1. Dopo aver creato il data warehouse relazionale, è necessario definire il modello tabulare.Once your relational data warehouse is in place, it’s time to define your tabular model. Il modello può essere creato usando SQL Server Data Tools (SSDT).The model can be created using SQL Server Data Tools (SSDT). Per altre informazioni su come definire un modello tabulare, consultare questo articolo.To get more information about how to define a tabular model, please refer this article.
  2. Importare tutte le tabelle necessarie nel modello come mostrato di seguito.Import all the necessary tables in to the model as shown below.

  3. Dopo aver importato le tabelle necessarie, occorre definire un ruolo denominato SalesTerritoryUsers con autorizzazione di lettura.Once you’ve imported the necessary tables, you need to define a role called SalesTerritoryUsers with Read permission. A questo scopo fare clic sul menu Modello in SQL Server Data Tools e quindi scegliere Ruoli.This can be achieved by clicking on the Model menu in SQL Server Data Tools, and then clicking Roles. Nella finestra di dialogo Gestione ruoli fare clic su Nuovo.In the Role Manager dialog box, click New.
  4. Nella scheda Membri della finestra di dialogo Gestione ruoli aggiungere gli utenti definiti nella tabella DimUserSecurity al passaggio 3 dell'attività 1.Under Members tab in the Role Manager, add the users that we defined in the DimUserSecurity table in Task 1 - step 3.

  5. Successivamente, aggiungere le funzioni appropriate per le tabelle DimSalesTerritory e DimUserSecurity, come illustrato di seguito nella scheda Filtri di riga.Next, add the proper functions for both DimSalesTerritory and DimUserSecurity tables, as shown below under Row Filters tab.

  6. In questo passaggio verrà usata la funzione LOOKUPVALUE per restituire i valori per una colonna in cui il nome utente di Windows è uguale a quello restituito dalla funzione 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. È possibile, quindi, limitare le query quando i valori restituiti da LOOKUPVALUE corrispondono ai valori nella stessa tabella o nella tabella correlata.Queries can then be restricted where the values returned by LOOKUPVALUE match values in the same or related table. Nella colonna Filtro DAX digitare la formula seguente:In the DAX Filter column, type the following formula:

    =DimSalesTerritory[SalesTerritoryKey]=LOOKUPVALUE(DimUserSecurity[SalesTerritoryID], DimUserSecurity[UserName], USERNAME(), DimUserSecurity[SalesTerritoryID], DimSalesTerritory[SalesTerritoryKey])
    
  7. In questa formula la funzione LOOKUPVALUE restituisce tutti i valori per la colonna DimUserSecurity [SalesTerritoryID], dove DimUserSecurity [UserName] è uguale al nome utente di Windows connesso e DimUserSecurity [SalesTerritoryID] è uguale 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].

    Il set di Sales SalesTerritoryKey restituito da LOOKUPVALUE viene quindi usato per limitare le righe visualizzate in DimSalesTerritory.The set of Sales SalesTerritoryKey's returned by LOOKUPVALUE is then used to restrict the rows shown in the DimSalesTerritory. Vengono visualizzate solo le righe in cui SalesTerritoryKey per la riga si trova nel set di ID restituiti dalla funzione LOOKUPVALUE.Only rows where the SalesTerritoryKey for the row is in the set of IDs returned by the LOOKUPVALUE function are displayed.

  8. Per la tabella DimUserSecurity, nella colonna Filtro DAX digitare la formula seguente.For the DimUserSecurity table, in the DAX Filter column, type the following formula.

    =FALSE()
    
  9. Questa formula specifica che tutte le colonne vengono risolte nella condizione booleana false, quindi non è possibile eseguire query nelle colonne della tabella DimUserSecurity.This formula specifies that all columns resolve to the false Boolean condition; therefore, no columns for the DimUserSecurity table can be queried.
  10. A questo punto è necessario elaborare e distribuire il modello.Now we need to process and deploy the model. Per assistenza sulla distribuzione del modello è possibile vedere questo articolo.You can refer this article for assistance in deploying the model.

Attività 3: Aggiunta di origini dati all'interno del gateway dati localeTask 3: Adding Data Sources within your on-premises data gateway

  1. Una volta che il modello tabulare è distribuito e pronto per l'utilizzo, è necessario aggiungere una connessione origine dati al server tabulare di Analysis Services locale con il portale di 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. Per consentire al servizio Power BI di accedere all'istanza di Analysis Services locale, è necessario che il gateway dati locale sia installato e configurato nell'ambiente.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. Dopo aver configurato correttamente il gateway, è necessario creare una connessione all'origine dati per l'istanza tabulare di Analysis Services.Once the gateway is correctly configured, you need to create a data source connection for your Analysis Services tabular instance. Questo articolo descrive come aggiungere un'origine dati con il portale di Power BI.This article will help you with adding data source within the Power BI portal.

  4. Dopo aver completato il passaggio precedente, il gateway è configurato e pronto per interagire con l'origine dati di Analysis Services locale.With the previous step complete, the gateway is configured and ready interact with your on-premises Analysis Services data source.

Attività 4: Creazione di un report basato sul modello tabulare di Analysis Services usando Power BI DesktopTask 4: Creating report based on analysis services tabular model using Power BI desktop

  1. Avviare Power BI Desktop e selezionare Recupera dati > Database.Launch Power BI Desktop and select Get Data > Database.
  2. Dall'elenco delle origini dati selezionare Database di SQL Server Analysis Services e quindi Connetti.From the list of data sources, select the SQL Server Analysis Services Database and select connect.

  3. Inserire i dettagli dell'istanza tabulare di Analysis Services e selezionare Connessione in tempo reale.Fill in your Analysis Services tabular instance details and select Connect Live. Selezionare OK.Select OK. In Power BI la sicurezza dinamica funziona solo se si seleziona l'opzione Connessione dinamica.With Power BI, dynamic security works only with Live connection.

  4. Si noterà che il modello che è stato distribuito nell'istanza di Analysis Services.You'll see that the model that was deployed in the Analysis Services instance. Selezionare il modello corrispondente, quindi selezionare OK.Select the respective model and select OK.

  5. In Power BI Desktop ora sono visualizzati tutti i campi disponibili, a destra dell'area di disegno nel riquadro Campi.Power BI Desktop now displays all the available fields, to the right of the canvas in the Fields pane.
  6. Nel riquadro Campi a destra selezionare la misura SalesAmount dalla tabella FactInternetSales e la dimensione SalesTerritoryRegion dalla tabella SalesTerritory.In the Fields pane on the right, select the SalesAmount measure from FactInternetSales table and SalesTerritoryRegion dimension from SalesTerritory table.
  7. Per mantenere la semplicità del report, al momento non verranno aggiunte altre colonne.We’ll keep this report simple, so right now we won’t add any more columns. Per una rappresentazione più significativa dei dati, la visualizzazione verrà impostata su Grafico ad anello.To have more meaningful representation of the data, we'll change the visualization to Donut chart.

  8. Quando il report è pronto, è possibile pubblicarlo direttamente nel portale di Power BI.Once your report is ready, you can directly publish it to the Power BI portal. Dalla barra multifunzione Home in Power BI Desktop selezionare Pubblica.From the Home ribbon in Power BI Desktop, select Publish.

Attività 5: Creazione e condivisione di un dashboardTask 5: Creating and sharing a dashboard

  1. Dopo aver creato il report e fatto clic su Pubblica in Power BI Desktop, il report viene pubblicato nel servizio Power BI.You’ve created the report and clicked Publish in Power BI Desktop, so the report is published to the Power BI service. Ora che è incluso nel servizio, è possibile fornire una dimostrazione di questo scenario di sicurezza del modello usando l'esempio creato nei passaggi precedenti.Now that it’s in the service, our model security scenario can be demonstrated by using the example we created in the previous steps.

    Nel suo ruolo, il responsabile vendite - Sumit può visualizzare i dati da tutte le varie aree di vendita.In his role, Sales Manager - Sumit can see data from all the different sales regions. Quindi, crea il report (il report creato nei precedenti passaggi delle attività) e lo pubblica nel servizio Power BI.So he creates this report (the report created in the previous task steps) and publishes it to the Power BI service.

    Dopo aver pubblicato il report, nel servizio Power BI crea un dashboard denominato TabularDynamicSec basato su tale report.Once he publishes the report, he creates a dashboard in the Power BI service called TabularDynamicSec based on that report. Nell'immagine seguente si noti che il responsabile vendite (Sumit) può visualizzare i dati corrispondenti a tutte le aree di vendita.In the following image, notice that the sales Manager (Sumit) is able to see the data corresponding to all the sales region.

  2. Ora Sumit condivide il dashboard con colleghi, Jon Doe, Responsabile vendite nell'area Australia.Now Sumit shares the dashboard with his colleague, Jon Doe, who is responsible for sales in Australia region.

  3. Quando Jon Doe accede al servizio Power BI e visualizza il dashboard condiviso creato da Sumit, dovrebbe vedere solo le vendite dall'area di cui è responsabile.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. Quindi Jon Doe effettua l'accesso, accede al dashboard che Sumit condivide con lui e vede solo le vendite dell'area Australia.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. Congratulazioni!Congratulations! La sicurezza dinamica a livello di riga definita nel modello tabulare Analysis Services locale è stata correttamente applicata e rispettata nel servizio 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 usa la proprietà effectiveusername per inviare le credenziali dell'utente di Power BI corrente all'origine dati locale per eseguire le query.Power BI uses the effectiveusername property to send the current Power BI user credential to the on-premises data source to run the queries.

Attività 6: Comprensione di ciò che accade "dietro le quinte"Task 6: Understanding what happens behind the scenes

  1. Questa attività presuppone che si abbia familiarità con SQL Profiler perché è necessario acquisire una traccia di SQL Server Profiler nell'istanza tabulare di SSAS locale.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. La sessione verrà inizializzata non appena l'utente (in questo caso, Jon Doe) accede al dashboard nel servizio Power BI.The session gets initialized as soon as the user (Jon Doe, in this case) accesses the dashboard in the Power BI service. È possibile notare che il ruolo salesterritoryusers avrà effetto immediato con il nome utente effettivo 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. In base alla richiesta del nome utente effettivo, Analysis Services converte la richiesta nella credenziale moonneo\jondoe effettiva dopo l'esecuzione di query nell'istanza di Active Directory locale.Based on the effective user name request, Analysis Services converts the request to the actual moonneo\jondoe credential after querying the local Active Directory. Una volta che Analysis Services ottiene la credenziale effettiva da Active Directory, in base alle autorizzazioni di accesso ai dati dell'utente, Analysis Services restituisce solo i dati per i quale l'utente non ha l'autorizzazione.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. Se si verificano altre attività con il dashboard, ad esempio se Jon Doe passa dal dashboard al report sottostante, in SQL Profiler si noterà una query specifica che viene nuovamente inviata al modello tabulare di Analysis Services come query 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. È anche possibile vedere di seguito la query DAX che viene eseguita per popolare i dati per il report.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>
    

ConsiderazioniConsiderations

Quando si lavora con la sicurezza a livello di riga, SSAS e Power BI è necessario tenere presenti alcune considerazioni.There are a few considerations to keep in mind when working with row level security, SSAS and Power BI.

  1. La sicurezza a livello di riga locale in Power BI è disponibile solo con la connessione dinamica.On-premises row level security with Power BI is only available with Live Connection.
  2. Le eventuali modifiche apportate ai dati dopo l'elaborazione del modello saranno immediatamente disponibili per gli utenti che accedono al report in base alla connessione dinamica del servizio 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.