Tópicos avanzados de bases de datos.

3.1 Bodegas de datos (Datawarehouse)

3.1.1 Definición y objetivo.

• En el contexto de la informática, un almacén de datos (del inglés data warehouse) es una colección de datos orientada a un determinado ámbito (empresa, organización, etc.), integrado, no volátil y variable en el tiempo, que ayuda a la toma de decisiones en la entidad en la que se utiliza

Un Data warehouse usa una representación multidimensional de los datos (cubos). Por la

dimensión de tiempo.

Fuentes y usos del Data warehouse.

Definición 2.

• Es un repositorio de datos de muy fácil acceso, alimentado de numerosas fuentes, transformadas en grupos de información sobre temas específicos de negocios, para permitir nuevas consultas, análisis, reportes y decisiones.

• Se trata, sobre todo, de un expediente completo de una organización, más allá de la información transaccional y operacional, almacenado en una base de datos diseñada para favorecer el análisis y la divulgación eficiente de datos (especialmente OLAP, procesamiento analítico en línea).

• Los almacenes de datos contienen a menudo grandes cantidades de información que se subdividen a veces en unidades lógicas más pequeñas dependiendo del subsistema de la entidad del que procedan o para el que sean necesario.

Objetivos fundamentales.• Registrar información a través del tiempo.• Tener información accesible, entendible,

navegable y con buen desempeño.• Tener información consistente. Significa

contabilizada y completa.• Información adaptable y elástica. Diseñado para

continuos cambios, permite nuevas preguntas y nuevos datos.

• Proteger la información. Permite buena visibilidad sobre el uso de los datos.

• Soportar la toma de decisiones.

3.1.2 Funcionamiento.• Un data warehouse se crea al extraer datos desde una

o más bases de datos de aplicaciones operacionales. Los datos extraídos son transformados para eliminar inconsistencias y resumir si es necesario y luego, cargados en el data warehouse.

• El proceso anterior ETL (extracción, transformación y carga), permite crear el detalle de tiempo variante, resumir y combinar los extractos de datos, ayudando a crear el ambiente para el acceso a la información Institucional.

• Este nuevo enfoque ayuda a las personas individuales, en todos los niveles de la empresa, a efectuar su toma de decisiones con más responsabilidad.

• La innovación de la Tecnología de Información dentro de un ambiente data warehouse, puede permitir a cualquier organización hacer un uso más óptimo de los datos, como un ingrediente clave para un proceso de toma de decisiones más efectivo.

• Las organizaciones tienen que aprovechar sus recursos de información para crear la información de la operación del negocio, pero deben considerarse las estrategias tecnológicas necesarias para la implementación de una arquitectura completa de data warehouse.

• operational data store (ODS)• Staging Area is temporary location where data from source systems is copied.

3.1.3 Consideraciones de diseño

• Antes de iniciar el diseño, es imperativo que los objetivos de la arquitectura del data warehouse sean claros y bien comprendidos.

• Es fundamental comprender a los diferentes tipos de usuarios, sus necesidades, y las características de sus interacciones con el data warehouse.

3.1.3 Consideraciones de diseño• Una de las claves del éxito en la construcción de

un data warehouse es el desarrollo de forma gradual, seleccionando a un departamento usuario como piloto y expandiendo progresivamente el almacén de datos a los demás usuarios. Por ello es importante elegir este usuario inicial o piloto, siendo importante que sea un departamento con pocos usuarios, en el que la necesidad de este tipo de sistemas es muy alta y se puedan obtener y medir resultados a corto plazo.

Consideraciones de diseño.

• Orientado a optimizar las consultas relacionadas con los aspectos del negocio que se desean estudiar.

• Identificar las tablas de hechos. Por cada aspecto del negocio que interese estudiar debe aparecer una tabla de hechos.

• Identificar las tablas de dimensión (esto es, decidir cuáles son los parámetros por los que interesa realizar el estudio).

Fuente de consulta.Leer artículo: Data Warehouse Design

Considerations• Microsoft SQL 2000 Technical Articles• Dave Browning and Joy Mundy

Microsoft Corporation• December 2001• http://msdn.microsoft.com/en-us/library/

aa902672(SQL.80).aspx

Resumen del artículo.• Designing a Data Warehouse: Prerequisites

Data Warehouse Architecture Goals Data Warehouse Users How Users Query the Data Warehouse

• Developing a Data Warehouse: Details Identify and Gather Requirements Design the Dimensional Model Develop the Architecture Design the Relational Database and OLAP Cubes Develop the Operational Data Store Develop the Data Maintenance Applications Develop Analysis Applications Test and Deploy the System.

• Documento completo en Word.

3.1.4 Herramientas para extraer, transformar y cargar fuentes de datos.• ETL (Extract, Transform and Load o “Extraer,

transformar y cargar”) es el proceso que permite a las organizaciones mover datos desde múltiples fuentes, reformatearlos y limpiarlos, y cargarlos en otra base de datos, data mart, o data warehouse para analizar, o en otro sistema operacional para apoyar un proceso de negocio.

Proceso ETL

Proceso ETL

Proceso ETL

Descripción de etapas ETLExtraer. • Consiste en obtener los datos desde los sistemas de

origen. La mayoría de los proyectos de almacenamiento de datos fusionan datos provenientes de diferentes sistemas de origen. Cada sistema separado puede usar una organización diferente de los datos o formatos distintos. Los formatos de las fuentes normalmente se encuentran en bases de datos relacionales o ficheros planos, pero pueden incluir bases de datos no relacionales u otras estructuras diferentes. La extracción convierte los datos a un formato preparado para iniciar el proceso de transformación.

Extraer.

• Una parte intrínseca del proceso de extracción es la de analizar los datos extraídos.

• Un requerimiento importante que se debe exigir a la tarea de extracción es que ésta cause un impacto mínimo en el sistema origen.

Transformar.

• La fase de transformación aplica una serie de reglas de negocio o funciones sobre los datos extraídos para convertirlos en datos que serán cargados. Algunas fuentes de datos requerirán alguna pequeña manipulación de los datos.

TransformarAlgunos ejemplos de transformaciones.• Seleccionar sólo ciertas columnas para su carga (por ejemplo, que las

columnas con valores nulos no se carguen). • Traducir códigos (por ejemplo, si la fuente almacena una "H" para Hombre

y "M" para Mujer pero el destino tiene que guardar "1" para Hombre y "2" para Mujer).

• Codificar valores libres (por ejemplo, convertir "Hombre" en "H" o "Sr" en "1").

• Obtener nuevos valores calculados (por ejemplo, total_venta = cantidad * precio).

• Unir datos de múltiples fuentes (por ejemplo, búsquedas, combinaciones, etc.).

• Calcular totales de múltiples filas de datos (por ejemplo, ventas totales de cada región).

• Generación de campos clave en el destino. • Transponer o pivotar (girando múltiples columnas en filas o viceversa). • Dividir una columna en varias (por ejemplo, columna "Nombre: García,

Miguel"; pasar a dos columnas "Nombre: Miguel" y "Apellido: García").

Transformar.

Al final del proceso de transformar, se tienen dos opciones generales:

• Datos correctos: Entregar datos a la siguiente etapa (Carga).

• Datos erróneos: Ejecutar políticas de tratamiento de excepciones (por ejemplo, rechazar el registro completo, dar al campo erróneo un valor nulo o un valor centinela).

Cargar

• Es el momento en el cual los datos de la fase anterior (transformar) son cargados en el sistema de destino.

• Dependiendo de los requerimientos de la organización, este proceso puede abarcar una amplia variedad de acciones diferentes. En algunas bases de datos se sobrescribe la información antigua con nuevos datos.

• Los data warehouse mantienen un historial de los registros de manera que se pueda hacer una auditoría de los mismos y disponer de un rastro de toda la historia de un valor a lo largo del tiempo.

CargarExisten dos formas básicas de desarrollar el proceso de carga:

• Acumulación simple: Es la más sencilla y común, y consiste en realizar un resumen de todas las transacciones comprendidas en el período de tiempo seleccionado y transportar el resultado como una única transacción hacia el data warehouse, almacenando un valor calculado que consistirá típicamente en un sumatorio o un promedio de la magnitud considerada.

• Rolling: Se aplica en los casos en que se opta por mantener varios niveles de granularidad. Para ello se almacena información resumida a distintos niveles, correspondientes a distintas agrupaciones de la unidad de tiempo o diferentes niveles jerárquicos en alguna o varias de las dimensiones de la magnitud almacenada (por ejemplo, totales diarios, totales semanales, totales mensuales, etc.).

Algunas Herramientas ETL

• Ab Initio • Barracuda Software (Integrator) • MakeWare Soluciones Tecnologicas http://• Benetl • Biable http://www.visiontecnologica.com www.makeware.net • BITool - ETL Software http://www.bitool.com/ • BOPOS TLOG-4690 rhiscom (back-office POS) • CloverETL [1] • Cognos Decisionstream • Data Integrator (herramienta de Business Objects) • Data Migraton Toolset de Backoffice Associates (BoA)

http://www.boaweb.com/migrationtoolset.htm • Genio, Hummingbird • IBM Websphere DataStage (Previously Ascential DataStage) • Informática PowerCenter • metaWORKS ( Document Tools) • Microsoft DTS (incluido en SQL-Server 2000) • Microsoft Integration Services (MS SQL Server 2005) • MySQL Migration Toolkit • Scriptella ETL - Libre, Apache-licensed ETL • Oracle Warehouse Builder • WebFocus-iWay DataMigrator Server

3.2 Procesamiento y análisis en línea (OLAP).

3.2.1 Definiciones y conceptos.• OLAP es el acrónimo en inglés de procesamiento

analítico en línea (On-Line Analytical Processing). Es una solución utilizada en el campo de la llamada Inteligencia empresarial (o Business Intelligence) cuyo objetivo es agilizar la consulta de grandes cantidades de datos. Para ello utiliza estructuras multidimensionales (o Cubos OLAP) que contienen datos resumidos de grandes Bases de datos o Sistemas Transaccionales (OLTP). Se usa en informes de negocios de ventas, marketing, informes de dirección, minería de datos y áreas similares.

• La razón de usar OLAP para las consultas es la velocidad de respuesta. Una base de datos relacional almacena entidades en tablas discretas si han sido normalizadas. Esta estructura es buena en un sistema OLTP pero para las complejas consultas multitabla es relativamente lenta. Un modelo mejor para búsquedas (aunque peor desde el punto de vista operativo) es una base de datos multidimensional.

• La principal característica que potencia a OLAP, es que es lo más rápido a la hora de ejecutar sentencias SQL de tipo SELECT, en contraposición con OLTP que es la mejor opción para operaciones de tipo INSERT, UPDATE Y DELETE.

Funcionalidad

• En la base de cualquier sistema OLAP se encuentra el concepto de cubo OLAP (también llamado cubo multidimensional o hipercubo). Se compone de hechos numéricos llamados medidas que se clasifican por dimensiones. El cubo de metadatos es típicamente creado a partir de un esquema en estrella o copo de nieve, esquema de las tablas en una base de datos relacional. Las medidas se obtienen de los registros de una tabla de hechos y las dimensiones se derivan de la dimensión de los cuadros.

Ejemplo de OLAP

Ejemplo de herramienta OLAP

Herramienta OLAP para Excel

3.2.2 Requerimientos funcionales de los sistemas OLAP.

• Para el funcionamiento de un sistema OLAP se requiere:

3.2.3 Operadores para manejo de cubos de datos del estándar SQL3.

• Los vimos en la unidad 1:

• Operaciones en cubos: rebanada, dado, ascenso, descenso, pivote.

• Operadores: cube, rollup, grouping sets.

3.2.4 Diseño de consultas a BDM.

• Normalmente las consultas se enfocan en obtener información resumida.

• Analizar las consultas vistas en la unidad 1.

• Leer el artículo: – Analyzing Data with ROLLUP, CUBE, AND TOP-N QUERIES– http://www.cs.umbc.edu/portal/help/oracle8/

server.815/a68003/rollup_c.htm– Del manual: Oracle8i Application Developer's Guide –

Fundamentals. Release 8.1.5. A68003-01

3.2.5 Utilización de herramientas para OLAP.

• Se tienen herramientas comerciales y otras gratuitas.

Comparación de servidores OLAP

3.3 Mercados de datos (Data Mart)

3.3.1 Definiciones y conceptos.

Data Mart.• En síntesis, se puede decir que los data marts

son pequeños data warehouse centrados en un tema o un área de negocio específico dentro de una organización.

Data mart.• A data mart is a subset of an organizational data

store, usually oriented to a specific purpose or major data subject, that may be distributed to support business needs. Data marts are analytical data stores designed to focus on specific business functions for a specific community within an organization. Data marts are often derived from subsets of data in a data warehouse, though in the bottom-up data warehouse design methodology the data warehouse is created from the union of organizational data marts.

3.3.2 Fases de construcción.

• Posibles etapas para la construcción de un Data mart:– Análisis. – Construcción.– Post-producción.

Metodologías de diseño.• En los manuales de Oracle se sugiere la siguiente

metodología:

• Artículo: Design the Data Mart• Autor: Oracle® Business Intelligence Standard Edition

One TutorialRelease 10g (10.1.3.2.1)E10312-01

• http://download-west.oracle.com/docs/cd/E10352_01/doc/bi.1013/e10312/dm_design.htm

• Clic aquí para enlace local al documento.

Metodologías de diseño.

• Moody y Kortink, describen una opción de metodología en el artículo:

• Artículo: From Enterprise Models to Dimensional Models: A Methodology for Data Warehouse and Data Mart Design

• Autores: Daniel L. Moody, Mark A.R. Kortink• Clic aquí para un enlace local al documento.

3.3.3 Tecnologías.• Algunos autores clasifican las tecnologías de software en las

categorías de “front_end” y “back-end”. El front-end es la parte del software que interactúa con el o los usuarios y el back-end es la parte que procesa la entrada desde el front-end.

• La separación del sistema en "front ends" y "back ends" es un tipo de abstracción que ayuda a mantener las diferentes partes del sistema separadas. La idea general es que el front-end sea el responsable de recolectar los datos de entrada del usuario, que pueden ser de muchas y variadas formas, y procesarlas de una manera conforme a la especificación que el back-end pueda usar. La conexión del front-end y el back-end es un tipo de interfaz.

Tecnologías.

Una clasificación más específica del software para los Data mart y Data warehouse y algunos ejemplos:

• Herramientas de Consulta y Reporte (Crystal Reports).• Herramientas de Base de Datos (OLAP Office, Analysis

Services).• Sistemas de Información Ejecutivos• Bases de Datos usados para Data Warehouse (Oracle,

MySQL, SQL Server, etc.).