Uso de particiones de tabla activa y inactiva para optimizar modelos de datos de Power BI muy grandes

En este artículo se describe cómo usar particiones de tabla activas y inactivas para optimizar modelos de datos muy grandes. Las particiones proporcionan una manera de dividir los datos de una tabla en subconjuntos discretos. Las particiones no se exponen directamente en las herramientas estándar de modelado de datos de Power BI, pero puede aprovechar los métodos avanzados de creación de particiones mediante la configuración de una directiva de actualización incremental en Power BI Desktop. La actualización incremental se basa en particiones, como se explica en Actualización incremental y datos en tiempo real para conjuntos de datos. Sin embargo, la configuración de particiones de tabla activas y inactivas va más allá de lo que puede lograr una directiva de actualización incremental y supone que está familiarizado con los esquemas típicos de creación de particiones de tablas y las herramientas basadas en XMLA.

Requisitos previos

Debido a la complejidad relativa de esta técnica de creación de particiones, es más adecuado para usuarios avanzados con experiencia en las siguientes áreas:

1. Descripción de los conceptos de creación de particiones de tablas, cómo funcionan las particiones del modo de importación, el modo DirectQuery y el modo dual .

2. Conocimientos sobre cómo crear tablas híbridas mediante herramientas basadas en XMLA. Las tablas híbridas usan una o varias particiones en modo de importación y una partición de DirectQuery .

3. Conocimiento de los requisitos de las funciones DAX que puede usar para especificar .DataCoverageDefinition Se trata de una nueva propiedad para las particiones de DirectQuery para describir qué datos contiene la partición de DirectQuery de una tabla híbrida para que el motor de Power BI pueda excluir esta partición del procesamiento de consultas cuando corresponda. La exclusión de la partición DirectQuery puede ayudar a evitar consultas innecesarias del origen de datos y mejorar el rendimiento del procesamiento de consultas DAX.

4. Descripción de la diferencia entre las relaciones de tabla normales y limitadas. Por ejemplo, la función RELATED es útil si desea definir la cobertura de datos de una partición de tabla de hechos basada en los valores de una tabla de dimensiones de fecha relacionada. Tenga en cuenta que la partición de tabla de hechos es una partición directQuery con posibilidad de una relación limitada con la tabla de fechas en la que la función RELATED no puede capturar valores. En este escenario, RELATED solo funciona si la tabla de dimensiones de fecha es una tabla dual. La tabla de fechas debe estar en modo DirectQuery o Dual . No puede ser una importación pura.

Tenga en cuenta que una definición DataCoverageDefinition incorrecta podría provocar resultados incorrectos porque Power BI podría excluir incorrectamente la partición directQuery del procesamiento de consultas. Por lo tanto, asegúrese de comparar los resultados con y sin para DataCoverageDefinition asegurarse de que se suman.

Cuándo usar particiones de tabla activas y inactivas

Este es un ejemplo en el que las particiones activas y inactivas pueden ayudar a ajustar una tabla híbrida para el análisis histórico. Supongamos que tiene un origen de datos muy grande, acumulado durante muchos años. El uso principal es analizar los datos más recientes de los últimos dos años. En ocasiones, también quiere analizar los datos más antiguos. Quizás haya observado un aumento de ventas afilado reciente año a año. ¿Eso pasó antes? ¿Es el pico de ventas más alto desde el principio del seguimiento de ventas?

Sin compatibilidad con particiones activas y inactivas, este tipo de análisis histórico requeriría importar todos los datos históricos junto con los datos más recientes en la tabla de hechos. En el mejor de los casos, se trata de un uso ineficaz de los recursos, ya que el análisis principal ni siquiera usa ninguno de los datos históricos más antiguos. En el peor de los casos, el volumen de datos es tan grande que ni siquiera se puede importar en su totalidad. Tiene que cambiar el modelo de datos al modo DirectQuery y aceptar una penalización de rendimiento en comparación con el modo de importación, o bien puede crear modelos independientes y forzar a los usuarios a cambiar entre informes. Una tabla híbrida con particiones activas y inactivas ofrece una mejor opción.

Uso de particiones de tablas activas y inactivas

En primer lugar, configure la tabla sales con una partición de modo de importación activa para los datos más recientes y mantenga los datos más antiguos en una partición de DirectQueryen frío, como se muestra en el diagrama siguiente para la tabla FactInternetSales de un modelo de datos de ejemplo adventureWorks. Las filas con orderDateKey mayores o iguales que 20200101 se importan en el modelo de datos a través de la partición en modo de importación activa. Las filas con orderDateKey inferiores a 20200101 se tratan a través de la partición de DirectQuery en frío. Ahora, Power BI puede entregar rápidamente los casos de uso principales con el modo de importación y no es necesario importar grandes volúmenes de datos históricos que solo analice ocasionalmente porque la partición directQuery tiene esto cubierto.

Si tiene un almacenamiento de datos de ejemplo de AdventureWorks y desea seguirlo, estos son los pasos generales:

1. Cree el conjunto de datos. Use Power BI Desktop para crear un conjunto de datos y un informe de AdventureWorks. Incluya todas las tablas en modo DirectQuery puro. A continuación, convierta todas las tablas excepto la FactInternetSales tabla en modo Dual . Deje la FactInternetSales tabla en modo DirectQuery .

2. Cargue el conjunto de datos. Use un área de trabajo hospedada en Power BI Premium con el punto de conexión XMLA habilitado para las operaciones de escritura.

3. Actualice el nivel de compatibilidad. Abra el área de trabajo con el conjunto de datos adventureWorks en SQL Server Management Studio (SSMS). Haga clic con el botón derecho en labase de datos script> de script> adventureWorks como crear o reemplazar y seleccione nueva ventana del editor de consultas. Establezca la propiedad compatibilityLevel en 1603 (o superior). Seleccione Ejecutar o presione F5. Compruebe que la operación finaliza correctamente.

   

4. Configure las particiones de tabla FactInternetSales. Haga clic con el botón derecho en labase de datos script> de script> adventureWorks como crear o reemplazar y seleccione nueva ventana del editor de consultas. Reemplace toda la sección de particiones por la sección siguiente. Asegúrese de actualizar las líneas de Sql.Database para que apunten a la base de datos AdventureWorksDW en su entorno. Seleccione Ejecutar o presione F5. Compruebe que la operación finaliza correctamente.

      "partitions": [ 
       { 
         "name": "FactInternetSales-DQ-Partition", 
         "mode": "directQuery", 
         "dataView": "full", 
         "source": { 
           "type": "m", 
           "expression": [ 
             "let", 
             "    Source = Sql.Database(\"demo.database.windows.net\", \"AdventureWorksDW\"),", 
             "    dbo_FactInternetSales = Source{[Schema=\"dbo\",Item=\"FactInternetSales\"]}[Data],", 
             "    #\"Filtered Rows\" = Table.SelectRows(dbo_FactInternetSales, each [OrderDateKey] < 20200101)", 
             "in", 
             "    #\"Filtered Rows\"" 
           ] 
         } 
       }, 
       { 
         "name": "FactInternetSales-Import-Partition", 
         "mode": "import", 
         "source": { 
           "type": "m", 
           "expression": [ 
             "let", 
             "    Source = Sql.Database(\"demo.database.windows.net\", \"AdventureWorksDW\"),", 
             "    dbo_FactInternetSales = Source{[Schema=\"dbo\",Item=\"FactInternetSales\"]}[Data],", 
             "    #\"Filtered Rows\" = Table.SelectRows(dbo_FactInternetSales, each [OrderDateKey] >= 20200101)", 
             "in", 
             "    #\"Filtered Rows\"" 
           ] 
         } 
       } 
     ],    
   

5. Procese el modelo de datos. En el portal de Power BI, abra el área de trabajo con el conjunto de datos AdventureWorks y realice una actualización a petición del conjunto de datos para cargar la partición de importación con datos.

6. Compruebe que los informes muestran datos recientes e históricos. Abra adventureWorks y compruebe que el informe puede mostrar los resultados de las transacciones de ventas antes y después del 1 de enero de 2020, como en la captura de pantalla siguiente.

Definición de la cobertura de datos de la partición de DirectQuery

La solución funciona perfectamente sobre los datos recientes e históricos. Sin embargo, de forma predeterminada, Power BI consulta todas las particiones de tabla, ya que no sabe qué datos cubre cada partición. Por lo tanto, Power BI sigue consultando la partición directQuery incluso durante esos años que la partición de DirectQuery no cubre. Los datos de ventas están disponibles fácilmente en la partición de importación y la partición directQuery no contribuye a ninguna fila, pero esta consulta de origen superflua todavía puede provocar una carga notable en el origen de datos y contribuir a retrasos en el procesamiento de consultas DAX. Para evitar esta consulta de origen superflua, use .DataCoverageDefinition

Como se muestra en la captura de pantalla siguiente, el informe de Power BI sigue enviando varias consultas SQL innecesarias para 2020 al origen de datos, ya que la consulta DAX de cada objeto visual hace que Power BI consulte la partición directQuery .

Al establecer la dataCoverageDefinition propiedad en la partición directQuery como en el siguiente fragmento de código TMSL, se evitan estas consultas SQL. Sin embargo, tenga en cuenta que debe actualizar el conjunto de datos después de aplicar o cambiar una definición de cobertura de datos. Un proceso recalcular es suficiente para evaluar la definición de cobertura de datos. Si olvida este paso, las consultas que tocan la partición producen un error con un mensaje de error que indica "DataCoverageDefinition de la partición DQ en la tabla "[Nombre de tabla]" aún no se calcula después de un cambio reciente. Debe volver a procesarse".

        { 
          "name": "FactInternetSales-DQ-Partition", 
          "mode": "directQuery", 
          "dataView": "full", 
          "source": { 
            "type": "m", 
            "expression": [ 
              "let", 
              "    Source = Sql.Database(\"demopm.database.windows.net\", \"AdventureWorksDW2020\"),", 
              "    dbo_FactInternetSales = Source{[Schema=\"dbo\",Item=\"FactInternetSales\"]}[Data],", 
              "    #\"Filtered Rows\" = Table.SelectRows(dbo_FactInternetSales, each [OrderDateKey] < 20200101)", 
              "in", 
              "    #\"Filtered Rows\"" 
            ] 
          },  
"dataCoverageDefinition": {  
                  "description": "DQ partition with all sales from 2017, 2018, and 2019.",  
                  "expression": "RELATED('DimDate'[CalendarYear]) IN {2017,2018,2019}"  
                }  
        } 

Como se mencionó anteriormente, la dataCoverageDefinition propiedad ayuda a eliminar la carga innecesaria del origen de datos. También mejora el rendimiento del análisis de los datos recientes porque ahora Power BI puede excluir la partición directQuery del procesamiento de consultas DAX cuando corresponda. Puede definir expresiones de cobertura de datos sencillas para valores únicos, así como intervalos con operadores AND, OR y NOT simples. También puede usar la función RELATED para definir la cobertura de datos en función de una columna de una tabla de dimensiones que tenga una relación regular con la tabla de hechos. Si una expresión de cobertura de datos usa columnas de una tabla de dimensiones, asegúrese de que la tabla de dimensiones está en modo dual . También puede definir la cobertura de datos en función de las columnas de la propia tabla de hechos. Consulte la tabla siguiente para ver las operaciones admitidas, clasificadas en tres grupos. 

La DataCoverageDefinition propiedad de las particiones de DirectQuery permite optimizar incluso los modelos de datos de Power BI más grandes basados en particiones activas en modo de importación y particiones inactivas en modo DirectQuery al evitar consultas innecesarias del origen de datos. Esta reducción de consultas de origen ayuda a aumentar el rendimiento del informe al analizar los datos activos. También ayuda a reducir la carga en el origen de datos y de esta manera ayuda a maximizar la escala del origen de datos. Sin embargo, tenga en cuenta que la optimización de un modelo de datos mediante el uso de la dataCoverageDefinition propiedad sigue siendo un escenario avanzado. Asegúrese de comprobar cuidadosamente los resultados.

Consideraciones y limitaciones

• Actualmente, la DataCoverageDefinition propiedad de las particiones de DirectQuery requiere valores estáticos, como RELATED('Date'[Year]) = 2020 o RELATED('Date'[Year]) IN {2020, 2021, 2022}. No se admiten asignaciones dinámicas, como RELATED('Date'[DateKey]) = TODAY().

• La actualización incremental con datos en tiempo real no aprovecha la DataCoverageDefinition propiedad . Si aplica una definición de cobertura de datos a una partición de DirectQuery (en tiempo real), la actualización incremental quita la definición de cobertura de datos al volver a crear la partición.