4. diseño de un almacén de datos

Data Warehousing y Data Mining 91

1. Introducción a los almacenes de datos: motivación definición y características.

2. Arquitectura de un sistema de almacén de datos.

3. Explotación de un almacén de datos: herramientas OLAP.

4. Diseño de un almacén de datos.

5. Sistemas ROLAP y MOLAP.

6. Mantenimiento de un almacén de datos.

Almacenes de datos (Data Warehouse)


4. Diseño de un almacén de datos

La visión multidimensional seguida por las herramientas de explotación de almacenes de datos (OLAP) ha inspirado los modelos y metodologías de diseño de este tipo de sistemas.

En la literatura se habla de “Bases de Datos Multidimensionales” y de “Diseño

Multidimensional”


Modelado multidimensional:

� en un esquema multidimensional se representa una actividad que es objeto de análisis (hecho) y las dimensiones que caracterizan la actividad (dimensiones).

� la información relevante sobre el hecho se representa por un conjunto de indicadores (medidas o atributos de hecho).

� la información descriptiva de cada dimensión se representa por un conjunto de atributos (atributos de dimensión).




� el modelado multidimensional se puede aplicar utilizando distintos modelos de datos (conceptuales o lógicos).

� la representación gráfica del esquema multidimensional dependerá del modelo de datos utilizado (relacional, ER, UML, OO, ...)



Metodología de diseño de almacenes de datos:

Cualquier metodología de diseño de almacenes de datos debe seguir las fases clásicas del diseño de bases de datos:

� Análisis

� Diseño

� Implementación



Diseño conceptual

Especificación de transacciones

Esquema conceptual

Universo de discurso

Recogida y análisis derequisitos

Requisitos de proceso Requisitos de información

EstáticaDinámica

Metodología de diseño de bases de datos:



Diseño físico

Esquema interno

Diseño lógico

Esquema lógico

Esp

ecífi

co p

ara

cada

SG

BD

SGBD disponible


Implementación de transacciones

Implementación

Creación BD


Esquema conceptual

Inde

pend

ient

e de

l SG

BD

Dinámica EstáticaDiseño conceptual



Diseño físico

Esquema interno

Diseño lógico

Esquema lógico(ROLAP, MOLAP)

ImplementaciónCreación del AD

Esquema conceptualmultidimensional

Diseño conceptual


Requisitos de consulta

Metodología de diseño de almacenes de datos:

Mod

elad

o m

ultid

imen

sion

al

ER, UML, ...



Diseño físico

Diseño lógico específico

Implementación

Diseño conceptual

Recogida y análisis derequisitos Análisis

Descripción del sistema de información de la organización (OLTP)

Requisitos de usuarios del AD

Esquema conceptual

Entidad-Relación

UML

…



Diseño físico


Implementación

Diseño conceptual


Diseño conceptual

Modelado multidimensional

Esquemas multidimensionales

(ER, UML, ..)




Estilo de modelado que se centra en la representación de la actividad objeto de interés (hecho) y en las dimensiones que la caracterizan (dimensiones).

Los modelos conceptuales clásicos (ER, UML,...) pueden usarse* con este enfoque o estilo multidimensional.* Existen muchas propuestas de extensión de estos modelos para adaptarse mejor a la filosofía multidimensional.



Modelado multidimensional con ER.


Producto

N

Ventas

1

N

Tiempo

1

unidades

Almacén

N

1

importe


Modelado multidimensional con ER.


N

VentasN

unidades

Nimporte

día

1

mes

trimestre

año

Tiempo

producto

categoría

departamento

1

Producto

ciudad

almacén 1

región Almacén


Modelado multidimensional con UML.


Producto

Ventas

importeunidades

Tiempo

1

Almacén

*

1

1

*

*

1 ≡ 1..1

* ≡ 0..*


Modelado multidimensional con UML.


Ventas

importeunidades

*

*

*

día

1

mes

trimestre

año

Tiempo

1producto

categoría

departamento

1Producto

1

ciudad

almacén1

regiónAlmacén


Esquema multidimensional en UML.


Hecho

medida1medida2

...

**

*

11

1

D1D2

D3 Dn

* 1




Hecho

medida1medida2

...

**

*

11

1..*

D1D2

D3

Dn

* 1

Algunos modelos multidimensionales aceptan

relaciones M:M entre el Hecho y algunas dimensiones.


Estructura de las dimensiones.

Ventas

importeunidades

*

*

*1

Almacén

1

D

1

D

almacén

nro_almacén

nombre

M2

tipo

ciudad

nombre

habitantes

región

nombre

habitantes

país

nombre

zona_ventas

nro_zona

nombre

1..1

1..1

1..1

1..1

1..*

1..*

1..*

1..*



jerarquía 1Estructura de las dimensiones.

Nivel0

id_nivel

Atr1

Atr2

...

Nivel11

id_nivel

...

....

id_nivel

...

Nivel1n

id_nivel

...

Nivel21

id_nivel

... ....

id_nivel

...

atributos de nivel

Una dimensión es un grafo dirigido, acíclico con un nivel raíz (Nivel0).

Una jerarquía es un camino en el grafo que parte del nivel raíz.

jerarquía 2

Nivel raíz(atributo identificador

de la dimensión)


Niveli

id_nivel

Atr1

Atr2

...

Nivel de dimensión

atributos de nivel

En un nivel de dimensión:

�atributos analíticos: sirven para establecer condiciones y criterios de agregación

• atributo identificador: jerarquías de agregación

• atributos de selección: definición de condiciones

�atributos descriptivos: completar los informes

Almacén

nro_almacén

tipo

nombre

M2

...

Nivel de dimensión

atributo de agregación

atributo de selección

atributo descriptivo



tipo=‘gran superficie’Importe total de ventas por almacén, para almacenes de tipo "gran superficie", indicando el nombre completo del almacén.

Almacén

nro_almacén

tipo

nombre

...

Nivel de dimensión

atributo de agregación

atributo de selección

atributo descriptivo


Ventas

importeunidades

1..1

0..*

SELECT nro_almacén, nombre, SUM (importe)

FROM Ventas V, Almacén A

WHERE V.nro_almacén=A.nro_almacén AND tipo=‘gran superficie’

GROUP BY nro_almacén

nro_almacén

tipo

nombre

... nro_almacén.....

importeunidades

Almacén

Ventas

Esquema Relacional

Esquema UML


Atributos analíticos:- evitar codificaciones

- dominios discretos

- utilizar rangos de valores en lugar de valores absolutos

Atributos descriptivos: sirven para completar los informes con información textual:

- de cualquier tipo

(descripción completa de un producto, nombre de un almacén, dirección de un almacén...)




Nivel0

id_nivel

Atr1

Atr2

...

Nivel i

id_nivel

...

Nivel i+1

id_nivel

...

Nivel1n

id_nivel

...

Nivel21

id_nivel

... ....

id_nivel

...

Nivel raíz

atributos de nivel

Los arcos de una jerarquía representan asociaciones

binarias de cualquier multiplicidad

Nota: Las jerarquías en una dimensión (grafo dirigido) se representan por la numeración de los niveles



Nivel0

id_nivel

Atr1

Atr2

...

Nivel i

id_nivel

...

Nivel i+1

id_nivel

...

Nivel1n

id_nivel

...

Nivel22

id_nivel

... ....

id_nivel

...

Nivel raíz

atributos de nivel

1..1

1..*

multiplicidad mas frecuente: relación jerárquica



Nivel i

id_nivel

...

Nivel i+1

id_nivel

...1..

completa

Nivel i

id_nivel

...

Nivel i+1

id_nivel

.....1

estricta

Nivel i

id_nivel

...

Nivel i+1

id_nivel

...1..

balanceada



Nivel i

id_nivel

...

Nivel i+1

id_nivel

...0..

no-completa

Categoría Departamento

Coñac

Vino

Servilletas

Pañuelos

Bisutería

Bebidas

Papel



Nivel i

id_nivel

...

Nivel i+1

id_nivel

.....*

no-estricta


Coñac

Vino

Servilletas

Pañuelos

Bebidas

Papel

Droguería



Nivel i

id_nivel

...

Nivel i+1

id_nivel

...0 ..

no-balanceada


Coñac

Vino

Servilletas

Pañuelos

Bebidas

Papel

Varios


Esquemas multidimensionales que comparten una dimensión a distinto nivel.


Hecho1

medida1medida2

...

*

*

1

1

D1

D2

Hecho2

medida1medida2

...

D1

D2

D3

Nivel 0

id_nivel

...

Nivel j

id_nivel

...

Nivel1n

id_nivel

...

1

1

1

**

*

**

1

1



Ventas

unidadesimporte

...

**

1

1

Suministros

unidadesimporte

...

día

...

mes

...

año

1

*

**

1

1

1..1

1..1

*

1

1..*

1..*


El desarrollo de la tecnología de almacenes de datos se ha caracterizado por:

- un temprano desarrollo industrial provocado por las demandas de los usuarios.

- el uso de metodologías de diseño centradas principalmente en los niveles lógico e interno. (la atención se ha centrado en mejorar la eficiencia en la ejecución de consultas)

Metodología de diseño basada en el modelo relacional: Modelo multidimensional de Kimball



Diseño físico

Esquema interno


Esquema lógico(ROLAP, MOLAP)

ImplementaciónCreación del AD

Esquema relacionalmultidimensional

Diseño conceptual


Requisitos de consulta

Metodología de diseño de almacenes de datos basada en el MR (modelo relacional):

Mod

elad

o m

ultid

imen

sion

al

MR



Método de diseño de almacenes de datos basado en el modelo relacional: modelo multidimensional de Kimball.

(nivel lógico)

esquema relacional compuesto de:

- 1 tabla de hechos

- n tablas de dimensiones

diseño multidimensional de Kimball

Esquema multidimensional

(esquema en estrella)

� tabla de hechos: actividad que es objeto del análisis

� tablas de dimensiones: dimensiones del análisis



esquema estrella (star schema)

tabla de hechos

tabla de dimensión

tabla de dimensión

tabla de dimensión

tabla de dimensión

tabla de dimensión

tabla de dimensión

CAj

CAj

CAj

CAj

CAj

CAj

medidas

visión multidimensional de los datos

¡ la

tabl

a de

hec

hos

se r

elac

iona

con

la ta

blas

de

dim

ensi

ones

a tr

avés

de

rela

cion

es 1

:M !



esquema copo de nieve(snowflake schema)

tabla de hechos

tabla de dimensión

tabla de dimensión

jerarquía de dimensión

tabla de dimensión

CAj

CAj

CAj

CAj

CAj

CAj

medidas

Extensión del esquema en estrella cuando las dimensiones se organizan en jerarquías de niveles de dimensión (normalizar las tablas de dimensiones)

CAj


CAjCAj




Pasos en el diseño conceptual del almacén de datos:

Paso 1. Elegir un “proceso” de la organización para modelar.

Paso 2. Decidir el gránulo (nivel de detalle) de representación del proceso.

Paso 3. Definir las dimensiones que caracterizan el proceso.

Paso 4. Decidir la información a almacenar sobre el proceso.



Para ilustrar los pasos de la metodología de diseño conceptual, se va a utilizar el modelo de Kimball.


Paso 1. Elegir un “proceso” de la organización para modelar.

Proceso: actividad de la organización soportada por unOLTP del cual se puede extraer información con elpropósito de construir el almacén de datos.

Pedidos (de clientes)

Compras (a suministradores)

Facturación

Envíos

Ventas

Inventario



Ejemplo: Cadena de supermercados.

Cadena de supermercados con 300 almacenes en la que se expenden unos 30.000 productos distintos.

Actividad: Ventas.

La actividad a modelar son las ventas de productos en los almacenes de la cadena.



Paso 2. Decidir el gránulo (nivel de detalle) de representación.

Gránulo: es el nivel de detalle al que se desea almacenarinformación sobre la actividad a modelar.

� El gránulo define el nivel atómico de datos en el almacénde datos.

� El gránulo determina el significado de las filas de la tabla de hechos.

� El gránulo determina las dimensiones básicas del esquema

• transacción en el OLTP

• información diaria

• información semanal

• información mensual. ....



id_dim1

id_dim2

id_dim3

...

id_dim n

....

(hechos)

tabla de hechos

tabla Dimensión 3

tabla Dimensión 1

tabla Dimensión 2

tabla Dimensión n




Gránulo: “se desea almacenar información sobre las ventasdiarias de cada producto en cada almacén de la cadena”.

Gránulo:

� define el significado de las filas de la tabla de hechos.

� determina las dimensiones básicas del esquema.

producto

día

almacén

ventas



Gránulo inferior: no se almacena información a nivel de línea de ticket porque no se puede identificar siempre al cliente de la venta lo que permitiría hacer análisis del comportamiento (hábitos de compra) de los clientes individuales.

Gránulo superior: no se almacena información a nivel semanal o mensual porque se perderían opciones de análisis interesantes: ventas en días previos a vacaciones, ventas en fin de semana, ventas en fin de mes, ....



En un almacén de datos se almacena información a unnivel de detalle (gránulo) fino no porque se vaya ainterrogar el almacén a ese nivel sino porque ello permiteclasificar y estudiar (analizar) la información desdemuchos puntos de vista.



producto

día

almacén

ventas

id_producto

id_fecha

id_almacén

.....

.....

......

tabla de hechos

la clave primaria* está formada por los identificadores de las dimensiones.

datos (medidas) sobre las ventas diarias de un producto en un almacén.

* pueden existir excepciones a esta regla general



Paso 3. Identificar las dimensiones que caracterizan el proceso.

� Dimensiones: dimensiones que caracterizan la actividad al nivel de detalle (gránulo) que se ha elegido.

Tiempo (dimensión temporal: ¿cuándo se produce la actividad?)

Producto (dimensión ¿cuál es el objeto de la actividad?)

Almacén (dimensión geográfica: ¿dónde se produce la actividad?)

Cliente (dimensión ¿quién es el destinatario de la actividad?)

� De cada dimensión se debe decidir los atributos (propiedades) relevantes para el análisis de la actividad.

� Entre los atributos de una dimensión existen jerarquías naturales que deben ser identificadas (Ej. día-mes-trimestre-año en la dimensiónTiempo)



id_dim1

....

tabla Dimensión 1


Por simplicidad se diseñarán esquemas en estrella, es decir las tablas de dimensiones contendrán todos los atributos de la dimensión.



definición de gránulo

dimensiones básicas

tiempo

producto

almacén

Nota: En las aplicaciones reales el número de dimensiones suele variar entre 4 y 15 dimensiones.



Dimensión Tiempo:

� dimensión presente en todo esquema multidimensional porque el AD contiene información histórica sobre la organización.

� aunque el lenguaje SQL ofrece funciones de tipo DATE, una dimensión Tiempo permite representar otros atributos temporales no calculables en SQL.

� se puede calcular de antemano

� atributos frecuentes:

– nro. de día, nro. de semana, nro. de año: valores absolutos del calendario juliano que permiten hacer ciertos cálculos aritméticos.

– día de la semana (lunes, martes, miércoles,...): permite hacer análisis sobre días de la semana concretos (ej. ventas en sábado, ventas en lunes,..).



� atributos frecuentes:

-día del mes (1..31): permite hacer comparaciones sobre el mismo día en meses distintos (ventas el 1º de mes).

- marca de fin de mes, marca de fin de semana : permite hacer comparaciones sobre el último día del mes o días de fin de semana en distintos meses.

- trimestre del año (1..4): permite hacer análisis sobre un trimestre concreto en distintos años.

- marca de día festivo: permite hacer análisis sobre los días contiguos a un día festivo.

- estación (primavera, verano..)

- evento especial: permite marcar días de eventos especiales (final de futbol, elecciones...)

� jerarquía natural:

día - mes - trimestre -año



Dimensión Producto:

� la dimensión Producto se define a partir del fichero maestro de productos del sistema OLTP.

� las actualizaciones del fichero maestro de productos deben reflejarse en la dimensión Producto (¿cómo?).

� la dimensión Producto debe contener el mayor número posible de atributos descriptivos que permitan un análisis flexible. Un número frecuente es de 50 atributos.

� atributos frecuentes: identificador (código en la organización), descripción, tamaño del envase, marca, categoría, departamento, tipo de envase, producto dietético, peso, unidades de peso, unidades por envase, fórmula, ...

� jerarquías: producto-subcategoría-categoría-departamento



Dimensión Almacén:

� la dimensión Almacén representa la información geográfica básica.

� en un proceso de integración como es la creación de un A.D, esta dimensión suele ser creada explícitamente recopilando información que sólo tiene sentido en el A.D y que no la tiene en un OLTP (número de habitantes de la ciudad del almacén, caracterización del tipo de población del distrito donde se encuentra el almacén, ...)

� atributos frecuentes: identificador (código en la organización), nombre, dirección, distrito, región, ciudad, país, teléfono, fax, tipo de almacén, superficie, fecha de apertura, fecha de la última remodelación, superficie para congelados, superficie para productos frescos, datos de la población del distrito, zona de ventas, ...

� jerarquías:

–almacén - distrito - ciudad - región - país (jerarquía geográfica)

–almacén - zona_ventas - región_ventas (jerarquía de ventas)



nro_almacén

nombre

dirección

distrito

región

ciudad

país

tlfno

fax

superficie

tipo_almacén

...

Almacén

día

semana

mes

año

día_semana

día_mes

trimestre

festivo

....

Tiempo

nro_producto

descripción

marca

subcategoría

categoría

departamento

peso

unidades_peso

tipo_envase

dietético

...

Producto



día

nro_producto

nro_almacén

...

...

...

Ventas

nro_almacén

nombre

dirección

distrito

región

ciudad

país

tlfno

fax

superficie

tipo_almacén

...

nro_producto

descripción

marca

subcategoría

categoría

departamento

peso

unidades_peso

tipo_envase

dietético

...

Almacén

Producto

día

semana

mes

año

día_semana

día_mes

trimestre

festivo

....

Tiempo




definición de gránulo

dimensiones básicas

D1

D2

Dn

id_dim 1

id_dim 2

id_dim 3

...

id_dim n

(medidas)

tabla de hechos

tabla Dimensión 3

tabla Dimensión 1

tabla Dimensión 2

tabla Dimensión n

...

Los identificadores de las dimensiones (básicas) identifican la tabla de hechos



id_dim 1

id_dim 2

id_dim 3

...

id_dim n

(medidas)

tabla de hechos

tabla Dimensión 3

tabla Dimensión 1

tabla Dimensión 2

tabla Dimensión n

Se pueden añadir nuevas dimensiones al conjunto de dimensiones inicial, para incluir nuevos puntos de vista en el análisis.

tabla Dimensión k



nro_producto

día

nro_almacén

id_promoción

ventas

Se desea incluir en el análisis el punto de vista de la promoción bajo la cual se ha realizado una venta.

Ventas

Si un producto, un día, en un almacén sólo puede ser ofertado con un tipo de promoción: la dimensión promoción está determinada por las dimensiones originales. El gránulo del esquema no ha variado.

Los identificadores de las dimensiones no constituyen el identificador de la tabla de hechos



nro_producto

día

nro_almacén

nro_promoción

ventas

Se desea incluir en el análisis el punto de vista de la promoción bajo la cual se ha realizado una venta.

Ventas

Si un producto, un día, en un almacén puede ser ofertado con varios tipos de promociones: las cuatro dimensiones son independientes entre sí. El gránulo del esquema ha variado.

Los identificadores de las dimensiones constituyen el identificador de la tabla de hechos


Paso 4. Decidir la información a almacenar sobre el proceso.

Hechos: información (sobre la actividad) que se desea almacenar en cada fila de la tabla de hechos y que será el objeto del análisis.

Precio

Unidades

Importe

....

Nota: algunos datos que en el OLTP coincidirían con valores de atributos de dimensiones, en el almacén de datos pueden representar atributos de hechos. (Ejemplo: el precio de venta de un producto).




Gránulo: “se desea almacenar información sobre las ventasdiarias de cada producto en cada almacén de la cadena”.

– importe total de las ventas del producto en el día

– número total de unidades vendidas del producto en el día

– número total de clientes distintos que han comprado el producto en el día.



día

nro_producto

nro_almacén

importe

unidades

nro_clientes

Ventas

nro_almacén

nombre

dirección

distrito

región

ciudad

país

tlfno

fax

superficie

tipo_almacén

...

nro_producto

descripción

marca

subcategoría

categoría

departamento

peso

unidades_peso

tipo_envase

dietético

...

Almacén

Producto

día

semana

mes

año

día_semana

día_mes

trimestre

festivo

....

Tiempo



Diseño físico


Implementación

Diseño conceptual


Diseño lógico

traducción del esquema conceptual multidimensional

(ER, UML, MR, ..)

Esquema relacional (ROLAP)

Esquema multidimensional (MOLAP)



Diseño físico


Implementación

Diseño conceptual


Met

odol

ogía

de

Kim

ball

traducción directa

esquema relacional (ROLAP)


(MR)

esquema relacional multidimensional

Se asumirá que el SAD es un sistema ROLAP, ya que es el caso mas frecuente.


Diseño físico


Implementación

Diseño conceptual


Mod

elad

o m

ultid

imen

sion

al

traducción

esquema relacional (ROLAP)


(ER, UML, OO, ...)

esquema conceptual multidimensional (ER, UML, OO, ..)




Hecho

medida1medida2

...

**

*

1 1

1

D2

D3

Dn

* 1

Nivel0

id_nivel

...

....

id_nivel

...

Nivel1n

id_nivel

...

clase de hecho

tabla de hechos

clase de nivel de dimensión

tabla de nivel

asociación

(1:M)

clave ajena

traducción



CAjCAj

CAj

D2

D3

Dn

id_nivel

...

id_nivel

...

id_nivel

...

CAj

CAj

CAj

id_dim1

id_dim2

..

id_dimn

medidas

Nivel0

Nivel..

Nivel1n

Hechos


Orientaciones de diseño lógico (ROLAP):

� uso de claves sin significado.

– en un almacén de datos debe evitarse el uso de las claves del sistema operacional.

– las claves de las dimensiones deben ser generadas artificialmente: claves de tipo entero (4 bytes) son suficiente para dimensiones de cualquier tamaño (232 valores distintos).

– la dimensión TIEMPO debe tener también una clave artificial.

Inconvenientes del uso de las claves del sistema operacional:

� en el OLTP se puede decidir reutilizar valores de la clave no utilizados actualmente en la organización.

� en el OLTP se puede decidir cambiar la codificación de las claves.

� reduce el tamaño de la tabla de hechos



id_almacén

nro_almacén

nombre

dirección

distrito

región

ciudad

país

tlfno

fax

superficie

tipo_almacén

...

Almacén

id_fecha

día

semana

mes

año

día_semana

día_mes

trimestre

festivo

....

Tiempo

id_producto

nro_producto

descripción

marca

subcategoría

categoría

departamento

peso

unidades_peso

tipo_envase

dietético

...

Producto



id_fecha

id_producto

id_almacén

importe

unidades

nro_clientes

Ventas

id_almacén

nro_almacén

nombre

dirección

distrito

región

ciudad

país

tlfno

fax

superficie

tipo_almacén

...

id_producto

nro_producto

descripción

marca

subcategoría

categoría

departamento

peso

unidades_peso

tipo_envase

dietético

...

Almacén

Producto

id_fecha

día

semana

mes

año

día_semana

día_mes

trimestre

festivo

....

Tiempo



Orientaciones de diseño lógico (ROLAP):

� evitar normalizar.

Si se ha definido una única tabla de dimensión para cada dimensión identificada en el análisis (esquema en estrella), es frecuente que entre el conjunto de atributos de la tabla aparezcan dependencias funcionales que hacen que la tabla no esté en 3ª F.N.

Evitar normalizar:

� el ahorro de espacio no es significativo

� se multiplican los JOIN durante las consultas.



id_almacén

nro_almacén

nombre

dirección

distrito

región

ciudad

país

tlfno

fax

superficie

tipo_almacén

...

id_producto

nro_producto

descripción

marca

subcategoría

categoría

departamento

peso

unidades_peso

tipo_envase

dietético

...

AlmacénProducto

id_fecha

día

semana

mes

año

día_semana

día_mes

trimestre

festivo

....

Tiempo

3ª F.N

¿normalizar?



id_producto

nro_producto

descripción

marca

subcategoría

categoría

departamento

peso

unidades_peso

tipo_envase

dietético

...

Producto• 30.000 productos• 30 departamentos

• ∼ 300 categorías (30 x10)

• ∼ 3000 subcategorías (300 x10)

Coca-Cola 33cl.→ Refresco-cola → Refrescos → Bebidas

• “el nombre de un departamento (20 bytes) se repite aproximadamente 1000 veces en la tabla Producto”.

•“el nombre de una categoría (20 bytes) se repite aproximadamente 100 veces en la tabla Producto”.

•“el nombre de una subcategoría (20 bytes) se repite aproximadamente 10 veces en la tabla Producto”.



id_producto

nro_producto

descripción

marca

id_subcat

peso

unidades_peso

tipo_envase

dietético

...

Productoid_subcat

subcategoría

id_cat

id_cat

categoría

id_dpto

id_dpto

departamento

Departamento

Categoría

Subcategoría

3ª F.N


esquema en copo de nieve


• “el nombre de un departamento (20 bytes) se repite aproximadamente 1000 veces en la tabla Producto”.

•“el nombre de una categoría (20 bytes) se repite aproximadamente 100 veces en la tabla Producto”.

•“el nombre de una subcategoría (20 bytes) se repite aproximadamente 10 veces en la tabla Producto”.

ahorro de espacio: ∼ 20.000 bytes

Tamaño frecuente de una tabla Producto: ∼ 3 GB




CAjCAj

CAj

D2

D3

Dn

id_nivel

...

id_nivel

...

id_nivel

...

CAj

CAj

CAj

id_dim1

id_dim2

..

id_dimn

medidas

Nivel0

Nivelj

Nivel1n

Hechos

desnormalizar

D1

CAj


Ventajas de la desnormalización:

� evitar la concatenación (join) de tablas durante las consultas

� sencillez de uso

Inconvenientes de la desnormalización:

� redundancia (ocupación de espacio)



Alternativas:

� dimensión desnormalizada: todos los atributos de la dimensión están incluidos en una única tabla.

�dimensión normalizada: cada tabla de nivel en la dimensión incluye el identificador interno (clave ajena) del nivel "padre" en cada jerarquía a la que pertenece (puede incluir también el identificador en la organización).

� dimensión moderadamente normalizada: cada tabla de nivel incluye los identificadores internos de todos los "antecesores" en cada jerarquía a la que pertenece (puede incluir también los identificadores en la organización).




Esquema en estrella

Dimensión Producto desnormalizada

id_producto

nro_producto

descripción

marca

subcategoría

categoría

departamento

peso

unidades_peso

tipo_envase

dietético

...

Producto


id_producto

nro_producto

descripción

marca

id_subcat

peso

unidades_peso

tipo_envase

dietético

...

Productoid_subcat

subcategoría

id_cat

id_cat

categoría

id_dpto

id_dpto

departamento

Departamento

Categoría

Subcategoría

3ª F.N


Esquema en copo de nieve

Dimensión Producto normalizada


id_producto

nro_producto

descripción

marca

id_subcat

subcategoría

peso

unidades_peso

tipo_envase

dietético

...

Productoid_subcat

subcategoría

id_cat

categoría

...

id_cat

categoría

id_dpto

departamento

...

id_dpto

departamento

...

Departamento

Categoría

Subcategoría

3ª F.N



Dimensión Producto normalizada


id_producto

nro_producto

descripción

marca

id_subcat

id_cat

id_dpto

peso

unidades_peso

tipo_envase

dietético

...

Producto id_subcat

subcategoría

id_cat

id_dpto

...

id_cat

categoría

id_dpto

...

id_dpto

departamento

...

Departamento

Categoría

Subcategoría



Dimensión Producto moderadamente normalizada


id_producto

nro_producto

descripción

marca

id_subcat

subcategoría

id_cat

categoría

id_dpto

departamento

peso

unidades_peso

tipo_envase

dietético

...

Productoid_subcat

subcategoría

id_cat

categoría

id_dpto

departamento

…

id_cat

categoría

id_dpto

departamento

…

id_dpto

departamento

…

Departamento

CategoríaSubcategoría



Dimensión Producto moderadamente normalizada


Diseño físico


Implementación

Diseño conceptual


Diseño físico

consideraciones de almacenamiento físico

Esquema físico en el SGBD disponible



Diseño físico: depende del tipo de SAD (MOLAP o ROLAP).

Sistemas MOLAP

� disponen de estructuras de almacenamiento específicas (matrices multidimensionales) y técnicas de compactación de datos que favorecen el rendimiento del almacén.

Sistemas ROLAP

� se implementan sobre tecnología relacional, pero disponen de algunas facilidades para mejorar el rendimiento (índices de mapas de bits, índices de JOIN, técnicas de particionamiento, ...).


Objetivo: obtener un buen tiempo de respuesta en la evaluación de las consultas.


Diseño físico: depende del tipo de SAD (MOLAP o ROLAP).

Las decisiones de diseño físico deben tener en cuenta la estrategia de optimización de consultas seguida en el SGBD disponible.


Objetivo: obtener un buen tiempo de respuesta en la evaluación de las consultas.


Diseño físico en un sistema ROLAP:

Plan de evaluación de una consulta*:

1. Evaluar las condiciones de la consulta sobre cada dimensión para obtener un conjunto de identificadores.

2. Combinar (producto cartesiano) los identificadores de todas las dimensiones obtenidos en el paso anterior.

3. Buscar las filas de la tabla de hechos correspondientes a estos identificadores.

4. Agrupar las filas y agregar las medidas.

* Este plan de evaluación de consultas presupone que el sistema ROLAP aplica una estrategia de optimización específica para esquemas multidimensionales (esquemas en estrella): star schema optimization.



34 12-12-98 12-98 lunes 4-98 1998 no

56 10-6-97 6-97 miércoles 2-97 1997 si

12 10-1-99 1-99 sábado 1-99 1999 no

34 12-8-98 8-98 lunes 3-98 1998 si

41 1-6-97 6-97 sábado 2-97 1997 no

Tiempo..... sábado domingo

0 0

0 1

1 0

0 0

1 0

…

Índice -día de la semana

12

41

Identificadores

(Tiempo)



día='sábado'


3 almacén1 Valencia Comunidad Valenciana España

34 almacén5 León Castilla-León España

21 almacén7 Valencia Comunidad Valenciana España

....

AlmacénValencia [•, • ]

León [• ]…

Índice -ciudad

3

21

Identificadores

(Almacén)



ciudad='Valencia'



3

21

Identificadores

(Almacén)

12

41

Identificadores

(Tiempo)

X

12 3

12 21

41 3

41 21

2. Combinar (producto cartesiano) los identificadores de todas las dimensiones obtenidos en el paso anterior.


id_fecha id_alm id_prod importe....

12 3 14 100000

.....

12 21 34 50000

.....

12 3 99 65000

Índice Tiempo-Almacén

12 3 [•, • ]

......

12 21 [• ]

.....

Ventas

RIDs

12 3

12 21

41 3

41 21


3. Buscar las filas de la tabla de hechos correspondientes a estos identificadores.


.......

12 3 14 100000

.....

12 21 34 50000

.....

12 3 99 65000

Ventas


.......

12 3 165000

.....

12 21 50000

.....

Informe

4. Agrupar las filas y agregar las medidas.



Recursos para el diseño físico en sistemas ROLAP:

� Diseño de índices

�índices en árbol B

�índices de mapa de bits

�índices de JOIN

� Particionamiento de la tabla de hechos

� particionamiento vertical (por columnas)

� particionamiento horizontal (por filas)


Diseño de índices:

Tabla de hechos:

�1 índice sobre la clave primaria (compuesta)

� índices sobre subconjuntos de componentes de la clave primaria.(utilizados frecuentemente en las búsquedas)

Tablas de dimensión:

� índices sobre los atributos usados frecuentemente para establecer restricciones sobre las dimensiones.



Diseño de índices:

Los índices compuestos deben diseñarse ordenando los campos en el índice de forma que favorezcan la evaluación de las consultas más frecuentes.



Estimación del tamaño del AD:

Hipótesis de trabajo:

- los atributos identificadores (clave) son de 4 bytes.

- los atributos (numéricos) de la tabla de hechos (medidas) son de 4 bytes

- en tablas de hechos con 4 o menos medidas el tamaño del índice maestro (sobre la clave primaria) suele tener un tamaño entre el 60% y el 80% del tamaño de la tabla de hechos.

- en tablas de hechos que tienen de 15 a 20 medidas el tamaño del índice maestro suele tener un tamaño entre el 30% y el 50% del tamaño de la tabla de hechos.

- el tamaño de las tablas de dimensiones y sus índices asociados es mucho menor que el tamaño de la tabla de hechos.



Estimación del tamaño del AD:


Tabla de hechos (sin índices ni datos agregados)

� Tiempo: información almacenada durante 2 años (2x365 días=730 días)

� Almacén: 300 almacenes.

� Producto: 30000 productos (10% se venden diariamente en un almacén).

3000 x 730 x 300= 657 millones de tuplas

657millones de tuplas x 6 atributos x 4 bytes ∼ 16 GB



Tecnología actual de Almacenes de Datos:

La tecnología actual está preparada para gestionar almacenes de datos con:

�nro. tuplas de tabla de hechos < 1billón de tuplas

�tamaño de la tabla de hechos < 100GB.



Sectores con almacenes de datos muy grandes:

� Compañías telefónicas: análisis de llamadas.

� Proveedores mayoristas: ventas a nivel de línea de ticket.

� Compañías de tarjetas de crédito: compras con tarjeta.



Ejemplo: Proveedores mayoristas.

Tiempo: 3 años

Ingresos anuales: $80 billones

Importe medio de una línea de factura: $5

Nro. de líneas de factura: $80 / $5=16 billones

Nro. de tuplas de la tabla de hechos: 16b. x 3=48b.

Nro. de campos de la clave: 4; Nro. de campos de datos: 4

Tamaño de la tabla de hechos:

48b. x 8 campos x 4bytes = 1.540GB.



Ejemplo: Compañía telefónica.

Tiempo: 3 años = 1095 días

Nro. llamadas por día: 100 millones

Nro. de tuplas de la tabla de hechos: 1095 x 1000000=109b.






Ejemplo: Tarjetas de crédito.

Tiempo: 3 años = 36 meses

Nro. de tarjetas de crédito: 50 millones

Nro. medio de compras mensuales con tarjeta: 30.

Nro. de tuplas de la tabla de hechos: 36 x 50000000 x 30 = 54b.






Consideraciones en el diseño de almacenes de datos.

Diseño de dimensiones:

�dimensión Tiempo

�dimensiones “muy grandes”

�dimensiones en la sombra

�dimensiones degeneradas

�dimensiones no-completas

�dimensiones no-estrictas




Diseño de hechos:

�diseño de la tabla de hechos

�relaciones M:M entre la tabla de hechos y las tablas de dimensiones

�tablas de hechos degeneradas




Otras consideraciones:

�dimensiones “que cambian”

�definición de agregados





�dimensión Tiempo

�dimensiones “muy grandes”

�dimensiones en la sombra

�dimensiones degeneradas

�dimensiones no-completas

�dimensiones no-estrictas



En un almacén de datos muchas consultas son restringidas y parametrizadas por criterios relativos a periodos de tiempo (último mes, este año, ...).


� introducir la dimensión Tiempo.



id_fecha

id_producto

id_almacén

importe

unidades

nro_clientes

Ventas

id_almacén

nro_almacén

nombre

dirección

distrito

región

ciudad

país

tlfno

fax

superficie

tipo_almacén

...

id_producto

nro_producto

descripción

marca

subcategoría

categoría

departamento

peso

unidades_peso

tipo_envase

dietético

...

Almacén

Producto

id_fecha

fecha

semana

mes

año

día_semana

día_mes

trimestre

festivo

....

Tiempo



id_fechafechamesdía-semanatrimestreañofestivo

12-12-98 12-98 lunes 4-98 1998 no

10-6-97 6-97 miércoles 2-97 1997 si

10-1-99 1-99 jueves 1-99 1999 no

12-8-98 8-98 lunes 3-98 1998 si

1-6-97 6-97 sábado 2-97 1997 no

Ejemplo de dimensión Tiempodesnormalizada



Atributos de la dimensión tiempo:

�fecha completa (en el formato del AD)

�mes, trimestre y año

�día de la semana (lunes.. domingo)

�día del mes (1..31)

�semana del mes (1..5)

�día del año (1..365)

�semana del año (1..48)

�trimestre del año (1..4)

�mes del año (enero..diciembre)

�víspera de día festivo

�día festivo



Diseño de dimensiones :

� dimensiones “muy grandes”.

Existen dimensiones especialmente grandes, de millones de registros, que pueden plantear problemas de eficiencia durante la explotación del A.D. (tabla de clientes de una compañía de servicios, tabla de clientes de una aseguradora, etc)

crear minidimensiones



Diseño de dimensiones: dimensiones “muy grandes”.

Ejemplo:

El tamaño usual de una tabla de dimensión es de un número de registros inferior a 50.000 filas y aproximadamente 50 atributos. (dimensión PRODUCTO en una cadena de supermercados).

Una tabla de dimensión muy grande puede tener millones o incluso decenas de millones de filas. (dimensión CLIENTE en una compañía telefónica).

El tamaño de la dimensión penaliza la evaluación de restricciones sobre los atributos de la dimensión.

Nota: Incluso una tabla de dimensión muy grande con 10 millones de

tuplas puede ocupar de 5 a 6 GB.




id_cliente

nro_cliente

dni

nombre

dirección

....

edad

nivel_ingresos

estado_civil

sexo

nivel_estudios

...

CLIENTE

� son utilizados frecuentemente para establecer condiciones en las consultas.

� tienen un rango de valores reducido.




id_cliente

nro_cliente

dni

nombre

dirección

....

id_demo

CLIENTE

id_demo

código

franja_edad

nivel_ingresos

estado_civil

sexo

nivel_estudios

minidimensióndemográfica

......

id_cliente

id_demo

......

......

Tabla de Hechos

separar un grupo de atributos afines de la dimensión original en una dimensión auxiliar.



Minidimensión:

– se crea un registro en la minidimensión para cada combinación distinta de valores de los atributos seleccionados.

– el tamaño de la minidimensión debe estar por debajo de 100.000 filas.

– la clave de la minidimensión se incluye en la dimensión original (“dimensión muy grande”) y en la tabla de hechos*.

* En este caso la inclusión de una nueva dimensión no cambia la granularidad del esquema ni la clave primaria de la tabla de hechos.



Ventajas del uso de las minidimensiones:

� ahorro de espacio

� mejora la evaluación de restricciones sobre los atributos de la minidimensión.

� restricciones sobre atributos de la minidimensión pueden ser aplicadas sin acceder a la dimensión original, concatenando directamente con la tabla de hechos.

� restricciones sobre atributos de la minidimensión y otros atributos de la dimensión original pueden ser evaluados (la clave de la minidimensión aparece en la dimensión original).

� facilita el tratamiento de cambios sobre atributos de la minidimensión: no es necesario crear un nuevo registro en la dimensión original.



id_demo ...........

1 ............

2 ............

3 ............

id_cliente ......... id_demo

11 ........... 2

26 ............ 1

33 ............ 1

.... id_cliente id_demo .............

.... 11 1 ............

.... 11 2 ............

.... 33 1 ............

Tabla de hechos

Demo

Cliente

El cliente de id_cliente 11 ha cambiado su dimensión demográfica de un valor 1 a un valor 2 (valor actual) teniendo hechos almacenados de cada etapa. Un nuevo cambio al valor 3 significaría modificar el valor de id_demo en Cliente y almacenar a partir de ese momento los hechos relativos al cliente 11 con el valor de id_demo igual a 3.



Diseño de dimensiones: dimensiones en la sombra.

id_almacén

id_fecha

id_producto

unidades

nro_clientes

importe

VentasAlmacén

11

1

N

N

N

Tiempo

Producto

Las tablas de dimensiones representan “los puntos de vistas del análisis”.



Las tablas de dimensiones deben contener atributos analíticos:

- los atributos analíticos no son propios de un valor (instancia) de la dimensión sino de conjuntos de valores. (Ejemplo: categoría, región, …)

- los atributos analíticos suelen tener un rango de valores limitado.

- los atributos analíticos son utilizados en el análisis de datos: aplicar condiciones sobre las dimensiones.




Cuando en el análisis de datos, una dimensión participa al nivel básico (producto, almacén, día), suele ser frecuente que en el informe del usuario se deseen incluir atributos descriptivos del valor (instancia) de la dimensión.

Ejemplo: En un informe de ventas por productos, regiones y años, puede ser interesante que aparezca la descripción completa del producto (nombre).

Este hecho justifica la inclusión de atributos que no son analíticos en las dimensiones: descripción del producto, nombre del cliente, ..

Para no penalizar el tamaño de las tablas de dimensiones con atributos que no son analíticos se suelen definir dimensiones en la sombra (shadow dimension).




id_almacén

id_fecha

id_producto

id_info_producto

unidades

nro_clientes

importe

VentasAlmacén

11

1

N

N

N

Tiempo

Producto1

N

Info_producto

dimensión en la sombra



* En este caso la inclusión de una nueva dimensión no cambia la granularidad del esquema ni la clave primaria de la tabla de hechos.


id_cliente

id_fecha

id_clinica

importe

FacturaciónCliente

11

1

N

N

N

Tiempo

Clínica

Tabla de hechos: facturación a clientes en las distintas clínicas de un sistema sanitario.

Diseño de dimensiones: dimensiones degeneradas.



id_cliente

id_fecha

id_clinica

id_factura

importe

FacturaciónCliente

11

1

N

N

N

Tiempo

Clínica


1

N

Facturadimensión degenerada




id_cliente

id_fecha

id_clinica

Id_factura

importe

FacturaciónCliente

11

1

N

N

N

Tiempo

Clínica


1NFactura

dimensión degenerada




id_cliente

id_fecha

id_clinica

id_factura

importe

FacturaciónCliente

11

1

N

N

N

Tiempo

Clínica


1N

Factura dimensión degenerada




Diseño de dimensiones: dimensiones no-completas.


Dimensión no-completa: dimensión que tiene una jerarquía no completa

Categoría

nombre

...

Departamento

nombre

...0..

no-completa


Coñac

Vino

Servilletas

Pañuelos

Bisutería

Bebidas

Papel





Coñac

Vino

Servilletas

Pañuelos

Bisutería

Bebidas

Papel

nro_producto

descripción

marca

subcategoría

categoría

departamento

peso

unidades_peso

tipo_envase

dietético

...

Producto

Solución: convertir la jerarquía no-completa en una jerarquía completa introduciendo valores artificiales para el nivel que causa el problema.




id_producto id_fecha id_almacén importe . ..

3 12 13 100

7 15 12 50

4 78 11 400

6 7 12 67

......

Ventas3 p23 Magno Coñac Bebidas

7 p34 Cune Vino Bebidas

4 p11 Dodot Servilleta Papel

5 p4 Klenex Pañuelo Papel

6 p14 Loewe Bisutería NULL

Producto

Departamento Ingresos

Bebidas 150

Papel 400

Total ingresos: 550

El total (550) es incorrecto

Informe de ingresos por departamento.

id_prod nro_prod marca categoría departamento




id_producto id_fecha id_almacén importe ....

3 12 13 100

7 15 12 50

4 78 11 400

6 7 12 67

......

Ventas

Producto


Bebidas 150

Papel 400

Varios 67

Total ingresos: 1220

El total (1220) es correcto


Definir un departamento Variospara convertir la jerarquía no-completa en completa.

3 p23 Magno Coñac Bebidas



5 p4 Klenex Pañuelo Papel

6 p14 Loewe Bisutería Varios



Diseño de dimensiones: dimensiones no-estrictas.


Categoría

nombre

...

Departamento

nombre

.....*

no-estricta


Coñac

Vino

Servilletas

Pañuelos

Bebidas

Papel

Droguería

Dimensión no-estricta: dimensión que tiene una jerarquía no-estricta





Coñac

Vino

Servilletas

Pañuelos

Bebidas

Papel

Droguería

nro_producto

descripción

marca

subcategoría

categoría

departamento

peso

unidades_peso

tipo_envase

dietético

...

Producto

Solución: documentar el esquema y advertir al usuario de los problemas.




id_prod id_fecha id_alm importe

3 12 13 100

7 15 12 50

4 78 11 400

5 7 12 67

......

Ventas

Productoid_prod nro_prod marca categoría

3 p23 Magno Coñac

7 p34 Cune Vino

4 p11 Dodot Servilleta

5 p4 Klenex Pañuelo

id_dpto departamento

1 Bebidas

2 Papel

3 Droguería

Departamento

id_prod id_dpto

3 1

7 1

4 2

5 2

5 3

Prod_Dpto




id_producto id_fecha id_almacén importe

3 12 13 100

7 15 12 50

4 78 11 400

5 7 12 67

......

Ventas

Producto


Bebidas 150

Papel 467

Droguería 67

Total ingresos: 684






5 p4 Klenex Pañuelo Papel, Droguería





id_producto id_fecha id_almacén importe

3 12 13 100

7 15 12 50

4 78 11 400

5 7 12 67

......

Ventas


No se puede agrupar por departamento, ya que la jerarquía no es estricta por debajo de departamento.




5 p4 Klenex Pañuelo Papel, Droguería




Diseño de hechos

�diseño de la tabla de hechos.

�relaciones M:M entre la tabla de hechos y las tablas de dimensiones.

�tablas de hechos degeneradas



Diseño de hechos:

� atributos aditivos, semiaditivos y no-aditivos

– los atributos de la tabla de hechos (medidas) son generalmente de dominios continuos, numéricos y de carácter aditivo.

– existen medidas que pueden ser aditivos para algunas dimensiones y no serlo para otras. ¡Atención!

– los cálculos realizados sobre las medidas pueden ser aditivos o no aditivos. ¡Atención!



Diseño de hechos: atributos aditivos, semiaditivos, no-aditivos

id_fecha

id_producto

id_almacén

importe

unidades

nro_clientes

Ventas

id_fecha id_producto id_almacén importe unidades nro_clientes

....

13 1 23 5000 10 10

13 23 23 2000 4 2

13 12 23 1000 2 1

....

agrupación por Fecha y Almacén

agregación sobre ProductoVentas

8000 16 ≤ 13

aditivo aditivo no aditivo

El atributo nro_clientes es semiaditivoporque no es aditivo sobre la dimensión Producto.

Nota: en el ejemplo cliente es sinónimo de compra (no se pueden identificar los clientes).



Diseño de hechos: atributos aditivos, semiaditivos y no-aditivos

Importe es aditivo sobre Tiempo, Producto, Almacén

Unidades es aditivo sobre Tiempo, Producto, Almacén

Nro-clientes es aditivo sobre Tiempo, Almacén y no es aditivo sobre Producto.

id_fecha id_producto id_almacén importe unidades nro_clientes

....

12 1 23 5000 10 10

12 1 25 2000 4 2

12 1 3 1000 2 1

....

Ventas

8000 16 13

aditivo aditivo aditivo



Diseño de hechos: atributos aditivos, semiaditivos y no-aditivos

Cálculos aditivos y no aditivos ¡Atención!

(beneficio es aditivo)beneficio = importe - coste

Z = X - Y (beneficio de cada venta individual)

∑X - ∑Y = ∑ (X -Y) = ∑Z

(el beneficio total es igual a la suma de beneficios individuales)

margen = beneficio/importe (margen no es aditivo)

Z= X/ Y (margen de beneficio en cada venta individual)

∑X/ ∑Y ≠ ∑(X/Y) = ∑Z

(el margen de beneficios total no es igual a la suma de márgenes de beneficio individuales)



id_producto

id_fecha

id_almacén

id_promoción

ventas

Diseño de hechos: tablas de hechos “sin medidas”

Ejemplo: el almacén de datos para la “cadena de supermercados” introduciendo la dimensión promoción.

¡En el esquema anterior no es posible saber qué productos han estado en promoción en un almacén en una fecha determinada si no han tenido ventas!




id_producto

id_fecha

id_almacén

id_promoción

Solución: tabla de hechos “sin medidas” para registrar las promociones diarias de productos en los almacenes de la cadena.

La tabla de hechos Ventas y la tabla de hechos Promociones permiten responder a las consultas:

� productos que han estado en promoción y han sido vendidos

� productos que han estado en promoción y no han sido vendidos

Promociones




Las tablas de hechos “sin medidas” son tablas de hechos compuestas por los identificadores de las dimensiones pero sin medidas (datos).

Las tablas de hechos “sin medidas” se usan para registrar la ocurrencia de eventos que no llevan información asociada



Diseño de hechos:� relaciones M:M entre la tabla de hechos y una dimensión

En un esquema multidimensional (en estrella) la tabla de hechos se relaciona con las tablas de dimensiones a través de relaciones 1:M.

id_dim1

id_dim2

id_dim3

...

id_dim n

....

(medidas)

tabla de hechos

tabla Dimensión 3

tabla Dimensión 1

tabla Dimensión 2

tabla Dimensión n

11

1

1

N

N

N

N



En un esquema multidimensional (en estrella) la tabla de hechos se relaciona con las tablas de dimensiones a través de relaciones 1:M.

id_dim1

id_dim2

id_dim3

...

id_dim n

....

(medidas)

tabla de hechos

tabla Dimensión 3

tabla Dimensión 1

tabla Dimensión 2

tabla Dimensión n

11

1

1

N

N

N

N




id_cliente

id_fecha

id_clinica

id_diag

importe

FacturasCliente

11

1 N

N

N

N

N

Tiempo

Clínica Diagnóstico

En un sistema sanitario, en la factura a un cliente la clínica puede facturar pruebas realizadas para distintos tipos de diagnóstico (hepatitis, sida, …)




� El identificador de las tuplas de la tabla de hechos esta formado por los identificadores: id_cliente, id_fecha, id_clínica.

� El atributo id_diag, en la tabla de hechos es multivaluado.



id_cliente

id_fecha

id_clinica

id_diag

importe

FacturasCliente

11

1 N

N

N

N

N

Tiempo



id_cliente id_fecha id_clinica importe i d_diag .....

23 4 12 145 3

7

23 6 15 300 3

25 78 12 400 7

......

Informe de facturación. 3 Diabetes

7 Hepatitis

4 Sida

Diagnósticos

Diagnóstico Ingresos

3 445

7 545

Total ingresos: 990


Informe de ingresos por tipo de diagnóstico.

Diseño de hechos: relaciones M:M entre la tabla de hechos y una dimensión



id_cliente

id_fecha

id_clinica

id_diag

importe

FacturasCliente

11

1 N

N

N

N

N

Tiempo






id_cliente

id_fecha

id_clinica

id_grupo

importe

FacturasCliente

id_grupo

id_diag

factor

11

1 N

N

N

N

1

Tiempo

Clínica Grupo_diag

Solución 1: Crear una tabla puente (Grupo_diag) que permite fijar un factor (porcentaje) para cada diagnóstico dentro de una factura (o el importe exacto del diagnóstico en la factura). Una factura estará asociada a tantas filas de la tabla puente como diagnósticos incluya la factura.

id_diag

desc

…

Diagnóstico

1

N

Solución 1




id_cliente

id_fecha

id_clinica

id_grupo

importe

FacturasCliente

id_grupo

id_diag

factor

11

1 N

N

N

N

1

Tiempo

Clínica Grupo_diag

Solución 1: el id_grupo representa una clave alternativa en la tabla de hechos.

id_diag

desc

…

Diagnóstico

1

N

Solución 1




id_cliente id_fecha id_clinica id_grupo imp orte

.....

23 4 12 1 145

24 6 15 6 300

25 78 12 53 400

......

Informe de facturación 3 Diabetes

7 Hepatitis

4 Sida

Diagnóstico

Diagnóstico Ingresos (factor × importe)

3 336.25

7 508.75

Total ingresos: 845 El total (845) es correcto


1 3 25

1 7 75

6 3 100

53 7 100

Se agrupa por tipo de diagnóstico

Grupo_diag

porcentaje que el diagnóstico

representa en el importe de la

factura




id_cliente id_fecha id_clinica id_grupo imp orte

.....

23 4 12 1 145

24 6 15 6 300

25 78 12 53 400

......

Facturas 3 Diabetes

7 Hepatitis

4 Sida

Diagnóstico

Diagnóstico Ingresos

3 336.25

7 508.75

Total ingresos: 845 El total (845) es correcto


1 3 25

1 7 75

6 3 100

53 7 100

Se agrupa por tipo de diagnóstico

Grupo_diag

porcentaje que el diagnóstico

representa en el importe de la

factura




id_cliente

id_fecha

id_clinica

id_diag

importe

FacturasCliente

11

1 N

N

N

N

N

Tiempo



Solución 2




id_cliente

id_fecha

id_clinica

id_diag

importe

FacturasCliente

11

1 1

N

N

N

N

Tiempo


Solución 2: Crear varias filas en la tabla de hechos que representen el mismo hecho (factura), una por cada diagnóstico incluido en la factura.

Solución 2




� El identificador de la tabla de hechos está compuesto por los identificadores: id_cliente, id_fecha, id_clinica, id_diag.

� Ha cambiado la granularidad del esquema en estrella: gránulo mas fino.

Solución 2



id_cliente

id_fecha

id_clinica

id_diag

importe

FacturasCliente

11

1 1

N

N

N

N

Tiempo



id_cliente

id_fecha

id_clinica

id_diag

importe

FacturasCliente

11

1 1

N

N

N

N

Tiempo


En las filas de la tabla de hechos se está almacenando información agregada (importe total de la factura). La medida importe no corresponde a la granularidad del esquema en estrella.

Con la granularidad del esquema en estrella, sólo se pueden usar la función COUNT sobre la tabla de hechos. No se puede utilizar la medida importe.

Solución 2




2004

“Resonancia”

“Valencia”

importe

Ciudad

Región

Nombre

DNI

Tipo_cliente Día

Mes

Día de la semana

Diagnóstico

Descripción

Departamento

Tipo_diag

Año

OLAP

Trimestre

Ciudad Región

Nombre

Clínica

Tipo_clinica

Número de diagnósticos del departamento de Resonancia realizados este año en

clínicas de Valencia, por diagnóstico y por clínica.



id_cliente

id_fecha

id_clinica

id_diag

importe

FacturasCliente

11

1 1

N

N

N

N

Tiempo


SELECT D.diagnóstico, C.clínica, COUNT (*)

FROM Facturas F, Tiempo T, Diagnóstico D, Clínica C

WHERE F.id_diag = D.id_diag AND F.id_fecha = T.id_fecha AND

F.id_clínica = C.id_clínica AND D.departamento= ‘ Resonancia’ AND

C.ciudad = 'Valencia' AND T.año= year (TODAY)

GROUP BY D.diagnóstico, C.clínica



id_cliente

id_fecha

id_clinica

id_diag

factura

importe

FacturasCliente

11

1 1

N

N

N

N

Tiempo


� Para poder controlar la información redundante sobre una misma factura en la tabla de hechos, es necesario incluir en la tabla de hechos un atributo que identifique a la factura: nivel de agregación en el que tiene significado la medida importe.

� Sólo cuando se agrupa por factura, se puede incluir la medida importe como atributo del grupo.

Solución 2




2004

“Análisis Valencia” factura

importe

Ciudad

Región

Nombre

DNI

Tipo_cliente Día

Mes

Día de la semana

Diagnóstico

Descripción

Departamento

Tipo_diag

Año

OLAP

Trimestre

Ciudad Región

Nombre

Clínica

Tipo_clinica

Facturación realizada en la clínica 'Análisis Valencia', para el año en curso.



id_cliente

id_fecha

id_clinica

factura

id_diag

importe

FacturasCliente

11

1

1

N

N

N

N

Tiempo

Clínica

Diagnóstico

SELECT T.dia, CL.dni, CL.nombre, F.factura, F.importe

FROM Facturas F, Tiempo T, Clínica C, Cliente CL

WHERE

F.id_clinica = C.id_clínica AND F.id_fecha = T.id_fecha AND CL.id_cliente=F.id_cliente

AND C.clínica = 'Análisis Valencia' AND T.año = year (TODAY)

GROUP BY T.dia, CL.dni, CL.nombre, F.factura, F.importe



� Observar que el atributo factura de la tabla de hechos determina funcionalmente a id_cliente, id_fecha e id_clínica, es decir una factura corresponde a un cliente y ha sido realizada en una fecha y en una clínica determinada.

Solución 2



id_cliente

id_fecha

id_clinica

factura

id_diag

importe

FacturasCliente

11

1

1

N

N

N

N

Tiempo

Clínica

Diagnóstico


id_cliente

id_fecha

id_clinica

id_diag

factura

importe

factor

FacturasCliente

11

1 1

N

N

N

N

Tiempo


� Se puede incluir también un atributo que represente el porcentaje del diagnóstico en la factura o el importe del diagnóstico: medidas al nivel de la granularidad del esquema en estrella.

Solución 2




2005

“Resonancia”

factura

importe

factor

Ciudad

Región

Nombre

DNI

Tipo_cliente Día

Mes

Día de la semana

Diagnóstico

Descripción

Departamento

Tipo_diag

Año

OLAP

Trimestre

Ciudad Región

Nombre

Clínica

Tipo_clinica

Importe total obtenido por pruebas deldepartamento de "Resonancia" , este año

por diagnóstico.

importe × factor



SELECT D.diagnóstico, SUM (importe × factor)

FROM Facturas F, Tiempo T, Diagnóstico D

WHERE F.id_diag = D.id_diag AND F.id_fecha = T.id_fecha

AND D.departamento = 'Resonancia' AND T.año = year (TODAY)

GROUP BY D.diagnóstico


id_cliente

id_fecha

id_clinica

id_diag

factura

importe

factor

FacturasCliente

11

1 1

N

N

N

N

Tiempo



id_cliente

id_fecha

id_clinica

id_grupo

importe

FacturasCliente

id_grupo

id_diag

importe

11

1 N

N

N

N

1

Tiempo

Clínica Grupo_diag

id_diag

desc

…

Diagnóstico

1

N

La tabla Grupo_diag es realmente una tabla de hechos degenerada: contiene información sobre la facturación a los clientes a nivel de diagnóstico.

Diseño de hechos: tablas de hechos degeneradas.



id_cliente

id_fecha

id_clinica

id_grupo

importe

FacturasCliente

id_grupo

id_diag

importe

11

1 N1

Tiempo

Clínica Grupo_diag

id_diag

desc

…

Diagnóstico

1

N

La tabla Grupo_diag es realmente una tabla de hechos de un esquema en estrella cuyas dimensiones son Diagnóstico y Facturas.




id_cliente

id_fecha

id_clinica

id_grupo

importe

FacturasCliente

id_grupo

id_diag

importe

11

1 N

N

N

N

1

Tiempo

Clínica Grupo_diag

id_diag

desc

…

Diagnóstico

1

N

La tabla Grupo_diag es realmente una tabla de hechos de un esquema en estrella cuyas dimensiones son Diagnóstico e indirectamente (a través de Facturas) Tiempo, Cliente y Clínica.





Otras consideraciones:

�dimensiones “que cambian”

�definición de agregados.



Orientaciones de diseño:

� dimensiones “que cambian”.

Ejemplo: En un A.D existe la dimensión CLIENTE. En la tabla correspondiente un registro representa la información sobre el cliente “María García” cuyo estado civil cambia el 15-01-1994 de soltera a casada. El estado civil del cliente es utilizado con frecuencia en el análisis de la información.

Se considera relevante el caso en que, en el mundo real, para un valor de una dimensión, cambia el valor de un atributo que es significativo para el análisis sin cambiar el valor de su clave.



Existen tres estrategias para el tratamiento de los cambios en las dimensiones:

Tipo 1: Realizar la modificación.

Tipo 2: Crear un nuevo registro.

Tipo 3: Crear un nuevo atributo.



Orientaciones de diseño:

� dimensiones “que cambian”.

Tipo 1: Realizar la modificación.

° estrategia mas fácil de implementar.

° la información histórica ya no es segura.

° es aconsejable para:

– corregir errores.

– tratar cambios que no son relevantes para el análisis (ej. dirección del cliente, nombre del cliente, ...).



Orientaciones de diseño: dimensiones “que cambian”.

Tipo 2: Crear un nuevo registro con los nuevos valores.

° a partir de ese momento existen en el A.D varias versiones de un mismo objeto del mundo real (cliente).

° es necesario crear un nuevo valor para la clave (generalización de la clave original o generación de un nuevo valor).

° implica una partición de la tabla de hechos en subconjuntos de tuplas asociados a cada versión del objeto.

° el usuario no puede relacionar la información sobre las distintas versiones del objeto (en la práctica son dos objetos distintos).

° el usuario puede reconstruir la historia del objeto a partir de otros atributos que no cambien de valor, como la clave del operacional (dni, nss, ..).

° es responsabilidad del S.G.A.D saber cuál es la última versión del objeto para asociarle los nuevos hechos que llegan al A.D.



Generalización de la clave original: cuando la clave de la dimensión coincide con una clave del sistema operacional (clave con significado). La solución mas usual es añadir a la clave original uno o dos dígitos de versión para contemplar los sucesivos cambios.

Generación de un nuevo valor para la clave: cuando los valores de la clave son generados artificialmente (clave sin significado).



nro_cliente dni nombre civil .......

123 1876543 María García Soltera ......

....

1231 1876543 María García Casada ......

.....

1232 1876543 María García Divorciada ....

CLIENTE

nro_cliente id_poliza id_fecha

123 1 12/12/01

....

1231 35 13/01/02

...

1232 359 11/04/02

....

• el cliente de nro_cliente 123 realiza una póliza (1) en la fecha 12/12/01



dígito de versión

ClientePóliza

Tiempo



Orientaciones de diseño: dimensiones “que cambian”.

Tipo 3: Crear un nuevo atributo.

º se mantiene el atributo original con su valor y se crea un nuevo atributo con el valor actual y un atributo para indicar la fecha del cambio

º la partición en las tuplas asociadas de la tabla de hechos se realiza utilizando la fecha del cambio

º sólo se conservan dos valores: el valor original y el valor actual (se pierden los valores intermedios).

º permite considerar la historia completa del objeto (independientemente del valor del atributo) o considerar la historia dividida en dos periodos (anterior y posterior a la fecha de cambio).

...

atributo_original

atributo_actual

fecha_cambio

...

Dimensión



Definición de agregados.

¡En un almacén de datos es usual consultar información agregada!

Ejemplos:

� ventas por almacén, día y categoría de producto

� ventas por ciudad, día y producto

� ventas por ciudad, mes y producto

� ventas por ciudad, día y categoría de producto

El almacenamiento de datos agregados por distintos criterios de agregación mejora la eficiencia del AD.




Estrategias de almacenamiento de datos agregados:

� Estrategia 1: definir nuevas tablas de hechos y de dimensiones para almacenar la información agregada y la descripción de los niveles de agregación. (definir un nuevo esquema en estrella).

� Estrategia 2: insertar en las tabla de hechos y en las tablas de dimensiones del esquema, tuplas que representan respectivamente la información agregada y los niveles de agregación.




Estrategia 1: definir nuevas tablas de hechos y de dimensiones (nuevos esquemas en estrella).

Si se desea poder almacenar información agregada por categorías, ciudades y meses, se necesitarán tres "nuevas tablas de dimensiones" (CATEGORÍA, CIUDAD y MES) y tantas nuevas tablas de hechos como tipos distintos de agregación se desee realizar combinando estos tres criterios de agregación y las tres dimensiones básicas. Por ejemplo:

–ventas por categoría, día y almacén

–ventas por producto, mes y almacén

–ventas por producto, día y ciudad

–ventas por categoría, mes y almacén

–ventas por categoría, día y ciudad

–ventas por producto, mes y ciudad

–ventas por categoría, mes y ciudad.




Estrategia 1: definir nuevas tablas de hechos y de dimensiones.

id_categoría

categoría

departamento id_mes

mes

añoid_ciudad

región

país

Categoría

Ciudad

Mes

Nuevas tablas de dimensiones





id_fecha

id_categoría

id_almacén

importe

unidades

nro_clientes

Ventas-categoría

id_almacén

nro_almacén

nombre

dirección

distrito

ciudad

país

...

Almacén

id_fecha

día

semana

mes

año

....

Tiempo

id_categoría

categoría

departamento

Categoría

Nueva tabla de hechos

Nuevo esquema en estrella





� La creación de "tablas de dimensiones de agregación" exige la definición de claves artificiales para identificar todos los valores posibles para cada criterio de agregación considerado (id_categoría, id_ciudad, id_mes)





La definición de tablas de agregación por cualquier criterio no siempre tiene sentido.

La definición de la tabla agregada Ventas-categoría aconseja no definir también la tabla agregada de Ventas-departamento, ya que ésta última es fácilmente calculable

a partir de la primera.



Definición de agregados.Estrategia 2: insertar nuevas tuplas en la tabla de hechos y en las tablas de dimensiones.

id_echa

id_producto

id_almacén

importe

unidades

nro_clientes

Ventas

id_almacén

nro_almacén

nombre

dirección

distrito

id_fecha

día

semana

mes

año

Tiempo

Nuevo atributo para indicar el nivel de agregación representado por la tupla

id_producto

nro_producto

nivel_agregación

descripción

marca

subcategoría

categoría

departamento

...

Producto

Almacén




Estrategia 2: insertar nuevas tuplas en la tabla de hechos y en las tablas de dimensiones.

id_producto

nro_producto

nivel_agregación

descripción

marca

subcategoría

categoría

departamento

...

ProductoValores del atributo nivel_agregación: base, categoría, departamento, todos, ...

(contemplando la definición de agregados a distintos niveles de agregación en la dimensión producto)




id_fecha id_producto id_almacén importe uni dades nro_clientes

....

12 145 28 50000 100 18

12 23 23 2000 4 2

1 13 23 600000 340 90

....

Ventas

id_prod nivel_agreg ... categoría . .. depto ... tipo_envase

.....

23 base refresco bebidas cristal

145 categoría servilleta droguería NULL

13 departamento NULL bebidas NULL

......

Producto

tuplas de agregación




� La estrategia 2, exige que en las consultas se controle el valor del atributo nivel_agregación: todas las tuplas seleccionadas al evaluar una restricción sobre la dimensión deben tener el mismo valor en el atributo nivel_agregación.

� En la estrategia 2, las claves nuevas definidas para representar los niveles de agregación deben ser compatibles con las claves de las dimensiones originales.




� la estrategia 2 puede plantear el problema del “doble conteo” en la evaluación de restricciones sobre una dimensión: considerar en una agregación tuplas de niveles distintos (ej. del nivel básico y de otro nivel de agregación).

id_producto

nro_producto

nivel_agregación

descripción

marca

subcategoría

categoría

departamento

...

Producto

Una restricción sobre la dimensión Producto que incluya la condición Categoría=‘Papel’ y que no controle el valor del atributo nivel_agregación , recuperaría tuplas correspondientes a productos básicos de la categoría ‘Papel’ y la tupla de nivel de agregación “categoría” correspondiente a la categoría ‘Papel’.





Diseño físico


Implementación

Diseño conceptual


Implementación

Definición del esquema ROLAP o MOLAP

Carga del AD

Preparación de las vistas de usuario

(herramienta OLAP)



Implementación

Definición del esquema ROLAP o MOLAP

Carga del AD

Preparación de las vistas de usuario

(herramienta OLAP)

Proceso de transformación:

� filtrado de datos

� consolidación de datos

� agregación de datos


4. diseño de un almacén de datos

Documents