Particiones en modelos tabulares

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

Las particiones dividen partes de los datos que necesita procesar (actualizar) con frecuencia de los datos que se pueden procesar con menos frecuencia. 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ógicos. Las particiones individuales, cada una que contiene un segmento único de datos, se pueden procesar incrementalmente de forma secuencial o en paralelo independientemente de otras particiones, o excluirse por completo de las operaciones de procesamiento.

Granularidad

De forma predeterminada, cada tabla de un modelo tiene una sola partición. En muchos casos, como con las tablas de hechos, dividir la partición única de una tabla en varias particiones puede usar mejor los recursos disponibles para el 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, al tiempo que se hace seguro de que los datos se actualizan con la frecuencia suficiente 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:

A medida que se agregan nuevos datos de ventas para el año fiscal actual de 2020, los 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 cada noche. Sin embargo, dado que los datos de ventas de los diez años fiscales anteriores todavía pueden cambiar debido a los resultados del producto y otros ajustes, deben procesarse con regularidad, 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 que solo se procesan anualmente.

Al especificar el año fiscal 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 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 el uso de recursos de procesamiento y memoria dentro de un intervalo predecible a lo largo del tiempo.

La granularidad se ve afectada por varios factores, como la cantidad de datos que se necesitan procesar incrementalmente en un período de tiempo aceptable. Por ejemplo, si solo es necesario procesar el último día completo diariamente, puede ser beneficioso usar la granularidad diaria. La granularidad mixta se puede configurar para escenarios como la actualización casi en tiempo real a nivel de grano bajo, junto con particiones estáticas históricas con una granularidad mayor. 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, solo se actualiza una vez al día después de cerrar todas las tiendas minoristas. Al crear particiones independientes en diferentes granularidades en la tabla Orders del modelo para los datos importados desde factInternetOrders y factRetailOrders, las operaciones de procesamiento de la tabla Orders se pueden separar y ejecutar más en línea 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 las que no.

Particiones del centro y límites por partición

Independientemente de la plataforma, no hay ningún límite 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 a la vez que sigue a la vez de forma eficaz a la hora de cumplir los objetivos de creación de particiones. Es más importante centrar una estrategia de creación de particiones eficaz basada en la granularidad y procesar solo las particiones con los datos cambiantes más pertinentes dentro de los recursos de procesamiento y memoria disponibles en momentos en los que las consultas del 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. En función del nivel de compatibilidad del modelo que cree, 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 posteriores 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 SQL consulta. Por ejemplo,

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

Observe que el argumento Filas filtradas de la expresión M de Power Query 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 Tabular Model Scripting Language (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 del objeto Partitions se pueden especificar en los siguientes comandos de TMSL:

• Crear
• CreateOrReplace
• Modificar
• Eliminar
• MergePartitions
• Actualizar

Los scripts TMSL se pueden ejecutar en SQL Server Management Studio, con PowerShell mediante la ejecución del comando Invoke-ASCmd o mediante una tarea script sqlserver Integration Services (SSIS).

Para los modelos con los 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 más información sobre las soluciones de programación que usan TOM como API, consulte Creación de tablas, particionesy columnas (TOM) y Estrategias avanzadas de creación de particiones más adelante en este artículo.

Para los modelos con los niveles de compatibilidad 1100 y 1103, use Objetos de administración de análisis (AMO).

Procesar particiones

Cuando se particiona la tabla de datos, esas particiones se pueden procesar a la vez y la cadencia adecuada 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 utiliza 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, se recompilen las relaciones y las jerarquías y se vuelve 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 Procesar tablas de SSMS, mediante la ejecución de un script que incluye el comando Refresh (TMSL)o mediante programación mediante el modelo de objetos tabulares (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á a un origen de datos y lo consultará para cada partición. El número máximo predeterminado de conexiones, especificado como la propiedad MacConnections a 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 entre la instancia del servidor. Es posible que un único comando como proceso no reciba todos los subprocesos disponibles. Los subprocesos que se inician para el procesamiento, uno para cada operación de procesamiento en 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 el procesamiento. Establecer el valor en 2 o más especifica que se puede usar un número fijo de subprocesos para las operaciones de procesamiento en paralelo.

Monitor

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

Estrategias avanzadas de creación de particiones

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

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)