Implementación de seguridad de nivel de fila con un modelo tabular local de Analysis ServicesImplement row-level security in an on-premises Analysis Services tabular model

Con un conjunto de datos de ejemplo con el que se trabaja en los pasos siguientes, en este tutorial se muestra cómo implementar la seguridad de nivel de fila en un modelo tabular de Analysis Services local y su uso en un informe de 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.

  • Creación de 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 permisos y roles de usuarioDefine user roles and permissions
  • Implementar el modelo en una instancia tabular de Analysis ServicesDeploy the model to an Analysis Services tabular instance
  • Crear un informe de Power BI Desktop que muestre los datos correspondientes al usuario que accede al informeBuild a Power BI Desktop report that displays data tailored 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 informeCreate a new dashboard based on the report
  • Compartir el panel con sus compañerosShare the dashboard with your coworkers

Este tutorial requiere la base de datos AdventureworksDW2012.This tutorial requires the AdventureworksDW2012 database.

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

Puede encontrar muchos artículos en los que se describe cómo definir la seguridad de nivel de fila dinámica con el modelo tabular de 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. Para nuestro ejemplo seguiremos el artículo Implementar seguridad dinámica utilizando filtros de fila.For our sample, we use Implement Dynamic Security by Using Row Filters.

Estos pasos requieren el uso de la base de datos relacional AdventureworksDW2012.The steps here require using the AdventureworksDW2012 relational database.

  1. En AdventureworksDW2012, cree la tabla DimUserSecurity, tal como se muestra debajo.In AdventureworksDW2012, create the DimUserSecurity table as shown below. Puede usar SQL Server Management Studio (SSMS) para crear la tabla.You can use SQL Server Management Studio (SSMS) to create the table.

    Creación de una tabla DimUserSecurity

  2. Una vez que cree y guarde la tabla, debe establecer la relación entre la columna SalesTerritoryID de la tabla DimUserSecurity y la columna SalesTerritoryKey de la tabla DimSalesTerritory, tal como se muestra debajo.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.

    En SSMS, haga clic con el botón derecho en DimUserSecurity y seleccione Diseño.In SSMS, right-click DimUserSecurity, and select Design. Después, seleccione Diseñador de tablas > Relaciones... . Cuando haya terminado, guarde la tabla.Then select Table Designer > Relationships.... When done, save the table.

    Relaciones de clave externa

  3. Agregue usuarios a la tabla.Add users to the table. Haga clic con el botón derecho en DimUserSecurity y seleccione Editar las primeras 200 filas.Right-click DimUserSecurity and select Edit Top 200 Rows. Una vez agregados los usuarios, la tabla DimUserSecurity debe ser similar a la del ejemplo siguiente:Once you've added users, the DimUserSecurity table should appear similar to the following example:

    Tabla DimUserSecurity con usuarios de ejemplo

    Volveremos a estos usuarios en tareas futuras.You'll see these users in upcoming tasks.

  4. Después, debe realizar una combinación interna con la tabla DimSalesTerritory, que muestra los detalles de la región asociados con el usuario.Next, do an inner join with the DimSalesTerritory table, which shows the user associated region details. Este código SQL realiza la combinación interna y la imagen muestra cómo aparece después la tabla.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 tabla combinada muestra quién es responsable de cada región de ventas, gracias a la relación que se creó en el paso 2.The joined table shows who is responsible for each sales region, thanks to the relationship created in Step 2. Por ejemplo, puede ver que Rita Santos es responsable de Australia.For example, you can see that Rita Santos is responsible for Australia.

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

Una vez que el almacenamiento de datos relacionales está listo, es el momento de definir el modelo tabular.Once your relational data warehouse is in place, you need to define the tabular model. El modelo se puede crear con SQL Server Data Tools (SSDT).You can create the model using SQL Server Data Tools (SSDT). Para más información, consulte Crear un nuevo proyecto de modelo tabular.For more information, see Create a New Tabular Model Project.

  1. Importe todas las tablas necesarias en el modelo tal y como se muestra a continuación.Import all the necessary tables into the model as shown below.

    SQL Server importado para su uso con las herramientas de datos

  2. 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. Seleccione el menú Modelo en SQL Server Data Tools y, a continuación, seleccione Roles.Select the Model menu in SQL Server Data Tools, and then select Roles. En Administrador de roles, seleccione Nuevo.In Role Manager, select New.

  3. En Miembros, en el Administrador de roles, agregue los usuarios que ha definido en la tabla DimUserSecurity de la Tarea 1.Under Members in the Role Manager, add the users that you defined in the DimUserSecurity table in Task 1.

    Adición de usuarios en el Administrador de roles

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

    Adición de funciones a los filtros de fila

  5. La función LOOKUPVALUE devuelve valores para una columna en la que el nombre de usuario de Windows coincide con el valor que devuelve la función USERNAME.The LOOKUPVALUE function returns values for a column in which the Windows user name matches the one the USERNAME function returns. Después, puede restringir las consultas a aquellas en las que los valores que devuelve LOOKUPVALUE coincidan con los de la propia tabla o la relacionada.You can then restrict queries to where the LOOKUPVALUE returned values match ones 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])
    

    En esta fórmula, la función LOOKUPVALUE devuelve todos los valores de la columna DimUserSecurity[SalesTerritoryID], donde DimUserSecurity[UserName] es equivale al nombre de usuario actual que ha iniciado sesión en Windows y DimUserSecurity[SalesTerritoryID] es igual 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].

    Importante

    Al usar la seguridad de nivel de fila, no se admite la función DAX USERELATIONSHIP.When using row-level security, the DAX function USERELATIONSHIP is not supported.

    Después, se utiliza el conjunto de devoluciones LOOKUPVALUE de SalesTerritoryKey de ventas para restringir las filas que se muestran en DimSalesTerritory.The set of Sales SalesTerritoryKey's LOOKUPVALUE returns is then used to restrict the rows shown in the DimSalesTerritory. Solo se muestran las filas en las que el valor de SalesTerritoryKey está entre los identificadores que devuelve la función LOOKUPVALUE.Only rows where the SalesTerritoryKey value is in the IDs that the LOOKUPVALUE function returns are displayed.

  6. En la tabla DimUserSecurity, en la columna Filtro DAX, agregue la siguiente fórmula:For the DimUserSecurity table, in the DAX Filter column, add the following formula:

        =FALSE()
    

    Esta fórmula especifica que todas las columnas se resuelven en false, lo que significa que no se pueden consultar las columnas de la tabla DimUserSecurity.This formula specifies that all columns resolve to false; meaning DimUserSecurity table columns can't be queried.

Ahora tiene que procesar e implementar el modelo.Now you need to process and deploy the model. Para obtener más información, consulte Implementación.For more information, see Deploy.

Tarea 3: Agregar orígenes de datos de la puerta de enlace de datos localTask 3: Add Data Sources within your On-premises data gateway

Una vez que el modelo tabular está implementado y listo para su uso, debe agregar una conexión de origen de datos al servidor tabular de Analysis Services local.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. Para permitir que el servicio Power BI acceda a su servicio de análisis local, es preciso disponer de una puerta de enlace de datos local instalada y configurada en el entorno.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. 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. Para más información, consulte Administrar el origen de datos: Analysis Services.For more information, see Manage your data source - Analysis Services.

    Creación de una conexión de origen de datos

Una vez concluido el procedimiento, la puerta de enlace estará configurada y lista para interactuar con el origen de datos local de Analysis Services.With this procedure complete, the gateway is configured and ready to 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: Create report based on analysis services tabular model using Power BI desktop

  1. Inicie Power BI Desktop y seleccione Obtener datos > Base de datos.Start Power BI Desktop and select Get Data > Database.

  2. En la lista de orígenes de datos, seleccione Base de datos de SQL Server Analysis Services y seleccione Conectar.From the data sources list, select the SQL Server Analysis Services Database and select Connect.

    Conexión a la base de datos de SQL Server Analysis Services

  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. Después, seleccione Aceptar.Then select OK.

    Detalles de Analysis Services

    Con Power BI, la seguridad dinámica solo funciona con una conexión dinámica.With Power BI, dynamic security works only with a live connection.

  4. Verá que el modelo implementado está en la instancia de Analysis Services.You can see that the deployed model is in the Analysis Services instance. Seleccione el modelo correspondiente y seleccione Aceptar.Select the respective model and then select OK.

    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.

  5. En el panel Campos, seleccione la medida SalesAmount de la tabla FactInternetSales y la dimensión SalesTerritoryRegion de la tabla SalesTerritory.In the Fields pane, select the SalesAmount measure from the FactInternetSales table and the SalesTerritoryRegion dimension from the SalesTerritory table.

  6. Para simplificar este informe, no agregaremos más columnas ahora mismo.To keep this report simple, we won't add any more columns right now. Para tener una representación más significativa de los datos, cambiamos la visualización a Gráfico de anillos.To have a more meaningful data representation, change the visualization to Donut chart.

    Visualización del gráfico de anillos

  7. 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 de opciones 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: Create and share a dashboard

Ha creado el informe y lo ha publicado en el servicio Power BI.You've created the report and published it to the Power BI service. Ahora puede usar el ejemplo creado en los pasos anteriores para demostrar el escenario de seguridad del modelo.Now you can use the example created in previous steps to demonstrate the model security scenario.

En el rol Director de ventas, la usuaria Gracia puede ver los datos de todas las regiones de ventas.In the role as Sales Manager, the user Grace can see data from all the different sales regions. Gracia crea este informe y lo publica en el servicio Power BI.Grace creates this report and publishes it to the Power BI service. Este informe se ha creado en las tareas anteriores.This report was created in the previous tasks.

Una vez que Gracia publica el informe, el siguiente paso consiste en crear un panel en el servicio Power BI llamado TabularDynamicSec que se basa en ese informe.Once Grace publishes the report, the next step is to create a dashboard in the Power BI service called TabularDynamicSec based on that report. En la siguiente imagen, observe que Gracia puede ver los datos correspondientes a todas las regiones de ventas.In the following image, notice that Grace can see the data corresponding to all the sales region.

Panel de servicio Power BI

Ahora Gracia comparte el panel con su compañera, Rita, responsable de ventas de la región de Australia.Now Grace shares the dashboard with a colleague, Rita, who is responsible for the Australia region sales.

Uso compartido de un panel de Power BI

Cuando Rita inicia sesión en el servicio Power BI y ve el panel compartido que ha creado Gracia, solo deberían ser visibles las ventas de la región de 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.

¡Enhorabuena!Congratulations! El servicio Power BI muestra la seguridad de nivel de fila dinámica definida en el modelo tabular de Analysis Services local.The Power BI service shows the dynamic row-level security defined in the on-premises Analysis Services tabular model. 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 lo que sucede en segundo planoTask 6: Understand what happens behind the scenes

En esta tarea se supone que está familiarizado con SQL Server Profiler, ya que tiene que capturar un seguimiento de SQL Server Profiler en la instancia tabular de SSAS local.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 sesión se inicializa en cuanto la usuaria (Rita) accede al panel en el servicio Power BI.The session gets initialized as soon as the user, Rita, accesses the dashboard in the Power BI service. Puede ver que el rol salesterritoryusers tiene efecto inmediato con el nombre de usuario vigente como rita@contoso.com .You 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>

Según la solicitud de nombre de usuario vigente, Analysis Services convierte la solicitud a la credencial real de contoso\rita después de consultar Active Directory local.Based on the effective user name request, Analysis Services converts the request to the actual contoso\rita credential after querying the local Active Directory. Una vez que Analysis Services obtiene la credencial, Analysis Services devuelve los datos para los que el usuario tiene permiso de visualización y acceso.Once Analysis Services gets the credential, Analysis Services returns the data the user has permission to view and access.

Si se produce más actividad con el panel, 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, with SQL Profiler you would see a specific query coming back to the Analysis Services tabular model as a DAX query. Por ejemplo, si Rita va del panel al informe subyacente, se produce la siguiente consulta.For example, if Rita goes from the dashboard to the underlying report, the following query occurs.

La consulta DAX vuelve al modelo de Analysis Services.

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

ConsideracionesConsiderations

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

  • Cualquier cambio efectuado en los datos después de procesar el modelo estaría inmediatamente disponible para los usuarios que accedieran al informe con 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 accessing the report with live connection from the Power BI service.