Particiones en modelos tabulares

Artículo
05/08/2023

Se aplica a: SQL Server Analysis Services Azure Analysis Services Fabric/Power BI Premium

Las particiones separan los datos que necesita procesar (actualizar) con frecuencia de los datos que se pueden procesar con una frecuencia menor. Por ejemplo, una tabla de hechos puede incluir determinados conjuntos de filas que contienen datos que rara vez cambian, pero otros conjuntos de filas tienen datos que cambian con frecuencia. No es necesario procesar todos los datos cuando solo es necesario procesar una parte de ellos.

Las particiones funcionan dividiendo una tabla en objetos de partición lógica. Las particiones individuales (cada una contiene un segmento único de datos) se pueden procesar de forma incremental de forma secuencial o en paralelo, independientemente de otras particiones, o excluirse de las operaciones de procesamiento por completo.

Granularidad

De forma predeterminada, cada tabla de un modelo tiene una sola partición. En muchos casos, como con tablas de hechos, dividir la partición única de una tabla en varias particiones puede usar mejor los recursos disponibles para su procesamiento.

Una estrategia eficaz de diseño y procesamiento de modelos utiliza particiones para eliminar la carga innecesaria del procesador y el consumo de memoria, mientras que al mismo tiempo se asegura de que los datos se actualizan con frecuencia lo suficientemente para reflejar los datos más recientes de los orígenes de datos. Por ejemplo, un modelo tabular puede tener una tabla Sales que incluye datos de ventas para el año fiscal actual y cada uno de los años fiscales anteriores. La tabla Sales del modelo tiene las siguientes particiones:

Partition	Datos de
Sales2020	Año fiscal actual
Sales2019-2010	Años fiscales 2010, 2011, 2012, 2013, 2014, 2015. 2016, 2017, 2018, 2019
SalesOld	Todos los años fiscales anteriores a los diez últimos años.

A medida que se agregan nuevos datos de ventas para el año fiscal actual de 2020, esos datos se deben procesar diariamente para reflejarse con precisión en el análisis de datos de ventas del año fiscal actual, por lo que la partición Sales2020 se procesa cada noche.

No es necesario procesar datos en la partición Sales2019-2010 por la noche. Sin embargo, dado que los datos de ventas de los diez ejercicios anteriores todavía pueden cambiar debido a devoluciones de productos y otros ajustes, todavía se deben procesar periódicamente, por lo que los datos de la partición Sales2019-2010 se procesan mensualmente. Los datos de la partición SalesOld rara vez cambian, por lo tanto, solo se procesan anualmente.

Al escribir el año fiscal de 2021, se agrega una nueva partición Sales2021 a la tabla Sales del modelo. La partición Sales2020 se puede combinar con la partición Sales2019-2010 y cambiar el nombre a Sales2020-2011. Los datos del año fiscal 2010 se eliminan de la partición Sales2020-2011 y se mueven a la partición SalesOld. Por último, se procesarán todas las particiones para reflejar los cambios. Esto se conoce normalmente como un patrón de ventana gradual : los datos de cada partición se encuentran dentro de un intervalo de fechas predefinido y se incrementan según sea necesario, manteniendo la memoria y el uso de recursos de procesamiento dentro de un intervalo predecible a lo largo del tiempo.

La granularidad se ve afectada por varios factores, incluida la cantidad de datos necesarios para procesarse incrementalmente dentro de una cantidad aceptable de tiempo. Por ejemplo, si solo es necesario procesar diariamente el último día entero, puede ser beneficioso usar granularidad diaria. La granularidad mixta se puede configurar para escenarios como la actualización casi en tiempo real en intervalos bajos junto con particiones estáticas históricas y de mayor granularidad. Esto da como resultado menos particiones, pero también aumenta la sobrecarga de administración para asegurarse de que los intervalos de particiones se definen correctamente.

La creación de particiones también es eficaz para las tablas que contienen datos de más de un origen de datos. Los distintos orígenes de datos pueden actualizar los datos en momentos diferentes, lo que puede determinar diferentes requisitos de granularidad y procesamiento para los datos de tabla del modelo. Por ejemplo, una tabla Orders de un modelo contiene transacciones de pedidos de dos tablas de hechos diferentes, factInternetOrders y factRetailOrders. En el origen de datos, factInternetOrders se actualiza cada hora. factRetailOrders, por otro lado, se actualiza una vez al día después de que todas las tiendas comerciales estén cerradas. Al crear particiones independientes en diferentes granularidades en la tabla Pedidos del modelo para los datos importados de factInternetOrders y factRetailOrders, las operaciones de procesamiento en la tabla Orders se pueden separar y ejecutar más alineadas con los datos de pedido en los orígenes de datos.

Cada escenario es único. Asegúrese de definir una granularidad para el modelo de datos que divida de forma más eficaz los datos en particiones que se deben procesar a menudo en comparación con los que no lo hacen.

Particiones del centro y límites por partición

Independientemente de la plataforma, no hay ningún límite estricto en el número de objetos de partición de un modelo. Sin embargo, cada partición tiene al menos un segmento de datos con una superficie de memoria. Demasiadas particiones pequeñas pueden dar lugar a demasiados segmentos pequeños. El rendimiento de las consultas puede verse afectado negativamente cuando el motor de almacenamiento tiene que examinar un número excesivo de segmentos. La velocidad de las operaciones de metadatos en demasiadas particiones también puede afectar negativamente a los recursos de procesamiento.

Cree el número mínimo de particiones mientras sigue cumpliendo eficazmente los objetivos de creación de particiones. Es más importante centrarse en una estrategia de creación de particiones eficaz basada en la granularidad y procesar solo esas particiones con los datos cambiantes más relevantes dentro de los recursos de procesamiento y memoria disponibles en momentos en que las consultas de usuario son bajas.

Tampoco hay ningún límite en la cantidad de datos de una partición. Aunque es poco probable, un modelo podría tener una sola tabla con una sola partición predeterminada y esa tabla podría contener todos los datos del modelo. La cantidad de datos de la partición solo estaría limitada por los recursos de memoria disponibles para el plan de servicio o el hardware.

Creación y administración de particiones

Al crear modelos con el diseñador de modelos tabulares en Visual Studio, cree nuevas particiones, edite, combine o elimine particiones en la base de datos del área de trabajo del modelo mediante el Administrador de particiones. Según el nivel de compatibilidad del modelo que está creando, el Administrador de particiones proporciona dos modos para seleccionar los datos que se van a incluir en una partición: para los modelos tabulares 1400 y superiores con orígenes de datos estructurados, las particiones se definen mediante una expresión Power Query M. Por ejemplo, la consulta siguiente define una partición para el año natural de 2019:

let
    Source = #"SQL/sqlserver database windows net;Contoso",
    dbo_Sales = Source{[Schema="dbo",Item="Sales"]}[Data],
    #"Filtered Rows" = Table.SelectRows(dbo_Sales, each [OrderDateKey] >= 20190101 and [OrderDateKey] <= 20191231)
in
    #"Filtered Rows"

En el caso de los orígenes de datos del proveedor, las particiones se definen mediante una consulta SQL. Por ejemplo,

SELECT [dbo].[Sales].* FROM [dbo].[Sales]
WHERE (([OrderDateKey] >= '20190101') AND ([OrderDateKey] <= '20191231'))

Observe el argumento Filtered Rows en la expresión Power Query M y la cláusula WHERE de la instrucción SQL definen exactamente un año natural mediante operadores mayores que (>), menores que (<) e iguales (=). Al definir particiones, es importante que la consulta de cada partición defina un intervalo único de datos que no pueda provocar la duplicación de datos con otras particiones.

SQL Server Management Studio (SSMS)

Después de implementar el modelo, las particiones aparecen como objetos en SQL Server Management Studio (SSMS). Cree, edite, combine y elimine particiones para un modelo implementado mediante el cuadro de diálogo Particiones de SSMS, mediante la ejecución de un script de lenguaje de scripting de modelos tabulares (TMSL) o mediante programación mediante el modelo de objetos tabulares (TOM).

Tabular Model Scripting Language (TMSL)

Las particiones de un modelo se definen en el objeto Partitions. En el ejemplo siguiente, la partición Sales2019 se define como:

"partition": {
      "name": "Sales2019",
      "mode": "import",
      "source": {
        "type": "m",
        "expression": [
          "let",
          "    Source = #\"SQL/sqlserver database windows net;Contoso\",",
          "    dbo_Sales = Source{[Schema=\"dbo\",Item=\"Sales\"]}[Data],",
          "    #\"Filtered Rows\" = Table.SelectRows(dbo_Sales, each [OrderDateKey] >= 20190101 and [OrderDateKey] <= 20191231)",
          "in",
          "    #\"Filtered Rows\""
        ]
      },

Las acciones en el objeto Partitions se pueden especificar en los siguientes comandos TMSL:

Los scripts TMSL se pueden ejecutar en SQL Server Management Studio, con PowerShell ejecutando el comando Invoke-ASCmd o mediante una tarea de script de SQLServer Integration Services (SSIS).

En el caso de los modelos con niveles de compatibilidad 1100 y 1103, analysis Services Scripting Language (ASSL) se usa en su lugar si TMSL.

Modelo de objetos tabulares (TOM)

En el modelo de objetos tabulares, las particiones se definen mediante una clase Partition en el espacio de nombres Microsoft.AnalysisServices.Tabular. Para obtener más información sobre las soluciones mediante programación que usan TOM como API, consulte Creación de tablas, particiones y columnas (TOM) y estrategias de creación de particiones avanzadas más adelante en este artículo.

Para los modelos en niveles de compatibilidad 1100 y 1103, use Analysis Management Objects (AMO).

Procesar particiones

Cuando se particionan los datos de tabla, esas particiones se pueden procesar a la vez y la cadencia adecuadas para la solución. Cuando se ejecuta una operación de proceso (actualización), se realiza una conexión al origen de datos mediante la conexión del origen de datos. Analysis Services usa las consultas especificadas para cada partición para consultar el origen de datos. Los datos nuevos y actualizados se cargan en las tablas del modelo, las relaciones y las jerarquías se vuelven a generar y se vuelven a calcular las columnas calculadas.

Al crear modelos en Visual Studio, puede ejecutar manualmente operaciones de proceso en particiones de base de datos del área de trabajo desde el menú o la barra de herramientas. En el caso de los modelos implementados, las operaciones de procesamiento se invocan manualmente mediante el cuadro de diálogo Tablas de proceso de SSMS, mediante la ejecución de un script que incluya el comando Refresh (TMSL) o mediante programación mediante el modelo de objetos tabular (TOM).

Procesamiento paralelo

Analysis Services utiliza el procesamiento paralelo para dos o más particiones, lo que aumenta el rendimiento del procesamiento. No hay valores de configuración para el procesamiento paralelo. El procesamiento paralelo se produce de forma predeterminada al procesar tabla o seleccionar varias particiones para la misma tabla y proceso. Sin embargo, hay configuraciones que limitan las operaciones de procesamiento en paralelo.

MaxConnections

De forma predeterminada, cada operación de procesamiento se conectará y consultará un origen de datos para cada partición. El número máximo predeterminado de conexiones, especificado como la propiedad MaxConnections en un único origen de datos es 10. Analysis Services determina el número de operaciones de procesamiento simultáneas que se ejecutarán en función del número de núcleos y subprocesos disponibles. Estos subprocesos se comparten en la instancia del servidor. Es posible que un único comando como process no reciba todos los subprocesos disponibles. Los subprocesos que se inician para su procesamiento, uno para cada operación de procesamiento paralelo, se pueden retrasar para permanecer dentro del límite de MaxConnections.

MaxParallelism

De forma predeterminada, las operaciones de procesamiento se ejecutan en paralelo tanto como sea posible. Sin embargo, puede elegir procesar particiones secuencialmente o en paralelo especificando la opción de propiedad maxParallism con el comando Sequence (TMSL). Establecer el valor en 1 significa que no es paralelo; se usa un subproceso para su procesamiento. Si se establece el valor en 2 o más, se especifica un número fijo de subprocesos que se pueden usar para las operaciones de procesamiento en paralelo.

Monitor

Para determinar el uso eficaz de los subprocesos disponibles durante las operaciones de proceso, para Azure Analysis Services, use el Explorador de métricas de Azure para supervisar CommandPoolIdleThreads y CommandPoolBusyThreads. Para más información, vea Supervisión de las métricas del servidor. Para SQL Server Analysis Services, use Monitor de rendimiento para supervisar los subprocesos no de E/S inactivos del grupo de procesamiento y los subprocesos no de E/S ocupados. Para más información, consulte Contadores de rendimiento (SSAS).

Nota:

Si se detecta una nueva codificación, el procesamiento paralelo puede provocar un mayor uso de recursos. Esto se debe a que es necesario interrumpir y reiniciar varias operaciones de partición con la nueva codificación en paralelo.

Estrategias avanzadas de creación de particiones

El artículo Administración de particiones automatizadas para los modelos tabulares de Analysis Services .pdf artículo, junto con el ejemplo de código asPartitionProcessing adjunto en GitHub, proporciona información detallada y un ejemplo de solución para la empresa ficticia, Advenure Works, mediante el modelo de objetos tabulares (TOM) para crear y administrar particiones. Los conceptos descritos en este artículo y proyecto se aplican a todas las plataformas de Analysis Services.

Consulte también

Creación y administración de particiones de modelos tabulares
Objeto Partitions (TMSL)
Crear tablas, particiones y columnas con el modelo de objetos tabulares (TOM)
Creación de particiones (lección del tutorial)

Compartir a través de