Actualización incremental avanzada y datos en tiempo real con el punto de conexión XMLA

Los modelos semánticos en una capacidad Premium con el punto de conexión XMLA habilitado para operaciones de lectura/escritura permiten actualizaciones más avanzadas, gestión de particiones y implementaciones solo de metadatos gracias al soporte de herramientas, scripts y API. Además, las operaciones de actualización a través del punto de conexión XMLA no se limitan a 48 actualizaciones al día y no se impone el límite de tiempo de actualización programado .

Partitions

Las particiones de tabla del modelo semántico no son visibles y no se pueden administrar mediante Power BI Desktop o el servicio Power BI. Para modelos en un área de trabajo asignada a una capacidad Premium, las particiones se pueden administrar a través del XMLA endpoint. Puede usar herramientas como SQL Server Management Studio (SSMS) o el Editor tabular de código abierto para administrar particiones mediante scripting con lenguaje de scripting de modelos tabulares (TMSL) y mediante programación con el modelo de objetos tabulares (TOM).

Cuando publica por primera vez un modelo en el servicio Power BI, cada tabla del nuevo modelo tiene una partición. En el caso de las tablas sin directiva de actualización incremental, esa partición contiene todas las filas de datos de esa tabla, a menos que se apliquen filtros. En el caso de las tablas con una directiva de actualización incremental, esa partición inicial solo existe porque Power BI aún no ha aplicado la directiva. La partición inicial se configura en Power BI Desktop al definir el filtro de intervalo de fecha y hora de la tabla en función de los parámetros RangeStart y RangeEnd y de cualquier otro filtro aplicado en el Editor de Power Query. Esta partición inicial contiene solo las filas de datos que cumplen los criterios de filtro.

Al realizar la primera operación de actualización, las tablas sin directiva de actualización incremental actualizan todas las filas contenidas en la partición única predeterminada de esa tabla. En el caso de las tablas con una directiva de actualización incremental, se crean automáticamente las particiones históricas y las filas se cargan en ellas según la fecha y hora de cada fila. Si la directiva de actualización incremental incluye la obtención de datos en tiempo real, Power BI también agrega una partición directQuery a la tabla.

Esta primera operación de actualización puede tardar bastante tiempo en función de la cantidad de datos que se deben cargar desde el origen de datos. La complejidad del modelo también puede ser un factor significativo, ya que las operaciones de actualización deben realizar más procesamiento y recálculo. Esta operación puede ser inicializada de forma autónoma. Para obtener más información, consulte Impedir tiempos de espera en la actualización completa inicial.

Las particiones se crean y se denominan por granularidad de período: años, trimestres, meses y días. Las particiones más recientes, las particiones de actualización, contienen filas dentro del período de actualización que especifique en la política. Las particiones históricas contienen filas por período completo hasta el período de actualización. Si está habilitado en tiempo real, una partición de DirectQuery recoge los cambios de datos que se produjeron después de la fecha de finalización del período de actualización. La granularidad de las particiones históricas y de actualización depende de los intervalos de tiempo de actualización e históricos (de almacenamiento) que elija cuando se define la política.

Por ejemplo, si la fecha de hoy es el 2 de febrero de 2021 y nuestra tabla FactInternetSales en el origen de datos contiene filas hasta hoy, si nuestra directiva especifica incluir cambios en tiempo real, actualizar filas en el último período de actualización de un día y almacenar filas en el período histórico de los últimos tres años. A continuación, con la primera operación de actualización, se crea una partición directQuery para los cambios en el futuro. Se crea una nueva partición de importación para las filas actuales y se crea una partición histórica para ayer, un período de día completo, el 1 de febrero de 2021. Se crea una partición histórica para el período de mes completo anterior (enero de 2021). Se crea una partición histórica para el período de año completo anterior (2020). Y se crean particiones históricas para los períodos completos del año 2019 y 2018. No se crean particiones de trimestre entero porque el 2 de febrero, el primer trimestre completo de 2021 aún no se ha completado.

Con cada operación de actualización, solo se actualizan las particiones del período de actualización. El filtro de fecha de la partición directQuery se actualiza para incluir solo los cambios que se producen después del período de actualización actual. Se crea una nueva partición de actualización para las nuevas filas con una nueva fecha y hora dentro del período de actualización actualizado. Las filas existentes con una fecha y hora ya dentro de las particiones existentes en el período de actualización se actualizan con nuevos datos. Las filas con una fecha y hora anteriores al período de actualización ya no se actualizan.

A medida que se cierran períodos completos, las particiones se fusionan. Por ejemplo, si se especifica un período de actualización de un día y un período de almacenamiento histórico de tres años en la política, el primer día del mes, todas las particiones diarias del mes anterior se fusionan en una partición mensual. En el primer día de un nuevo trimestre, las tres particiones del mes anterior se combinan en una partición trimestral. En el primer día de un año nuevo, las cuatro particiones del trimestre anterior se combinan en una partición de año.

Un modelo siempre conserva las particiones para todo el período de almacenamiento histórico, además de las particiones completas del período hasta el período de actualización vigente. En el ejemplo, se conservan datos históricos de tres años completos en particiones de 2018, 2019, 2020, y también en particiones para el período mensual 2021Q101, el período diario 2021Q10201, y la partición del período de actualización del día actual. Dado que el ejemplo conserva los datos históricos durante tres años, la partición de 2018 se conserva hasta la primera actualización el 1 de enero de 2022.

Con la actualización incremental de Power BI y los datos en tiempo real, el servicio gestiona automáticamente la administración de particiones en función de la política. Aunque el servicio puede controlar toda la administración de particiones automáticamente, mediante herramientas a través del punto de conexión XMLA, puede actualizar de forma selectiva particiones de forma individual, secuencial o en paralelo.

Patrones comunes de actualización de particiones

Al trabajar con operaciones de punto de conexión XMLA, tenga en cuenta estos patrones comunes para administrar las operaciones de actualización:

• Actualizaciones pequeñas frecuentes: ejecute varias operaciones de actualización pequeñas y dirigidas durante el horario comercial mediante comandos de partición XMLA o la API REST mejorada para mantener los datos recientes actualizados sin procesar toda la tabla.
• Reposición histórica selectiva: realice actualizaciones de particiones históricas más grandes o correcciones de datos puntuales fuera del horario laboral con TMSL con applyRefreshPolicy: false para reconstruir períodos históricos específicos sin afectar al comportamiento automático de la política.
• Cargas iniciales almacenadas provisionalmente: para períodos históricos grandes, divida la actualización inicial en lotes más pequeños mediante el procesamiento de particiones incrementalmente para evitar tiempos de espera y administrar el consumo de recursos.

Estos patrones permiten equilibrar la actualización de datos en tiempo real con restricciones de recursos y rendimiento del sistema.

Administración de actualizaciones con SQL Server Management Studio

SQL Server Management Studio (SSMS) se puede usar para ver y administrar particiones creadas por la aplicación de directivas de actualización incremental. Mediante el uso de SSMS, puede, por ejemplo, actualizar una partición histórica específica no en el período de actualización incremental para realizar una actualización con fecha posterior sin tener que actualizar todos los datos históricos. SSMS también se puede usar al arrancar para cargar datos históricos para modelos grandes mediante la adición o actualización incremental de particiones históricas en lotes.

Invalidar el comportamiento de actualización incremental

Con SSMS, también tiene más control sobre cómo invocar actualizaciones mediante el lenguaje de scripting de modelos tabulares y el modelo de objetos tabulares. Por ejemplo, en SSMS, en el Explorador de objetos, haga clic con el botón derecho en una tabla y, a continuación, seleccione la opción de menú Procesar tabla y, a continuación, seleccione el botón Script para generar un comando tmSL refresh.

Estos parámetros se pueden usar con el comando tmSL refresh para invalidar el comportamiento de actualización incremental predeterminado:

• applyRefreshPolicy. Si una tabla tiene definida una directiva de actualización incremental, applyRefreshPolicy determina si la directiva se aplica o no. Si no se aplica la directiva, una operación completa del proceso deja sin cambios las definiciones de partición y todas las particiones de la tabla se actualizan por completo. El valor predeterminado es true.

• effectiveDate. Si se aplica una directiva de actualización incremental, debe conocer la fecha actual para determinar los intervalos de ventana graduales para los períodos de actualización incremental e históricos. El effectiveDate parámetro permite invalidar la fecha actual. Este parámetro es útil para probar, demostraciones y escenarios empresariales en los que los datos se actualizan incrementalmente hasta una fecha en el pasado o en el futuro, por ejemplo, presupuestos en el futuro. El valor predeterminado es la fecha actual.

{ 
  "refresh": {
    "type": "full",

    "applyRefreshPolicy": true,
    "effectiveDate": "12/31/2013",

    "objects": [
      {
        "database": "IR_AdventureWorks", 
        "table": "FactInternetSales" 
      }
    ]
  }
}

Para más información sobre cómo invalidar el comportamiento de actualización incremental predeterminado con TMSL, consulte Comando Actualizar.

Administración de directivas con el Editor tabular

Además de SSMS, puede usar el Editor tabular para crear y modificar directivas de actualización incremental directamente en modelos semánticos a través del punto de conexión XMLA. Este método permite ajustar la configuración de directiva( como períodos de actualización, períodos históricos y expresiones de origen) sin necesidad de volver a publicar el modelo desde Power BI Desktop. El Editor tabular también se puede usar para aplicar directivas de actualización a las tablas existentes y para administrar expresiones de parámetros RangeStart y RangeEnd. Para obtener más información, vea Actualización incremental en la documentación del Editor tabular.

Actualización de la orquestación y automatización

Además de usar SSMS, TMSL y TOM para administrar las actualizaciones a través del punto de conexión XMLA. También puede orquestar operaciones de actualización de modelos semánticos mediante la API REST de Power BI. La API de actualización mejorada proporciona más funcionalidades, como la actualización de nivel de tabla y de nivel de partición, la lógica de reintento, la cancelación y la administración de tiempo de espera personalizada. Este enfoque es útil para integrar las operaciones de actualización en flujos de trabajo automatizados y canalizaciones de CI/CD. Para obtener instrucciones detalladas, consulte Actualización mejorada con la API REST de Power BI.

Garantizar un rendimiento óptimo

Con cada operación de actualización, el servicio Power BI puede enviar consultas de inicialización al origen de datos para cada partición de actualización incremental. Es posible que pueda mejorar el rendimiento de la actualización incremental mediante la reducción del número de consultas de inicialización al garantizar la siguiente configuración:

• La tabla para la que configura la actualización incremental debe obtener datos de un único origen de datos. Si la tabla obtiene datos de más de un origen de datos, el número de consultas enviadas por el servicio para cada operación de actualización se multiplica por el número de orígenes de datos, lo que podría reducir el rendimiento de la actualización. Asegúrese de que la consulta de la tabla de actualización incremental es para un único origen de datos.
• Para soluciones con actualización incremental de particiones de importación y datos en tiempo real con Direct Query, todas las particiones deben consultar datos desde un único origen de datos.
• Si los requisitos de seguridad permiten, establezca la opción Nivel de privacidad del origen de datos en Organización o Pública. De forma predeterminada, el nivel de privacidad es Privado. Sin embargo, este nivel puede impedir que los datos se intercambie con otros orígenes de nube. Para establecer el nivel de privacidad, seleccione el menú Más opciones y, a continuación, elija Configuración>Credenciales de origen de datos>Editar credenciales>Configuración del nivel de privacidad para este origen de datos. Si el nivel de privacidad se establece en el modelo de Power BI Desktop antes de publicarlo en el servicio, no se transfiere al servicio al publicar. Todavía debes configurarlo en la configuración del modelo semántico en el servicio. Para más información, consulte Niveles de privacidad.
• Si usa una puerta de enlace de datos local, asegúrese de que usa la versión 3000.77.3 o posterior.

Impedir tiempos de espera en la actualización completa inicial

Después de publicar en el servicio Power BI, la operación de actualización completa inicial para el modelo crea particiones para la tabla de actualización incremental, carga y procesa los datos históricos durante todo el período definido en la directiva de actualización incremental. En algunos modelos que cargan y procesan grandes cantidades de datos, la cantidad de tiempo que tarda la operación de actualización inicial puede superar el límite de tiempo de actualización impuesto por el servicio o un límite de tiempo de consulta impuesto por el origen de datos.

El arranque de la operación de actualización inicial permite al servicio crear objetos de partición para la tabla de actualización incremental, pero no cargar y procesar datos históricos en ninguna de las particiones. A continuación, SSMS se usa para procesar particiones de forma selectiva. Dependiendo de la cantidad de datos que se van a cargar para cada partición, puede procesar cada partición secuencialmente o en lotes pequeños. Este método reduce la posibilidad de que una o varias de esas particiones provoquen un tiempo de espera. Los métodos siguientes funcionan para cualquier origen de datos.

Aplicar directiva de actualización

La herramienta Tabular Editor 2 de código abierto proporciona una manera sencilla de arrancar una operación de actualización inicial. Después de publicar un modelo con una directiva de actualización incremental definida para él desde Power BI Desktop al servicio, conéctese al modelo mediante el punto de conexión XMLA en modo de lectura y escritura. Ejecute Aplicar directiva de actualización en la tabla de actualización incremental. Con solo la directiva aplicada, se crean particiones, pero no se cargan datos en ellas. A continuación, conéctese con SSMS para actualizar las particiones secuencialmente o en lotes para cargar y procesar los datos. Para obtener más información, vea Actualización incremental en la documentación del editor tabular.

Filtro de Power Query para particiones vacías

Antes de publicar el modelo al servicio, en el Editor de Power Query agregue otro filtro a la ProductKey columna que excluya cualquier valor distinto de 0, lo que resulta en una eliminación eficiente de todos los datos de la tabla FactInternetSales.

Después de seleccionar Cerrar y aplicar en el Editor de Power Query, definir la directiva de actualización incremental y guardar el modelo, el modelo se publica en el servicio. Desde el servicio, la operación de actualización inicial se ejecuta en el modelo. Las particiones de la tabla FactInternetSales se crean según la directiva, pero no se cargan ni procesan datos porque se filtran todos los datos.

Una vez completada la operación de actualización inicial, de nuevo en el Editor de Power Query, se quita el otro filtro de la ProductKey columna. Después de seleccionar Cerrar y aplicar en el Editor de Power Query y guardar el modelo, el modelo no se vuelve a publicar. Si el modelo se vuelve a publicar, sobrescribe la configuración de la directiva de actualización incremental y fuerza una actualización completa en el modelo cuando se realiza una operación de actualización posterior desde el servicio. En su lugar, realice una implementación solo de metadatos mediante el Application Lifecycle Management (ALM) Toolkit que quite el filtro de la ProductKey columna del modelo. A continuación, SSMS se puede usar para procesar particiones de forma selectiva. Cuando todas las particiones se procesan por completo, lo cual debe incluir un recálculo del proceso en todas las particiones, desde SSMS, las operaciones de actualización posteriores en el modelo desde el servicio solo actualizan las particiones de refresco incremental.

Para obtener más información sobre el procesamiento de tablas y particiones desde SSMS, consulte Procesar base de datos, tabla o particiones (Analysis Services). Para obtener más información sobre el procesamiento de modelos, tablas y particiones mediante TMSL, consulte Refresh command (TMSL).

Consultas personalizadas para detectar cambios de datos

TMSL y TOM se pueden usar para invalidar el comportamiento de los cambios de datos detectados. Este método se puede usar para evitar conservar la columna de última actualización en la caché de memoria. También puede habilitar escenarios en los que los procesos de extracción, transformación y carga (ETL) preparan una tabla de configuración o de instrucción. Le permite marcar solo las particiones que deben actualizarse. Este método puede crear un proceso de actualización incremental más eficaz en el que solo se actualizan los períodos necesarios, independientemente de cuánto tiempo se realicen las actualizaciones de datos.

pollingExpression está diseñado para ser una expresión M ligera o un nombre de otra consulta M. Debe devolver un valor escalar y se ejecuta para cada partición. Si el valor devuelto es diferente a lo que fue la última vez que se produjo una actualización incremental, la partición se marca para el procesamiento completo.

El siguiente ejemplo cubre los 120 meses del período histórico para los cambios con fecha atrasada. Especificar 120 meses en lugar de 10 años significa que la compresión de datos podría no ser tan eficaz. Sin embargo, evita tener que actualizar un año histórico completo, lo que sería más caro cuando un mes sería suficiente para un cambio retroactivo.

"refreshPolicy": {
    "policyType": "basic",
    "rollingWindowGranularity": "month",
    "rollingWindowPeriods": 120,
    "incrementalGranularity": "month",
    "incrementalPeriods": 120,
    "pollingExpression": "<M expression or name of custom polling query>",
    "sourceExpression": [
    "let ..."
    ]
}

Implementación solo de metadatos

Al publicar una nueva versión de un archivo .pbix de Power BI Desktop en un área de trabajo. Verá el siguiente mensaje para reemplazar el modelo existente, si ya existe un modelo con el mismo nombre.

En algunos casos, es posible que no desee reemplazar el modelo, especialmente por la actualización incremental. El modelo de Power BI Desktop podría ser considerablemente menor que el del servicio Power BI. Si el modelo del servicio Power BI tiene aplicada una directiva de actualización incremental, es posible que pierda varios años de datos históricos si se reemplaza el modelo. La actualización de todos los datos históricos podría tardar horas y provocar un tiempo de inactividad del sistema para los usuarios.

En su lugar, es mejor realizar una implementación solo de metadatos, lo que permite la implementación de nuevos objetos sin perder los datos históricos. Por ejemplo, si solo agrega algunas medidas, puede implementar solo las nuevas medidas sin necesidad de actualizar los datos, lo que ahorra tiempo.

En el caso de las áreas de trabajo asignadas a una capacidad Premium configurada para lectura y escritura del punto de conexión XMLA, las herramientas compatibles habilitan solo la implementación de metadatos. Por ejemplo, ALM Toolkit es una herramienta de comparación de esquemas para los modelos de Power BI y se puede usar solo para realizar la implementación de metadatos.

Descargue e instale la versión más reciente del kit de herramientas de ALM desde el repositorio de Git de Analysis Services. La guía paso a paso sobre el uso del kit de herramientas de ALM no se incluye en la documentación de Microsoft. Los vínculos de documentación de ALM Toolkit e información sobre la compatibilidad están disponibles en la cinta de ayuda . Para realizar una implementación solo de metadatos, realice una comparación y seleccione la instancia de Power BI Desktop en ejecución como origen y el modelo existente en el servicio Power BI como destino. Tenga en cuenta las diferencias mostradas y omita la actualización de la tabla con particiones de actualización incremental o use el cuadro de diálogo Opciones para conservar las particiones de las actualizaciones de la tabla. Valide la selección para garantizar la integridad del modelo de destino y, a continuación, actualice.

Adición de una directiva de actualización incremental y datos en tiempo real mediante programación

También puede usar TMSL y TOM para agregar una directiva de actualización incremental a un modelo existente a través del punto de conexión XMLA.

El proceso incluye los pasos siguientes:

1. Asegúrese de que el modelo de destino tiene el nivel de compatibilidad mínimo necesario. En SSMS, haga clic con el botón derecho en el [nombre del modelo]>Nivel decompatibilidad>. Para aumentar el nivel de compatibilidad, use un script TMSL createOrReplace o compruebe el siguiente código de ejemplo de TOM para ver un ejemplo.

   a. Import policy - 1550
   b. Hybrid policy - 1565
   

2. Agregue los RangeStart parámetros y RangeEnd a las expresiones de modelo. Si es necesario, agregue también una función para convertir valores de fecha y hora en claves de fecha.

3. Defina un RefreshPolicy objeto con el archivado deseado (ventana deslizante) y los períodos de actualización incremental, y una expresión de origen que filtre la tabla de destino en función de RangeStart y RangeEnd. Establezca el modo de directiva de actualización en Importar o Híbrido según sus requisitos de datos en tiempo real. El híbrido hace que Power BI agregue una partición de DirectQuery a la tabla para capturar los cambios más recientes del origen de datos que se produjo después de la última hora de actualización.

4. Agregue la directiva de actualización a la tabla y realice una actualización completa para que Power BI particione la tabla según sus requisitos.

En el ejemplo de código siguiente se muestra cómo realizar los pasos anteriores mediante TOM. Si desea usar este ejemplo tal como está, debe tener una copia para la base de datos AdventureWorksDW e importar la tabla FactInternetSales en un modelo. En el ejemplo de código se supone que los parámetros RangeStart y la función RangeEnd no existen en el modelo. Solo tiene que importar la tabla FactInternetSales y publicar el modelo en un área de trabajo en Power BI Premium. A continuación, actualice el workspaceUrl para que el ejemplo de código pueda conectarse a su modelo. Actualice más líneas de código según sea necesario.

using System;
using TOM = Microsoft.AnalysisServices.Tabular;
namespace Hybrid_Tables
{
    class Program
    {
        static string workspaceUrl = "<Enter your Workspace URL here>";
        static string databaseName = "AdventureWorks";
        static string tableName = "FactInternetSales";
        static void Main(string[] args)
        {
            using (var server = new TOM.Server())
            {
                // Connect to the dataset.
                server.Connect(workspaceUrl);
                TOM.Database database = server.Databases.FindByName(databaseName);
                if (database == null)
                {
                    throw new ApplicationException("Database cannot be found!");
                }
                if(database.CompatibilityLevel < 1565)
                {
                    database.CompatibilityLevel = 1565;
                    database.Update();
                }
                TOM.Model model = database.Model;
                // Add RangeStart, RangeEnd, and DateKey function.
                model.Expressions.Add(new TOM.NamedExpression {
                    Name = "RangeStart",
                    Kind = TOM.ExpressionKind.M,
                    Expression = "#datetime(2021, 12, 30, 0, 0, 0) meta [IsParameterQuery=true, Type=\"DateTime\", IsParameterQueryRequired=true]"
                });
                model.Expressions.Add(new TOM.NamedExpression
                {
                    Name = "RangeEnd",
                    Kind = TOM.ExpressionKind.M,
                    Expression = "#datetime(2021, 12, 31, 0, 0, 0) meta [IsParameterQuery=true, Type=\"DateTime\", IsParameterQueryRequired=true]"
                });
                model.Expressions.Add(new TOM.NamedExpression
                {
                    Name = "DateKey",
                    Kind = TOM.ExpressionKind.M,
                    Expression =
                        "let\n" +
                        "    Source = (x as datetime) => Date.Year(x)*10000 + Date.Month(x)*100 + Date.Day(x)\n" +
                        "in\n" +
                        "    Source"
                });
                // Apply a RefreshPolicy with Real-Time to the target table.
                TOM.Table salesTable = model.Tables[tableName];
                TOM.RefreshPolicy hybridPolicy = new TOM.BasicRefreshPolicy
                {
                    Mode = TOM.RefreshPolicyMode.Hybrid,
                    IncrementalPeriodsOffset = -1,
                    RollingWindowPeriods = 1,
                    RollingWindowGranularity = TOM.RefreshGranularityType.Year,
                    IncrementalPeriods = 1,
                    IncrementalGranularity = TOM.RefreshGranularityType.Day,
                    SourceExpression =
                        "let\n" +
                        "    Source = Sql.Database(\"demopm.database.windows.net\", \"AdventureWorksDW\"),\n" +
                        "    dbo_FactInternetSales = Source{[Schema=\"dbo\",Item=\"FactInternetSales\"]}[Data],\n" +
                        "    #\"Filtered Rows\" = Table.SelectRows(dbo_FactInternetSales, each [OrderDateKey] >= DateKey(RangeStart) and [OrderDateKey] < DateKey(RangeEnd))\n" +
                        "in\n" +
                        "    #\"Filtered Rows\""
                };
                salesTable.RefreshPolicy = hybridPolicy;
                model.RequestRefresh(TOM.RefreshType.Full);
                model.SaveChanges();
            }
            Console.WriteLine("{0}{1}", Environment.NewLine, "Press [Enter] to exit...");
            Console.ReadLine();
        }
    }
}

Contenido relacionado

• Configuración de la actualización incremental y los datos en tiempo real
• Solución de problemas de actualización incremental y datos en tiempo real
• Actualización mejorada con la API REST de Power BI
• Particiones en modelos tabulares
• Herramientas externas en Power BI Desktop
• Configuración de actualización programada