Didacticiel : sécurité dynamique au niveau des lignes avec le modèle tabulaire Analysis ServicesTutorial: Dynamic row level security with Analysis services tabular model

Ce didacticiel présente les étapes nécessaires pour implémenter la sécurité au niveau des lignes dans votre modèle tabulaire Analysis Services. Il montre également comment l’utiliser dans un rapport 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. Les étapes de ce didacticiel sont conçues pour vous aider à suivre facilement son déroulement et à apprendre les étapes nécessaires en se basant sur un exemple de jeu de données.The steps in this tutorial are designed to let you follow along and learn the steps necessary by completing on a sample dataset.

Elles sont décrites en détail ci-dessous pour vous aider à comprendre ce que vous devez faire pour implémenter la sécurité dynamique au niveau des lignes avec le modèle tabulaire 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:

  • Créer une table de sécurité dans la base de données AdventureworksDW2012Create a new security table in the AdventureworksDW2012 database
  • Générer le modèle tabulaire avec les tables de faits et de dimension nécessairesBuild the tabular model with necessary fact and dimension tables
  • Définir les rôles et les autorisations des utilisateursDefine the roles and permissions for the users
  • Déployer le modèle dans une instance du modèle tabulaire Analysis ServicesDeploy the model to an Analysis Services tabular instance
  • Utiliser Power BI Desktop pour générer un rapport qui affiche les données correspondant à l’utilisateur qui accède au rapportUse Power BI Desktop to build a report that displays the data corresponding to the user accessing the report
  • Déployer le rapport dans le service Power BIDeploy the report to Power BI service
  • Créer un tableau de bord basé sur le rapportCreate a new dashboard based on the report, and finally,
  • Partager le tableau de bord avec vos collèguesShare the dashboard with your coworkers

Pour suivre les étapes de ce didacticiel, vous avez besoin de la base de données AdventureworksDW2012 que vous pouvez télécharger ici.To follow the steps in this tutorial you need the AdventureworksDW2012 database, which you can download here.

Tâche 1 : Créer la table de sécurité utilisateur et définir la relation de donnéesTask 1: Create the user security table and define data relationship

De nombreux articles publiés décrivent comment définir la sécurité dynamique au niveau des lignes avec le modèle tabulaire 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. Dans le cadre de notre exemple, nous suivons cet article.For our sample we follow this article. Les étapes suivantes vont vous guider lors de la première tâche de ce didacticiel.The following steps walk you through the first task in this tutorial.

  1. Notre exemple utilise la base de données relationnelle AdventureworksDW2012.For our sample, we're using AdventureworksDW2012 relational database. Dans celle-ci, créez la table DimUserSecurity, comme illustré dans l’image suivante.In that database, create the DimUserSecurity table, as shown in the following image. Pour cet exemple, nous utilisons SQL Server Management Studio (SSMS) pour créer la table.For this sample, we're using SQL Server Management Studio (SSMS) to create the table.

  2. Une fois la table créée et enregistrée, nous devons créer la relation entre la colonne SalesTerritoryID de la table DimUserSecurity et la colonne SalesTerritoryKey de la table DimSalesTerritory, comme illustré dans l’image suivante.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. Pour cela, il est possible d’utiliser SSMS en cliquant sur la table DimUserSecurity et en sélectionnant Modifier.This can be done from SSMS by right-clicking on the DimUserSecurity table, and selecting Edit.

  3. Enregistrez la table, puis ajoutez quelques lignes d’informations utilisateur en cliquant à nouveau avec le bouton droit sur la table DimUserSecurity et en sélectionnant Modifier les 200 premières lignes.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. Une fois que vous avez ajouté les utilisateurs, les lignes de la table DimUserSecurity ont le même aspect que dans l’image suivante :Once you’ve added those users, the rows of the DimUserSecurity table look like they do in the following image:

    Nous reviendrons à ces utilisateurs lors des prochaines tâches.We’ll come back to these users in upcoming tasks.

  4. Ensuite, nous faisons une jointure interne avec la table DimSalesTerritory, qui affiche les détails de région associés à l’utilisateur.Next we do an inner join with the DimSalesTerritory table, which shows the region details associated with the user. Le code suivant exécute la jointure interne et l’image ci-dessous montre comment la table s’affiche une fois la jointure interne correctement effectuée.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. Notez que l’image ci-dessus présente des informations telles que l’utilisateur responsable de la région de ventes.Notice that the above image shows information such as which user is responsible for which sales region. Ces données sont affichées en raison de la relation que nous avons créée à l’étape 2.That data is displayed because of the relationship that we created in Step 2. Notez également que l’utilisateur Jon Doe fait partie de la région de ventes Australie.Also, note that the user Jon Doe is part of the Australia sales region. Nous reviendrons à John Doe lors des tâches et étapes à venir.We’ll revisit John Doe in upcoming steps and tasks.

Tâche 2 : Créer le modèle tabulaire avec les tables de faits et de dimensionTask 2: Create the tabular model with facts and dimension tables

  1. Une fois que votre entrepôt de données relationnelles est en place, il est temps de définir votre modèle tabulaire.Once your relational data warehouse is in place, it’s time to define your tabular model. Celui-ci peut être créé à l’aide de SQL Server Data Tools (SSDT).The model can be created using SQL Server Data Tools (SSDT). Pour obtenir plus d’informations sur la définition d’un modèle tabulaire, consultez cet article.To get more information about how to define a tabular model, please refer this article.
  2. Importez toutes les tables nécessaires dans le modèle, comme indiqué ci-dessous.Import all the necessary tables in to the model as shown below.

  3. Définissez ensuite un rôle appelé SalesTerritoryUsers avec l’autorisation Lecture.Once you’ve imported the necessary tables, you need to define a role called SalesTerritoryUsers with Read permission. Pour cela, cliquez sur le menu Modèle de SQL Server Data Tools, puis sur Rôles.This can be achieved by clicking on the Model menu in SQL Server Data Tools, and then clicking Roles. Dans la boîte de dialogue Gestionnaire de rôles, cliquez sur Nouveau.In the Role Manager dialog box, click New.
  4. Sous l’onglet Membres du Gestionnaire de rôles, ajoutez les utilisateurs que nous avons définis dans la table DimUserSecurity lors de la tâche 1 - étape 3.Under Members tab in the Role Manager, add the users that we defined in the DimUserSecurity table in Task 1 - step 3.

  5. Ensuite, ajoutez les fonctions appropriées pour les tables DimSalesTerritory et DimUserSecurity , comme indiqué ci-dessous sous l’onglet Filtres de lignes .Next, add the proper functions for both DimSalesTerritory and DimUserSecurity tables, as shown below under Row Filters tab.

  6. Dans cette étape, nous utilisons la fonction LOOKUPVALUE pour renvoyer les valeurs d’une colonne dans laquelle le nom d’utilisateur Windows est le même que celui renvoyé par la fonction 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. Les requêtes peuvent ensuite être restreintes pour que les valeurs renvoyées par LOOKUPVALUE correspondent aux valeurs de cette même table ou d’une table connexe.Queries can then be restricted where the values returned by LOOKUPVALUE match values in the same or related table. Dans la colonne Filtre DAX, tapez la formule suivante :In the DAX Filter column, type the following formula:

    =DimSalesTerritory[SalesTerritoryKey]=LOOKUPVALUE(DimUserSecurity[SalesTerritoryID], DimUserSecurity[UserName], USERNAME(), DimUserSecurity[SalesTerritoryID], DimSalesTerritory[SalesTerritoryKey])
    
  7. Dans cette formule, la fonction LOOKUPVALUE renvoie toutes les valeurs de la colonne DimUserSecurity[SalesTerritoryID], où DimUserSecurity[UserName] est identique à l’utilisateur Windows actuellement connecté et où DimUserSecurity[SalesTerritoryID] est identique à 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].

    L’ensemble de ventes SalesTerritoryKey renvoyé par LOOKUPVALUE est ensuite utilisé pour limiter les lignes affichées dans DimSalesTerritory.The set of Sales SalesTerritoryKey's returned by LOOKUPVALUE is then used to restrict the rows shown in the DimSalesTerritory. Seules les lignes où l’élément SalesTerritoryKey de la ligne est compris dans la plage d’ID renvoyée par la fonction LOOKUPVALUE sont affichées.Only rows where the SalesTerritoryKey for the row is in the set of IDs returned by the LOOKUPVALUE function are displayed.

  8. Pour la table DimUserSecurity, dans la colonne Filtre DAX, tapez la formule suivante.For the DimUserSecurity table, in the DAX Filter column, type the following formula.

    =FALSE()
    
  9. Cette formule indique que toutes les colonnes sont converties en condition booléenne false et que, par conséquent, aucune colonne de la table DimUserSecurity ne peut être interrogée.This formula specifies that all columns resolve to the false Boolean condition; therefore, no columns for the DimUserSecurity table can be queried.
  10. Nous devons à présent traiter et déployer le modèle.Now we need to process and deploy the model. Vous pouvez consulter cet article pour obtenir de l’aide lors du déploiement du modèle.You can refer this article for assistance in deploying the model.

Tâche 3 : Ajout de sources de données dans la passerelle de données localeTask 3: Adding Data Sources within your on-premises data gateway

  1. Une fois que votre modèle tabulaire est déployé et prêt à être utilisé, vous devez ajouter une connexion à la source de données à votre serveur tabulaire Analysis Services dans votre portail 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. Pour permettre au service Power BI d’accéder à votre service d’analyse local, la passerelle de données locale doit être installée et configurée dans votre environnement.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. Une fois que la passerelle de données locale est correctement configurée, vous devez créer une connexion à la source de données pour votre instance tabulaire Analysis Services.Once the gateway is correctly configured, you need to create a data source connection for your Analysis Services tabular instance. Cet article va vous aider à ajouter une source de données à partir du portail Power BI.This article will help you with adding data source within the Power BI portal.

  4. Une fois que l’étape précédente est terminée, la passerelle de données locale est configurée et prête à interagir avec votre source de données Analysis Services locale.With the previous step complete, the gateway is configured and ready interact with your on-premises Analysis Services data source.

Tâche 4 : Création d’un rapport basé sur le modèle tabulaire Analysis Services à l’aide de Power BI DesktopTask 4: Creating report based on analysis services tabular model using Power BI desktop

  1. Lancez Power BI Desktop et sélectionnez Obtenir des données > Base de données .Launch Power BI Desktop and select Get Data > Database.
  2. Dans la liste des sources de données, sélectionnez Base de données SQL Server Analysis Services, puis Connexion.From the list of data sources, select the SQL Server Analysis Services Database and select connect.

  3. Renseignez les détails de l’instance tabulaire Analysis Services et sélectionnez Connexion directe.Fill in your Analysis Services tabular instance details and select Connect Live. Sélectionnez OK.Select OK. Avec Power BI, la sécurité dynamique fonctionne uniquement avec Connexion directe.With Power BI, dynamic security works only with Live connection.

  4. Vous voyez le modèle qui a été déployé dans l’instance Analysis Services.You'll see that the model that was deployed in the Analysis Services instance. Sélectionnez le modèle en question, puis OK.Select the respective model and select OK.

  5. Power BI Desktop affiche maintenant tous les champs disponibles, à droite de la zone de dessin dans le volet Champs.Power BI Desktop now displays all the available fields, to the right of the canvas in the Fields pane.
  6. Dans le volet Champs situé à droite, sélectionnez la mesure SalesAmount dans la table FactInternetSales et la dimension SalesTerritoryRegion dans la table SalesTerritory.In the Fields pane on the right, select the SalesAmount measure from FactInternetSales table and SalesTerritoryRegion dimension from SalesTerritory table.
  7. Comme nous voulons que ce rapport reste simple, nous n’allons pas ajouter de colonnes.We’ll keep this report simple, so right now we won’t add any more columns. Pour avoir une représentation plus significative des données, nous allons définir la visualisation sur Graphique en anneau.To have more meaningful representation of the data, we'll change the visualization to Donut chart.

  8. Une fois que le rapport est prêt, vous pouvez le publier directement sur le portail Power BI.Once your report is ready, you can directly publish it to the Power BI portal. Sur le ruban Accueil de Power BI Desktop, sélectionnez Publier.From the Home ribbon in Power BI Desktop, select Publish.

Tâche 5 : Création et partage d’un tableau de bordTask 5: Creating and sharing a dashboard

  1. Vous avez créé le rapport et cliqué sur Publier dans Power BI Desktop. Le rapport est maintenant publié dans le service Power BI.You’ve created the report and clicked Publish in Power BI Desktop, so the report is published to the Power BI service. Nous pouvons à présent faire la démonstration de notre scénario de sécurité du modèle en utilisant l’exemple que nous avons créé lors des étapes précédentes.Now that it’s in the service, our model security scenario can be demonstrated by using the example we created in the previous steps.

    Dans son rôle, Responsable des ventes - Sumit peut voir les données des différentes régions de ventes.In his role, Sales Manager - Sumit can see data from all the different sales regions. Par conséquent, il crée ce rapport (celui créé lors des étapes de la tâche précédente) et le publie dans le service Power BI.So he creates this report (the report created in the previous task steps) and publishes it to the Power BI service.

    Il crée ensuite un tableau de bord dans le service Power BI, qu’il nomme TabularDynamicSec en fonction de ce rapport.Once he publishes the report, he creates a dashboard in the Power BI service called TabularDynamicSec based on that report. Dans l’image suivante, notez que le responsable des ventes (Sumit) peut voir les données correspondant à l’ensemble des régions de vente.In the following image, notice that the sales Manager (Sumit) is able to see the data corresponding to all the sales region.

  2. Sumit partage désormais le tableau de bord avec son collègue, Jon Doe, responsable des ventes dans la région Australie.Now Sumit shares the dashboard with his colleague, Jon Doe, who is responsible for sales in Australia region.

  3. Quand Jon Doe se connecte au service Power BI et affiche le tableau de bord partagé créé par Sumit, il doit voir uniquement les ventes de la région dont il est responsable.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. Par conséquent, Jon Doe se connecte, accède au tableau de bord que Sumit a partagé avec lui et voit uniquement les ventes de la région Australie.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. Félicitations !Congratulations! La sécurité dynamique au niveau des lignes qui a été définie dans le modèle tabulaire Analysis Services local a été correctement répercutée et observée dans le service 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 utilise la propriété effectiveusername pour envoyer les informations d’identification Power BI actuelles à la source de données locale pour exécuter les requêtes.Power BI uses the effectiveusername property to send the current Power BI user credential to the on-premises data source to run the queries.

Tâche 6 : Comprendre ce qui se passe en arrière-planTask 6: Understanding what happens behind the scenes

  1. Cette tâche suppose que vous êtes familiarisé avec le Générateur de profils SQL, car vous devez capturer une trace du Générateur de profils SQL Server sur votre instance tabulaire 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 session est initialisée dès que l’utilisateur (dans ce cas, Jon Doe) accède au tableau de bord dans le service Power BI.The session gets initialized as soon as the user (Jon Doe, in this case) accesses the dashboard in the Power BI service. Vous pouvez voir que le rôle salesterritoryusers prend effet immédiatement avec le nom d’utilisateur en vigueur 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. En fonction de la demande de nom d’utilisateur en vigueur, Analysis Services convertit la demande en informations d’identification moonneo\jondoe après interrogation de l’annuaire 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. Une fois qu’Analysis Services obtient les informations d’identification réelles à partir d’Active Directory, puis en fonction de l’accès aux données dont dispose l’utilisateur, Analysis Services renvoie les seules données pour lesquelles il dispose d’une autorisation.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. Si d’autres activités ont lieu dans le tableau de bord, par exemple, si Jon Doe passe du tableau de bord au rapport sous-jacent, avec le Générateur de profils SQL, vous voyez une requête spécifique revenir vers le modèle tabulaire Analysis Services en tant que requête 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. Vous pouvez également voir ci-dessous la requête DAX exécutée pour remplir les données du rapport.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>
    

ConsidérationsConsiderations

Voici quelques considérations à prendre en compte lorsque vous utilisez la sécurité au niveau des lignes, SSAS et Power BI.There are a few considerations to keep in mind when working with row level security, SSAS and Power BI.

  1. La sécurité locale au niveau des lignes avec Power BI est uniquement disponible avec une connexion directe.On-premises row level security with Power BI is only available with Live Connection.
  2. Une fois que le modèle a été traité, les modifications apportées aux données sont immédiatement disponibles pour les utilisateurs qui accèdent au rapport avec une connexion directe à partir du service 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.