Tutorial: Seguridad dinámica de nivel de fila con el modelo tabular de Analysis ServicesTutorial: Dynamic row level security with Analysis services tabular model

En este tutorial se muestran los pasos necesarios para implementar la seguridad de nivel de fila dentro del modelo tabular de Analysis Services y se muestra cómo usarla en un informe de 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. Los pasos de este tutorial se han diseñado para permitirle seguir adelante y conocer los pasos necesarios al completar un conjunto de datos de ejemplo.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, los pasos siguientes se describen en detalle, lo que le ayudará a comprender lo que necesita para implementar la seguridad de nivel de fila dinámica con el modelo tabular de 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:

  • Crear una nueva tabla de seguridad en la base de datos AdventureworksDW2012Create a new security table in the AdventureworksDW2012 database
  • Generar el modelo tabular con las tablas de hechos y dimensiones necesariasBuild the tabular model with necessary fact and dimension tables
  • Definir los roles y permisos para los usuariosDefine the roles and permissions for the users
  • Implementar el modelo en una instancia tabular de Analysis ServicesDeploy the model to an Analysis Services tabular instance
  • Usar Power BI Desktop para crear un informe que muestre los datos correspondientes al usuario que obtiene acceso al informeUse Power BI Desktop to build a report that displays the data corresponding to the user accessing the report
  • Implementar el informe en el servicio Power BIDeploy the report to Power BI service
  • Crear un nuevo panel basado en el informe y, finalmente,Create a new dashboard based on the report, and finally,
  • Compartir el panel con sus compañerosShare the dashboard with your coworkers

Para seguir los pasos de este tutorial necesita la base de datos AdventureworksDW2012, que puede descargar aquí.To follow the steps in this tutorial you need the AdventureworksDW2012 database, which you can download here.

Tarea 1: Crear la tabla de seguridad del usuario y definir la relación de los datosTask 1: Create the user security table and define data relationship

Hay muchos artículos publicados que describen cómo definir la seguridad de nivel de fila dinámica con el modelo tabular de 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. Para nuestro ejemplo, seguimos este artículo.For our sample we follow this article. En los pasos siguientes se le guía a través de la primera tarea de este tutorial.The following steps walk you through the first task in this tutorial.

  1. Para nuestro ejemplo, usamos la base de datos relacional AdventureworksDW2012.For our sample, we're using AdventureworksDW2012 relational database. En esa base de datos, cree la tabla DimUserSecurity, tal y como se muestra en la siguiente imagen.In that database, create the DimUserSecurity table, as shown in the following image. En este ejemplo, usamos SQL Server Management Studio (SSMS) para crear la tabla.For this sample, we're using SQL Server Management Studio (SSMS) to create the table.

  2. Una vez que se ha creado y guardado la tabla, hay que crear la relación entre la columna SalesTerritoryID de la tabla DimUserSecurity y la columna SalesTerritoryKey de la tabla DimSalesTerritory, tal y como se muestra en la siguiente imagen.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. Para hacerlo desde SSMS, haga clic con el botón derecho en la tabla DimUserSecurity y seleccione Editar.This can be done from SSMS by right-clicking on the DimUserSecurity table, and selecting Edit.

  3. Guarde la tabla, vuelva a hacer clic con el botón derecho en la tabla DimUserSecurity y después seleccione Edit top 200 rows (Editar las primeras 200 filas) para agregar algunas filas de información de usuario en la tabla.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. Una vez que haya agregado esos usuarios, las filas de la tabla DimUserSecurity tendrán una apariencia similar a las de la siguiente imagen:Once you’ve added those users, the rows of the DimUserSecurity table look like they do in the following image:

    Volveremos a estos usuarios en tareas futuras.We’ll come back to these users in upcoming tasks.

  4. Después, realizamos una combinación interna con la tabla DimSalesTerritory, que muestra los detalles de la región asociados con el usuario.Next we do an inner join with the DimSalesTerritory table, which shows the region details associated with the user. El siguiente código realiza la combinación interna y la imagen siguiente muestra la apariencia de la tabla una vez que la combinación interna se ha realizado correctamente.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. Tenga en cuenta que la imagen anterior muestra información como qué usuario es responsable de qué región de ventas.Notice that the above image shows information such as which user is responsible for which sales region. Esos datos se muestran debido a la relación que hemos creado en el Paso 2.That data is displayed because of the relationship that we created in Step 2. Además, tenga en cuenta que el usuario Jon Doe es parte de la región de ventas de Australia.Also, note that the user Jon Doe is part of the Australia sales region. Volveremos a John Doe en tareas y pasos futuros.We’ll revisit John Doe in upcoming steps and tasks.

Tarea 2: Crear el modelo tabular con tablas de hechos y dimensionesTask 2: Create the tabular model with facts and dimension tables

  1. Una vez que el almacenamiento de datos relacionales está en su sitio, es el momento de definir el modelo tabular.Once your relational data warehouse is in place, it’s time to define your tabular model. El modelo se puede crear con SQL Server Data Tools (SSDT).The model can be created using SQL Server Data Tools (SSDT). Para obtener más información sobre cómo definir un modelo tabular, consulte este artículo.To get more information about how to define a tabular model, please refer this article.
  2. Importe todas las tablas necesarias en el modelo tal y como se muestra a continuación.Import all the necessary tables in to the model as shown below.

  3. Una vez que haya importado las tablas necesarias, debe definir un rol denominado SalesTerritoryUsers con permiso de lectura.Once you’ve imported the necessary tables, you need to define a role called SalesTerritoryUsers with Read permission. Para ello, haga clic en el menú Modelo en SQL Server Data Tools y luego haga clic en Roles.This can be achieved by clicking on the Model menu in SQL Server Data Tools, and then clicking Roles. En el cuadro de diálogo Administrador de roles, haga clic en Nuevo.In the Role Manager dialog box, click New.
  4. En la pestaña Miembros en el Administrador de roles, agregue los usuarios que hemos definido en la tabla DimUserSecurity en la tarea 1 - paso 3.Under Members tab in the Role Manager, add the users that we defined in the DimUserSecurity table in Task 1 - step 3.

  5. Después, agregue las funciones adecuadas para las tablas DimSalesTerritory y DimUserSecurity , tal y como se muestra a continuación en la pestaña Filtros de fila .Next, add the proper functions for both DimSalesTerritory and DimUserSecurity tables, as shown below under Row Filters tab.

  6. En este paso, usamos la función LOOKUPVALUE para devolver valores para una columna en la que el nombre de usuario de Windows es el mismo que el nombre de usuario devuelto por la función 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. Después, se pueden restringir las consultas en las que los valores devueltos por LOOKUPVALUE coinciden con los valores de la misma tabla o de la tabla relacionada.Queries can then be restricted where the values returned by LOOKUPVALUE match values in the same or related table. En la columna Filtro DAX, escriba la siguiente fórmula:In the DAX Filter column, type the following formula:

    =DimSalesTerritory[SalesTerritoryKey]=LOOKUPVALUE(DimUserSecurity[SalesTerritoryID], DimUserSecurity[UserName], USERNAME(), DimUserSecurity[SalesTerritoryID], DimSalesTerritory[SalesTerritoryKey])
    
  7. En esta fórmula, la función LOOKUPVALUE devuelve todos los valores de la columna DimUserSecurity[SalesTerritoryID], donde DimUserSecurity[UserName] es igual que el nombre de usuario de Windows que ha iniciado sesión actualmente y DimUserSecurity[SalesTerritoryID] es el mismo que 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].

    El conjunto de Sales SalesTerritoryKey devuelto por LOOKUPVALUE se usa después para restringir las filas que se muestran en DimSalesTerritory.The set of Sales SalesTerritoryKey's returned by LOOKUPVALUE is then used to restrict the rows shown in the DimSalesTerritory. Solo se muestran las filas donde SalesTerritoryKey para la fila se encuentra en el conjunto de identificadores devueltos por la función LOOKUPVALUE.Only rows where the SalesTerritoryKey for the row is in the set of IDs returned by the LOOKUPVALUE function are displayed.

  8. Para la tabla DimUserSecurity, en la columna Filtro DAX, escriba la siguiente fórmula.For the DimUserSecurity table, in the DAX Filter column, type the following formula.

    =FALSE()
    
  9. Esta fórmula especifica que todas las columnas se resuelven en la condición booleana falsa; por tanto, no se pueden consultar columnas de la tabla DimUserSecurity.This formula specifies that all columns resolve to the false Boolean condition; therefore, no columns for the DimUserSecurity table can be queried.
  10. Ahora hay que procesar e implementar el modelo.Now we need to process and deploy the model. Puede consultar este artículo para obtener asistencia para implementar el modelo.You can refer this article for assistance in deploying the model.

Tarea 3: Agregar orígenes de datos a una puerta de enlace de datos localTask 3: Adding Data Sources within your on-premises data gateway

  1. Una vez que el modelo tabular está implementado y listo para usarlo, debe agregar una conexión de origen de datos al servidor tabular de Analysis Services local en el portal de 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. Para permitir que el servicio Power BI acceda a su servicio de análisis local, es preciso que tenga una puerta de enlace de datos local instalada y configurada en su entorno.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. Una vez que la puerta de enlace esté configurada correctamente, debe crear una conexión de origen de datos para la instancia tabular de Analysis Services.Once the gateway is correctly configured, you need to create a data source connection for your Analysis Services tabular instance. Este artículo le ayudará a agregar el origen de datos en el portal de Power BI.This article will help you with adding data source within the Power BI portal.

  4. Una vez completado el paso anterior, la puerta de enlace está configurada y lista para interactuar con el origen de datos local de Analysis Services.With the previous step complete, the gateway is configured and ready interact with your on-premises Analysis Services data source.

Tarea 4: Crear un informe basado en el modelo tabular de Analysis Services con Power BI DesktopTask 4: Creating report based on analysis services tabular model using Power BI desktop

  1. Inicie Power BI Desktop y seleccione Obtener datos > Base de datos .Launch Power BI Desktop and select Get Data > Database.
  2. En la lista de orígenes de datos, seleccione la Base de datos de SQL Server Analysis Services y seleccione Conectar.From the list of data sources, select the SQL Server Analysis Services Database and select connect.

  3. Rellene los detalles de la instancia tabular de Analysis Services y seleccione Conectar en directo.Fill in your Analysis Services tabular instance details and select Connect Live. Seleccione Aceptar.Select OK. Con Power BI, la seguridad dinámica solo funciona con Conexión dinámica.With Power BI, dynamic security works only with Live connection.

  4. Verá el modelo que se ha implementado en la instancia de Analysis Services.You'll see that the model that was deployed in the Analysis Services instance. Seleccione el modelo correspondiente y seleccione Aceptar.Select the respective model and select OK.

  5. Power BI Desktop ahora muestra todos los campos disponibles a la derecha del lienzo en el panel Campos.Power BI Desktop now displays all the available fields, to the right of the canvas in the Fields pane.
  6. En el panel Campos de la derecha, seleccione la medida SalesAmount de la tabla FactInternetSales y la dimensión SalesTerritoryRegion de la tabla SalesTerritory.In the Fields pane on the right, select the SalesAmount measure from FactInternetSales table and SalesTerritoryRegion dimension from SalesTerritory table.
  7. Este informe seguirá siendo sencillo, así que ahora no agregaremos más columnas.We’ll keep this report simple, so right now we won’t add any more columns. Para tener una representación más significativa de los datos, cambiaremos la visualización a Gráfico de anillos.To have more meaningful representation of the data, we'll change the visualization to Donut chart.

  8. Una vez que el informe esté listo, puede publicarlo directamente en el portal de Power BI.Once your report is ready, you can directly publish it to the Power BI portal. En la cinta Inicio de Power BI Desktop, seleccione Publicar.From the Home ribbon in Power BI Desktop, select Publish.

Tarea 5: Crear y compartir un panelTask 5: Creating and sharing a dashboard

  1. Ha creado el informe y ha hecho clic en Publicar en Power BI Desktop, por lo que el informe se ha publicado en el servicio Power BI.You’ve created the report and clicked Publish in Power BI Desktop, so the report is published to the Power BI service. Ahora que está en el servicio, el escenario de seguridad del modelo se puede demostrar al usar el ejemplo que hemos creado en los pasos 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.

    En su rol, director de ventas: Sumit puede ver los datos de todas las regiones de ventas diferentes.In his role, Sales Manager - Sumit can see data from all the different sales regions. Por lo que crea este informe (el informe creado en los pasos de la tarea anteriores) y lo publica en el servicio Power BI.So he creates this report (the report created in the previous task steps) and publishes it to the Power BI service.

    Una vez que publica el informe, crea un panel en el servicio Power BI denominado TabularDynamicSec basándose en ese informe.Once he publishes the report, he creates a dashboard in the Power BI service called TabularDynamicSec based on that report. En la siguiente imagen, observe que el director de ventas (Sumit) puede ver los datos correspondientes a toda la región de ventas.In the following image, notice that the sales Manager (Sumit) is able to see the data corresponding to all the sales region.

  2. Ahora Sumit comparte el panel con su compañero, Jon Doe, responsable de ventas en la región de Australia.Now Sumit shares the dashboard with his colleague, Jon Doe, who is responsible for sales in Australia region.

  3. Cuando Jon Doe inicia sesión en el servicio Power BI y ve el panel compartido que ha creado Sumit, Jon Doe debería ver solo las ventas de su región, de la que es 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. Jon Doe inicia sesión, obtiene acceso al panel que Sumit ha compartido con él y Jon Doe solo ve las ventas de la región de 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. Enhorabuena.Congratulations! La seguridad de nivel de fila dinámica definida en el modelo tabular local de Analysis Services se ha reflejado y observado correctamente en el servicio 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 propiedad effectiveusername para enviar las credenciales de usuario de Power BI actuales al origen de datos local para ejecutar las 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.

Tarea 6: Comprender qué sucede en segundo planoTask 6: Understanding what happens behind the scenes

  1. En esta tarea se supone que está familiarizado con SQL Profiler, ya que tiene que capturar un seguimiento de SQL Server Profiler en la instancia tabular local de SSAS.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 sesión se inicializa tan pronto como el usuario (Jon Doe, en este caso) obtiene acceso al panel en el servicio Power BI.The session gets initialized as soon as the user (Jon Doe, in this case) accesses the dashboard in the Power BI service. Puede ver que el rol salesterritoryusers tiene efecto inmediato con el nombre de usuario vigente como jondoe@moonneo.com.You 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. Según la solicitud de nombre de usuario vigente, Analysis Services convierte la solicitud a la credencial real de moonneo\jondoe después de consultar 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. Una vez que Analysis Services obtiene la credencial real de Active Directory, después, según el acceso para el que el usuario tiene permisos en los datos, Analysis Services devuelve solo los datos para los que tiene permiso.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 se produce más actividad con el panel, por ejemplo, si Jon Doe va del panel al informe subyacente, con SQL Profiler verá una consulta específica que vuelve al modelo tabular de Analysis Services como una 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. También puede ver a continuación la consulta DAX que se ejecuta para rellenar los datos para el informe.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>
    

ConsideracionesConsiderations

Hay algunas consideraciones que tener en cuenta al trabajar con la seguridad de nivel de fila, SSAS y Power BI.There are a few considerations to keep in mind when working with row level security, SSAS and Power BI.

  1. La seguridad de nivel de fila local con Power BI solo está disponible con conexión dinámica.On-premises row level security with Power BI is only available with Live Connection.
  2. Cualquier cambio en los datos después de procesar el modelo estaría inmediatamente disponible para los usuarios que obtienen acceso al informe que se basa en conexión dinámica desde el servicio 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.