Implementare la sicurezza a livello di riga in un modello tabulare di Analysis Services localeImplement row-level security in an on-premises Analysis Services tabular model

Partendo da un set di dati di esempio da usare nella procedura descritta di seguito, questa esercitazione illustra come implementare la sicurezza a livello di riga in un modello tabulare di Analysis Services locale e applicarla in un report di Power BI.Using a sample dataset to work through the steps below, this tutorial shows you how to implement row-level security in an on-premises Analysis Services Tabular Model and use it in a Power BI report.

  • Creare una nuova tabella di sicurezza nel database AdventureworksDW2012 databaseCreate 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 ruoli utente e autorizzazioniDefine user roles and permissions
  • Distribuire il modello in un'istanza tabulare di Analysis ServicesDeploy the model to an Analysis Services tabular instance
  • Compilare un report di Power BI Desktop che visualizzi i dati personalizzati per l'utente che accede al reportBuild a Power BI Desktop report that displays data tailored to the user accessing the report
  • Distribuire il report nel servizio Power BIDeploy the report to Power BI service
  • Creare un nuovo dashboard sulla base del reportCreate a new dashboard based on the report
  • Condividere il dashboard con i colleghiShare the dashboard with your coworkers

Per questa esercitazione è necessario il database AdventureworksDW2012.This tutorial requires the AdventureworksDW2012 database.

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

Esistono molti articoli che descrivono come definire la sicurezza dinamica a livello di riga con il modello tabulare di SQL Server Analysis Services (SSAS) .You can find many articles describing how to define row-level dynamic security with the SQL Server Analysis Services (SSAS) tabular model. Per questo esempio si seguiranno le istruzioni dell'articolo Implementare la sicurezza dinamica tramite filtri di riga.For our sample, we use Implement Dynamic Security by Using Row Filters.

Per eseguire questi passaggi è necessario usare il database relazionale AdventureworksDW2012.The steps here require using the AdventureworksDW2012 relational database.

  1. In AdventureworksDW2012 creare la tabella DimUserSecurity come illustrato di seguito.In AdventureworksDW2012, create the DimUserSecurity table as shown below. Per creare la tabella, è possibile usare SQL Server Management Studio (SSMS).You can use SQL Server Management Studio (SSMS) to create the table.

    Creare la tabella DimUserSecurity

  2. Dopo aver creato e salvato la tabella, è necessario stabilire la relazione tra la colonna SalesTerritoryID della tabella DimUserSecurity e la colonna SalesTerritoryKey della tabella DimSalesTerritory, come illustrato di seguito.Once you create and save the table, you need to establish the relationship between the DimUserSecurity table's SalesTerritoryID column and the DimSalesTerritory table's SalesTerritoryKey column, as shown below.

    In SSMS fare clic con il pulsante destro del mouse su DimUserSecurity e selezionare Progetta.In SSMS, right-click DimUserSecurity, and select Design. Selezionare quindi Progettazione tabelle > Relazioni. Al termine, salvare la tabella.Then select Table Designer > Relationships.... When done, save the table.

    Relazioni chiavi esterne

  3. Aggiungere utenti alla tabella.Add users to the table. Fare clic con il pulsante destro del mouse su DimUserSecurity e selezionare Modifica le prime 200 righe.Right-click DimUserSecurity and select Edit Top 200 Rows. Dopo l'aggiunta di utenti, la tabella DimUserSecurity dovrebbe assumere un aspetto simile all'esempio seguente:Once you've added users, the DimUserSecurity table should appear similar to the following example:

    Tabella DimUserSecurity con utenti di esempio

    Si tornerà a questi utenti nelle attività successive.You'll see these users in upcoming tasks.

  4. A questo punto, eseguire un inner join con la tabella DimSalesTerritory che contiene i dettagli delle aree associate agli utenti.Next, do an inner join with the DimSalesTerritory table, which shows the user associated region details. Il codice SQL seguente esegue l'inner join, quindi l'immagine mostra l'aspetto della tabella.The SQL code here does the inner join, and the image shows how the table then appears.

    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]
    

    La tabella risultante dal join mostra chi è il responsabile di ogni area di vendita, grazie alla relazione creata nel passaggio 2.The joined table shows who is responsible for each sales region, thanks to the relationship created in Step 2. Si può ad esempio vedere che Rita Santos è responsabile dell'area Australia.For example, you can see that Rita Santos is responsible for Australia.

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

Dopo aver creato il data warehouse relazionale, è necessario definire il modello tabulare.Once your relational data warehouse is in place, you need to define the tabular model. Il modello può essere creato usando SQL Server Data Tools (SSDT).You can create the model using SQL Server Data Tools (SSDT). Per altre informazioni, vedere Creare un nuovo progetto di modello tabulare.For more information, see Create a New Tabular Model Project.

  1. Importare tutte le tabelle necessarie nel modello come illustrato di seguito.Import all the necessary tables into the model as shown below.

    SQL Server importato per l'uso con Data Tools

  2. Dopo aver importato le tabelle necessarie, è necessario 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. Selezionare il menu Modello in SQL Server Data Tools, quindi selezionare Ruoli.Select the Model menu in SQL Server Data Tools, and then select Roles. In Gestione ruoli selezionare Nuovo.In Role Manager, select New.

  3. Nella scheda Membri della finestra di dialogo Gestione ruoli aggiungere gli utenti definiti nella tabella DimUserSecurity durante l'attività 1.Under Members in the Role Manager, add the users that you defined in the DimUserSecurity table in Task 1.

    Aggiungere utenti in Gestione ruoli

  4. Aggiungere quindi le funzioni appropriate per entrambe 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.

    Aggiungere funzioni ai filtri di riga

  5. La funzione LOOKUPVALUE restituisce i valori per una colonna in cui il nome utente di Windows è uguale a quello restituito dalla funzione USERNAME.The LOOKUPVALUE function returns values for a column in which the Windows user name matches the one the USERNAME function returns. È quindi possibile limitare le query al punto in cui i valori restituiti dalla funzione LOOKUPVALUE sono uguali a quelli presenti nella stessa tabella o in quella correlata.You can then restrict queries to where the LOOKUPVALUE returned values match ones 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])
    

    In questa formula la funzione LOOKUPVALUE restituisce tutti i valori per la colonna DimUserSecurity[SalesTerritoryID], dove DimUserSecurity[UserName] corrisponde al nome utente di Windows attualmente 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].

    Importante

    Quando si usa la sicurezza a livello di riga, la funzione DAX USERELATIONSHIP non è supportata.When using row-level security, the DAX function USERELATIONSHIP is not supported.

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

  6. Per la tabella DimUserSecurity, aggiungere la formula seguente nella colonna Filtro DAX:For the DimUserSecurity table, in the DAX Filter column, add the following formula:

        =FALSE()
    

    Questa formula specifica che tutte le colonne vengono risolte in false, pertanto non è possibile eseguire query sulle colonne della tabella DimUserSecurity.This formula specifies that all columns resolve to false; meaning DimUserSecurity table columns can't be queried.

A questo punto è necessario elaborare e distribuire il modello.Now you need to process and deploy the model. Per altre informazioni, vedere Distribuzione.For more information, see Deploy.

Attività 3: Aggiungere origini dati all'interno del gateway dati localeTask 3: Add Data Sources within your On-premises data gateway

Quando il modello tabulare è stato distribuito ed è pronto per l'uso, è necessario aggiungere una connessione origine dati al server tabulare di Analysis Services locale.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.

  1. Per consentire al servizio Power BI di accedere all'istanza di Analysis Services locale, è necessario che nell'ambiente sia installato e configurato un gateway dati locale.To allow the Power BI service access to your on-premises analysis service, you need an on-premises data gateway installed and configured in your environment.

  2. 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. Per altre informazioni, vedere Gestire l'origine dati - Analysis Services.For more information, see Manage your data source - Analysis Services.

    Creare una connessione all'origine dati

Al termine di questa procedura, il gateway è configurato e pronto per interagire con l'origine dati di Analysis Services locale.With this procedure complete, the gateway is configured and ready to interact with your on-premises Analysis Services data source.

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

  1. Avviare Power BI Desktop e selezionare Recupera dati > Database.Start Power BI Desktop and select Get Data > Database.

  2. Dall'elenco delle origini dati selezionare Database di SQL Server Analysis Services, quindi Connetti.From the data sources list, select the SQL Server Analysis Services Database and select Connect.

    Connettersi al database di SQL Server Analysis Services

  3. Inserire i dettagli dell'istanza tabulare di Analysis Services e selezionare Connessione dinamica.Fill in your Analysis Services tabular instance details and select Connect live. Selezionare OK.Then select OK.

    Dettagli di Analysis Services

    In Power BI la sicurezza dinamica funziona solo con una connessione dinamica.With Power BI, dynamic security works only with a live connection.

  4. È possibile notare che il modello distribuito si trova nell'istanza di Analysis Services.You can see that the deployed model is in the Analysis Services instance. Selezionare il modello corrispondente, quindi selezionare OK.Select the respective model and then select OK.

    In Power BI Desktop sono ora 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.

  5. Nel riquadro Campi selezionare la misura SalesAmount dalla tabella FactInternetSales e la dimensione SalesTerritoryRegion dalla tabella SalesTerritory.In the Fields pane, select the SalesAmount measure from the FactInternetSales table and the SalesTerritoryRegion dimension from the SalesTerritory table.

  6. Per mantenere la semplicità del report, al momento non verranno aggiunte altre colonne.To keep this report simple, we won't add any more columns right now. Perché la rappresentazione dei dati sia più significativa, cambiare la visualizzazione in Grafico ad anello.To have a more meaningful data representation, change the visualization to Donut chart.

    Visualizzazione del grafico ad anello

  7. 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. Nella scheda Home della barra multifunzione di Power BI Desktop selezionare Pubblica.From the Home ribbon in Power BI Desktop, select Publish.

Attività 5: Creare e condividere un dashboardTask 5: Create and share a dashboard

Il report è stato creato e pubblicato nel servizio Power BI.You've created the report and published it to the Power BI service. A questo punto è possibile usare l'esempio creato nei passaggi precedenti per illustrare lo scenario di sicurezza del modello.Now you can use the example created in previous steps to demonstrate the model security scenario.

Nel ruolo di Responsabile vendite, l'utente Grace può visualizzare i dati di tutte le varie aree di vendita.In the role as Sales Manager, the user Grace can see data from all the different sales regions. Grace crea il report e lo pubblica nel servizio Power BI.Grace creates this report and publishes it to the Power BI service. Questo report è stato creato nelle attività precedenti.This report was created in the previous tasks.

Dopo la pubblicazione del report, il passaggio successivo consiste nel creare un dashboard del servizio Power BI denominato TabularDynamicSec, in base a tale report.Once Grace publishes the report, the next step is to create a dashboard in the Power BI service called TabularDynamicSec based on that report. Nella figura seguente si può notare che Grace può visualizzare i dati corrispondenti all'intera area di vendita.In the following image, notice that Grace can see the data corresponding to all the sales region.

Dashboard del servizio Power BI

Ora Grace condivide il dashboard con una collega, Rita, responsabile delle vendite nell'area Australia.Now Grace shares the dashboard with a colleague, Rita, who is responsible for the Australia region sales.

Condividere un dashboard di Power BI

Quando Rita accede al servizio Power BI e visualizza il dashboard condiviso che Grace ha creato, può vedere solo le vendite dell'area Australia.When Rita logs in to the Power BI service and views the shared dashboard that Grace created, only sales from the Australia region are visible.

Congratulazioni!Congratulations! Il servizio Power BI illustra la sicurezza dinamica a livello di riga definita nel modello tabulare di Analysis Services locale.The Power BI service shows the dynamic row-level security defined in the on-premises Analysis Services tabular model. 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: Conoscere ciò che accade dietro le quinteTask 6: Understand what happens behind the scenes

Questa attività presuppone che si abbia familiarità con SQL Server Profiler perché è necessario acquisire una traccia di SQL Server Profiler nell'istanza tabulare di SSAS locale.This task assumes you're familiar with SQL Server Profiler, since you need to capture a SQL Server profiler trace on your on-premises SSAS tabular instance.

La sessione verrà inizializzata non appena l'utente Rita eseguirà l'accesso al dashboard nel servizio Power BI.The session gets initialized as soon as the user, Rita, accesses the dashboard in the Power BI service. È possibile notare che il ruolo salesterritoryusers avrà effetto immediato con il nome utente effettivo rita@contoso.comYou can see that the salesterritoryusers role takes an immediate effect with the effective user name as rita@contoso.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>rita@contoso.com</EffectiveUserName></PropertyList>

In base alla richiesta del nome utente effettivo, Analysis Services converte la richiesta nelle credenziali contoso\rita effettive dopo l'esecuzione di una query sull'istanza di Active Directory locale.Based on the effective user name request, Analysis Services converts the request to the actual contoso\rita credential after querying the local Active Directory. Dopo aver ottenuto le credenziali, Analysis Services restituisce i dati per i quali l'utente ha l'autorizzazione di visualizzazione e accesso.Once Analysis Services gets the credential, Analysis Services returns the data the user has permission to view and access.

Se vengono eseguite altre attività con il dashboard, 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, with SQL Profiler you would see a specific query coming back to the Analysis Services tabular model as a DAX query. Se, ad esempio, Rita passa dal dashboard al report sottostante, viene eseguita la query seguente.For example, if Rita goes from the dashboard to the underlying report, the following query occurs.

La query DAX torna al modello di Analysis Services

È anche possibile vedere di seguito la query DAX, eseguita per popolare i dati del report.You can also see below the DAX query that is getting executed to populate report data.

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>

ConsiderazioniConsiderations

  • 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.

  • Le eventuali modifiche apportate ai dati dopo l'elaborazione del modello saranno immediatamente disponibili per gli utenti che eseguiranno l'accesso tramite connessione dinamica dal servizio Power BI.Any changes in the data after processing the model would be immediately available for the users accessing the report with live connection from the Power BI service.