Miembros inferidos en SSIS

Miembros inferidos en SSIS

La primera vez que tuve que crear un paquete de SSIS teniendo en cuenta los miembros inferidos me encontré un poco perdido. Me sabía la teoría de esos miembros de una dimensión que llegan en los hechos y que aún no están cargados en su correspondiente tabla de dimensiones, pero no tenía ni idea de cómo podría implementarlo de una forma eficiente y efectiva. Vamos, quiero decir, que lo hiciera rápido y bien, que es como hay que hacerlo. Para ello hay que tener en cuenta dos cosas, la primera es que se debe hacer la inserción del miembro desconocido pero además recuperar la SK que se le asigna. La segunda es que pueden venir varias filas referenciando el mismo miembro y no se puede insertar cada vez que aparezca para evitar duplicidades. Y además, debemos intentar hacer las mínimas consultas posibles al DW para no sobrecargarlo.

 

Buscando por la bibliografía de la que disponía y por Internet encontré muchos artículos en los que se usaban distintos métodos, la mayoría con componentes script. Pero entre ellos apareció un artículo de Thomas Kejser donde explicaba cómo hacerlo de una forma muy interesante: con dos lookup y un stored procedure en el DW. Como mi escenario era bastante complejo y su ejemplo demasiado sencillo, me llevó un tiempo implementarlo de forma que funcionara y sacarle todo el partido que se puede obtener de esta solución. Es por ello que, a continuación, voy a elaborar un ejemplo también sencillo pero con varias situaciones que se nos pueden presentar.

Empezaremos creando 3 tablas que vamos a necesitar con los valores necesarios:

CREATE TABLE StageCompras(
  Alimento VARCHAR(25) NOT NULL,
  Color VARCHAR(25) NULL,
  Unidades INT NOT NULL,
  PrecioUnidad FLOAT NOT NULL
  )

CREATE TABLE DimAlimentos(
  SKAlimento INT PRIMARY KEY IDENTITY(1,1),
  Alimento VARCHAR(25) NOT NULL,
  Color VARCHAR(25),
  Tipo VARCHAR(25),
  Calorias100gr INT,
  FlagInferido BIT
  )

CREATE TABLE FactCompras(
  Unidades INT NOT NULL,
  PrecioUnidad FLOAT NOT NULL,
  FKAlimento INT NOT NULL,
  Fecha SMALLDATETIME DEFAULT GETDATE()
  )
GO

INSERT INTO StageCompras VALUES 
  ('Patata', 'Amarillo', 4, 2.95)
  ,('Naranja', 'Naranja', 1, 1.50)
  ,('Kiwi', 'Marrón', 5, 2.75)
  ,('Manzana', 'Roja', 4, 1.99)
  , ('Manzana', 'Roja', 6, 1.99);

INSERT INTO DimAlimentos VALUES 
  ('Patata', 'Amarillo', 'Planta', 77, 0)
  ,('Kiwi', 'Marrón', 'Fruta', 61, 0)
  ,('Manzana', 'Verde', 'Fruta', 52, 0)

Lo siguiente será crear un paquete SSIS, añadir un Data Flow y construir el sistema. Para ponernos en situación al final nos quedará algo así:

1

Para comenzar con el flujo debemos tener un origen de datos que lea de la tabla StageCompras.

2

A continuación añadiremos un Lookup conectado a la tabla DimAlimentos. En la pestaña General le indicaremos que las filas que no hagan join las derive a la salida de no encontradas. En la pestaña de Columnas enlazaremos Alimento y Color y le marcaremos SKAlimento para que nos devuelva ese campo. Con esto tenemos un lookup que tendrá en caché todos los elementos de la dimensión DimAlimentos con una única consulta.

3

Añadiremos un segundo Lookup, que es el que tiene la mitad de la magia de esta solución, a la salida del no encontrados del primer Lookup. En la pestaña General, en el grupo Cache mode le indicaremos que queremos que haga Partial cache. Esto quiere decir que se guardará en caché todas aquellas filas por las que se le pregunte. Y a él solo llegaran las filas que contienen miembros desconocidos puesto que está unido a esa salida del anterior. Por esto solo consultará al DW la primera vez que aparezca un elemento desconocido. Las siguientes veces que aparezca el mismo elemento desconocido ya lo tendrá en memoria y no necesitará repetir la consulta.

4

En la pestaña de Connection haremos la conexión con la tabla DimAlimentos utilizando la opción de usar una tabla o vista. Este punto es importante y volveremos sobre él más adelante.

5

En el apartado de columnas haremos lo mismo que en el caso del anterior Lookup, enlazar Alimento y Color y marcar SKAlimento.

Nos queda una pestaña por configurar, la de Advanced. En este punto nos dará error puesto que nos falta la otra parte del truco, pero vamos a indicar ya lo que haremos. Marcaremos el check de Modify the SQL statement y escribiremos la siguiente sentencia

EXEC Generate_SKAlimento ?, ?, 'NA'

Le estamos indicando que cuando llegue una fila desconocida, que no tenga ya en caché, ejecute esa llamada a un Stored Procedure el cual insertará el elemento en el DW y nos devolverá la SK que le asigne SQL Server (por ser un Identity).

6

Y presionando sobre el botón de parámetros configuraremos dos de ellos

7

Para terminar el Data Flow nos falta hacer una unión de los dos lookup y un destino OLE DB conectado a la tabla FactCompras.

8

Y configurando el mapeo de filas

9

Y con esto hemos acabado con el paquete SSIS. Vayamos con el comienzo del procedimiento almacenado que debemos crear en el DW.

CREATE PROCEDURE Generate_SKAlimento 
  @Alimento VARCHAR(25)
  ,@Color VARCHAR(25)
  ,@Tipo VARCHAR(25)
AS
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

Éste va a tener tres parámetros: Alimento, Color y Tipo. En nuestro caso solo tenemos el nombre del alimento y el color pero, ¿y si en otro punto de nuestro ETL también pudiéramos tener miembros inferidos que sean de esta dimensión y conociéramos más datos de ellos? Por ejemplo, el tipo de alimento que es (igual proviene de una receta con más datos). Para eso podemos generalizar un poco el procedimiento y que admita más entradas. Es por ello que en la sentencia SQL del Lookup había dos interrogaciones, una para Alimento, otra para Color, y luego le estábamos diciendo que el tipo era ‘NA’.

Prosigamos con el código del script:

DECLARE @SKAlimento INT
  ,@Calorias100gr INT = 0
  ,@FlagInferido BIT = 1

Necesitamos declarar al menos la variable SKAlimento del tipo int, pero ya de paso podemos inicializar dos más con los valores por defecto que le vamos a dar a los campos de los miembros inferidos que no conocemos. Esto no es completamente necesario hacerlo así ni en este punto, pero queda muy organizado y en un futuro, si queremos cambiar algo, no tendremos que mirar el resto del código.

El siguiente paso es comprobar si ese alimento ya existe o debemos insertarlo.

SELECT @SKAlimento = SKAlimento
FROM DimAlimentos
WHERE @Alimento = Alimento
  AND @Color = Color

IF @SKAlimento IS NULL
BEGIN
  INSERT INTO DimAlimentos
  VALUES (
    rtrim(@Alimento)
    ,rtrim(@Color)
    ,@Tipo
    ,@Calorias100gr
    ,@FlagInferido
    )

  SET @SKAlimento = SCOPE_IDENTITY()
END

Los rtrim() son muy importantes puesto que las cadenas que reciba el procedimiento van a rellenarse con espacios en blanco por el final hasta rellenar el máximo del tipo y nos dará problemas al hacer joins por estos campos.

En nuestro ejemplo no lo hemos recogido, pero podría darse el caso que pudiéramos complementar información de otros miembros inferidos que se hayan cargado en otros puntos. Para contemplar esto primero tendríamos que añadir ese campo en el Lookup a la hora de hacer join (para que no lo encuentre y utilice el segundo Lookup) y luego escribir algo similar a:

ELSE IF @Tipo != 'NA' AND @TipoDW = 'NA' BEGIN
    UPDATE DimAlimentos SET Tipo = @Tipo
    WHERE SKAlimento = @SKAlimento
END

Por último, debemos devolver la consulta con el nuevo SK. Y en este punto tenemos que pararnos un momento. Al crear el segundo Lookup indicamos que íbamos a usar la opción de vista o tabla para consultar la tabla DimAlimentos, pero también podríamos haber hecho una consulta propia SQL. Pues bien, en el procedimiento, la consulta que devolvamos debe responder exactamente igual a la consulta que indicamos al configurar el Lookup. Si usamos la primera opción deberemos devolver todos los campos que tenga la tabla, si escribimos una consulta propia solo aquellos que indicásemos.

SELECT @SKAlimento AS SKAlimento
  ,@Alimento AS Alimento
  ,@Color AS Color
  ,@Tipo AS Tipo
  ,@Calorias100gr AS Calorias100gr
  ,@FlagInferido AS FlagInferido
FROM DimAlimentos

Los tipos deben ser iguales y los nombres de las columnas también. Podríamos haber hecho lo siguiente (aunque no aconsejo esta práctica).

SELECT *
FROM DimAlimentos
WHERE SKAlimento = @SKAlimento

Con todo esto habríamos completado el ejemplo, solo nos quedaría ejecutarlo para comprobar que todo ha funcionado correctamente y no hemos insertado dos manzanas en la dimensión DimAlimentos. El código al completo sería:

CREATE PROCEDURE Generate_SKAlimento 
  @Alimento VARCHAR(25)
  ,@Color VARCHAR(25)
  ,@Tipo VARCHAR(25)
AS
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

DECLARE @SKAlimento INT
  ,@Calorias100gr INT = 0
  ,@FlagInferido BIT = 1

SELECT @SKAlimento = SKAlimento
FROM DimAlimentos
WHERE @Alimento = Alimento
  AND @Color = Color

IF @SKAlimento IS NULL
BEGIN
  INSERT INTO DimAlimentos
  VALUES (
    rtrim(@Alimento)
    ,rtrim(@Color)
    ,@Tipo
    ,@Calorias100gr
    ,@FlagInferido
    )

  SET @SKAlimento = SCOPE_IDENTITY()
END

SELECT @SKAlimento AS SKAlimento
  ,@Alimento AS Alimento
  ,@Color AS Color
  ,@Tipo AS Tipo
  ,@Calorias100gr AS Calorias100gr
  ,@FlagInferido AS FlagInferido
FROM DimAlimentos

(más…)

Temporal tables en SQL Server 2016

Temporal tables en SQL Server 2016

La llegada de SQL Server 2016 trae numerosas nuevas funcionalidades para ayudarnos con las necesidades propias de aplicaciones empresariales. Una necesidad bastante habitual consiste en disponer de un histórico de ciertas tablas para, en el futuro, poder comprobar su contenido en distintos momentos del tiempo.

Con este requerimiento de tipo “multidimensional” es fácil pensar en que si ya disponemos de un DW (Data Warehouse) éste sería el lugar donde acudir cuando necesitamos dicha información. Esto puede presentar varios problemas, entre ellos que necesitamos primero que nuestra organización utilice un DW que contemple esta información. Otro problema es que para la carga del DW habitualmente se realizan procesos ETL que transforman los datos por lo que puede que no podamos restituir la información a su estado original.

(más…)

Optimizando el componente DQS Cleansing en SSIS 2012 (parte 1 de 2)

Optimizando el componente DQS Cleansing en SSIS 2012 (parte 1 de 2)

En la actualización para la Cumulative Update 1 del documento DQS Performance Best Practices podíamos encontrar las recomendaciones hardware así como las estimaciones de tiempo que el grupo de producto daba como orientación a la hora de ejecutar los diferentes procesos que puede llevar a cabo SQL Server 2012 Data Quality Services. Además, encontramos información acerca de los factores de mejora de estos procesos en el caso hipotético de que doblemos el número de núcleos de proceso (cores), siempre dependientes de la arquitectura general de la que dispongamos, conocido como el scale-up factor. (más…)
Data Explorer: llega el self-service ETL

Data Explorer: llega el self-service ETL

Recientemente se publicó la versión preview de Data Explorer para Excel (La podéis descargar de aquí)

El producto llega a su versión previa al lanzamiento después de haber tenido dos versiones, el cliente de escritorio y el servicio en la nube, publicadas hace algo más de un año, y que ofrecían un servicio muy similar aunque con ligeras diferencias respecto a la conectividad y posibilidad de publicación de resultados.

(más…)

Automatizando la generación de paquetes SSIS

Tras muchas experiencias en desarrollo de procesos ETL con Integration Services, en los que buena parte de ellos tienen como destino final un Data Mart o un Data Warehouse, hemos ido recopilando buenas prácticas, pros y contras de dar un enfoque u otro a la solución de cada problema, cuáles de ellas obtienen mejores resultados de rendimiento según qué casos, etc. (más…)
Cargar Slowly Changing Dimensions sin castigar a nuestro DWH relacional

Cargar Slowly Changing Dimensions sin castigar a nuestro DWH relacional

En ocasiones al construir sistemas de Inteligencia de Negocios nos podemos olvidar de las buenas prácticas sobre nuestros sistemas relacionales en los que nos apoyamos. Sin embargo, esto puede derivar en problemas para nuestros procesos de carga. Para evitarlo es conveniente diseñar correctamente nuestros sistemas a todos los niveles, desde la primera fuente de datos hasta el último reporte que construyamos. (más…)
Administrando SSIS 2012 mediante T-SQL

Administrando SSIS 2012 mediante T-SQL

En SQL Server 2012 se introduce la nueva arquitectura para Integration Services, con una base de datos dedicada y un nuevo modelo de despliegue basado en esta arquitectura. Podéis encontrar información acerca de estos cambios en los artículos anteriores de esta serie. (más…)