En este artículo os vamos a contar como afrontar escenarios Near Real-Time (NRT) con la ayuda del particionado de cubos SSAS Tabular. Vamos a utilizar el nuevo Tabular Object Model (TOM) el cuál sólo se puede utilizar en modelos con nivel de compatibilidad 1200 o superior, lo que quiere decir que vamos a necesitar SQL Server 2016 o posterior.
Los escenarios Near Real-Time ocurren cuando una espera de unos pocos minutos, desde extracción de datos hasta que están disponibles en un cubo, es aceptada por los usuarios de negocio. Vamos a contaros como podemos reducir significativamente el tiempo de procesado particionando nuestro cubo con TOM:
Problema:
Tenemos la tabla FactInternetSales de AdventureWorksDW que contiene las ventas realizadas por Internet de diferentes países para varios años. Además, le hemos añadido registros ficticios pasando de 60.000 registros a 44.558.080 de registros, lo que hace que un procesado full sólo de esta tabla tarde más de 11 minutos en nuestro equipo. Si deseamos disponer de un escenario NRT donde un país necesite refrescar varias veces al día los datos de sus ventas por internet en el cubo, necesitamos reducir considerablemente este tiempo de procesado:
Diseño de la solución:
Vamos a particionar el cubo dinámicamente por dos variables: por año y por país de la venta. De esta forma podremos refrescar los datos del cubo a demanda por país y para su último periodo (año en curso), consiguiendo así reducir drásticamente el tiempo de procesado. En nuestro caso vamos a tener 53 particiones:
Se trata de número elevado de particiones y su manejo ya se nos plantea así de primeras complicado. A continuación, os explicamos cómo crearlas y gestionarlas de forma sencilla utilizando TOM desde SSIS. En el siguiente esquema presentamos el flujo de la solución:
1. Vamos a llenar una tabla de configuración con la información de las particiones existentes en nuestra tabla de hechos.
2. Vamos a generar un paquete SSIS que realizará 2 acciones:
a. Va a generar y procesar todas las particiones.
b. Va a procesar la partición a demanda
Implementación de la solución:
Lo primero que vamos a hacer es crear una tabla donde vamos a almacenar las particiones que nos han salido con nuestro criterio de particionado. Nos basta con insertar en una tabla como la siguiente: el año, el país y los territorios que componen cada país. La query de particionado y el nombre de la partición, son campos calculados.
USE [AdventureWorksDW]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [ssas].[InternetSalesPartitions](
[OrderYear] [int] NULL,
[SalesCountry] [varchar](50) NULL,
[SalesTerritoryKeys] [varchar](50) NULL,
[PartitionName] AS ((CAST([OrderYear] AS VARCHAR)+'_')+[SalesCountry]),
[PartitionQuery] AS ('SELECT * FROM FactInternetSales where year(OrderDate) = '+ (CAST([OrderYear] AS VARCHAR)) + ' and SalesTerritoryKey in (' +[SalesTerritoryKeys] + ')' ),
[PendingProcess] [int] NULL,
) ON [PRIMARY]
GO
Esta es la query con la que generemos las particiones y llenamos nuestra tabla que albergará las particiones:
USE [AdventureWorksDW]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
INSERT [ssas].[InternetSalesPartitions] (OrderYear, SalesCountry, SalesTerritoryKeys,PendingProcess)
SELECT year([OrderDate]) as OrderYear
,t.SalesTerritoryCountry
,STUFF(
(
SELECT ',' + cast(SalesTerritoryKey as varchar)
FROM AdventureWorksDW.dbo.DimSalesTerritory t2
where t2.SalesTerritoryCountry = t.SalesTerritoryCountry
FOR XML PATH('')
)
,1
,1
,''
) AS SalesTerrytoryKeys,
1 as PendingProcess
FROM [AdventureWorksDW].[dbo].FactInternetSales f
inner join AdventureWorksDW.dbo.DimSalesTerritory t on
t.SalesTerritoryKey = f.SalesTerritoryKey
GROUP BY YEAR([OrderDate])
,t.SalesTerritoryCountry
GO
Antes de seguir, necesitamos hacer un Process Clear de nuestro cubo y generar una partición vacía llamada Default que contendrá cero rows. Hay que ser cuidadoso con la generación de particiones ya que podemos provocar que se repitan o nos falten datos en nuestro cubo:
Implementación de SSIS:
Vamos a crear el proyecto de SSIS que va a generar estas particiones siguiendo estos pasos:
1) Creación de los parámetros del proyecto:
a. SSAS_Server: Servidor de SSAS donde tenemos alojado nuestro cubo:
b. SSAS_DB: ID de la BBDD de SSAS donde tenemos alojado nuestro cubo:
c. ProcessFull: parámetro que nos indica si queremos procesar todas las particiones del cubo (1) o queremos hacer un procesado selectivo (0).
2) Creamos un paquete llamado CreatePartitions, añadimos un Data Flow Task y un componente Data Flow para leer las particiones de la tabla ssas.InternetSalesPartitions:
3) Añadimos un script component y le pasamos como ReadOnlyVariables los parámetros que hemos creado en el proyecto:
4) Editamos el script component y lo primero que hay que hacer es añadir como referencia las 3 DLL de Analysis Services que necesitamos. Las podemos añadir haciendo Browse y buscarlas manualmente en la carpeta SDK-> Assemblies donde tenemos instalado Sql Server. Otra opción es descargar el instalador SQL_AS_AMO.msi incluido Microsoft® Analysis Server® Feature Pack y registrar las librerías:
5) En PreExecute realizamos la conexión al servidor de SSAS y a la tabla de hechos del cubo:
6) Creamos las particiones que hemos leído de la tabla ssas.InternetSalesPartitions:
7) Comprobamos con el parámetro ProcessFull si se trata de la creación y procesado de una partición individual o de la creación inicial de todas las particiones. En el primer caso, crearemos la partición y la procesaremos. En el segundo, solicitaremos un procesado full de la tabla. Desde SQL Server 2016 la acción de Process Full, procesará todas las particiones en paralelo y el tiempo será inferior al procesado secuencial que es lo que hace SQL Server 2014-2012:
8) Ya podemos ejecutar nuestro paquete y comprobar que se han creado todas las particiones. Observamos que ha tardado 10 minutos:
Procesemos ahora, por ejemplo, la partición con los datos de Alemania para el año actual (2018_Germany) y comprobemos el resultado:
Observamos que ha tardado 45 segundos logrando así nuestro objetivo de reducir el tiempo de procesado: hemos pasado de tardar 11 minutos a menos de un minuto.
Conclusión:
En este artículo hemos podido comprobar la sencillez, en comparación con otros métodos de scripting (XMLA, ASSL …), con la que TOM nos permite gestionar nuestras particiones. Además, hemos solucionado un problema habitual de la parte de negocio: nos pedían disponer de los datos actualizados en el cubo varias veces al día en cada país con un tiempo de procesado razonable.
También nos gustaría destacar la ventana de oportunidades que nos abre la aplicación de TOM en otras áreas como serían DevOps (Deploys incrementales de cubos …) o Self Service para el usuario final, por ejemplo, la creación de aplicaciones cliente donde el propio usuario de negocio podría añadir al modelo fórmulas, atributos etc.
Esperamos que os haya gustado y os haya resultado de utilidad el artículo.
Código fuente del proyecto: SSIS_Dynamic_Partition_TOM
