Diseño Lógico de Data Warehouse

Capítulo 1

Diseño Lógico de Data Warehouse

Objetivos del examen en este capítulo:

■ ■ Diseñar e implementar un Data Warehouse

■ ■ Diseñar e implementar dimensiones.

■ ■ Diseñar e implementar tablas de hechos.

Analizar los datos de bases de datos que soportan la línea de negocio (LOB) por lo general no es una tarea fácil. Los esquemas relacionales normalizados utilizados para una aplicación LOB pueden consistir de miles de tablas. La convención de nombrado de tablas con frecuencia no son usadas. Por lo tanto, es difícil de descubrir la uniformidad de almacenamiento de datos. Las empresas suelen tener múltiples Aplicaciones empresariales, a menudo trabajan con más de una base de datos.

Para efectos de análisis, estas empresas debe ser capaz de combinar los datos de varias bases de datos. La calidad de los datos es un problema común. Además, muchas aplicaciones LOB no rastrean los datos a tiempo, aunque muchos análisis dependen de los datos históricos.

Una solución común a estos problemas es crear un almacén de datos (DW). Un DW es un

silo de datos centralizada para una empresa que contienen los datos combinados, limpios e histórico. Los esquemas DW se simplifican y por lo tanto son más adecuados para la generación de informes normalizados. Para un DW, normalmente se utiliza un tipo especial de diseño lógico llamado Esquema de estrella, o una variante del esquema de estrella llamado es el esquema de copo de nieve. Tablas de una estrella o Esquema de copo de nieve se dividen en tablas de dimensiones (comúnmente conocido como dimensiones) y tablas de hechos.

Los datos de un DW generalmente proviene de las bases de datos LOB, estos datos son transformados tomando una copia de datos de origen. Por supuesto, existe un tiempo de latencia entre el momento en que aparecen los datos en una base de datos LOB y el momento en que aparece en un DW. Un método común de abordar esta latencia implica la actualización de los datos en un almacén de datos como un trabajo nocturno. Utilice los

datos actualizados principalmente para los informes, por lo tanto, los datos se leen sobre todo y rara vez actualizado.

Las consultas a menudo implican la lectura de grandes cantidades de datos y requieren grandes exploraciones. Para apoyar tales consultas, es imprescindible utilizar un diseño físico apropiado para un DW. El diseño lógico DW parece simple a primera vista. En definitiva, es mucho más simple que un diseño relacional normalizado. Sin embargo, a pesar de la sencillez, todavía se puede encontrar un poco de problemas avanzados. En este capítulo, aprenderá cómo diseñar un almacén de datos y la forma de resolver algunos de los problemas comunes de diseño avanzadas. Usted explorará esquemas estrella y copo de nieve, dimensiones, y las tablas de hechos. Usted también aprenderá cómo realizar el seguimiento de la fuente y la hora de los datos procedentes en un DW través de la auditoría, o, en la terminología de DW, información de linaje.

Lecciones en este capítulo:

■■ Lección 1: Introducción al Esquema Estrella y Esquema Copo de Nieve

■■ Lección 2: Diseño de Dimensiones

■■ Lección 3: Diseño de Tablas de Hechos

Antes de Iniciar

Al completar este capítulo, usted deberá:

 Entender el esquema relacional normalizado.

 Experimentar con Microsoft SQL Server 2012 Management Studio.

 Obtener conocimiento práctico con Transact-SQL.

 Tener instalado ejemplos de las siguientes bases de datos AdventureWorks2012 and AdventureWorksDW2012.

Lección 1: Introducción al Esquema Estrella y Esquema Copo de Nieve

Antes de diseñar un data warehouse, necesita entender algunos patrones de diseños comunes usado para un DW, llamados esquema estrella y esquema copo de nieve. Estos esquemas evolucionaron en 1980. En particular, el esquema estrella es tan ampliamente utilizado que se ha convertido en una especie de estándar informal para todo tipo de aplicaciones de inteligencia de negocios (BI).

Después de esta lección, usted estará habilitado para:

 Entender por qué un esquema normalizado provoca problemas de información.

 Comprender el esquema en estrella.

 Comprender el esquema de copo de nieve.

 Determinar granularidad y necesidades de auditoría.

Problemas con un esquema normalizado

Esta lección se inicia con esquemas relacionales normalizados. Vamos a suponer que usted tiene que crear un informe de negocio de un esquema relacional en la base de datos de ejemplo AdventureWorks2012. El informe debe incluir la cantidad de ventas por Internet en diferentes países a lo largo de múltiples año. La tarea (o desafío) es averiguar qué tablas y columnas se necesita para crear el informe. Se empieza por la investigación de las tablas que almacenan los datos que necesita, como se muestra en Figure 1-1 que fue creada con la utilidad de en SQL Server Management Studio (SSMS).

Figura 1-1 Un diagrama de tablas que necesitas para el ejemplo del reporte de ventas.

Incluso para este relativamente simple informe, que terminaría con 10 tablas. Necesita las ventas tablas de ventas y las tablas que contienen información sobre los clientes. El esquema de base de datos AdventureWorks2012 está altamente normalizado, está pensado como un ejemplo de esquema para apoyar aplicaciones LOB. Aunque tal esquema funciona muy bien para aplicaciones de LOB, puede causar problemas cuando se utiliza como fuente para informes, como se verá en el resto de esta sección.

La normalización es un proceso en el que se definen las entidades de tal manera que una sola tabla representa exactamente una entidad. El objetivo es tener un esquema completo y no redundante. Cada pieza de información debe ser almacenada exactamente una vez. De esta manera, se puede exigir la integridad de los datos. Usted tiene un lugar para cada pieza de datos, y porque cada elemento de datos se almacena sólo una vez, usted no tiene problemas de consistencia. Sin embargo, después de una normalización adecuada, normalmente terminar con muchas tablas. En una base de datos que soporta una aplicación LOB de una empresa, usted puede terminar con miles de tablas!

Encontrar las tablas y columnas adecuadas que necesita para un informe puede ser doloroso en una base de datos normalizada, simplemente por el número de tablas involucradas. Añadir a esto el hecho de que nada obliga a los desarrolladores de bases de datos para mantener buenas convenciones de nombres en una base de datos LOB. Es relativamente fácil encontrar las tablas pertinentes en AdventureWorks2012, ya que las

tablas y columnas tienen nombres significativos. Pero imagínese si la base de datos contiene tablas denominada Tabla1, Tabla2, así sucesivamente, y columnas denominadas columna 1, columna 2, etc. Descubrir los objetos que necesita para su informe sería una pesadilla. Las herramientas como SQL Profiler podrían ayudar. Por ejemplo, puede crear un entorno de prueba, trate de insertar algunos datos a través de una aplicaciones empresariales, y tiene SQL Profiler para identificar dónde se insertarán los datos. Un esquema normalizado no es muy narrativo. No se puede detectar fácilmente la ubicación de almacenamiento de datos algo, tal como la cantidad de ventas en este ejemplo, o los datos de contexto para las medidas, tales como los países y años.

Además, una consulta que combine las tablas 10, como se requiere en el reporte de ventas países y el año, no sería muy rápido. La consulta leerá grandes cantidades de datos sobre de múltiples años y, por tanto interferirían con el trabajo transaccional regular de la inserción y la actualización de los datos.

Otro problema en este ejemplo es el hecho de que no hay ninguna tabla de búsqueda explícita para las fechas. Hay que extraer los años de la fecha o de la columna date/time en la tabla de ventas, como OrderDate de la tabla SalesOrderHeader en este ejemplo. Extraer años de una columna de fecha no es una cosa muy importante, sin embargo, la primera pregunta es, ¿la base de datos LOB almacena múltiples años? En muchos casos, las bases de datos LOB se purgan después del inicio de cada nuevo año fiscal. Incluso si usted tiene todos los datos históricos para las transacciones de ventas, podría tener un problema que muestra los datos históricos correctamente. Por ejemplo, es posible que sólo tenga la última dirección del cliente, que pueda impedir el cálculo de las ventas históricas por país correctamente.

La base de datos de ejemplo AdventureWorks2012 almacena todos los datos en una sola base de datos. Sin embargo, en una empresa, puede haber múltiples aplicaciones LOB, cada una de las cuales puede almacenar datos en su base de datos propia. También puede tener una parte de los datos de ventas en una base de datos y la otra parte en otra.

Y podría tener datos de los clientes en ambas bases de datos, sin una identificación común. En estos casos, se enfrentan a los problemas de cómo combinar todos estos datos y cómo identificar qué cliente a partir de una base de datos es realmente lo mismo que un cliente

...