Manual del estudiante de Ingeniería en Sistemas de UTN/Sistemas de Gestión II/Unidad Temática 3

Data Warehouse: Modelado Dimensional

Ejercicio 1

La base de datos del sistema de registros de operaciones de las plantas de producción de una industria láctea, que posee varias plantas ubicadas en distintas localidades, contiene los siguientes datos:

• Planta: IDplanta, nombre, localidad, departamento, provincia.
• Recibo leche: IDplanta, IDtambo, fecha, kilogramos, grasa butirométrica, sólidos totales.
• Producción: IDproducto, IDlote, IDplanta, IDalmacen, kilogramos, kilogramos de leche utilizada.
• Inventario (fin del día): IDproducto, IDlote, IDalmacen, fecha, kilogramos.
• Despacho: IDalmacen (origen), IDalmacen (destino), fecha, kilogramos, IDproducto, IDlote.
• Despacho exportación: IDproducto, IDlote, país, fecha, kilogramos.
• Producto: IDproducto, nombre, envase, rubro, sub-rubro.
• Almacén: IDalmacen, nombre, localidad, departamento, provincia.
• Lote: IDlote, fecha elaboración, fecha vencimiento.
• País: IDpais, nombre.

Requerimientos

• Kilogramos de leche procesados diariamente por producto, por localidad. (1)
• Rendimiento promedio diario (kg producto)/(kg leche) por producto, por planta. (2)
• Rendimiento promedio mensual (kg producto)/(kg sólidos totales) por producto, por planta. (3)
• Kilogramos diarios en inventario por rubro de producto, por almacén. (4)
• Número promedio de días útiles del inventario (fecha vencimiento – fecha actual) por producto, por almacén por día. (5)
• Kilogramos exportados por rubro de producto, por país destino, por año. (6)
• Distribución de los productos (según almacén destino) en kilogramos mensuales, por sub-rubro de producto, por localidad. (7)
• Porcentaje de la producción exportada anualmente por rubro de producto por país destino. (8)
• Kilogramos de leche recibida por departamento. (9)

Consigna

1. Desarrollar un modelo dimensional de datos que se ajuste a los requerimientos (no inferir otros requerimientos)
2. Describir cómo generaría los requerimientos a partir de las Tablas de Hechos, detallando como se harían las agregaciones de los hechos respectivos (suma, promedio, etc) y el nivel de agregación de cada dimensión.

El modelo dimensional

Hechos

Hecho	Requerimientos satisfechos
Leche procesada	1
Producción	2, junto con la leche procesada
Sólidos procesados	3, junto con la producción
Inventario al final del día	5
Días útiles del inventario	6
Distribución de los productos	7
Exportación	8, junto con la producción
Recepción de leche	9

En resumen, los hechos a registrar son:

• Leche procesada $[kg]$
• Producción $[kg]$
• Sólidos procesados $[kg]$
• Inventario al final del día $[kg]$
• Días útiles del inventario $[dia]$
• Distribución de los productos $[kg]$
• Exportación $[kg]$
• Recepción de leche $[kg]$

Los atributos correspondientes son:

• día, mes, año.
• producto, sub-rubro, rubro.
• localidad, departamento, provincia.
• planta.
• almacén.
• país de destino.

Estos atributos originan tablas dimensionales:

TD: Tiempo pk tiempo año mes día	TD: Producto pk producto rubro sub-rubro nombre	TD: Distribución geográfica pk dist_geo provincia departamento localidad
TD: Planta pk planta nombre	TD: Almacén pk almacén nombre	TD: País de destino pk pais_destino nombre

Los hechos originan tablas de hechos, que se relacionan con las tablas dimensionales. Los hechos se pueden agrupar, siempre y cuando puedan ser discriminados de acuerdo a las mismas dimensiones. Además, los snapshots de cada hecho deberían ser tomados en el mismo momento.

La leche procesada, la producción y los sólidos procesados deben discriminarse por día. La leche procesada deberá ser discriminada por planta, y lo demás por localidad, pero como la discriminación por plantas generará un conjunto sumarizable para discriminar por localidad (una planta no puede pertenecer a dos localidades), se pueden utilizar ambos atributos y sumarizar en este caso.

FT: Producción fk1 fk2 fk3 fk4 tiempo producto dist_geo planta Leche procesada $[kg]$ Producción $[kg]$ Sólidos procesados $[kg]$

FT: Inventario fk1 fk2 fk3 fk4 tiempo producto planta almacén Inventario al final del día $[kg]$ Días útiles del inventario $[dia]$

El resto de los hechos no puede agruparse, por lo tanto les corresponderá una tabla a cada uno.

FT: Distribución de los productos fk1 fk2 fk3 fk4 tiempo producto dist_geo almacen Cantidad $[kg]$

FT: Exportación fk1 fk2 fk3 tiempo producto pais_destino Cantidad exportada $[kg]$

FT: Recepción de leche fk1 fk2 tiempo dist_geo Cantidad $[kg]$

Generación de los datos requeridos

• Kilogramos de leche procesados diariamente por producto, por localidad (1):

FT: Producción
     $x_{1}$ = Leche procesada
  TD: Tiempo (día, mes, año)                   /* cuando aparece un atributo jerárquico, */ 
  TD: Producto (nombre, sub-rubro, rubro)      /* aparecerán todos los superiores en jerarquía*/
  TD: Distribución geográfica (localidad, departamento, provincia)
  TD: Planta (ALL)                             /* porque para la producción no hace falta */
                                               /* discriminar por planta */

• Rendimiento promedio diario (kg producto)/(kg leche) por producto, por planta (2):

FT: Producción
     $x_{1}$ = Leche procesada
     $x_{2}$ = Producción
        Rendimiento promedio = ${\frac {x_{2}}{x_{1}}}$
  TD: Tiempo (día, mes, año)
  TD: Producto (nombre, sub-rubro, rubro)
  TD: Distribución geográfica (ALL)
  TD: Planta (nombre)

• Rendimiento promedio mensual (kg producto)/(kg sólidos totales) por producto, por planta (3):

FT: Producción
     $x_{1}$ = Producción
     $x_{2}$ = Sólidos procesados
        Rendimiento promedio = ${\frac {x_{1}}{x_{2}}}$
  TD: Tiempo (mes, año)
  TD: Producto (nombre, sub-rubro, rubro)
  TD: Distribución geográfica (ALL)
  TD: Planta (nombre)

• Kilogramos diarios en inventario por rubro de producto, por almacén (4):

FT: Inventario
     $x_{1}$ = Cantidad
  TD: Tiempo (día, mes, año)
  TD: Producto (rubro)
  TD: Distribución geográfica (ALL)
  TD: Planta (ALL)
  TD: Almacén (nombre)

• Número promedio de días útiles del inventario (fecha vencimiento – fecha actual) por producto, por almacén por día (5):

FT: Inventario
     $x_{1}$ = Días útiles del inventario
  TD: Tiempo (día, mes, año)
  TD: Producto (nombre, sub-rubro, rubro)
  TD: Distribución geográfica (ALL)
  TD: Planta (ALL)
  TD: Almacén (nombre)

• Kilogramos exportados por rubro de producto, por país destino, por año (6):

FT: Exportación
     $x_{1}$ = Cantidad exportada
  TD: Tiempo (año)
  TD: Producto (rubro)
  TD: País de destino (nombre)

• Distribución de los productos (según almacén destino) en kilogramos mensuales, por sub-rubro de producto, por localidad (7):

FT: Distribución de los productos
     $x_{1}$ = Cantidad
  TD: Tiempo (mes, año)
  TD: Producto (sub-rubro, rubro)
  TD: Distribución geográfica (localidad, departamento, provincia)
  TD: Planta (ALL)
  TD: Almacén (nombre)

• Porcentaje de la producción exportada anualmente por rubro de producto por país destino (8):

FT: Exportación
     $x_{1}$ = Cantidad exportada
  TD: Tiempo (año)
  TD: Producto (rubro)
  TD: País de destino (nombre)

FT: Producción
     $x_{2}$ = Producción
        Porcetaje = ${\frac {x_{1}}{x_{2}}}*100$
  TD: Tiempo (año)
  TD: Producto (rubro)
  TD: Distribución geográfica (ALL)
  TD: Planta (ALL)

• Kilogramos de leche recibida por departamento. (9):

(como no especifica granularidad temporal, se muestra diariamente)

FT: Recepción de leche
     $x_{1}$ = Cantidad
  TD: Tiempo (día, mes, año)
  TD: Distribución geográfica (departamento, provincia)

Ejercicio 8

La base de datos del sistema de registros de operaciones de una empresa de industrialización de tomates para exportación, que posee plantas de producción ubicadas en distintas localidades de las provincias de Mendoza, San Juan, Río Negro y Neuquén, contiene los siguientes datos:

• Planta: nombre, localidad, departamento, provincia.
• Recibo de tomates: fecha, planta, kilos, proveedor, calidad: grado (A, B, o C), precio de compra por grado.
• Producción: fecha, planta, fecha de vencimiento, kilos, producto, kilos tomate utilizado y Mix de grado.
• Inventario: producto, planta, kilos, fecha elaboración.
• Exportación: código país, fecha, kilos, producto, precio de venta.
• Producto: nombre, tipo, familia, envase.
• Proveedor: nombre, establecimiento, departamento, provincia.

Requerimientos

• kg de tomate procesados por mes, por producto, por planta de producción (1).
• Calidad del tomate recibido: porcentaje promedio mensual (kg grado x / kg totales) por departamento de origen y por planta de producción (2).
• Costo promedio mensual del tomate por grado y por planta de producción (3).
• Inventario promedio mensual por producto, por planta (4).
• Porcentaje de tomate procesado en relación al total recibido por mes, por grado y por planta (5).
• Precio promedio mensual del tomate comprado por grado por departamento de origen (6).
• Kilogramos exportados por tipo de producto, por país destino (7).
• Número de días útiles del inventario (fecha vencimiento – fecha actual) por producto, por mes(8).

Consigna

1. Desarrollar un modelo dimensional de datos que se ajuste a los requerimientos (no inferir otros requerimientos).
2. Describir cómo generaría los requerimientos a partir de las Tablas de Hechos, detallando como se harían las agregaciones de los hechos respectivos (suma, promedio, etc) y el nivel de agregación de cada dimensión.
3. Definir las jerarquías de agregación y los respectivos niveles de agregación necesarias para dar respuesta a estos requerimientos de modo directo.

El modelo dimensional

Hechos

Hecho	Requerimientos satisfechos
Tomate procesado	1
Tomate recibido	2, 5 (junto con el tomate procesado)
Precio del tomate recibido	3, 6
Inventario	4
Exportación	7
Días útiles del inventario	8

En resumen, los hechos a registrar son:

• Tomate procesado $[kg]$
• Tomate recibido $[kg]$
• Precio del tomate recibido $[kg]$
• Inventario $[kg]$
• Exportación $[kg]$
• Días útiles del inventario $[kg]$

Los atributos correspondientes son:

• día, mes, año.
• grado de calidad.
• planta.
• departamento, provincia.
• país de destino.
• nombre, tipo, familia (producto).

Estos atributos originan tablas dimensionales:

TD: Tiempo pk tiempo año mes día	TD: Calidad pk calidad grado	TD: Distribución geográfica pk dist_geo provincia departamento
TD: Planta pk planta nombre	TD: País de destino pk pais_destino nombre	TD: Producto pk producto familia tipo nombre

Los hechos originan tablas de hechos:

FT: Tomate procesado fk1 fk2 fk3 fk4 tiempo producto planta calidad Cantidad $[kg]$	FT: Recepción de tomate fk1 fk2 fk3 fk4 tiempo planta dist_geo calidad Cantidad$[kg]$ Precio $[\$/kg]$
FT: Inventario fk1 fk2 fk3 tiempo producto planta Inventario al final del día $[kg]$ Días útiles del inventario $[dia]$	FT: Exportación fk1 fk2 fk3 tiempo producto pais_destino Cantidad exportada $[kg]$

Generación de los datos requeridos

• kg de tomate procesados por mes, por producto, por planta de producción (1):

FT: Tomate procesado
     $x_{1}$ = Cantidad
  TD: Tiempo (mes, año)
  TD: Producto (nombre, tipo, familia)
  TD: Planta (nombre)
  TD: Calidad (ALL)

• Calidad del tomate recibido: porcentaje promedio mensual (kg grado x / kg totales) por departamento de origen y por planta de producción (2):

FT: Tomate recibido
     $x_{g}$ = Cantidad
        Promedio de A = ${\frac {x_{A}}{x_{A}+x_{B}+x_{C}}}*100$
  TD: Tiempo (mes, año)
  TD: Distribución geográfica (departamento, provincia)
  TD: Planta (nombre)
  TD: Calidad (grado)

FT: Tomate recibido
     $x_{T}$ = Cantidad
        Promedio de C = ${\frac {x_{g}}{x_{T}}}*100$
  TD: Tiempo (mes, año)
  TD: Distribución geográfica (departamento, provincia)
  TD: Planta (nombre)
  TD: Calidad (ALL)
  

• Costo promedio mensual del tomate por grado y por planta de producción (3):

FT: Tomate recibido
     $x_{1}$ = Cantidad
     $x_{2}$ = Precio * Cantidad              /* Precio en este caso sería unitario */
        Precio promedio = ${\frac {x_{2}}{x_{1}}}*100$   /* Sería la sumarización de los precios de cada grupo */
                               /* dividido por la sumarización de las cantidades de cada grupo */
  TD: Tiempo (mes, año)
  TD: Distribución geográfica (ALL)
  TD: Planta (nombre)
  TD: Calidad (grado)

• Inventario promedio mensual por producto, por planta (4):

FT: Inventario
     $x_{1}$ = Cantidad
  TD: Tiempo (mes, año)
  TD: Producto (nombre, tipo, familia)
  TD: Planta (nombre)

• Porcentaje de tomate procesado en relación al total recibido por mes, por grado y por planta (5):

FT: Tomate procesado
     $x_{1}$ = Cantidad
  TD: Tiempo (mes, año)
  TD: Producto (ALL)
  TD: Planta (nombre)
  TD: Calidad (grado)

FT: Tomate recibido
     $x_{2}$ = Cantidad
        Porcentaje = ${\frac {x_{1}}{x_{2}}}*100$
  TD: Tiempo (mes, año)
  TD: Distribución geográfica (ALL)
  TD: Planta (nombre)
  TD: Calidad (grado)

• Precio promedio mensual del tomate comprado por grado por departamento de origen (6):

FT: Tomate recibido
     $x_{1}$ = Cantidad
     $x_{2}$ = Precio * Cantidad                   /* Precio en este caso sería unitario */
        Precio promedio = ${\frac {x_{2}}{x_{1}}}*100$   /* Sería la sumarización de los precios de cada grupo */
                                    /* dividido por la sumarización de las cantidades de cada grupo */
  TD: Tiempo (mes, año)
  TD: Distribución geográfica (departamento, provincia)
  TD: Planta (ALL)
  TD: Calidad (grado)

• Kilogramos exportados por tipo de producto, por país destino (7):

FT: Exportación
     $x_{1}$ = Cantidad exportada
  TD: Tiempo (año)
  TD: Producto (tipo, familia)
  TD: País de destino (nombre)

• Número de días útiles del inventario (fecha vencimiento – fecha actual) por producto, por mes(8):

FT: Inventario
     $x_{1}$ = Días útiles del inventario
  TD: Tiempo (mes, año)
  TD: Producto (nombre, tipo, familia)
  TD: Planta (ALL)