Implementare la sicurezza a livello di riga in un modello tabulare di Analysis Services locale

Usando un modello semantico di esempio per eseguire la procedura seguente, questa esercitazione illustra come implementare la sicurezza a livello di riga in un modello tabulare di Analysis Services locale e usarla in un report di Power BI.

  • Creare una nuova tabella di sicurezza nel database AdventureworksDW2012
  • Compilare il modello tabulare con le tabelle dei fatti e delle dimensioni necessarie
  • Definire autorizzazioni e ruoli utente
  • Distribuire il modello in un'istanza tabulare di Analysis Services
  • Creare un report di Power BI Desktop che visualizza i dati personalizzati per l'utente che accede al report
  • Distribuire il report in servizio Power BI
  • Creare un nuovo dashboard basato sul report
  • Condividere il dashboard con i colleghi

Questa esercitazione richiede il database AdventureworksDW2012.

Attività 1: Creare la tabella di sicurezza utente e definire la relazione di dati

È possibile trovare molti articoli che descrivono come definire la sicurezza dinamica a livello di riga con il modello tabulare di SQL Server Analysis Services (SSAS).

I passaggi seguenti richiedono l'uso del database relazionale AdventureworksDW2012.

  1. In AdventureworksDW2012 creare la DimUserSecurity tabella come illustrato di seguito. È possibile usare SQL Server Management Studio (SSMS) per creare la tabella.

    Create DimUserSecurity table

  2. Dopo aver creato e salvato la tabella, è necessario stabilire la relazione tra la DimUserSecurity colonna della SalesTerritoryID tabella e la DimSalesTerritory colonna della SalesTerritoryKey tabella, come illustrato di seguito.

    In SSMS fare clic con il pulsante destro del mouse su DimUserSecurity e scegliere Progetta. Selezionare quindi Relazioni di Progettazione>tabelle.... Al termine, salvare la tabella.

    Foreign Key Relationships

  3. Aggiungere utenti alla tabella. Fare clic con il pulsante destro del mouse su DimUserSecurity e scegliere Modifica prime 200 righe. Dopo aver aggiunto gli utenti, la DimUserSecurity tabella dovrebbe essere simile all'esempio seguente:

    DimUserSecurity table with example users

    Questi utenti verranno visualizzati nelle attività future.

  4. Eseguire quindi un inner join con la DimSalesTerritory tabella, che mostra i dettagli dell'area associata all'utente. Il codice SQL esegue il inner join e l'immagine mostra come viene visualizzata la tabella.

    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 unita in join mostra chi è responsabile di ogni area di vendita, grazie alla relazione creata nel passaggio 2. Ad esempio, si può vedere che Rita Santos è responsabile per l'Australia.

Attività 2: Creare il modello tabulare con fatti e tabelle delle dimensioni

Dopo aver creato il data warehouse relazionale, è necessario definire il modello tabulare. È possibile creare il modello usando SQL Server Data Tools (SSDT). Per altre informazioni, vedere Creare un nuovo progetto di modello tabulare.

  1. Importare tutte le tabelle necessarie nel modello, come illustrato di seguito.

    Imported SQL Server for use with data tools

  2. Dopo aver importato le tabelle necessarie, è necessario definire un ruolo denominato SalesTerritoryUsers con autorizzazione di lettura. Selezionare il menu Modello in SQL Server Data Tools e quindi selezionare Ruoli. In Gestione ruoli selezionare Nuovo.

  3. In Membri in Gestione ruoli aggiungere gli utenti definiti nella tabella nell'attività DimUserSecurity1.

    Add users in Role Manager

  4. Aggiungere quindi le funzioni appropriate per entrambe DimSalesTerritory le tabelle e DimUserSecurity , come illustrato di seguito nella scheda Filtri di riga.

    Add functions to Row Filters

  5. La LOOKUPVALUE funzione restituisce valori per una colonna in cui il nome utente di Windows corrisponde a quello restituito dalla USERNAME funzione. È quindi possibile limitare le query alla posizione in cui i LOOKUPVALUE valori restituiti corrispondono a quelli nella stessa tabella o correlata. Nella colonna Filtro DAX digitare la formula seguente:

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

    In questa formula, la LOOKUPVALUE funzione restituisce tutti i valori per la DimUserSecurity[SalesTerritoryID] colonna, dove DimUserSecurity[UserName] è uguale al nome utente di Windows connesso corrente e DimUserSecurity[SalesTerritoryID] corrisponde a DimSalesTerritory[SalesTerritoryKey].

    Importante

    Quando si usa la sicurezza a livello di riga, la funzione DAX U edizione Standard RELATIONSHIP non è supportata.

    Il set di restituzioni di LOOKUPVALUE Sales SalesTerritoryKeyviene quindi usato per limitare le righe visualizzate in DimSalesTerritory. Vengono visualizzate solo le righe in cui il SalesTerritoryKey valore si trova negli ID restituiti dalla LOOKUPVALUE funzione.

  6. Per la DimUserSecurity tabella, nella colonna Filtro DAX aggiungere la formula seguente:

        =FALSE()
    

    Questa formula specifica che tutte le colonne vengono risolte in false, ovvero DimUserSecurity le colonne della tabella non possono essere sottoposte a query.

A questo momento è necessario elaborare e distribuire il modello. Per altre informazioni, vedere Distribuire.

Attività 3: Aggiungere origini dati all'interno del gateway dati locale

Dopo aver distribuito e pronto per l'utilizzo il modello tabulare, è necessario aggiungere una connessione all'origine dati al server tabulare di Analysis Services locale.

  1. Per consentire all'servizio Power BI l'accesso al servizio di analisi locale, è necessario un gateway dati locale installato e configurato nell'ambiente.

  2. Dopo aver configurato correttamente il gateway, è necessario creare una connessione origine dati per l'istanza tabulare di Analysis Services . Per altre informazioni, vedere Gestire l'origine dati - Analysis Services.

    Create data source connection

Al termine di questa procedura, il gateway è configurato e pronto per interagire con l'origine dati di Analysis Services locale.

Attività 4: Creare un report basato sul modello tabulare di Analysis Services usando Power BI Desktop

  1. Avviare Power BI Desktop e selezionare Recupera database dati>.

  2. Nell'elenco delle origini dati selezionare il database di SQL Server Analysis Services e selezionare Connessione.

    Connect to SQL Server Analysis Services Database

  3. Compilare i dettagli dell'istanza tabulare di Analysis Services e selezionare Connessione live. Selezionare OK.

    Analysis Services details

    Con Power BI, la sicurezza dinamica funziona solo con una connessione dinamica.

  4. È possibile notare che il modello distribuito si trova nell'istanza di Analysis Services. Selezionare il rispettivo modello e quindi selezionare OK.

    Power BI Desktop visualizza ora tutti i campi disponibili, a destra dell'area di disegno nel riquadro Campi .

  5. Nel riquadro Campi selezionare la misura SalesAmount dalla tabella FactInternetSales e dalla dimensione SalesTerritoryRegion dalla tabella SalesTerritory.

  6. Per semplificare questo report, non verranno aggiunte altre colonne al momento. Per avere una rappresentazione dei dati più significativa, modificare la visualizzazione in Grafico ad anello.

    Donut chart visualization

  7. Quando il report è pronto, è possibile pubblicarlo direttamente nel portale di Power BI. Nella barra multifunzione Home in Power BI Desktop selezionare Pubblica.

Attività 5: Creare e condividere un dashboard

Il report è stato creato e pubblicato nel servizio Power BI . È ora possibile usare l'esempio creato nei passaggi precedenti per illustrare lo scenario di sicurezza del modello.

Nel ruolo sales manager l'utente Grace può visualizzare i dati di tutte le diverse aree di vendita. Grace crea questo report e lo pubblica nel servizio Power BI. Questo report è stato creato nelle attività precedenti.

Dopo aver pubblicato il report, il passaggio successivo consiste nel creare un dashboard nella servizio Power BI denominata TabularDynamicSec in base a tale report. Nell'immagine seguente si noti che Grace può visualizzare i dati corrispondenti a tutte le aree di vendita.

Power BI service dashboard

Grace condivide ora il dashboard con un collega, Rita, responsabile delle vendite dell'area australia.

Share a Power BI dashboard

Quando Rita accede al servizio Power BI e visualizza il dashboard condiviso creato da Grace, sono visibili solo le vendite dell'area Australia.

Complimenti. Il servizio Power BI mostra la sicurezza dinamica a livello di riga definita nel modello tabulare di Analysis Services locale. Power BI usa la EffectiveUserName proprietà per inviare le credenziali utente correnti di Power BI all'origine dati locale per eseguire le query.

Attività 6: Comprendere cosa accade dietro le quinte

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.

La sessione viene inizializzata non appena l'utente, Rita, accede al dashboard nel servizio Power BI. È possibile notare che il ruolo salesterritoryusers ha un effetto immediato con il nome utente effettivo come <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>

In base alla richiesta di nome utente effettivo, Analysis Services converte la richiesta nella credenziale effettiva contoso\rita dopo l'esecuzione di query su Active Directory locale. Dopo che Analysis Services ottiene le credenziali, Analysis Services restituisce i dati a cui l'utente ha l'autorizzazione per visualizzare e accedere.

Se si verificano più attività con il dashboard, con SQL Profiler viene visualizzata una query specifica che torna al modello tabulare di Analysis Services come query DAX. Ad esempio, se Rita passa dal dashboard al report sottostante, viene eseguita la query seguente.

DAX query comes back to Analysis Services model

È anche possibile vedere sotto la query DAX che viene eseguita per popolare i dati del 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>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>

Considerazioni

  • La sicurezza a livello di riga locale con Power BI è disponibile solo con la connessione dinamica.

  • Tutte le modifiche apportate ai dati dopo l'elaborazione del modello saranno immediatamente disponibili per gli utenti che accedono al report con connessione dinamica dal servizio Power BI.