Tips de diseño de DWH: Bus Matrix

Por Rut Almoguera Poggi

Si te estás iniciando en el mundo de los Datawarehouses y la única “Matrix” que conoces es la película de los 90’s cuyo héroe es Neo, este post es para ti.

Aparte de la famosa película Matrix también existe otra Matrix que es igualmente famosa e importante, que es la Bus Matrix (o Matriz de Bus en español).  Esta es una de las más importante herramientas en el diseño de un Datawarehouse y que se usa universalmente al momento del levantamiento de los requerimientos. 

La Bus Matrix o Matriz de Bus es un instrumento de documentación de alcance, y de definición de la estructura de las tablas Fact.

Como sabemos, cuando estamos diseñando un Datawarehouse, debemos definir principalmente tres tipos de entes: las Facts , las Medidas que están en dichas Facts y las Dimensiones.  Una vez que hemos decidido cuales son las Dimensiones, las Medidas y las Facts tenemos que tener una manera de representar estos entes del diseño y las relaciones existentes entre ellos. Hacer por cada Fact una lista de las Dimensiones con las que se relaciona, seria tedioso e ilegible, no solo para un técnico, sino también para discutir y revisar el modelo con nuestros clientes.  Es aquí que surge la Matriz de Bus, que es una excelente y casi indispensable herramienta que nos ayuda a representar nuestro diseño.

Principalmente se encarga de mostrar cuales serán las medidas a implementar en cada Fact y como están relacionadas con las distintas Dimensiones del modelo.  Una vez que sabemos cuáles son las Dimensiones y las Facts que tendrá nuestro Datawarehouse, para construir la Matriz de Bus de cada Fact en nuestro modelo, debemos crear una matriz o tabla mostrando las Medidas en las filas y las Dimensiones en las columnas, y marcando la intersección existente entre ellas con una ‘x’.

Las Matrices de Bus son en general como se muestran a continuación:

Nombre Fact	Dimensión 1	Dimensión 2	Dimensión 3	…	Dimensión M
Medida 1	X	X	X		X
Medida 2		X	X	X
Medida 3	X	X	X	X	X
Medida 4	X				X
…
Medida N	X		X	X	X

Y ud.  mi apreciado lector, seguramente vera esto y pensara que evidentemente es más sencillo visualmente, pero probablemente también se preguntara:  ¿Por qué debo colocar una equis (X) marcando las asociaciones entre las Dimensiones y las Medidas? ¿Acaso no están TODAS las Dimensiones relacionadas con TODAS las Medidas de una Fact específica? La respuesta a esta interrogante es: NO.

Para ilustrar esto, consideremos el siguiente ejemplo: Imaginemos que tenemos un cliente que es una Tienda, y que ellos desean crear un Datawarehouse para sus ventas.  Esta tienda tiene sucursales en todo el país, y también tiene un Site o Pagina Web por donde los clientes pueden hacer sus compras que recibirán en la dirección que deseen pagando un monto extra por transporte y entrega a domicilio.

En nuestro Datawarehouse tenemos que reflejar en este caso en una Fact de Ventas, cuándo se hace una venta, a que cliente se le realizo la venta, en qué fecha, cuál fue el articulo comprado, cuántos compro y cuál fue el vendedor que realizo la venta (en caso que aplique), teniendo por separado las ventas por internet de las ventas en las tiendas.

En este caso, tendríamos las Medidas: “Ventas Tienda” y “Ventas Internet” para la Fact de Ventas.

Y las Dimensiones: Fecha, Cliente, Vendedor y Producto.

Como podemos notar, en este caso la medida “Ventas Tienda” tendrá un vendedor asociado, pero en el caso de la medida “Ventas Internet”, el vendedor NO existe, por lo tanto, la Matriz de Bus de la Fact de Ventas que tendríamos para nuestro ejemplo sería la siguiente:

Fact Ventas	Fecha	Cliente	Producto	Vendedor
Ventas Tienda	X	X	X	X
Ventas Internet	X	X	X

Como podemos ver, la Matriz de Bus en este caso nos sirvió para mostrar de una manera fácil y sencilla cuáles son nuestras Dimensiones, cuáles son nuestras Medidas y cuáles son las relaciones entre ellas.

Así que querido lector, adopta la Matriz de Bus como una herramienta para plasmar tu diseño y así poder mostrárselo a tu cliente, que te vera como a un héroe (igual que Neo en Matrix), por simplificarle el entendimiento del modelo del Datawarehouse para su negocio.

Tips de diseño de DWH: La Omnisciente Dimensión Time – Parte 2

Por Rut Almoguera Poggi 

Ahora que ya hemos visto en el post anterior lo imprescindible que es la dimensión Time, en esta entrega vamos a hablar sobre cuáles son las mejores prácticas para construirla.

La dimensión Time es especial pues no suele tener una fuente de donde llenarla, por lo que tenemos nosotros mismos que cargarla en el Datawarehouse.

A diferencia del resto de las tablas de dimensión, podemos construir la tabla Time por adelantado, cargándola con 5 o 10 años de registros cubriendo algunos años hacia atrás y/o hacia adelante (10 años = 3.650 registros, sí la granularidad es de día). 

Es recomendable que creemos un Store Procedure en la BD o algún programa para pre-cargarla.  Por lo general es común que se pre-carguen algunos años en la dimensión Time aunque también podría programarse el proceso de llenado de la dimensión Time para que revise al inicio del mismo si el año actual esta presenta ya en la data de la dimensión, y sino esta insertar el año actual completo, y en caso contrario, simplemente terminar.

Otra consideración importante al momento de crear la dimensión Time es revisar la granularidad de la misma.  Por ejemplo, si la dimensión Time por necesidades del negocio requiere tener un detalle más fino que el de día, por ejemplo, que necesitáramos guardar la hora exacta en la que sucede un evento, sería recomendable que la misma se partiera en dos dimensiones, una que contenga solo las fechas, y otra que contenga solo las horas del día, pues de lo contrario la dimensión terminaría siendo demasiado grande, y degradaría el performance de las consultas al Datawarehouse.

También al momento de diseñarla tenemos que considerar como serán los Rollups en las jerarquías de dicha dimensión.

Por ejemplo, si nuestra dimensión Time tiene una granularidad de día, y tiene una jerarquía como la siguiente:

No tendremos mayores problemas, con el Rollup.

Pero consideremos una jerarquía diferente en donde esté presente la Semana del año y el Mes como niveles de la jerarquía.  Algo como lo siguiente:

En este caso, si tendríamos un problema al hacer el Rollup, pues por lo general un mes NO termina o comienza en una semana completa, sino que puede hacerlo a la mitad de una semana, con lo cual la semana podría pertenecer a dos meses diferentes.  Veamos por ejemplo las fechas 31/03/2015 que fue martes,  y 01/04/2015 que fue miércoles:

Como podemos ver, ambas fechas están en la semana 14 del año, pero pertenecen a meses diferentes, por lo tanto NO podemos colocar el nivel Semana del Año en una jerarquía que también contenga el nivel Mes pues el resultado de hacer Rollup por dicha jerarquía podría arrojar valores incorrectos.  Si necesitamos tener los dos niveles, porque el cliente lo requiere para los reportes, se deberán tener dos jerarquías para dicha dimensión, una que contenga el Mes y otra que contenga la Semana del Año.

Otro tema importante a considerar cuando estamos diseñando una dimensión Time es sobre la decisión de cuál campo sería el Primary Key de la tabla.  Por lo general para el resto de las dimensiones lo recomendable es un usar un Surrogate Key, pero en el caso de la dimensión Time, esto no aplicaría, pues por lo general la data de las tablas Facts, suelen particionarse por la fecha, y por lo tanto, es buena idea tener como Primary Key de la dimensión Time un código inteligente que contenga el año, y nos permita particionar la Fact por la data de dicho campo.

Esto nos facilitaría poder “quitar” una partición de la tabla Fact, que corresponda a un año específico cuando ya la data relativa a ese año no sea de utilidad o se requiera consultarla con muy poca frecuencia, y el costo de mantenerla en el Datawarehouse sea mayor que los beneficios que obtendríamos en el performance de las consultas si la eliminamos.  Como por ejemplo podrían ser las ventas en una tabla Fact de Ventas que sean de 10 años atrás.

Lo más recomendable para usar como la clave de dicha dimensión es un código “inteligente” que contenga el año.  Por ejemplo si tenemos una dimensión Time cuya granularidad llega solo hasta el nivel de mes, podríamos crear el identificador del mes, concatenando el año con el mes:

YYYYMM: en donde YYYY es el año y MM el mes, y para este cálculo se debe multiplicar el año por 100 y sumarle el mes (getyear(Fecha)*100  + getmonth(Fecha)).

Si por ejemplo la granularidad de nuestra dimensión Time es de día.  Siguiendo con esta regla podríamos tener el siguiente Primary Key:

YYYYMMDD: en donde YYYY es el año, MM el mes y DD el día. Para el cálculo es necesario obtener el año, multiplicarlo por 10000, sumarle el mes multiplicado por 100 y sumarle el día (getyear(Fecha)*10000 + getmonth(Fecha)*100 + getday(Fecha)).

Así cuando se cree la tabla de la Fact particionada, se realizaría la partición por los valores del campo de la fecha, y todas las filas que correspondan a un año específico irían a una partición especial para ese año, y así será muy sencillo mantener en la tabla Fact solo la data relevante, pues, si no hacemos limpieza de la misma y dejamos que se llene de data muy vieja, el performance de los queries contra la Fact se degradaría notablemente.

Con esto terminamos la discusión sobre la dimensión Time, espero que consideres estos consejos cuando diseñes dicha dimensión.

Por Edgar Barrios

Continuando con el ejemplo de implementación de un DWH, ahora vamos entrar en detalle con los objetos que se deben crear en la base de datos que contendrá el DWH. Para un mejor entendimiento de lo expuesto en este artículo, se recomienda ver primero el artículo Fuentes de datos y área de staging en un Data Warehouse (DWH).

Lo primero es identificar el tipo de modelo a implementar, de los cuales tenemos el modelo estrella y el modelo copo de nieve, para entender un poco de que se trata a continuación se presenta una breve definición y las diferencias existentes entre ellos:

•       Estrella: Desnormaliza las dimensiones.

•       Copo de nieve: Normaliza las dimensiones.

Tabla Comparativa Modelo Estrella Vs Modelo Copo de Nieve

Dadas las razones expuestas en la tabla anterior, lo más recomendable es siempre usar el modelo estrella.  Sin embargo, dependiendo de las necesidades el usuario puede usar el que desee.

Para nuestro ejemplo el modelo a utilizar es el modelo estrella, el cual se puede observar en la siguiente imagen:

A continuación se muestran las tablas que se deben crear:

Dim_Sales_Rep: en esta tabla se encuentra la data de los vendedores, y prácticamente es la misma información que tenemos en el Staging en la tabla STA_Sales_Rep, con la excepción del campo surrogate key (SK), que no es más que una clave numérica que se usará en esta tabla como el primary key.

Dim_Customer: en esta tabla se encuentra la data de los clientes, y es exactamente la misma data de la tabla STA_Customer con la excepción de que esta tabla tendrá el surrogate key como PK.

Dim_Date: en esta tabla se encuentra exactamente la misma data que en la tabla STA_Date del Staging.

Dim_Product: en esta tabla se encuentra la misma data que se encuentra en la tabla STA_Product, pero aquí existirá el campo surrogate key.

Fact_Sales: esta será la tabla de la fact, Aquí estarán los SKs de las tablas de dimensión, excepto la tabla Dim_Date que en su lugar tendrá un campo con la clave de la tabla Dim_Date y los campos de las medidas, que en este caso serán: las cantidades vendidas y el monto de la venta

Se deben crear las claves primarias para las tablas de dimensión y definir índices tanto para las tablas de dimensión como para las tablas fact.

Ya pudimos ver cuáles son las áreas involucradas en un DWH, ahora vamos ver a manera de resumen los pasos a seguir para ejecutar el proceso de Extracción, Transformación y Carga (ETL por sus siglas en inglés) del DWH:

•       Crear las tablas en el área de Staging (planas y de dimensión) y en el DWH con sus respectivas primary keys. La tabla de la Fact en el DWH debe ser particionada. Generalmente dicho particionado se hace por el año.

•       Crear los índices que puedan ser necesarios.

•       Crear en la herramienta para desarrollar el ETL los procesos para cargar las tablas planas  desde la fuente hasta el Staging. Es importante que a estas tablas se les haga un TRUNCATE antes de insertar la información que viene de la fuente.

•       Es importante solo traerse las columnas y filas necesarias.

•       Llenar en el ETL las tablas de las dimensiones en el Staging.  En  caso que alguno de los queries para el llenado de las dimensiones sea muy lento, crear los índices necesarios para optimizarlos. Es importante recordar que en estas tablas NO deben estar los surrogates keys.

•       Llenar la tabla STA_Date, construyendo para esto o bien un Store Procedure o un desarrollo en el ETL que se encargue de llenar dicha tabla.

•       Llenar las dimensiones en el DWH con un proceso del ETL usando como fuente las dimensiones ya calculadas en el Staging.  Estas tablas son casi iguales a las que están en el Staging con la excepción  del surrogate key, que debe ser el primary key de las dimensiones.

•       Desarrollar los procesos para cargar las medidas en la fact en el ETL.  En caso de que alguno de los queries creados para llenar las medidas de la fact sea muy lento, crear los índices necesarios para optimizarlos.

•       Crear un proceso automático que ejecute los procesos del ETL en el orden mencionado con la frecuencia requerida de carga (diario, mensual, etc).

Fuentes de datos y área de staging en un Data Warehouse (DWH)

Por: Edgar Barrios

Para entrar más en detalle sobre las áreas involucradas en la construcción de un DWH, hoy vamos a hablar sobre las fuentes de datos y el área de staging, donde con un ejemplo práctico se podrá comprender mejor la idea expresada. 

Vamos a suponer que se desea construir un DWH que contiene las ventas por vendedor, cliente, producto y fecha, y que la fuente de la información es una Base de Datos que tiene el diagrama E-R que se muestra en la siguiente imagen (Fuente de la data):

                                        

Al momento de comenzar con la implementación, se debe definir cuales tablas del origen de datos serán útiles en la construcción del DWH, esto se debe a que quizás no todas las tablas de la fuente van a aportar información, por lo tanto lo ideal es no incluirlas.

Luego de tener definidas las tablas del origen de datos que se van a utilizar en la construcción del DWH, se procede a construirlas en el área de staging, en esta área estas tablas son conocidas como tablas planas. En la construcción de estas tablas se deben respetar los nombres y tipos de datos que se tienen en la fuente.

El siguiente paso es verificar que atributos comunes existen entre las tablas planas, de modo tal que aquellas tablas que compartan atributos comunes puedan ser integradas para formar dimensiones. Por ejemplo, las tablas Customer_Type y Customer se convierten en la dimensión D_Customer. Las tablas resultantes de la integración de tablas planas, se conocen en el área de staging como tablas de dimensión.

Para el ejemplo se van a utilizar todas las tablas de la fuente, por lo tanto las tablas planas que se deben crear en el staging son las siguientes:

•       Customer Type

•       Customer

•       Sales Area

•       Sales Rep

•       Product Line

•       Product Type

•       Product

•       Order Header

•       Order Detail

Luego se procede a crear las tablas de dimensión (se recuerda que estas tablas serán alimentadas por las tablas planas):

•       D_Customer: contendrá integrada toda la información proveniente del origen de datos que está asociada a los clientes.

•       D_Sales_Rep: contendrá integrada toda la información proveniente del origen de datos que está asociada a los vendedores.

•       D_Product: contendrá integrada toda la información proveniente del origen de datos que está asociada a los productos.

•       D_Date: no proviene del origen de datos. Se debe generar y debe comprender periodos de fecha de interés.

Es importante acotar que si el llenado de las tablas de dimensión es muy lento, se deben crear índices sobre las tablas planas, asegurándose de crear solamente aquellos índices que sean estrictamente necesarios.

Las tablas de dimensión deben poseer como clave primaria la clave de negocio existente en la información de la fuente, por ejemplo para la dimensión cliente, la clave primaria podría ser el Id de usuario o la cedula de identidad. También se deben crear índices sobre estas tablas, lo cuales estarán regidos por las jerarquías definidas en cada dimensión.

 Con esto podemos cerrar el tema relacionado a las fuentes de datos y el área de staging, en un próximo artículo hablaremos sobre el área de DWH siguiendo con la idea mostrada en este ejemplo.

Slowly Changing Dimensions

Por Rut Almoguera 

                                           

En el post anterior definimos el concepto de “Surrogate Key”, y mencionamos cuáles son las ventajas de su uso.  En esta ocasión vamos a explicar el concepto de “Slowly Changing Dimension”,  para lo cual es necesario tener claro el concepto de “Surrogate Key”.

“Slowly Changing Dimensions” o “Dimensiones de cambio lento en el tiempo” (o también SCD por sus siglas en inglés) se refiere a que las dimensiones NO permanecen estáticas en el tiempo, sino que sus atributos sufren cambios, y con frecuencia se desea guardar un registro de esos cambios.

En un DataWarehouse es necesario aplicar el concepto de SCD para hacer seguimiento de los cambios en los atributos de dimensión, con el fin de informar el estado correcto de los datos en un momento determinado. Es por esto que el SCD se define por cada atributo de la dimensión.

Para poder comprender este concepto, veamos primero los tres primeros tipos de SCD que existen:

Tipo 0 (SCD0): El atributo o columna de la dimensión no permite que se le hagan actualizaciones. Por ejemplo, imaginemos que tenemos una dimensión CLIENTES, en donde tenemos un campo que representa el número de seguridad social de la persona (número de CI en Venezuela, o el SSN en USA), dicho atributo “jamás” debería poder ser cambiado, por lo tanto se definiría como SCD0.

Tipo 1(SCD1): El atributo o columna si puede cambiar en el tiempo, pero solo se desea conocer el último valor, sin guardar un histórico de sus cambios.  Un ejemplo de este comportamiento, podría ser el número telefónico de un cliente, en donde es poco probable que se requiera conocer los números anteriores para contactarlo.

Tipo 2(SCD2): El atributo o columna en la dimensión puede sufrir cambios y se desea guardar un histórico de dichos cambios. Pues sus cambios son relevantes para el análisis del negocio.  Por ejemplo imaginemos el precio de un producto, del cual se desea conocer su valor en un momento determinado del tiempo y no solo saber el precio actual.  Es en este caso, diremos que dicho atributo tiene SCD2.

Para comprender como influye el uso de los “Surrogate Key” en la definición de los SCD de los atributos de la dimensión, veamos cuales son las técnicas que se utilizan para implementarlos:

Implementación SCD0:  En este caso, simplemente cualquier cambio que sufra el campo en la tabla origen, será ignorado por el ETL, y no se actualizara en la dimensión.

Implementacion SCD1:  En este caso, simplemente se sobrescribe el valor viejo del atributo con el valor nuevo (UPDATE).

Implementacion SCD2:  En este caso, dado que se desea guardar la historia, el registro anterior en la dimensión debe ser marcado como “NO VIGENTE” (bien sea con un campo extra de vigencia o con dos campos para la fechas de inicio de vigencia del registro y otro para la fecha de fin del registro), e insertar un registro nuevo en la tabla de la dimensión, usando la misma clave del negocio, pero con un “Surrogate Key” nuevo, marcando dicho registro como “VIGENTE”.

Para comprender mejor este caso vamos a imaginarnos que tenemos una dimensión de los productos (DIM_PRODUCTO), la cual tiene el precio de los productos, y que se desea guardar el cambio histórico del precio de los mismos.

La tabla de PRODUCTOS el día 2 de agosto del 2015 tiene los siguientes registros:

Codigo_Producto	Descripcion	Precio
SANDDAMLUJ0012	Sandalia de Dama de Fiesta	20.000,00
ZAPCABACUER0018	Zapato Caballero Cuero Cerrados	35.000,00
ZAPDAMTAC0039	Zapato Dama Tacón Corrido	15.000,00

En este caso, como se tendrá que manejar SCD2 para el campo Precio, se agregaron en la dimensión  dos campos que representan la fecha del inicio de vigencia del registro y la fecha de fin de vigencia del registro, quedando la tabla DIM_PRODUCTO como se muestra a continuación:

SK_Producto	Codigo_Producto	Descripcion	Precio	Fecha_Inicio_Vigencia	Fecha_Fin_Vigencia
1	SANDDAMLUJ0012	Sandalia de Dama de Fiesta	20.000,00	2015-08-02
2	ZAPCABACUER0018	Zapato Caballero Cuero Cerrados	35.000,00	2015-08-02
3	ZAPDAMTAC0039	Zapato Dama Tacón Corrido	15.000,00	2015-08-02

Como se puede observar en el ejemplo, el campo Fecha_Fin_Vigencia está vacío, pues todos los precios están vigentes. Pero ahora asumamos que el precio del último artículo (ZAPDAMTAC0039 ) sube a 25.000,00 el día 17 de agosto del 2015.  En la tabla PRODUCTO en el origen tendríamos lo siguiente:

Codigo_Producto	Descripcion	Precio
SANDDAMLUJ0012	Sandalia de Dama de Fiesta	20.000,00
ZAPCABACUER0018	Zapato Caballero Cuero Cerrados	35.000,00
ZAPDAMTAC0039	Zapato Dama Tacón Corrido	25.000,00

Ahora en la tabla de la dimensión, como ha cambiado el precio de un producto, se debe agregar una fila nueva, pues el campo precio es SCD2.  La tabla de la dimensión quedara como se muestra a continuación:

SK_Producto	Codigo_Producto	Descripcion	Precio	Fecha_Inicio_Vigencia	Fecha_Fin_Vigencia
1	SANDDAMLUJ0012	Sandalia de Dama de Fiesta	20.000,00	2015-08-02
2	ZAPCABACUER0018	Zapato Caballero Cuero Cerrados	35.000,00	2015-08-02
3	ZAPDAMTAC0039	Zapato Dama Tacón Corrido	15.000,00	2015-08-02	2015-08-17
4	ZAPDAMTAC0039	Zapato Dama Tacón Corrido	25.000,00	2015-08-17

Como podemos notar, se ha insertado una fila nueva con un “Surrogate Key” nuevo (4), con el nuevo precio, y la fila anterior (que tiene el “Surrogate Key” 3) ha sido pasada a NO VIGENTE, pues se lleno el campo “Fecha_Fin_De_Vigencia” con la fecha del 17 de agosto del 2015, que fue cuando dicho registro termino su vigencia.

Es importante destacar que las herramientas de ETL registran correctamente los hechos (FACTS) tomando en cuenta solamente los registros vigentes acorde a la fecha del evento.