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