Modelo F

1
Modelo Físico

• Mg. Samuel Oporto Díaz

2
Mapa del Curso
3
Tabla de Contenido

• Modelo Físico
• Modelo dimensional ? Modelo Físico
• Dimensionamiento de la BD
• Estrategia de indexación.
• Estrategia de particionamiento.
• Actualización de la metadata
• MOLAP, ROLAP, HOLAP

4
Objetivos

• Después de completar esta lección, comprendera
• Trasladar el modelo dimensional a un modelo
  físico.
• Explicar varias técnicas de dimensionamiento.
• Describir las técnicas de indexación para data
  warehousing.
• Discutir las estrategias de almacenamiento de
  tablas e índices.
• Discutir las opciones de particionamiento.
• Actualizar el documento de meta data con la
  información del modelo físico.

5
MODELO FÍSICO
6
Ciclo de Vida
7
El modelo físico

• El modelo físico reside en un (RDBMS), por lo que
  es necesario asegurar
• Que el almacenamiento de cada objeto (tablas) se
  soporte de manera apropiada y contenga los
  índices necesarios.
• El desempeño de la base de datos.
• El particionamiento de la base de datos.
• Trasladar el modelo dimensional al modelo físico.
• Usar estándares de nombres, adicionar formato a
  los atributos, medir el desempeño de la BD y
  plantear las estrategias de indexación y
  particionamiento.

8
Checklist del Modelo Físico

• Trasladar el modelo dimensional a un modelo
  físico
• Aplicar estándares de nombramiento y BD.
• Identificar el formato y longitud de columnas.
• Etiquetar atributos como Not Null () o Null (o)
• Desarrollar el dimensionamiento de la BD.
• Definir la estrategia de indexación inicial.
• Definir la estrategia de almacenamiento de
  tablas/índices.
• Definir la estrategia de particionamiento.
• Actualizar el documento de la meta data.

9
TRASLADAR EL MODELO DIMENSIONAL AL MODELO FÍSICO
10
Estándares para nombres de objetos

• Mantener los nombre lógicos y físicos similares.
• Encontrar un balance entre nombres específicos y
  vagos.
• Nombre de tablas y atributos en mayúscula.
• Utilizar subrayado para separar palabras.
• Utilizar el sufijo _ID para indicar llaves
  producción.
• Utilizar el sufijo _KEY para indicar llave
  primaria.
• Desarrollar una lista razonable de abreviaciones.
• Listar todos los nombres de los objetos y
  trabajar con la comunidad de usuarios para
  definirlos.
• Resolver las disputas de nombres.
• Documentar estándares de nombres en un documento.

11
Modelo Dimensional ? Modelo Físico

• Listar las columnas de una tabla con las columnas
  claves primarias al inicio.
• Etiquetas
• Las claves primarias con un PK y ( )
• Las llaves únicas con un ()
• Las columnas con restricciones NULL (o) o NOT
  NULL ().
• Las llaves foráneas con una FK y ( ) si es
  parte de la llave primaria (PK).
• Identificar el formato y longitud de las
  columnas.
• d para decimales
• n para numéricos
• c para caracteres
• v para caracter variables
• date para fecha

12
Ejercicio 1

• Trasladar el modelo dimensional lógico a un
  modelo dimensional físico.
• Aplicar los estándares propuestos.

13
Ejercicio 1 Modelo dimensional lógico
14
Ejercicio 1 - Modelo dimensional físico
DAY_KEY (PK) n
PRODUCT_KEY (PK) n
DAY_ID date DAY_DESC v(9) WEEKEND
v HOLIDAY_EVE v HOLIDAY_DAY
v HOLIDAY_WKND_DAY v MONTH_KEY
n MONTH_ID n MONTH_DESC
v(9) YEAR_KEY n YEAR_ID
n YEAR_DESC v(9) TIME_ BAND v(2)
PRODUCT_ID v(11) PRODUCT_DESC
v(125) CATEGORY_KEY n CATEGORY_ID v(20) CA
TEGORY_DESC v(25) TYPE_KEY n TYPE_ID v(20)
TYPE_DESC v(15) STATUS v(15) AGE
n CLASSIFICATION v(25) RENTAL_PERIOD n SOL
D_OUT_RATING v DAILY_RATE n
CUSTOMER_KEY (PK) n
STORE_KEY (PK) n
CUSTOMER_ID v(25) CUSTOMER_NAME v(50) STATUS
v GEOG_PROXIMITY n ACTIVITY_LEVEL v TYPE_P
REF v(20) MOVIE_CAT_PREF v(20) MOVIE_AGE_PREF
v(20) MOVIE_RATE_PREF v(20) GAME_CAT_PREF v(2
0) GAME_AGE_PREF v(20) GAME_RATE_PREF v(20)
STORE_ID
v STORE_DESC v LOCATION
v(50) SIZE n TYPE v(15) DISTRICT_KEY n
DISTRICT_ID n DISTRICT_DESC v(50) REGION_KE
Y n REGION_ID n REGION_DESC v(50)
15
DIMENSIONAMIENTO DE LA BASE DE DATOS
16
Dimensionar Base de Datos

• Calcular el tamaño físico de la base de datos.
• Espacio en disco para almacenar
• Tablas, vistas, indices.
• El dimensionamiento es requerido para
• La arquitectura del medio ambiente.
• Tareas de backup y recuperación.
• Técnicas de BD espejos
• Espacio temporal y técnicas de carga de datos.
• Dimensionamiento no es una ciencia, existen
  varias técnicas

17
Estimar el tamaño de la Base de Datos

• Estimar el tamaño de fila de la tabla hechos.
• Estimar el nro. filas de la tabla de hechos
• Determinar la granularidad de cada dimensión.
• Estimar el número de entradas en el nivel mas
  fino.
• Multiplicar el número de filas de c/dimensión.
• Tamaño BD tamaño de fila nro. filas.
• Estimar la reducción o incremento en el tamaño
  según el grado de dispersión.
• Baja dispersión restar entre el 5 al 10
• Media dispersión restar entre el 10 al 25
• Alta dispersión restar entre el 25 al 50

18
Estimar el tamaño de la Base de Datos

• Tamaño fila S tamaño columnas.
• Nro de filas estimación de transacciones por
  año
• Dispersión Es el de dispersión de los datos.

19
Tipos de dato - SQL Server
Tipo de datos bytes
bigint 8
int 4
smallint 2
tinyint 1
decimal( 9) 5
decimal(19) 9
decimal(28) 13
decimal(38) 17
float, double 16
real 4
datetime 4
smalldatetime 2
char(n) n
nchar(n) 2n2
20
Tipos de dato - Integration Services.
Tipo de datos Bytes Bytes Max Descripción
DT_BOOL 1   Un valor booleano.
DT_BYTES 1 8000 Un valor de datos binarios
DT_CY 4   Un valor de moneda
DT_DATE 8   año, mes, día y hora.
DT_DBDATE 8   año, mes, día y hora.
DT_DBTIME 8   horas, minutos y segundos.
DT_DBTIMESTAMP 8   por año, mes, día, horas, minutos, segundos y milisegundos.
DT_DECIMAL 12   Un valor numérico exacto con una precisión fija y una escala fija.
DT_FILETIME 8   Un valor de 64 bits
DT_GUID     Identificador único global (GUID).
DT_I1 1   Un entero con signo de un byte.
DT_I2 2   Un entero con signo de dos bytes.
DT_I4 3   Un entero con signo de cuatro bytes.
DT_I8 4   Un entero con signo de ocho bytes.
DT_NUMERIC 16   Un valor numérico exacto con una precisión y escala fijas.
DT_R4 8   Un valor de coma flotante y precisión única.
DT_R8 16   Un valor de coma flotante y precisión doble.
DT_STR 1 8000 Una cadena de caracteres ANSI/MBCS terminada en NULL
DT_UI1 1   Un entero sin signo de un byte.
DT_UI2 2   Un entero sin signo de dos bytes.
DT_UI4 4   Un entero sin signo de cuatro bytes.
DT_UI8 8   Un entero sin signo de ocho bytes.
DT_WSTR   4000 Una cadena de caracteres Unicode terminada en NULL
DT_IMAGE   2,147,483,647 Un valor binario
DT_NTEXT   1,073,741,823 Una cadena de caracteres Unicode
21
Ejercicio 2

• Calcular el tamaño de la tabla de hechos para el
  ejemplo propuesto.
• El horizonte es de 3 años, asuma una dispersión
  del 10 (baja dispersión)

22
Ejercicio 2 método 1
Descripción Fórmula Valor Comentario
Tamaño de una fila de la tabla hechos 16444 80 asumido para este ejemplo

Estimar el número de entradas en el nivel mas bajo para cada dimensión Estimar el número de entradas en el nivel mas bajo para cada dimensión Estimar el número de entradas en el nivel mas bajo para cada dimensión Estimar el número de entradas en el nivel mas bajo para cada dimensión
DAY KEY (FK) n 3365 1095 3 años (48 meses)
CUSTOMER KEY (FK) n   65 65 clientes
PRODUCT KEY (FK) n   36 36 ítems
STORE KEY (FK) n   4 3 canales
       
Número de entradas   10,249,200  

nro de entradas de cada dimensión X tamaño fila tabla hechos 10,249,200 80 819,936,000  
Dispersión 0.1819,936,000 81,993,600 Si es baja se asumen 10

Tamaño estimado BD 0.9819,936,001 737,942,401 737.9 MB
23
Ejercicio 2 método 2
Descripción fórmula valor unidades
Ingresos totales de la empresa   2000 billones X año
Precio promedio venta por línea de ítem   50
       
Línea ítems por año 200 billones/50 4 billones
Registros en 3 años 4 billones3 años 12 billones
       
       
Tamaño de registro   80 bytes
       
Tamaño tabla hecho base 12 billiones 80 bytes 960,000,000,000  
960 GB
24
Aplicando el Test de la carga ejemplo

• Analizar estadísticamente el significado de los
  datos ejemplo.
• Utilizar test de carga para diferentes periodos.
• Reflejar operaciones día a día.
• Incluir datos estacionales y casos de escenarios
  pesimistas
• Calcular el número de transacciones
• Utilizar la aproximación del precio promedio de
  ventas.
• Considerar índices y resúmenes.

25
Test ejemplo de carga usando el servidor

• Cargar un ejemplo de datos.
• Consultar para determinar el número de filas por
  bloque.
• Estimación basado en el número de filas.

26
Ejercicio 3

• Por qué pueden existir diferencias entre el
  tamaño calculado analíticamente y tamaño el real
  ocupado en la BD??

27
ESTRATEGIA DE INDEXACIÓN DE DATOS
28
Índices

• Los índices son objetos de la bases de datos,
  cuya función es optimizar el acceso a datos.
• A medida que las tablas se van haciendo más
  grandes y se desea hacer consultar sobre estas
  tablas, los índices son indispensables.

29
Índices

• Internamente un índice normal es una estructura
  de árbol, que cuenta con una página principal y
  luego esta con paginas hijas, que a su vez tiene
  más paginas hijas hasta llegar a la pagina final
  del índice (leaf level).
• La clave del índice está repartida en las páginas
  del índice, de modo tal que la búsqueda se haga
  leyendo la menor cantidad posible de datos.

Hoja de páginas
30
Tipos de Índices

• Índice no agrupado
• El orden físico de las filas es independiente
  del orden indizado
• CREATE NONCLUSTERED INDEX IX_SalesPerson_SalesQu
  ota_SalesYTD ON Sales.SalesPerson (SalesQuota,
  SalesYTD)
• Indice agrupado.
• El orden lógico de los valores de clave determina
  el orden físico de las filas correspondientes de
  la tabla.
• El nivel inferior u hoja, de un índice agrupado
  contiene las filas de datos en sí de la tabla.
• Una tabla o vista permite un índice agrupado al
  mismo tiempo.
• CREATE INDEX IX_ProductVendor_VendorID ON
  Purchasing.ProductVendor (VendorID, SalesQuota,
  ),

31
índice no agrupado

• Almacena, por cada elemento del índice, un
  "identificador" para encontrar en la tabla los
  datos asociados a este.
• Cuando el DBMS utiliza el índice y determina que
  debe ir a buscar los datos, utiliza ese
  "identificador" para ubicar los datos en la
  tabla.
• En este caso el identificador utilizado es el RID
  (row id)
• RID está conformado por
• el numero del archivo de la base de datos
• el número de la página dentro de ese archivo.
• el número de la fila en la página.

RID
32
Índice Agrupado

• El identificador utilizado es la llave del
  índice agrupado.
• La llave del índice agrupado está conformado por
• Todas las columnas del índice agrupado.
• No confundir la llave del índice con la llave
  primaria de una tabla, aunque muchas veces tienen
  el mismo "valor," en especial cuando la tabla se
  creó sin darle mayor importancia a este tema.

33
Búsqueda con índice

• También se denomina LookUp
• Realiza una operación de búsqueda, con un
  "identificador" de una fila, para traer más
  información, de la fila específica, desde la
  tabla.
• Índice agrupado. Utiliza el RID.
• Índice no agrupado. Utiliza la llave del índice
  agrupado.

34
Búsqueda con índices

• Índice no agrupado.
• Se usa el RID.
• Se identifica las filas que cumplen con la
  condición de filtrado.
• Resultado Celdas amarillas.
• Índice agrupado.
• Se usan los tres identificadores de filas (rojo,
  verde y azul).
• Se realiza la operación de búsqueda en el índice
  agrupado.
• Se identifica las filas que cumplan los tres
  identificadores.

35
Índice agrupado Vs no agrupado

• Índice non-clustered, la clave tiene un puntero a
  la página de datos donde se encuentra el
  registro.
• Índice clustered, la leaf level es la pagina de
  datos.
• (se ahorra un salto para leer los datos del
  registro)
• La diferencia es importante, ya que el uso de
  este tipo de índices al evitar tener que hacer
  lecturas adicionales para traer el registro.

36
Índice único

• Dos filas no pueden tener valor de clave del
  índice.
• Las columnas utilizadas deben establecerse en NOT
  NULL (dado los valores nulos se consideran
  duplicados)
• No se admite la creación de un índice único sobre
  columnas que ya contengan valores duplicados,
  independientemente de si se ha establecido o no
  IGNORE_DUP_KEY en ON.
• CREATE UNIQUE INDEX AK_UnitMeasure_Name ON
  Production.UnitMeasure(Name)
• CREATE UNIQUE INDEX AK_Index ON Test (C2) WITH
  (IGNORE_DUP_KEY ON)
• CREATE UNIQUE INDEX AK_Index ON Test (C2) WITH
  (IGNORE_DUP_KEY OFF)

37
Ejercicio 4

• Diga qué tipo de índice (agrupado o no agrupado,
  único o no único) recomienda para cada columna de
  la tabla de hechos.
• Es necesario crear índices para las dimensiones?
• Es necesario crear índices para las medidas?

38
Estrategias

• Un DBMS optimiza una consulta usando dos
  enfoques
• Optimización basada en reglas
• rule-based optimization (RBO)
• Que consiste en usar índices siempre.
• Optimización basada en costos
• cost-based optimization (CBO)
• El cuál determina si usar un índice es apropiado

39
Tipos de índices

• Índices B-tree
• Usado para pequeño número de valores distintos en
  una columna
• Índices Bitmap
• Usado con el enfoque CBO.
• Se crea un índice para cada clave foránea.
• Índices Particionados
• Se denomina índices localizados, colocándose el
  índice en un partición determinada.
• Tablas organizadas por índice.
• En este tipo de índice la data esta sostenida en
  el índice, cambios en los datos solo representa
  cambios en el índice.

40
Estrategias de indexación
Que columnas son? Que tablas? Que tipo de
índice?
41
Índice B-tree

• Es usado para pocas filas
• Los datos se organizan como un árbol.
• A la derecha están los menores a la izquierda los
  mayores
• La forma de llegar a un valor es fija.
• Si los datos son
• A D G H P R Z

42
Ejercicio 5

• Crear un índice b-tree para los siguientes datos
• A B E F H I J P R T U X Z
• indicar el número de consultas para llegar a los
  siguientes datos
• A P Q

43
Índice Bitmap

• Almacena valores con 1s y 0s
• Utilizado en lugar de índices B-tree cuando
• Tablas son muy grandes
• Columnas tienen una cardinalidad baja
• Múltiples columnas son restringidas en la misma
  consulta.

44
Ejemplo índice Bitmap
CUSTOMER table
CUSTOMER MARTIAL_STATUS REGION GENDER INCOME_L
EVEL 101 single east male bracket_1 102 ma
rried central female bracket_4 103 married
west female bracket_2 104 divorced west ma
le bracket_4 105 single central female bracke
t_2 106 married central female bracket_3
CREATE BITMAP INDEX cust_region ON
customer(region)
Ejemplo de índice bitmap en la columna REGION
REGION east REGION central REGION
west 1 0 0 0 1 0 0 0 1 0 0 1 0
1 0 0 1 0
45
Ejemplo índice Bitmap
SELECT COUNT() FROM CUSTOMER WHERE
MARITAL_STATUS married AND REGION IN
(central, west)
status region region Querymarried
central west Result 0 0 0 0 0
0 1 1 0 1 1 1 1 AND 0 OR 1 1
AND 1 1 0 0 1 0 1 0 0 1 0 0 1 0
1 1 0 1 1 1
col 2
col 3
col 5
46
ESTRATEGIA DE ALMACENAMIENTO
47
Archivos de Datos

• Controlan la asignación de espacio en el disco.
• Asigna cuotas de espacio.
• Controla la disponibilidad de datos.
• Desarrolla el respaldo y recuperación parcial de
  la Base de Datos.
• Asigna almacenamiento de datos a través de los
  dispositivos

48
Estructura Física de la Base de Datos

• Provee asignación automática de extensiones
  (extents).
• Evita asignación dinámica de extensiones
  (extents).

Segmento 148KB
Extent 36KB
Extent 112KB
Base de Datos
2KB
2KB
2KB
2KB
2KB
49
Creando una base de datos

• CREATE DATABASE Sales ON
• ( NAME Sales_dat,
• FILENAME ''' _at_data_path 'saledat.mdf'',
• SIZE 10 MB,
• MAXSIZE 50MB,
• FILEGROWTH 5
• )
• LOG ON (
• NAME Sales_log,
• FILENAME ''' _at_data_path 'salelog.ldf'',
• SIZE 5MB,
• MAXSIZE 25MB,
• FILEGROWTH 5MB
• )

50
Creando una base de datos

• CREATE DATABASE Archive ON
• (FILENAME ''' _at_data_path 'archdat1.mdf'')
• FOR ATTACH
• CREATE DATABASE AdventureWorks ON
• (FILENAME 'c\Data\AdventureWorks_Data.mdf'),
• (FILENAME 'c\Data\AdventureWorks_log.ldf'),
• (FILENAME 'c\myFTCatalogs\AdvWksFtCat')
• FOR ATTACH

51
Alterando una Base de Datos

• ALTER DATABASE AdventureWorks ADD FILE
• ( NAME Test1dat2,
• FILENAME ''' _at_data_path 't1dat2.ndf'',
• SIZE 5MB,
• MAXSIZE 100MB,
• FILEGROWTH 5MB
• )

52
Evitando la Fragmentación

• Recordar actividades de carga e indexación.
• Determinar la tasa de crecimiento de la BD para
  no crecer constantemente.
• Mantener suficiente espacio en el DD

Extent 50 KB
CREATE TABLE sales_fact ... SIZE 50KB,
FILEGROWTH 25KB
Extent 50 KB
Extent 75 KB
53
Consideraciones de Archivos de Datos

• Crear un archivo de datos con las características
  del objeto que el va ha mantener.
• Localizar objetos grandes dentro de un archivo de
  datos .
• Localizar objetos con propiedades y patrones de
  acceso similares juntos.
• Se puede cambiar los datos de un archivo de datos
  
• Incrementando el numero de archivos de datos.
• Creando archivos de datos que puedan crecer
  dinámicamente

Tablespace SALES
Tablespace SALES
Tablespace SALES_1
Tablespace SALES_2
54
ESTRATEGIA DE PARTICIONAMIENTO
55
Particionamiento de índices y tablas

• Tablas grandes e índices pueden ser particionados
  en piezas mas pequeñas y manejables.

Estructura
56
Ventajas del Particionamiento

• Particionamiento puede ser útil en las siguientes
  áreas
• Grandes Base de Datos (VLDBs)
• Reducción de tiempo de baja para mantenimiento
  programado.
• Reducción de tiempo de baja por fallas de datos.
• Performance en Sistemas de Soporte a las
  Decisiones (DSS).
• Performance de I/O
• Disk striping.
• Particionamiento transparente.

57
Particionamiento Horizontal

• Particionamiento de datos de tablas e índices
  por
• Tiempo
• Ventas
• Geografía
• Organización
• Línea del negocio
• Partición por tiempo

58
Particionamiento Vertical

• Partición de datos por columna (raro)
• Permite acceso a cierto grupo de usuarios.

Col A Col B Col C Col D
59
Particiones

• El ejemplo siguiente crea un índice no agrupado
  con particiones en TransactionsPS1, un esquema de
  partición existente.
• Este ejemplo asume que se ha instalado el ejemplo
  de índice con particiones.
• CREATE NONCLUSTERED
• INDEX IX_TransactionHistory_ReferenceOrderID
• ON Production.TransactionHistory
  (ReferenceOrderID)
• ON TransactionsPS1 (TransactionDate)

60
ACTUALIZACIÓN DE LA META DATA
61
Actualizando el documento de Metadata
Información del modelo Físico
62
Actualizando el documento de Metadata
Información del plan de indexación
63
ROLAP, MOLAP, HOLAP
64
Esquema Físico

• Existe tres formas de almacenar los datos
• .

MOLAP - Multidimensional OLAP.
HOLAP - OLAP híbrido
ROLAP - Relacional OLAP.
65
MOLAP - OLAP Multidimensional

• Los datos origen y sus agregaciones están en una
  estructura multidimensional.
• Los objetos dimensionales son procesados para
  incorporar cambios de los datos operacionales, el
  resumen de datos se hace por adelantado.
• Existe latencia. tiempo comprendido entre
  procesamientos
• Características
• Provee excelente rendimiento y compresión de
  datos.
• Mejor tiempo de respuesta, depende de las las
  agregaciones.
• Estructura optimizada para maximizar las
  consultas.
• Apropiado para cubos de rápida respuesta.

66
ROLAP - OLAP Relacional

• La información del cubo, sus datos, su
  agregación, sumas son almacenados en una base de
  datos relacional.
• No copia la BD original, accede a las tablas
  origen.
• Es más lenta que las otras estrategias (MOLAP o
  HOLAP).
• Se utiliza para ahorrar espacio de almacenamiento
  en grandes DB de baja frecuencia de consulta.
• Usos comunes
• Cuando los clientes desean ver los cambios
  inmediatamente.
• Cuando contamos con grandes conjuntos de datos
  que no son frecuentemente buscados

67
HOLAP OLAP Híbrido

• Combina atributos de MOLAP y ROLAP.
• Las agregaciones se almacenen en una estructura
  multidimensional y los detalle, en la BD
  original.
• Cubos más pequeños q MOLAP y más rápidos q
  ROLAP.
• Usos comunes
• Cubos que requieren rápida respuesta
• Cuando existen sumarizaciones basadas en una gran
  cantidad de datos de origen.
• Solución de compromiso para bajar el espacio
  ocupado sin perjudicar totalmente el rendimiento
  de las consultas.

68
Características
  MOLAP ROLAP HOLAP
Almacenamiento de las Agregaciones Modelo Multidimensional Base de datos relacional Modelo Multidimensional
Almacenamiento de los datos Modelo Multidimensional Base de datos relacional Base de datos relacional
Facilidad de Creación Sencillo Muy Sencillo Sencillo
Velocidad de respuesta Buena Regular o Baja Buena para consultas que posean agregaciones, Regular para datos de bajo nivel
Escalabilidad Problemas de escalabilidad Son más escalables  
Recomendados para Cubos con uso frecuente Datos que no son frecuentemente usados Si el cubo requiere una rápida respuesta
69
Ventajas y Desventajas
  Ventajas Desventajas
MOLAP Mejor performance en los tiempos de respuesta Duplica el almacenamiento de datos (ocupa más espacio)
    Tiempo de Latencia
ROLAP Ahorra espacio de almacenamiento. Útil cuando se trabaja con muy grandes conjuntos de datos. El tiempo de respuesta a consultas es mayor.
HOLAP Buen tiempo de respuesta sólo para información sumarizada Volúmenes de datos más grandes en la base de datos relacional
70

• PREGUNTAS