15.4. Modelo Fisico. Modelo normalizado y desnormalizado (Dimensional).

Partiendo del modelo lógico que hemos construido en la entrada anterior, vamos a pasar al modelo físico, pero haciendo un pequeño ejercicio. En primer lugar, vamos a construir el modelo físico normalizado (como si hubiesemos obviado las recomendaciones de Kimball). Podría quedar algo así:

Esquema Fisico Normalizado

Podemos ver que en la parte derecha de los campos el valor PK que indica que un campo es clave primaria de una tabla, o el valor FK para las claves foráneas (valores que han existir en otras tablas relacionadas). El modelo es algo orientativo de como puede quedar un esquema físico totalmente (o casi) normalizado. No he entrando en profundidad en todas las posibilidades de diseño para la construcción de los datos geográficos (población, provincia, región, pais), ni en la codificación de datos sociográficos del club de vinos (sexo, nivel estudios, estado civil, tipo de vivienda), que también podrían haber estado codificados con su consiguiente tabla normalizada (tupla código-descripción).

A continuación, lo vamos a desnormalizar, hasta llegar a nuestro esquema físico de estrella (tal y como diseñamos en la entrada anterior del blog).  El modelo físico final sería el siguiente:

Esquema Fisico Desnormalizado con campos para SCD y Audit

Podeís ver que hemos seguido las siguientes convenciones respecto a los nombres de las tablas y de los campos que las conforman:

• Tablas: la tablas de hechos empiezan por DWH (DWH_VENTAS para las ventas), mientras que las tablas de dimensiones lo hacen por DWD (DWD_TIEMPO para la dimensión tiempo).
• La dimensiones que son dimensiones conformadas o derivadas de otras (o como la dimensión Club de Vinos que es una dimensión outrigger) se llaman igual que la dimensión de la que dependen, añadiendo al final del nombre el nombre del subconjunto (la tabla para la dimensión mes se llama DWD_TIEMPO_MES).
• Campos: la nomenclatura de los nombres de campo incluye el sufijo ID para el caso de que el campo represente un código y el sufijo DESC para lo que son una descripción, nombre o texto explicativo del atributo.

Tambien podeís observar como hemos añadido en la tabla de la dimensión Producto, en la dimensión Cliente y en la dimensión outrigger del Club de Vinos campos adicionales que no habiamos enumerado hasta ahora. Por un lado, hemos añadido campos de auditoría,para registrar información del momento en el que se crean los registros en la base de datos y del momento de la ultima modificación (INSERT_DATE y UPDATE_DATE). Ademas, nos guardamos el nombre del proceso ETL que realiza la inserción (INSERT_PROC) o la ultima modificación (UPDATE_PROC). Con estos campos podremos realizar verificaciones o nos podrán ser utiles en el caso de realizar debug o corregir errores en los procesos de ETL.

Igualmente, hemos añadido otros campos para la gestión de las dimensiones lentamente cambiantes.Vamos a realizar una gestión SCD del tipo 2. Para realizar el versionado de los registros de la dimensión, tendremos los siguientes campos:

Campos adicionales para gestión de SCD

Cuando se produzca un cambio en los campos que hayamos definido como importantes, generaremos un nuevo registro en la tabla, construyendose una nueva clave subrogada (inventada) para el nuevo registro. Ademas, se llenaran los valores de los nuevos campos con los valores apropiados:

• SCD_FEC_INI: fecha en la que entra en vigor los valores del nuevo registro.
• SCD_FEC_FIN: fecha final.
• SCD_VERSION: numerador de la versión de datos. Valor correlativo que nos indicara el número de registros que hay para un determinado cliente o producto, por ejemplo.
• SCD_ACTIVO: flag que indica si el registro es el activo (por ejemplo, podremos tener varios registros para un cliente, con diferentes claves subrogadas, pero con el mismo código original del sistema operacional). Y solo uno de los registros estará activo.

Al insertar un nuevo registro para un cliente, habrá que actualizar igualmente los registros anteriores (para indicar que ya no será el registro activo y para limitarle la fecha final de validez). Todos los campos adicionales para la gestión de las dimensiones lentamente cambiantes tienen el prefijo SCD.

Para las dimensiones derivadas (dimensión Mes, Tipo de Cliente y Canal), hemos creado una tabla con los campos que forman el subconjunto de la dimension original. Podiamos haber creado una vista para definirlas, pero hemos preferido crearlas como tablas independientes para luego hacer los correspondientes procesos ETL de llenado a partir de la dimensión original.

Particionado de la tabla de hechos de ventas por Año.

Aunque en el esquema no lo vemos, hemos realizado un particionado de la tabla de hechos de ventas, creado una partición diferente para cada año. Tenemos 16 particiones, partiendo del año 2005 hasta el 2020. Mas que suficiente seguramente para lo que será la vida útil de la aplicación. La sentencia Sql para la creación de la tabla sería la siguiente:

/*==============================================================*/
/* Table: DWH_VENTAS                                            */
/*==============================================================*/
create table DWH_VENTAS  (
     FECHA_SK             INTEGER                         not null,
     CLIENTE_SK           INTEGER                         not null, 
     MATERIAL_SK          INTEGER                         not null,
     CENTRO_ID            INTEGER                         not null,
     PROMOCION_ID         INTEGER                         not null,
     PEDIDO_ID            INTEGER                         not null,
     UNIDADES             FLOAT                           not null,
     LITROS               FLOAT                           not null,
     PRECIO               FLOAT                           not null,
     IMPORTE_DTOS         FLOAT                           not null,
     IMPORTE_PROMO        FLOAT                           not null,
     COSTE_UNIT           FLOAT                           not null,
           constraint PK_DWH_VENTAS primary key (FECHA_SK, CLIENTE_SK, MATERIAL_SK, CENTRO_ID, PEDIDO_ID, PROMOCION_ID))
partition by range(fecha_sk)
      (partition year2005 values less than (20060101),
     partition year2006 values less than (20070101),
     partition year2007 values less than (20080101),
     partition year2008 values less than (20090101),
     partition year2009 values less than (20100101),
     partition year2010 values less than (20110101),
     partition year2011 values less than (20120101),
     partition year2012 values less than (20130101),
     partition year2013 values less than (20140101),
     partition year2014 values less than (20150101),
     partition year2015 values less than (20160101),
     partition year2016 values less than (20170101),
     partition year2017 values less than (20180101),
     partition year2018 values less than (20190101),
     partition year2019 values less than (20200101),
     partition year2020 values less than (20210101) );

Os dejo el link a las sentencias Sql para la creación de todos los objetos que forman el modelo. Os recuerdo que estoy trabajando con Oracle 10g. Para el modelado he utilizado la herramienta de Sybase Power Designer, aunque podría haber sido una alternativa trabajar con Oracle Sql Developer (y la herramienta Data Modeler que ofrece también Oracle para el modelado de datos).

15.3. Analisis de Dimensiones y Hechos. Modelo Lógico Final.

El modelo inicial sería el siguiente:

Modelo Lógico Inicial

Vamos a detallar cada una de las dimensiones, enumerando los atributos que las forman y construyendo el modelo lógico. Tendremos en cuenta todas las consideraciones vistas hasta ahora, verificando igualmente durante la identificación de los atributos e indicadores la información existente en los sistemas operacionales y distintos origenes de datos desde los que se llenara el DW.

Dimensión Tiempo.

Como todos sabemos, es la dimesión básica de cualquier modelo, pues el tiempo siempre es una de las perspectivas por las que queremos analizar la información. Los datos que forman esta dimensión los generaremos para un periodo de tiempo determinado (por ejemplo 10 o 20 años, para incluir periodos pasados y periodos futuros). Vamos a intentar generar el mayor número posible de atributos para esta dimensión para facilitar luego el análisis. Ademas, el número de componentes o registros de esta dimensión va a ser limitado y no hay problemas de tamaño en la BD (20 años con 1 registro por dia no son mas de 8000 registros en la tabla).

Incluira los siguientes atributos:

• Fecha Clave: fecha en la notacion yyyymmdd para procesarla como enteros. Será la clave de la dimensión. Convertiremos la fecha a este formato (seria una especie de clave subrogada).
• Fecha Id: fecha en la notación habitual de tipo date.
• Fecha Desc: fecha formateada en texto ( por ejemplo: 15 de abril de 2009).
• Dia de la semana Id: dia 1,2,3…7.
• Dia de la semana Desc:  dia de la semana en texto (Lunes, Martes, Miercoles,..).
• Dia del Mes Id: numero de dia de la fecha en el mes (dia 14, dia 28, dia 31).
• Dia del Año Id: numero de día de la fecha en el año (dia 234, dia 365).
• Semana Id: notacion año-semana para comparativas, cabeceras (YYYYSS, 200845). Es la clave que identifica cada semana.
• Semana Desc: semana del año en formato descriptivo ( Semana 45 de 2008).
• Numero de Semana Id: numero de semana donde se incluye la fecha (1, 12, 23). Desvinculada del año para otro tipo de análisis.
• Numero de Semana Desc: descripción en texto del numero de semana (Semana 1, Semana 23).
• Festivo: indicador de si la fecha es un festivo o no (S,N).
• Fin de semana: indicador si la fecha es fin de semana o dia entre semana (S,N).

• Mes Id: notación año-mes para comparativas,cabeceras (YYYYMM, 200811). Es la clave que va a identificar cada mes.
• Mes Desc: mes del año en formato descriptivo ( Enero de 2008, Abril de 2010, etc).
• Número de Mes Id: número del mes en el año (Enero = 1, Febrero = 2, etc). Desvinculado del año para otro tipo de análisis.
• Número de Mes Desc: descripción en texto del mes (Enero, Febrero, Marzo,…).

• Trimestre Id: notacion año-trimestre para comparativas, cabeceras (YYYYT, 20081). Es la clave que va a identificar cada trimestre.
• Trimestre Descripcion: trimestre en formato descriptivo ( 1 Trim 2008, 2 Trim 2008, etc.).
• Numero de Trimestre Id: trimestre donde se incluye la fecha (1,2,3 o 4). Desvinculado del año para otro tipo de análisis.
• Numero de Trimestre Desc: descripcion en texto del trimestre (1 Trimestre,2 Trimestre, etc).

• Año Id: año de la fecha, con 4 digitos. Es la clave que va a identificar cada año.
• Año Ant Id: año anterior al actual, con 4 digitos.

La dimensión Tiempo tendría la siguiente estructura, incluyendo todos los atributos que hemos enumerado:

Modelo Lógico - Dimensión Tiempo

Como vamos a tener una tabla de hechos a nivel de día (nivel de granuralidad) y otras dos tablas de hechos a nivel mes (diferente nivel), necesitaremos tener una tabla de dimensión para el mes. Para solucionar este problema, tenemos dos opciones:

• Construcción de una dimensión derivada o roll-up: creamos un subconjunto de la dimensión original, llevandonos todos los atributos que afectan al nivel de granuralidad o niveles superiores (por ejemplo, para el mes, nos llevamos también el trimestre y el año). Es un subconjunto estricto de la dimensión tiempo. Tendría el siguiente aspecto:

Dimensión derivada para el mes en la dimensión Tiempo

• Normalizar la dimensión y pasarla a copo de nieve: esta sería la otra opción. En la imagen siguiente, podeis ver como quedaría el esquema en el caso de hubieramos decidido utilizar esta solución. Como veis, hemos creado una dimensión para el mes (la que hos hace falta para la tablas de hechos mensuales), y ademas hemos sacado también fuera los trimestres y años (para el caso de que en el futuro hubiera otros tipos de análisis que lo requiriesen).

Modelo Lógico - Dimension Tiempo Normalizada

Para nuestro caso, nos quedamos con la opción 1, que es la recomendada por Kimball. Como vemos, hemos creado un set o subconjunto de la dimensión tiempo, y todos los atributos tienen los mismos nombres tanto en la dimensión original como en la dimensión derivada. Con esta filosofia podemos construir dimensiones conformadas que nos ayudan a implementar el concepto del data warehouse Bus. La técnica la podemos utilizar para cualquier caso que tengamos un diferente nivel de granuralidad. También se podría utilizar para el caso de que tengamos un datamart donde nos llevamos la dimensión con la misma estructura de atributos, pero limitando el número de valores (imaginar que tenemos varios canales de venta y para un análisis concreto, solo nos llevamos a la dimensión derivada los valores de clientes de uno de esos canales).

Concepto de Dimensión Roll-up o derivada

Dimensión Producto.

En esta dimensión incluiremos todos los atributos relacionados con los productos de venta o materiales, tales como las diferentes clasificaciones, propiedades, etc. En nuestro modelo, tendrán relevancia las siguientes:

• Material Clave: clave independiente (subrogada), que va a identificar a los diferentes productos de la compañia. Con el realizaremos la gestión de las dimensiones lentamente cambiantes para gestionar los cambios que se produzcan en los datos.
• Material Id: clave que identifica a cada uno de los productos vendidos en el sistema operacional.
• Material Desc : texto que describe las características del material (por ejemplo, Viña Pomela Reserva 2004 ).
• Familia Id: clasificación de los productos por naturaleza del producto (codigo nemotecnico que incluimos en el DW para facilitar el trabajo de los usuarios y para las relaciones con el sistema operacional).
• Familia Desc : texto que describe los diferentes valores del atributo familia ( 01 Blanco Joven, 02 Blanco Crianza, 03 Rosado, 04 Tinto Joven, 05 Tinto Barrica 6m, 06 Tinto Crianza, 07 Tinto Reserva, 08 Tinto Gran Reserva, 09 Tinto Seleccion, 20 Aceites, 30 Productos Gourmet, 40 Quesos). Los ultimos códigos nos indican que tambien se venden productos que no son vinos, y que tambien tendremos en cuenta en nuestros análisis.
• Denominación Origen Id: clave que identifica a cada una de las denominaciones de origen de las que vendemos productos.
• Denominación Origen Desc: texto que nos describe la denominación de origen a la que esta adscrita el vino. Los posibles valores para este atributo serían Ribera del Duero, Rueda, Toro, Somontano, Priorat, Yecla, La Mancha y Alicante. Igualmente, hay otros vinos que estan adscritos a la categoria Vinos de la Tierra, Vinos de Autor y Vinos sin DO.
• Varietal Id: clave que identifica a la variedad de uva (o sus combinaciones) utilizadas en la elaboración de un vino.
• Varietal Desc: descripción de la/s variedad/es de uva utilizadas en la elaboración. Por ejemplo, Monastrell, Tempranillo, Syrah, Merlot, Carbenet, Petit Verdot, Verdejo y las combinaciones de variedades.
• Target Id: clave que identifica el segmento destino del producto.
• Target Desc: descripción del segmento de mercado al que esta dirigido el producto ( Gran Consumo, Gourmet, Lujo, Jovenes, etc).
• Formato Venta Id: clave que identifica el formato de venta.
• Formato Venta Desc:  descripcion del formato de venta en el que se distribuye el producto. Cada vino (aun viniendo de la misma elaboración), tendrá un codigo de producto diferente según el formato en el que se venda. Ejemplos de valores para este atributo serán Granel, Tetra Brik, Botella, Pack, Bote, Paquete (estos ultimos para productos no vinicolas).
• Unidad Medida Id: código Iso de la unidad de medida utilizada para la venta de los materiales.
• Unidad Medida Desc: descripción de la unidad de medida (C/U, Litros, Cajas, etc).
• Linea de producto Id: clave que identifica a la otra clasificación de los productos. Se incluyen los códigos del sistema operacional para facilitar el trabajo de los usuarios.
• Linea de producto Desc: texto que describe cada una de las lineas de producto definidas. Por ejemplo 01 Vinos Mesa, 02 Bodega Seleccion, 03 Cavas, 04 Gran Bodega, 05 Vinos unicos, 10 Merchandising, 20 Ediciones y Publicaciones, 30 Productos Selectos. Nos permiten ver la clasificación de los materiales desde otra perspectiva.

Todos estos datos estan presente en nuestro sistema operacional. El modelo lógico para esta dimensión seria algo asi:

Modelo Lógico - Dimension Producto y Dimensión Promoción

Dimensión Promocion.

La dimensión promocion es una dimensión causal (a diferencia de todas las que hemos visto hasta ahora, que eran casuales). Esto implica que el contenido de esta dimensión afecta a los procesos de venta y no todo ha quedado registrado en los sistemas transaccionales (en el erp seguramente solo haya quedado identificada la promoción y los descuentos o ventajas que aplica), pero no otros muchos aspectos.

Los atributos de la dimensión promociones serán los siguientes:

• Promocion Id: código que identifica a la promoción. Es la clave de la dimensión.
• Promoción Desc: texto explicativo de la promoción.
• Tipo Promocion Id: Código que determina el tipo de promoción.
• Tipo Promocion Desc: Descripción del tipo de promoción (promocion propia, promoción estudio, promoción en cadena, promoción-concurso, etc.).
• Tipo Descuento Id: Código que determina el tipo de descuento asociado a la promoción.
• Tipo Descuento Desc: Descripción del tipo de descuento asociado a la promoción (descuento porcentual, descuento importe, 3×2, 2×1, regalo por volumen, etc.).
• Tipo Publicidad Id: Código que determina el tipo de publicidad utilizado en la promoción.
• Tipo Publicidad Desc: Descripción del tipo de publicidad asociado a la promoción (folletos, prensa, internet, etc.).
• Tipo Accion Id: Código que determina el tipo de accion comercial asociado a la promoción.
• Tipo Accion Desc: Acciones comerciales incluidas en la campaña (degustación, stand, promotores, vehiculos, etc).
• Fecha Inicio: fecha inicio de la promoción.
• Fecha Fin: fecha final de la promoción.

Dimensión Cliente.

La dimensión Cliente nos permitira el análisis desde la perspectiva de todos los atributos relacionados con el cliente, como código cliente, agrupador, responsable comercial, canal, tipo de cliente, pais, region, provincia, cod. postal y población. No van a permitir analizar quien nos compra, bajo que agrupaciones, clasificaciones y criterios geográficos (que van asociados a cada cliente).

• Cliente Clave: clave independiente (subrogada), que identifica a cada cliente de la organización. Es un numerador independiente que nos permitira realizar la gestión de las dimensiones lentamente cambiantes para gestionar los cambios que se produzcan en los datos.
• Cliente Id: codigo del cliente en el sistema operacional.
• Cliente Desc: datos identificativos de cada cliente. Por ejemplo, Mercadona Muchamiel I, Carrefour San Juan, etc.
• Agrupador Id: los clientes que pertenecen a una cadena (por ejemplo, grandes superficies), estan identificados individualmente (cada tienda, centro, etc). El agrupador es el código que agrupa a todas las tiendas o centros en un nivel superior (que podría coincidir con el nivel de facturación o contable). Por ejemplo, todos los clientes que realizan pedidos que pertenecen a Centro Comerciales Carrefour compartiran el código de agrupador, que los agrupa a todos con fines analíticos.
• Agrupador Desc: Datos identificativos del cliente agrupador. Para el ejemplo anterior, la descripción sería Centros Comerciales Carrefour.
• Responsable Comercial Id: código del responsable del departamento comercial que tiene asignado al cliente.
• Responsable Comercial Desc: nombre del responsable del departamento comercial que tiene asignado al cliente.
• Canal Id: canal de venta. Incluimos la codificación del sistema operacional para facilitar el uso.
• Canal Desc: descripciones de cada canal de venta (por ejemplo, 01 Venta Club, 02 Venta Directa, 03 Venta Intermediacion Propia, 04 Venta Representantes, etc.).
• Tipo Cliente Id: Código de la clasificacion de los clientes según su naturaleza. Es la codificación existente en el ERP.
• Tipo Cliente Desc: descripciones de los tipos de cliente ( 01 Cliente contado, 02 Cliente Club, 03 Minorista, 04 Restauracion, 05 Hoteles, 06 Tiendas Gourmet, 07 Tiendas Bodega, 08 Supermercados, 09 Grandes Superficies, 10 Hipermercados, 11 Centrales de compras, …).
• Codigo Nielsen Id: codigo asociado al cliente según la codificación Nielsen para estudios de mercado.
• Pais Desc.
• Region Desc.
• Provincia Desc.
• Cod Postal Id.
• Población Desc.

Ademas, nos guardaremos los datos de información sociográfica de los clientes (información del club de vinos, que utilizaremos para estudios concretos sobre este canal de venta o analisis de data mining). Los datos del club serán una Mini dimension o Dimension Outrigger. En estos casos, si esta permitido normalizar la dimensión en la metodología de Kimball.

• Club de Vinos Clave: clave independiente (subrogada) que identifica al cliente del club de vinos, definida para la gestión de cambios en la dimensión.
• Club de Vinos Id: clave del cliente del club de vinos en el sistema operacional.
• Fecha de nacimiento: nos permitira determinar la edad del cliente en cada momento.
• Estado Civil: estado civil del cliente (soltero, casado, divorciado, separado, viudo, pareja de hecho, etc).
• Numero Hijos: numero de hijos del socio.
• Tipo Vivienda: clasificacion del tipo de vivienda (en propiedad, alquiler, etc).
• Sexo.
• Email.
• Nivel Estudios: formación academica del socio (estudios primarios, medios, superiores, otros, etc).
• Nivel Ingresos: ingresos anuales.
• Fecha Antiguedad en el club: fecha de inscripción en el club.
• Tipo Pago: tipo de pago en las transacciones del club de vinos (tarjeta, transferencia, domiciliación bancaria, contra reembolso, etc).
• Nivel Club: clasificación del cliente dentro del club (Estandar, Oro, Platino).

Para esta dimensión, ocurre la misma casuistica que en la dimensión tiempo con el mes para los atributos tipo de cliente y canal pues tendremos, como ya vimos, una tabla de hechos a este nivel de granuralidad. Realizaremos la creación de una dimensión derivada para esos atributos. El modelo lógico para la dimensión cliente sería el siguiente:

Modelo Lógico - Dimension Cliente

Dimensión Logística.

En esta dimensión veremos la información desde la perspectiva logística de la venta, es decir, desde que lugares se sirven las ventas. Esta información nos puede permitir en el futuro optimizar nuestros procesos de distribucion. Los atributos de esta dimensión serán los siguientes:

• Centro Id: centro de distribución. Sera la clave de la dimensión logística.
• Centro Descripcion: Identificacion del centro logístico.
• Capacidad Almacenamiento: volumen de almacenamiento disponible en m3.
• Almacenes: numero de almacenes en el centro logistico/bodega.
• Tecnologia almacén: tipo de tecnologia usada para la gestión del almacén.

Dimensión Pedido de Venta (Dimensión Degenerada).

La dimensión pedido de venta, que es clave en la tabla de hechos, es una dimensión degenerada. Esto significa que la clave se identifica a si mismo y no tiene ningún atributo adicional, por lo que no es necesario construir una tabla de dimensión para ella. Solo lo vamos a utilizar para realizar analisis a nivel de transacción y no es necesario identificar ni concretar nada mas en el.

Indicadores de nuestro modelo (Hechos)

Los valores de negocio que querremos analizar para nuestras perspectivas o dimensiones serán los siguientes:

• Indicadores principales: unidades vendidas, litros vendidos, precio bruto unitario (sin descuentos), coste unitario, importe descuentos comerciales e importe descuentos promociones. Para los presupuestos, se incluyen los siguientes indicadores:  importe venta presupuestado y margen de beneficio presupuestado.
• Indicadores derivados: serán todos los indicadores que vamos a poder derivar de los indicadores principales. Algunos de los indicadores serán:
  • Importes: importe bruto venta (unidades vendidas x precio bruto unitario), importe total descuentos (importe descuentos comerciales + importe descuentos promociones), importe neto venta (importe bruto venta – importe total descuentos), coste total (unidades vendidas x coste bruto unitario), margen bruto total (importe bruto venta – coste total), margen bruto unitario (margen bruto total / unidades vendidas), margen neto total (importe neto venta – coste total), margen neto unitario (margen neto total / unidades vendidas),descuento comercial unitario (importe descuentos comerciales / unidades vendidas), descuento promocion unitario (importe descuentos promociones / unidades vendidas), descuento total unitario ( importe total descuentos / unidades vendidas), precio neto (importe neto venta / unidades).  A nivel de presupuestos, los indicadores calculados serán los siguientes: importe desviacion ventas sobre presupuesto (importe neto venta – importe venta presupuestado), desviación margen sobre presupuesto ( margen neto total – margen beneficio presupuestado).
  • Porcentajes: Porcentaje Margen Bruto, Porcentaje Margen Neto, Porcentaje Coste s/Bruto, Porcentaje Coste s/Neto, Porcentaje Dto Comercial, Porcentaje Dto Promociones, Porcentaje Dto Tota. A nivel de presupuestos, los indicadores serán Porcentaje Desviacion Ventas sobre Presupuesto y Porcentaje Desviación Margen sobre Presupuesto.

Habrá otros muchos indicadores que se podrán construir a partir o como calculo de los definidos. A la hora de construir el modelo físico, determinaremos que indicadores nos guardamos en la base de datos y cuales se calcularan en el momento de ejecutar los informes o herramientas (por ejemplo, los indicadores no sumarizables). También puede haber indicadores que nos interese tener calculados en la base de datos por temas de diseño o por evitar errores en los cálculos según el usuario que lo defina (sería algo asi como unificar un indicador).

Para el diseño del modelo lógico, solo vamos a considerar los indicadores básicos (a partir de los cuales se calculan el resto de indicadores). Ademas, vamos a tener tres tablas de hechos, determinadas por las diferentes granuralidades de la información (Hechos de Venta, Hechos de Presupuesto de Ventas por Tipo de Cliente y Mes y Hechos de Presupuesto de Ventas por Canal Cliente y Mes). Nuestro modelo lógico completo, incluyendo todas las dimensiones e indicadores, con las diferentes tablas de hechos sería el siguiente:

Modelo Lógico Completo - Esquema de Estrella

En el caso de que hubieramos elegido desnormalizar las dimensiones afectadas por la diferente granuralidad, hubieramos obtenido un modelo lógico como el que veis en la imagen siguiente (este módelo lo descartamos, ya que vamos a trabajar con el esquema de estrella).

Modelo Lógico Completo - Esquema de Copo de Nieve (Snowflake)

Ahora vamos a construir el modelo físico. Vamos a construir primero el modelo de forma normalizada, y luego lo pasaremos al modelo dimensional desnormalizado. Así haremos mas ilustrativo y didáctico el ejemplo. Para el módelo físico tendremos en cuenta también conceptos de particionado de tablas (vamos a aplicarlo a la tabla de hechos). Ademas, añadiremos campos adicionales a los de los atributos para auditoria  y para la gestión de las dimensiones lentamente cambiantes.

Indicar ademas que a la vez que hemos ido revisando cada uno de los atributos de las dimensiones y sus características en los sistemas origen, hemos completado una plantilla, que utilizaremos para determinar las características del modelo físico de nuestro Dw. En ella indicamos como vamos a tratar las dimensiones lentamente cambiantes según el cambio de valores en cada uno de los atributos. Nos servirá igualmente para la preparación de los procesos ETL. Podeis ver otro ejemplo de metodo para la identificación y documentación de los atributos de las dimensiones y las transformaciones a realizar para obtenerlos en una entrada anterior del blog.

Plantilla Identificación Atributos

15.2.Kimball vs Inmon. Ampliación de conceptos del Modelado Dimensional.

Como hemos visto en la entrada anterior del Blog, estamos utilizando la metodología desarrollada por Kimball (y su enfoque dimensional), para la construcción de nuestro DW. Aunque existen otras metodologias o enfoques para la construcción de un Data Warehouse, las mas importantes son la propia de Ralph Kimball y la definida por Will Inmon (y su enfoque Enterprise Warehouse o CIF).  Es ahí donde llegamos al que parece eterno dilema entre Kimball e Inmon.

Para entender las diferencias entre ambos enfoques, es necesario en primer lugar tener claro algun concepto, como es la diferencia entre Data Warehouse y Data Mart ( Josep Curto nos lo explica muy bien en su blog).

• Definición de Data Warehouse: Un Data Warehouse proporciona una visión global, común e integrada de los datos de la organización, independiente de cómo se vayan a utilizar posteriormente por los consumidores o usuarios. Normalmente en el almacén de datos habrá que guardar información histórica que cubra un amplio período de tiempo. Pero hay ocasiones en las que no se necesita la historia de los datos, sino sólo sus últimos valores, siendo además admisible generalmente un pequeño desfase o retraso sobre los datos operacionales. En estos casos el almacén se llama almacén operacional (ODS, Operational Data Store).
• Definición de Data Mart: Podemos entender un Data Mart como un subconjunto de los datos del Data Warehouse con el objetivo de responder a un determinado análisis, función o necesidad y con una población de usuarios específica. Al igual que en un data warehouse, los datos están estructurados en modelos de estrella o copo de nieve y un data mart puede ser dependiente o independiente de un data warehouse. Por ejemplo, un posible usos sería para el data mining.¿Qué diferencia existe entonces entre un data mart y un data warehouse? Su alcance. El data mart está pensado para cubrir las necesidades de un grupo de trabajo o de un determinado departamento dentro de la organización. Es el almacén natural para los datos departamentales. En cambio, el ámbito del data warehouse es la organización en su conjunto. Es el almacén natural para los datos corporativos comunes.

Teniendo en cuenta esto, vamos a intentar realizar un resumen de los aspectos mas importantes de cada una de las metodologías:

Paradigma Bill Inmon.

Bill Inmon ve la necesidad de transferir la información de los diferentes OLTP (Sistemas Transaccionales) de las organizaciones a un lugar centralizado donde los datos puedan ser utilizados para el analisis (sería el CIF o Corporate Information Factory). Insiste ademas en que ha de tener las siguientes características:

• Orientado a temas.- Los datos en la base de datos están organizados de manera que todos los elementos de datos relativos al mismo evento u objeto del mundo real queden unidos entre sí.
• Integrado.- La base de datos contiene los datos de todos los sistemas operacionales de la organización, y dichos datos deben ser consistentes.
• No volátil.- La información no se modifica ni se elimina, una vez almacenado un dato, éste se convierte en información de sólo lectura, y se mantiene para futuras consultas.
• Variante en el tiempo.- Los cambios producidos en los datos a lo largo del tiempo quedan registrados para que los informes que se puedan generar reflejen esas variaciones.

La información ha de estar a los máximos niveles de detalle. Los Dw departamentales o datamarts son tratados como subconjuntos de este Dw corporativo, que son construidos para cubrir las necesidades individuales de analisis de cada departamento, y siempre a partir de este Dw Central (del que también se pueden construir los ODS ( Operational Data Stores ) o similares).

Enfoque Inmon - DW Corporativo

El enfoque Inmon tambien se referencia normalmente como Top-down. Los datos son extraidos de los sistemas operacionales por los procesos ETL y cargados en las areas de stage, donde son validados y consolidados en el DW corporativo, donde ademas existen los llamados metadatos que documentan de una forma clara y precisa el contenido del DW. Una vez realizado este proceso, los procesos de refresco de los Data Mart departamentales obtienen la información de el, y con las consiguientes transformaciones, organizan los datos en las estructuras particulares requeridas por cada uno de ellos, refrescando su contenido.

La metodologia para la construcción de un sistema de este tipo es la habitual para construir un sistema de información, utilizando las herramientas habituales (esquema Entidad Relacion, DIS (Data Item Sets, etc). Para el tratamiento de los cambios en los datos, usa la Continue and Discrete Dimension Management (inserta fechas en los datos para determinar su validez para las Continue Dimension o bien mediante el concepto de snapshot o foto para las Discrete Dimension).

Al tener este enfoque global, es mas dificil de desarrollar en un proyecto sencillo (pues estamos intentando abordar el «todo», a partir del cual luego iremos al «detalle»).

Paradigma Ralph Kimball.

El Data Warehouse es un conglomerado de todos los Data Marts dentro de una empresa, siendo una copia de los datos transaccionales estructurados de una forma especial para el analisis, de acuerdo al Modelo Dimensional (no normalizado), que incluye, como ya vimos, las dimensiones de análisis y sus atributos, su organización jerarquica, asi como los diferentes hechos de negocio que se quieren analizar. Por un lado tenemos tablas para las representar las dimensiones y por otro lado tablas para los hechos (las facts tables). Los diferentes Data Marts estan conectados entre si por la llamada bus structure, que contiene los elementos anteriormente citados a traves de las dimensiones conformadas (que permiten que los usuarios puedan realizar querys conjuntos sobre los diferentes data marts, pues este bus contiene los elementos en común que los comunican). Una dimensión conformada puede ser, por ejemplo, la dimensión cliente, que incluye todos los atributos o elementos de analisis referentes a los clientes y que puede ser compartida por diferentes data marts (ventas, pedidos, gestión de cobros, etc).

Enfoque Kimball - Arquitectura Bus del DW

Este enfoque también se referencia como Bottom-up, pues al final el Datawarehouse Corporativo no es mas que la unión de los diferentes datamarts, que estan estructurados de una forma común a través de la bus structure. Esta caracteristica le hace mas flexible y sencillo de implementar, pues podemos construir un Data Mart como primer elemento del sistema de análisis, y luego ir añadiendo otros que comparten las dimensiones ya definidas o incluyen otras nuevas. En este sistema, los procesos ETL extraen la información de los sistemas operacionales y los procesan igualmente en el area stage, realizando posteriormente el llenado de cada uno de los Data Mart de una forma individual, aunque siempre respetando la estandarizacion de las dimensiones (dimensiones conformadas).

La metodología para la construcción del Dw incluye las 4 fases que vimos en la entrada anterior del blog, que son: Selección del proceso de negocio, definición de la granuralidad de la información, elección de las dimensiones de análisis e identificación de los hechos o métricas. Igualmente define el tratamiento de los cambios en los datos a través de las Dimensiones Lentamente Cambiantes (SCD).

Si quereis profundizar en cada una de las filosofias, incluyendo las similitudes y diferencias, os recomiendo leer la presentación realizada por Ian Abramson:

Ver este documento en Scribd

Ahora llega el momento de elegir cual de los enfoques es el mas apropiado para nuestro proyecto (suponiendo que aun no lo hubieramos hecho). En la entrada de blog de Jorge Fernández se planteo un interesante debate sobre la conveniencia de utilizar uno u otro enfoque. Podemos resumir que el enfoque Inmon es mas apropiado para sistemas complejos, donde ademas queremos asegurar su perdurabilidad y consistencia aunque cambien los procesos de negocio en la organización. Pero para pequeños proyectos, donde ademas queremos asegurar la usabilidad de los usuarios con un sistema facil de entender y el rapido desarrollo de la solución, el enfoque Kimball es mas apropiado.

En nuestro caso, vamos a realizar un DW departamental, que ademas es un proyecto piloto. Dado el ambito, y los recursos que se van a destinar a el, es mas conveniente utilizar el enfoque Kimball para el diseño del DW. El DW seria lo mas cercano a un datamart, y lo vamos a desarrollar intentando que las dimensiones esten conformadas (dentro del concepto de datawarehouse bus), con lo que dejaremos la puerta abierta a una ampliación posterior dentro el ámbito de la compañia, añadiendo nuevos cubos que utilizaran las dimensiones conformadas ya definidas.

Ademas de estos dos enfoques, existen otros de los que no hablaremos, como el Hybrid DW o el Federated DW, que utilizan una aproximación intermedia para la construcción del sistema.

Ampliación de Conceptos del Modelado Dimensional

Veamos algunos conceptos más sobre el modelado dimensional:

Dimensiones

Las dimensiones, como ya vimos son los diferentes puntos de vista por los que queremos analizar la información. Las dimensiones incluyen los diferentes atributos que queremos analizar, que ademas se estructuran de forma jerárquica, conforme a diferentes niveles de detalle. Las tablas de dimensiones se construyen incluyendo todos los atributos que la incluyen de una forma desnormalizada, con una clave que identifica el mínimo nivel de detalle. Podemos distinguir varios tipos de dimensiones:

• Dimensiones Normales: aquellas que agrupan diferentes atributos que estan relacionados por el ambito al que se refieren (todas las características de un cliente, los diferentes componentes de la dimensión tiempo, etc).
• Dimensiones Causuales: aquella que incluye atributos que pueden causar cambios en los procesos de negocio (por ejemplo la dimensión promoción en el proceso de negocio de ventas).
• Dimensiones Heterogeneas:  dimensiones que agrupar conjuntos heterogeneos de atributos, que no estan relacionados entre si.
• Dimensiones Roll-Up: es una dimensión que es un subconjunto de otra, necesarias para el caso en que tenemos tablas de hechos con diferente granuralidad (ver la entrada anterior del blog).
• Dimensiones Junk: dimension que agrupa indicadores de baja cardinalidad como pueden ser flags o indicadores.
• Dimensiones Role-playing:  cuando una misma dimensión interviene en una tabla de hechos varias veces (por ejemplo, la fecha en una tabla de hechos donde se registran varias fechas referidas a conceptos diferentes), es necesario reutilizar la misma dimension, pues no tiene sentido crear tantas dimensiones como usos se hagan de ella. Para ello se definen las dimensiones Role-playing. Podemos crear vistas sobre la tabla de la dimensión completa que nos permiten utilizarla varias veces o jugar con los alias de tabla. La misma dimensión juega un rol diferente según el sitio donde se utiliza.
• Dimensiones Degeneradas: son dimensiones que no tienen ningún atributo y por tanto, no tienen una tabla especifica de dimensión. Solo se incluye para ellas un identificador en la tabla de hechos, que identifica completamente a la dimensión (por ejemplo, un pedido de ventas). Nos interesa tener identificada la transacción (para realizar data mining, por ejemplo), pero los datos interesantes de este elemento los tenemos repartidos en las diferentes dimensiones (cliente, producto, etc).
• Mini dimensiones o Dimensiones Outrigger: conjunto de atributos de una dimensión que se extraen la tabla de dimensión principal pues se suelen analizar de forma diferente. El tipico ejemplo son los datos sociodemográficos asociados a un cliente (que se utilizan, por ejemplo, para el datamining).

Es necesario gestionar de una forma correcta los cambios que se producen en los atributos de las dimensiones (por ejemplo, el cambio de comercial o de canal de un cliente, el cambio de familia de un material, etc), que nos permitan realizar de una forma correcta el análisis histórico de los datos. Para ello se introduce el concepto de Dimensión Lentamente Cambiante (SCD), estableciendo varios metodos para su procesamiento (que tendran que ser tenidos en cuenta en los procesos ETL). Resumiendo, tenemos varios tipos de metodos para el tratamiento (ampliar información en el blog de Bernabeu Dario o en BI Facil):

• SCD Tipo 1: Sobreescribir: cuando hay un cambio en los valores de un atributo, sobrescribimos el valor antiguo con el nuevo sin registrar una historia. Esto significa perder toda la historia del dato, y cuando hagamos un análisis veremos la información histórica desde el punto de vista actual.
• SCD Tipo 2: Añadir fila: cuando hay un cambio, creamos un nuevo registro en la tabla. El nuevo registro tiene una nueva clave subrogada, de forma que una entidad de sistema operacional (por ejemplo, un cliente), puede tener varios registros en la tabla de la dimensión según se van produciendo los cambios. Estamos gestionando un versionado, que ademas puede incluir unas fechas para indicar los periodos de validez, numerador de registros o un indicador de registro activo o no.
• SCD Tipo 3: Añadir columna: cuando hay un cambio, nos guardamos el valor anterior en una columna distinta, actualizando el campo con el nuevo valor (para cada campo, tendremos una tupla valor anterior, valor actual). Solo nos vamos a guardar, por tanto, los dos ultimos valores.

Cada una de las dimensiones tiene una clave que identifica cada uno de los registros que la conforman. Para definir esta clave, podemos utilizar los mismos valores que se utilizan en los sistemas operacionales (con lo que nos estamos limitando a la forma en que estan definidos alli y seguramente estableciendo limitaciones para el futuro) o bien utilizar las llamadas Surrogated Keys (Claves Subrogadas), que son identificadores que nos inventamos en el Dw, que nos va a permitir optimizar las consultas sql y evitar las posibles limitaciones de la definicion de las claves existentes, desvinculandola totalmente de los sistemas origen, ademas del tratamiento de las SCD. Os recomiendo la lectura de la entrada del blog de BI Facil referente a este tema.

Hechos

Los hechos son los indicadores de negocio que dan sentido al análisis de las dimensiones. Las tablas de hechos incluyen los indicadores asociados a un proceso de negocio en concreto, ademas de las claves de las dimensiones que intervienen en dicho proceso, en el mínimo nivel de granuralidad o detalle. Podemos tener varios tipos de tablas de hechos, como describe muy bien otra vez Josep Curto:

• Transaction Fact Tables: representan eventos que suceden en un determinado espacio-tiempo. Se caracterizan por permitir analizar los datos con el máximo detalle. Reflejan las transacciones relacionadas con nuestros procesos de negocio (ventas, compras, inventario, contabilidad, etc).
• Factless Fact Tables: Son tablas que no tienen medidas y representan la ocurrencia de un evento determinado. Por ejemplo, la asistencia a un curso puede ser una tabla de hechos sin metricas asociadas.
• Periodic Snapshot Fact Tables: Son tablas de hecho usadas para recoger información de forma periódica a intervalos de tiempo regulares sobre un hecho. Nos permiten tomar una foto de la situación en un momento determinado (por ejemplo al final del dia, de una semana o de un mes). Un ejemplo puede ser la foto del stock de materiales al final de cada día.
• Accumulating Snapshot Fact Table: representan el ciclo de vida completo de una actividad o proceso, que tiene un principio y final. Suelen representar valores acumulados.
• Consolidated Fact Tables: tablas de hechos construidas como la acumulación, en un nivel de granuralidad o detalle diferente, de las tablas de hechos de transacciones.

Podemos distinguir diferentes tipos de medidas o indicadores, basadas en el tipo de información que recopilan así como su funcionalidad asociada (ver blog de Josep Curto):

• Métricas: valores que recogen el proceso de una actividad o los resultados de la misma. Esto medidas proceden del resultado de la actividad de negocio.
  • Métricas de realización de actividad (leading): miden la realización de un actividad. Por ejemplo, la participación de una persona en un evento.
  • Métricas de resultado de una actividad (lagging): recogen los resultados de una actividad. Por ejemplo, la cantidad de unidades vendidas.

• Indicadores clave: entendemos por este concepto, valores correspondientes que hay que alcanzar, y que suponen el grado de asunción de los objetivos. Estas medidas proporcionar información sobre el rendimiento de una actividad o sobre la consecución de una meta.
  • Key Performance Indicator (KPI): Indicadores clave de rendimiento. Más allá de la eficacia, se definen unos valores que nos explican en qué rango óptimo de rendimiento nos deberíamos situar al alcanzar los objetivos. Son métricas del proceso.
  • Key Goal Indicator (KGI): Indicadores de metas. Aquí podriamos incluir por ejemplo, el objetivo de rentabilidad del proceso de negocio de ventas.

Las medidas se pueden clasificar igualmente como aditivas, semiaditivas y no aditivas según si se pueden sumarizar a lo largo de todas las dimensiones, solo para algunas o para ninguna. Igualmente, las medidas son derivadas cuando se calculan a partir de los valores de otras medidas o indicadores.

Según si desnormalizamos  las tablas de dimensiones o no, tendremos un esquema de estrella (star) o copo de nieve (snowflaked). Kimball recomienda utilizar siempre la desnormalización total, pero esta claro que hay situaciones en las que no queda mas remedio que pasarnos al esquema copo de nieve (aunque solo sea para alguna dimensión).

Para terminar, si quereis realmente profundizar en el modelado dimensional y en las multiples variantes de situaciones que os podeis encontrar, os recomiendo la lectura del libro Advanced Data Warehouse Design, en formato electrónico.

15.1. Definición de Objetivos. Análisis de Requerimientos.

La Empresa

La compañia Bodegas Vinicolas SA (BVSA) nacio en la denominación de origen Rioja en 1970 y en la actualidad, tras varios procesos de compra y ampliación de instalaciones, dispone de bodegas en Ribera del Duero, Rueda, Toro, Somontano, Priorat, Yecla, La Mancha y Alicante. En su portfolio de productos hay graneles, vinos jovenes, crianzas, reservas y gran reserva, de diferentes variedades, con o sin denominación de origen  y con diferentes formatos de envasado ( garrafas, tetra brik, granel, botellas de diferentes tamaños, etc ). Cada bodega tiene su propio almacén desde el que se sirve la mercancia, aunque las ventas estan centralizadas en la Bodega de La Rioja, donde hay un Call Center que recoge todos los pedidos nacionales e internacionales, y desde el que se atiende a los clientes del Club de Vinos. Cada bodega puede tener sus propias referencias así como referencias producidas en las otras bodegas, para venta en la tiendas propias o para distribución.

La compañia trabaja con clientes de todos los tipos, desde tiendas Gourmets o especializadas en vinos, Restaurantes y Hoteles, Mayoristas, Grandes cadenas de Distribución e Hipermercados. En el caso de grandes clientes, se sirve directamente a los centros desde cada bodega, aunque hay un estudio para construir un centro logístico que recoga la producción de todas las bodegas y realize la distribución, bien directamente a los clientes o a los centros logísticos propios de cada uno de ellos. Igualmente, se venden vinos a traves de internet y venta por correo en un Club de Vinos que se ha montado con el soporte de un portal Web donde se pueden comprar las referencias, se hacen selecciones mensuales y se ofrece gran cantidad de información referente al mundo del vino a los clientes que forman parte de este club.

El Club de Vinos se quiere fomentar como un canal de venta directa por su gran potencial de crecimiento, y a partir del cual también realizar estudios especificos de mercado y lanzamiento de nuevos productos, asi como programas de fidelización.

La empresa es una empresa joven y apuesta por la tecnologia en todos los ambitos de actuación, desde el propiamente relacionado con su sector (maquinaria, recolección automatica, instalaciones modernas en las bodegas), como a nivel de sistemas de información, donde trabajan con el ERP de Sap.

Objetivos

La empresa  pretende desarrollar un proyecto de BI para sacar el mayor partido posible  a la información de ventas de la que dispone. El director comercial actua como sponsor de este proyecto, que ademas es un proyecto piloto dentro de la compañia y que se ampliara a otros departamentos en el caso de obtener de él el exito esperado.

La construcción del DW y el uso posterior de herramientas de BI se va a centrar en los procesos de negocio de ventas. Se pretende construir un Datamart departamental para ventas y marketing que será alimentado con la información proveniente de su sistema operacional.

El objetivo es disponer de toda la información referente a ventas, productos, clientes, logistica, promociones en un unico almacén de datos a partir del cual poder extraer información de las siguiente manera:

1) A nivel de reporting y consultas ad-hoc: se montara un sistema de informes para dar soporte a todas las necesidades de información que se cubren en la actualidad con el sistema información de ventas en Sap y con desarrollos personalizados en Abap.

2) Navegación dimensional por los datos utilizando herramientas OLAP, con el objetivo de descubrir tendencias, desviaciones, descubrir oportunidades, realizar seguimiento de previsiones y establecer un sistema de alertas e indicadores de control.

3) Preparacion de cuadros de mando para los diferentes niveles de actuación: bodegas, representantes de ventas, servicios centrales, dirección, etc. Por tanto, se construiran cuadros de mando integral, cuadros de mando operativos y tableros de control.

4) Establecer las bases para estudio de promociones,  analisis y datamining en los clientes del Club de Vinos: se piensa que el canal Internet es algo de gran futuro y se quieren sentar las bases para su explotación actual y futura.

Analisis de requerimientos

Siguiendo el método de Diseño Dimensional en 4 Estapas (The Datawarehouse Toolkit: The Complete Guide to Dimensional Modelling.2ed. Ralph Kimball y Margie Ross, pag.30)

Modelado Dimensional 4 Etapas - Kimball

Despues de varias reuniones con el responsable comercial, los responsables comerciales de cada bodega/delegación y el departamento de sistemas, y a partir de los requerimientos de información, se decide:

a) El ambito del proyecto será, como hemos indicado, los procesos de negocio relacionados con ventas. El objetivo es tener un mejor conocimiento de la casuistica de ventas, incluyendo las promociones, para poder analizar la información desde todas las perspectivas posibles de interes para el negocio. Se pretende realizar igualmente un seguimiento de las previsiones de venta, comparando la evolución con lo planificado para poder establecer las posibles medidas correctoras (via promociones, descuentos, etc). Para ello, también incluiremos la información de presupuestos.

b) Granularidad de la información: en un principio se piensa, a partir de los requerimientos de información, quedarnos en el detalle acumulado de ventas por dia, producto, cliente. Pero durante la revisión de requerimientos se amplia el ambito del proyecto con los estudios referentes al Club de Vinos y se decide incluir también en el DW la información referente a promociones, para poder explotarla posteriormente. Por eso se decide que el nivel de granularidad sea cada uno de  los pedidos de ventas, para poder reflejar en el las promociones y permitir luego estudios mas profundos a nivel de data mining. Obviando la sumarización, permitimos mas funcionalidades en el futuro. Como incoveniente, el numero de registros será mucho mayor, pero no es significativo en este proyecto, pues se trabaja con unos 2000 clientes, unas 500 referencias y no mas de 100 mil pedidos anuales.

Por otro lado, se va a incluir información de previsión de ventas, cuya granuralidad actual es a nivel de presupuesto mensual. En la compañia, se realiza a principio del ejercicio una estimación de ventas para todo el año, detallandose la información a nivel mensual. Ademas, esta estimación se realiza a nivel de canal de venta y por tipo de cliente. Se planifica el importe de ventas asi como el margen de rentabilidad objetivo (a conseguir).

c) Dimensiones: Para el proceso de negocio de ventas, las perspectivas por las que se quiere analizar la informacion en la empresa BVSA son las siguientes:

• Dimensión Tiempo: incluira los atributos dia, mes, año, semana, trimestre, semestre, dia semana, indicador de festivo e indicador de fin de semana.
• Dimensión Producto: incluira los atributos material, familia, denominacion origen, varietal, formato venta, unidad medida, litros por envase, linea de producto, target (segmento mercado destino). Las dimensiones familia y linea de producto son dos clasificaciones diferentes de los materiales.
• Dimensión Cliente: incluira todos los atributos relacionados con el cliente, como código cliente, agrupador (codigo que agrupa a varios clientes, para el caso de empresas asociadas o cadenas), responsable comercial, canal de venta, tipo de cliente, pais, region, provincia, cod. postal y población. Aquí incluiremos toda la información sociodemográfica de los clientes que nos permitirán posteriormente los estudios del Club de Vinos.
• Dimensión Logística: incluira los atributos relacionados con la distribucion. Los atributos serán centro y sus propiedades (numero almacenes, capacidad, etc).
• Dimensión Promociones: incluira el tipo de promoción, fechas de la promoción, dto. aplicado y toda la información de interes relacionada con la promoción (publicidad, cupones, carteleria, ubicacion, etc).

Para los presupuestos, las perspectivas se reducen, pues la granularidad de la información es diferente. Serán las siguientes:

• Dimensión Tiempo: mes, año, trimestre, semestre (determinado por el hecho que los presupuestos se realizan a nivel de mes).
• Dimensión Cliente: canal de venta y tipo de cliente.

Por tanto, cuando estemos realizando análisis de ventas que incluyan la información de presupuestos, las dimensiones y atributos por las que podremos analizar la información se verán reducidas a estas.

d) Hechos: los valores de negocio que vamos a querer analizar serán los siguientes:

• Indicadores principales: unidades vendidas, litros vendidos, precio bruto unitario (sin descuentos), coste unitario, importe descuentos comerciales e importe descuentos promociones. Para los presupuestos, se incluyen los siguientes indicadores:  importe venta presupuestado y margen de beneficio presupuestado.
• Indicadores derivados: serán todos los indicadores que vamos a poder derivar de los indicadores principales. Algunos de los indicadores serán:
  • Importes: importe bruto venta (unidades vendidas x precio bruto unitario), importe total descuentos (importe descuentos comerciales + importe descuentos promociones), importe neto venta (importe bruto venta – importe total descuentos), coste total (unidades vendidas x coste bruto unitario), margen bruto total (importe bruto venta – coste total), margen bruto unitario (margen bruto total / unidades vendidas), margen neto total (importe neto venta – coste total), margen neto unitario (margen neto total / unidades vendidas),descuento comercial unitario (importe descuentos comerciales / unidades vendidas), descuento promocion unitario (importe descuentos promociones / unidades vendidas), descuento total unitario ( importe total descuentos / unidades vendidas), precio neto (importe neto venta / unidades).  A nivel de presupuestos, los indicadores calculados serán los siguientes: importe desviacion ventas sobre presupuesto (importe neto venta – importe venta presupuestado), desviación margen sobre presupuesto ( margen neto total – margen beneficio presupuestado).
  • Porcentajes: Porcentaje Margen Bruto, Porcentaje Margen Neto, Porcentaje Coste s/Bruto, Porcentaje Coste s/Neto, Porcentaje Dto Comercial, Porcentaje Dto Promociones, Porcentaje Dto Tota. A nivel de presupuestos, los indicadores serán Porcentaje Desviacion Ventas sobre Presupuesto y Porcentaje Desviación Margen sobre Presupuesto.

De este analisis podemos ya construir el modelo conceptual preeliminar del DW de la empresa, que podría ser algo asi:

Modelo Conceptual

Con las dimensiones y hechos definidos, podriamos elaborar un modelo lógico inicial que tendría el siguiente aspecto:

Modelo Logico Inicial

En el momento de realizar una pausa y de realizar unas cuantas consideraciones sobre el modelado dimensional. Cuando empezamos a estudiar sobre la materia, encontramos tanto en los libros de referencia, páginas web/blogs o en los propios productos software, ejemplos sencillos del esquema de estrella (casí siempre en el ambito de las ventas). Este ejemplo básico, con una única tabla de hechos y diferentes dimensiones a su alrededor (dimensión cliente, dimensión producto, dimensión tiempo, etc), es un buen punto de partida para iniciarse en materia, pero la realidad de los procesos de negocio no se puede representar con un esquema tan sencillo. En nuestro ejemplo, por el solo hecho de añadir información de presupuestos, ya vemos que tenemos varias tablas de hechos. Ademas, el nivel de granularidad de las tablas es diferente, pues no tenemos la información al mismo nivel de detalle (entender que es imposible y ademas no aportaria nada, realizar un presupuesto de ventas a nivel de cada pedido realizado, para cada material, cliente). Es más lógico realizarlo solo por algunos aspectos importantes, que querremos analizar y para el que querremos verificar un cumplimiento de objetivos (como serán las ventas por canal, por tipo de cliente o como podia haber sido las ventas por familia de producto o por linea de product0).

Además, si fuesemos añadiendo mas funcionalidades a nuestro modelo, seguramente aun lo complicariamos mas y aparecerían mas tablas de hechos para representar otros aspectos. Puede igualmente que quisieramos añadir tablas de hechos agregados para incluir resumenes (por ejemplo a nivel de mes, o a nivel de familia de producto), para optimizar la ejecución de determinados análisis o simplemente para consolidar información histórica o mejorar el rendimiento. Estas nuevos hechos estarian igualmente en otro nivel de granuralidad, que no coincidiría con el nivel básico descrito o no incluiria todas las dimensiones o todos los atributos de estas.

En las recomendaciones que nos da Kimball para el diseño de un DW, se habla de que hemos de intentar llevar todos los hechos al máximo nivel de granuralidad. El tipico ejemplo de las cabeceras y lineas de un pedido de venta. Si hay un dato que esta en la cabecera del pedido (por ejemplo, un descuento global para el pedido), hemos intentar llevarlo a nivel de cada posición de pedido, y así conseguimos llevar toda la información al máximo nivel de detalle. Puede haber varios trucos para hacer esto (por ejemplo, podriamos haber llevado el presupuesto al máximo nivel de detalle en la dimensión tiempo asignandole la fecha del primer dia del mes y trabajando con esa fecha en la tabla de hechos de presupuestos en lugar de trabajar con el mes), pero eso no nos vale para la dimensión cliente o la del producto (o si lo hacemos no sería algo muy fino a nivel de diseño). En otros casos, es imposible llegar a este nivel de detalle, y no queda mas remedio que construir una nueva tabla de hechos con diferente granuralidad (aunque siempre compartiendo las dimensiones).

Además, en nuestro caso,la estructura de dimensiones definida no nos valdría (pues la clave de nuestra dimensión tiempo es el dia, y no el mes, como en la tabla de hechos de presupuestos). Para solucionar esto, Kimball propone la creación de subsets o subconjuntos de la dimension que llama «Dimension Roll-Up» o Derivada. En la imagen siguiente, podemos ver un ejemplo de creación de una dimensión de este tipo para crear un subconjunto de la dimensión tiempo relacionada con los meses (y con todos los atributos que estan en el nivel jerarquico superior, como pueden ser el trimestre, año, etc).

Dimension Roll-up (conformada)

La nueva dimensión (o subconjunto de la dimensión tiempo) ha de estar conformada. Esto quiere decir que ha de incluir los atributos de su nivel con la misma definición que la dimensión original. Esto nos va a permitir realizar luego análisis por esa dimensión juntando información de diferentes tablas de hechos (en nuestro caso, juntando la información de ventas y de presupuestos). En nuestro DW, en los procesos ETL construiremos una única tabla para la dimensión, y luego crearemos los subconjuntos a partir de esta unica tabla de partida, manteniendo asi la homogeneidad de la información. Puede haber varias formas de construir esta tabla, pero podría ser por ejemplo una nueva tabla o una vista (igual una vista materializada si estuvieramos en Oracle).

Ejemplo de dimensión tiempo en los Analytic Modules de Microstrategy

Otra posible forma de solucionar el problema sería el pasar nuestra esquema de estrella (star) a un esquema de copo de nieve (snowflake). Kimball solo lo recomienda en las llamadas dimensiones Outrigger (que veremos en la próximo entrada del blog). En la imagen, podeis ver una modelo donde se ha sacado de la dimensión producto la categoria. Esto nos permitiria utilizarla en una tabla de hechos con otro nivel de granularidad.

Esquema Diseño Copo de Nieve (Snowflake Schemas)

Esta ampliación del modelo lógico, que llevara a la existencia de multiples tablas de hechos y dimensiones, puede complicar la realización de las consultas. La mayoría de sistemas de Business Intelligence tienen sus analizadores de querys que son capaces de descubrir, según el tipo de consulta que vamos a realizar, las tablas de donde van a obtener la información y las sentencias Sql necesarias para obtener los resultados. Por eso hemos de ser cuidadosos con el diseño y tener en cuenta todas las recomendaciones vistas, pues un mal diseño acabara afectando al rendimiento y a los tiempos de ejecución de las consultas.

Antes de continuar con la ampliación de teoría del modelado dimensional, os recomiendo visualizar la magnifica presentación realiza por Manuel Torres Gil de la Universidad de Almeria, en la que se explica la metodología propuesta por Kimball para la construcción de DW (la que hemos utilizado nosotros). Esta en castellano y ella podeis ver todas las cosas que he tenido en cuenta para el analisis y el diseño de mi modelo.

En la siguiente entrada del Blog veremos las diferencias entre el enfoque Kimball y el del Inmon para la construcción de un Data Warehouse, así como algo de teoria con definiciones sobre los elementos que intervienen en el diseño de un DW, con una pequeña clasificación de los diferentes elementos, para continuar con el modelo lógico completo y la definición del modelo físico del DW.

Y os vuelvo a recomendar, igualmente,  la lectura de la serie de artículos realizada por BI Fácil sobre la construcción de un DW, y las interesantes entradas del blog de Chris Adamson, donde nos habla de las 10 cosas que no aprendiste de un esquema de estrella de ejemplo y las 10 cosas que deberiamos de saber sobre el esquema de estrella.