Reportes

Mg. Samuel Oporto Díaz

Mapa del Curso

Inteligencia de Negocios

Metodología Kimball

Planeamiento del Proyecto

Modelo del

Negocio

Modelado Dimensional

Modelado Físico

ETL

Reportes

Minería de Datos

Tabla de Contenido

• Resúmenes• Selección de resúmenes• Resúmenes en Oracle

Objetivos

Explicar porque los resúmenes son usados en el warehouse y listar los beneficios de tablas resumidas.

Discutir la configuración de tablas resumen. Describir recomendaciones para seleccionar dimensiones

y niveles de resumen. Identificar las restricciones para administrar las tablas

resumen.

RESÚMENES

¿Que son los Resúmenes?¿Que son los Resúmenes?

Almacena datos pre resumidos. Son basados en requerimientos de consultas de usuarios.

Almacena datos pre resumidos. Son basados en requerimientos de consultas de usuarios.

ProductoProducto

RegiónRegión

TiempoTiempo

Resumen de Resumen de VentasVentas

CiudadCiudad

VentasVentas

EstadoEstado

¿Porque resumir Datos?¿Porque resumir Datos?

Incrementa el tiempo de respuesta de consultas

Optimiza la utilización de recursos.

Mejora el análisis de procesos.

Incrementa el tiempo de respuesta de consultas

Optimiza la utilización de recursos.

Mejora el análisis de procesos.

¿Porque un modelo de Resúmenes ahora?¿Porque un modelo de Resúmenes ahora?

Diseñar los resúmenes antes de la implementación. Crear los resúmenes. Evaluar el uso de resúmenes y potencialmente revisar la

aproximación.

Diseñar los resúmenes antes de la implementación. Crear los resúmenes. Evaluar el uso de resúmenes y potencialmente revisar la

aproximación.

Un ejemplo simple sin resúmenesUn ejemplo simple sin resúmenes

Tiempo

1095 días

Tabla hechos ventas

109,500,000filas

Producto

10,000 producto

Almacén

100 almacenes

Búsqueda (Scan)

Total ventas por año

Un ejemplo simple con resúmenesUn ejemplo simple con resúmenes

Año

3 años

tablaresumenventas

3,000,000filas

Producto

10,000 productos

Almacén

100 almacenes

Atributos Jerárquicos - Tablas dimensiónAtributos Jerárquicos - Tablas dimensión

Jerarquía GeográficaJerarquía Mercado

Grupo Grupo

Total

Clase Clase

Producto

Total

Región Región

Distrito Distrito

Store

Total

Estado Estado

Ciudad Ciudad

Store

Dimensión Producto:Dimensión Producto:1 Jerarquía1 Jerarquía

Dimensión Almacén:Dimensión Almacén:2 Jerarquías2 Jerarquías

Resúmenes de N - CaminosResúmenes de N - Caminos

1 - Camino

2 - Camino

3 - Camino

Total ventas por año,por ítem,por almacén

Total ventas por día,por categoría, por región

Total ventas por mes,por categoría, por región

T3

T2

T1

P2S3

S2

S1

AñoMesDía

Cat.

ÍtemAlmacén

Distrito

Región

P1

T3

T2

T1 P1

S2

S1

P2S3

T3

T2

T1

P2

P1

S2

S1

S3

Alternativas de diseñoAlternativas de diseño

Dos aproximaciones fundamentales de diseño para resúmenes:

Múltiples tablas hechos resumen (configuración de constelación)

Una tabla hechos grande con datos hechos detallados y datos resumidos almacenados en la misma tabla.

Dos aproximaciones fundamentales de diseño para resúmenes:

Múltiples tablas hechos resumen (configuración de constelación)

Una tabla hechos grande con datos hechos detallados y datos resumidos almacenados en la misma tabla.

Configuración de ConstelaciónConfiguración de Constelación

Tablas Dimensión Resumen

Hecho Atómico

Hecho Resumen

Resumen 1-Camino: DistritoResumen 1-Camino: Distrito

Tabla hecho Atómica

Tabla Hecho Resumen (por distrito) d

P

T

C

S

Distrito (almacén) Tabla Dimensión Resumen

Resumen 1-Camino: RegiónResumen 1-Camino: Región

Tabla hecho Atómico

Tabla Hecho Resumen (por región)

P

T

C

S

d

r

Región (almacén) Tabla resumen dimensión

Resumen 2-Caminos: Categoría y DistritoResumen 2-Caminos: Categoría y Distrito

Distrito (almacén) tabla dimensión resumen

Tabla hecho Atómico

Tabla Hecho resumen(por categoría y distrito)

c d

Categoría (producto) tabla dimensión resumen

P

T

C

S

Entendiendo las restricciones de ResumenEntendiendo las restricciones de Resumen

Tamaño restricciones

Restricciones de Carga en lote

Tamaño restricciones

Restricciones de Carga en lote

DimensiónProductoMercadoTiempo

Base250045060

Level1503020

Level2555

Level3111

Estimando el tamaño de hecho resumenEstimando el tamaño de hecho resumen

• Creando resúmenes en toda combinación de niveles dentro de los siguientes esquemas:

• Creando resúmenes en toda combinación de niveles dentro de los siguientes esquemas:

Escogiendo ResúmenesEscogiendo Resúmenes

6 atributos dimensionales con profundidades jerárquicas modestas

SELECCIÓN DE RESÚMENES

Guía para selección de resúmenesGuía para selección de resúmenes

Orden de la clase/análisis agregado Utilización de análisis de patrones.

Orden de la clase/análisis agregado Utilización de análisis de patrones.

Análisis Orden de la clase/agregadoAnálisis Orden de la clase/agregado

1. Análisis orden de la clase es desarrollado para determinar el beneficio relativo de pre ordenar la tabla hecho.

2. Análisis agregado es desarrollado para determinar el impacto de adicionar resúmenes para una tabla hechos pre ordenada.

3. La mejor combinación de ordenamiento y resúmenes son seleccionados.

1. Análisis orden de la clase es desarrollado para determinar el beneficio relativo de pre ordenar la tabla hecho.

2. Análisis agregado es desarrollado para determinar el impacto de adicionar resúmenes para una tabla hechos pre ordenada.

3. La mejor combinación de ordenamiento y resúmenes son seleccionados.

Paso 1: Fact Table orden de la clasePaso 1: Fact Table orden de la clase

Objetivo: Almacena los datos en un orden de clase que mezcla un acceso de camino primario.

Beneficios: Provee datos localmente para consultas a través de un

camino de acceso primario. Disminuye la necesidad para resumir.

Objetivo: Almacena los datos en un orden de clase que mezcla un acceso de camino primario.

Beneficios: Provee datos localmente para consultas a través de un

camino de acceso primario. Disminuye la necesidad para resumir.

Una tabla de hechos no ordenadaUna tabla de hechos no ordenada

Trabajo de carga de Alquileres Hollywood: Los administradores del almacén necesitan alquileres

totales para sus almacenes cada día: 3000 I/Os por 3000 filas de productos.

Los administradores del Producto necesitan alquileres totales para sus productos cada dia: 150 I/Os por 150 filas de almacén.

Total de carga de trabajo: 3150 I/Os

Trabajo de carga de Alquileres Hollywood: Los administradores del almacén necesitan alquileres

totales para sus almacenes cada día: 3000 I/Os por 3000 filas de productos.

Los administradores del Producto necesitan alquileres totales para sus productos cada dia: 150 I/Os por 150 filas de almacén.

Total de carga de trabajo: 3150 I/Os

Ordenada por AlmacénOrdenada por Almacén

Día / Almacén / ProductoDía / Almacén / Producto

Bloque 1 Día 1, Store 1, Prod 1, unidades, dólares-- -- -- -- --

fila 200 Día 1, Store 1, Prod 200, unidades, dólaresBloque 2 Día 1, Store 1, Prod 201, unidades, dólares

-- -- -- -- --fila 400 Día 1, Store 1, Prod 400, unidades, dólares

-- -- -- -- --Bloque 15 Día 1, Store 1, Prod 2801, unidades, dólares

-- -- -- -- --fila 3000 Día 1, Store 1, Prod 3000, unidades, dólares

-- -- -- -- --Bloque 2250 Día 1, Store 150, Prod 2801,unidades, dólares

-- -- -- -- --fila 450000 Día 1, Store 150, Prod 3000,unidades, dólares

Ordenada por ProductoOrdenada por Producto

Día / Producto / AlmacénDía / Producto / Almacén

Bloque 1 Día 1, Prod 1, Store 1, units, dólares-- -- -- -- --

fila 150 Día 1, Prod 1, Store 150, units, dólaresfila 151 Día 1, Prod 2, Store 1, units, dólaresfila 200 Día 1, Prod 2, Store 50, units, dólaresBloque 2 Día 1, Prod 2, Store 51, units, dólares

-- -- -- -- --fila 400 Día 1, Prod 3, Store 100, units, dólares

-- -- -- -- --Bloque 2250 Día 1, Prod 2999, Store 101, units, dólares

-- -- -- -- --Día 1, Prod 2999, Store 150, units, dólaresDía 1, Prod 3000, Store 1, units, dólares-- -- -- -- --

fila 450000 Día 1, Prod 3000, Store 150, units, dólares

Ordenado versus No ordenadoOrdenado versus No ordenado

Carga trabajo total para Almacén y Producto: No ordenada = 3,150 I/Os Ordenada por almacén = 165 I/Os Ordenada por producto = 2,251 I/Os

Carga trabajo total para Almacén y Producto: No ordenada = 3,150 I/Os Ordenada por almacén = 165 I/Os Ordenada por producto = 2,251 I/Os

Paso 2: Considerar inclusión de ResúmenesPaso 2: Considerar inclusión de Resúmenes

Datos localmente pueden ser eliminar la necesidad para algunos resúmenes.

Tomar dentro de la cuenta: Caminos de acceso Primario Requerimiento de tiempo de respuesta. Requerimiento de Carga Requerimiento de Construcción

Desarrolla regla del pulgar: 10/20

Datos localmente pueden ser eliminar la necesidad para algunos resúmenes.

Tomar dentro de la cuenta: Caminos de acceso Primario Requerimiento de tiempo de respuesta. Requerimiento de Carga Requerimiento de Construcción

Desarrolla regla del pulgar: 10/20

Análisis de resúmenes: Desarrollar CriterioAnálisis de resúmenes: Desarrollar Criterio

Si es ordenada por almacén: 15 I/Os para total almacén (contiguo): Resumen de

almacén no necesariamente requerido. 150 I/Os para total producto: Resumen Producto

requerido. Si es ordenada por producto:

1 I/O para total producto: Resumen Producto no es requerido.

2,250 I/Os para total almacén: Resumen Almacén requerido.

Si es ordenada por almacén: 15 I/Os para total almacén (contiguo): Resumen de

almacén no necesariamente requerido. 150 I/Os para total producto: Resumen Producto

requerido. Si es ordenada por producto:

1 I/O para total producto: Resumen Producto no es requerido.

2,250 I/Os para total almacén: Resumen Almacén requerido.

Análisis de resúmenesAnálisis de resúmenes

Impacto de resúmenes en el tamaño y performance de la tabla hechos

Ordenado por almacén con resumen producto: 3,000 total resumen filas por día. 2 I/Os requerido (1 I/O por total de ventas por producto

de la tabla hecho resumen; 1 I/O para total ventas por almacén de la tabla hechos base).

Ordenada por producto con el resumen de almacén: 150 total de filas resumidas por día. 2 I/Os requerido (1 I/O por total venta por producto de

la tabla hecho base; 1 I/O por total venta por almacén de la tabla hecho resumen)

Impacto de resúmenes en el tamaño y performance de la tabla hechos

Ordenado por almacén con resumen producto: 3,000 total resumen filas por día. 2 I/Os requerido (1 I/O por total de ventas por producto

de la tabla hecho resumen; 1 I/O para total ventas por almacén de la tabla hechos base).

Ordenada por producto con el resumen de almacén: 150 total de filas resumidas por día. 2 I/Os requerido (1 I/O por total venta por producto de

la tabla hecho base; 1 I/O por total venta por almacén de la tabla hecho resumen)

Paso 3: Que combinación es la mejor?Paso 3: Que combinación es la mejor?

• Ordenada por producto con un resumen de almacén: 2 I/Os y 150 filas agregadas por día.

Regla del pulgar:• El camino de acceso primario dimensiona con la mas alta

cardinalidad es la mejor candidata para el ordenamiento.

• Ordenada por producto con un resumen de almacén: 2 I/Os y 150 filas agregadas por día.

Regla del pulgar:• El camino de acceso primario dimensiona con la mas alta

cardinalidad es la mejor candidata para el ordenamiento.

Resumen de NavegaciónResumen de Navegación

Uso efectivo de tablas resumen requiere conocimiento de la tabla resumen.

Métodos para navegación resumida: Motor de Base de Datos Warehouse. Productos propietarios completamente resumidos. Middleware abierto completamente resumidos. 3GL y soluciones de meta data.

Uso efectivo de tablas resumen requiere conocimiento de la tabla resumen.

Métodos para navegación resumida: Motor de Base de Datos Warehouse. Productos propietarios completamente resumidos. Middleware abierto completamente resumidos. 3GL y soluciones de meta data.

select total_sales...

Que resumenes?

Administrando datos resumen históricosAdministrando datos resumen históricos

1997199619951993/1994

Detalle diario últimos 12 meses

Datosresumidosmensualmente

DatosresumidosCuatrimestre

Datosresumidosanualmente

RESÚMENES EN EL ORACLE

Administración de resúmenes en OracleAdministración de resúmenes en Oracle

• Resúmenes son creados utilizando vistas materializadas (materialized views) y dimensiones.

• Summary Advisor provee consejo en la creación, retención y borrado de vistas materializadas.

VentasVentas

Ventas RegionalesVentas Regionales

Cantidad ProductosCantidad Productos

Ventas CuatrimestralesVentas Cuatrimestrales

CREATE MATERIALIZED VIEW sales_sumry TABLESPACE sum_data STORAGE(INITIAL 200K NEXT 200K PCTINCREASE 0) PARALLEL(...) BUILD IMMEDIATE REFRESH FAST ENABLE QUERY REWRITE ASSELECT p.brand, c.city_name, t.month, SUM(s.amt) AS tot_sales. . .GROUP BY p.brand,c.city_name,t.month;

CREATE MATERIALIZED VIEW sales_sumry TABLESPACE sum_data STORAGE(INITIAL 200K NEXT 200K PCTINCREASE 0) PARALLEL(...) BUILD IMMEDIATE REFRESH FAST ENABLE QUERY REWRITE ASSELECT p.brand, c.city_name, t.month, SUM(s.amt) AS tot_sales. . .GROUP BY p.brand,c.city_name,t.month;

Creando una vista materializada resumidaCreando una vista materializada resumida

Consultando reescritura en OracleConsultando reescritura en Oracle

SELECT p.brand, c.city_name, t.month, SUM(s.amt)FROM sales s, city c, timetab t, product pWHERE s.city_code = c.city_code

AND s.state_code = c.state_codeAND s.sdate = t.sdateAND s.prod_code = p.prod_code

GROUP BY p.brand, c.city_name, t.monthHAVING SUM(s.amt) > 5000000;

SELECT brand, city_name, month, tot_salesFROM sales_sumryWHERE tot_sales > 5000000;

Refrescando las vistas materializadasRefrescando las vistas materializadas

• Vistas materializadas necesitan ser actualizadas para reflejar modificaciones para datos de la tabla base utilizando uno de los tipos contemplados: Complete Fast Forcé Never

• Vistas materializadas necesitan ser actualizadas para reflejar modificaciones para datos de la tabla base utilizando uno de los tipos contemplados: Complete Fast Forcé Never

Dimensión de OracleDimensión de Oracle

Estructura de Diccionario de Datos que definimos jerarquías basadas en las columnas existentes.

Dimensiones son opcional, pero altamente recomendadas, porque ellos: Facilita reescribir consultas adicionales sin el uso de

restricciones. Ayuda documentación de Jerarquías. Puede ser usado por herramientas OLAP de

procesamiento analítico en línea (OLAP).

Estructura de Diccionario de Datos que definimos jerarquías basadas en las columnas existentes.

Dimensiones son opcional, pero altamente recomendadas, porque ellos: Facilita reescribir consultas adicionales sin el uso de

restricciones. Ayuda documentación de Jerarquías. Puede ser usado por herramientas OLAP de

procesamiento analítico en línea (OLAP).

Año _KeyAño _Key

Cuatrimestre_KeyCuatrimestre_Key

Mes_KeyMes_Key

Dimensiones y Jerarquías en OracleDimensiones y Jerarquías en Oracle

TodosTodos

Jerarquía Jerarquía CalendarioCalendario

Fecha _ ventasFecha _ ventas

Desc_MesDesc_Mes

Nivel Nivel llavesllaves

AtributoAtributo

Ejemplo DimensiónEjemplo Dimensión

Table TIME- YEAR_KEY- QUARTER_KEY- MONTH_KEY- MONTH_DESC- SALES_DATE

Table TIME- YEAR_KEY- QUARTER_KEY- MONTH_KEY- MONTH_DESC- SALES_DATE

Dimension TIME_DIM- YR - QTR - MON, MONTH_DESC - SDATE

Dimension TIME_DIM- YR - QTR - MON, MONTH_DESC - SDATE

Definiendo Dimensiones y JerarquíasDefiniendo Dimensiones y Jerarquías

CREATE DIMENSION time_dim LEVEL sdate IS time.sales_date LEVEL mon IS time.month_key LEVEL qtr IS time.quarter_key LEVEL yr IS time.year_keyHIERARCHY calendar_rollup ( sdate CHILD OF mon CHILD OF qtr CHILD OF yr )ATTRIBUTE mon DETERMINES month_desc;

CREATE DIMENSION time_dim LEVEL sdate IS time.sales_date LEVEL mon IS time.month_key LEVEL qtr IS time.quarter_key LEVEL yr IS time.year_keyHIERARCHY calendar_rollup ( sdate CHILD OF mon CHILD OF qtr CHILD OF yr )ATTRIBUTE mon DETERMINES month_desc;

Año

Cuatrim.

Mes

Fecha Ventas

Dimensiones con múltiples JerarquíasDimensiones con múltiples Jerarquías

YR

QTR

MON

YR

WK

DTDT

JerarquíaCALENDARIO

JerarquíaSEMANA

=

=

Rescribe usando Dimensiones en OracleRescribe usando Dimensiones en Oracle

La siguiente reescritura utiliza un rollup a lo largo de la dimensión TIME_DIM:

La siguiente reescritura utiliza un rollup a lo largo de la dimensión TIME_DIM:

SELECT v.year, s.brand, s.city_name, SUM(s.tot_sales) FROM sales_sumry s, (SELECT distinct t.month, t.year FROM time t) v WHERE s.month = v.month GROUP BY v.year, s.brand, s.city_name;

SELECT t.year, p.brand , c.city_name, SUM(s.amt) FROM sales s, city c, time t, product pWHERE s.sales_date = t.sdate AND s.city_name = c.city_name AND s.state_code = c.state_codeAND s.prod_code = p.prod_code GROUP BY t.year, p.brand, c.city_name;

DiccionarioDiccionariode Datosde Datos

Summary Advisor en OracleSummary Advisor en Oracle

Utilizaciónde sumario

Summary Advisor(DBMS_OLAP package)

RecomendacionesRecomendacionesResumen Resumen

RequerimientoRequerimientode Espacio de Espacio

Carga trabajo Carga trabajo OpcionalOpcional

Oracle Trace

ResumenResumen

En esta lección, ud debería haber aprendido como: Explicar porque los resúmenes son usados en el

warehouse y listar los beneficios de tablas resumidas. Discutir configuraciones de tabla resumen. Describir guías para seleccionar dimensiones y niveles de

resumen. Identificar las restricciones para administrar las tablas

resumen. Discutir las técnicas de administración de resúmenes en

Oracle.

En esta lección, ud debería haber aprendido como: Explicar porque los resúmenes son usados en el

warehouse y listar los beneficios de tablas resumidas. Discutir configuraciones de tabla resumen. Describir guías para seleccionar dimensiones y niveles de

resumen. Identificar las restricciones para administrar las tablas

resumen. Discutir las técnicas de administración de resúmenes en

Oracle.

PrácticaPráctica

Esta practica cubre los siguientes tópicos: Estimando el tamaño de la tabla hechos resumen si los

resúmenes son creados por producto y tiempo. Desarrollar una estrategia de tabla resumen para soportar

los requerimientos del negocio para el usuario.

Esta practica cubre los siguientes tópicos: Estimando el tamaño de la tabla hechos resumen si los

resúmenes son creados por producto y tiempo. Desarrollar una estrategia de tabla resumen para soportar

los requerimientos del negocio para el usuario.

PREGUNTAS