data transformation services y analysis services

Data Transformation Services y Analysis Services

Salvador Ramos

MVP SQL Server

MCP SQL Server

Columnista de dotNetManía

Mi web: www.helpdna.netwebmaster@helpdna.net

Agenda

Tecnologías OLTP vs OLAP Business Intelligence Diseño de Datawarehouse y soluciones

OLAP Introducción a Data Mining Introducción al lenguaje MDX y

herramientras cliente OLAP Novedades de OLAP y DTS en

SQL Server 2005

Agenda

Tecnologías OLTP vs OLAP Business Intelligence Diseño de Datawarehouse y soluciones

OLAP Introducción a Data Mining Introducción al lenguaje MDX y

herramientras cliente OLAP Novedades de OLAP y DTS en

SQL Server 2005

Tecnologías OLTP vs OLAP

OnLine Transaction Processing Sistemas transaccionales, enfocados a

gestionar un gran número de transacciones concurrentes

Permiten insertar, actualizar, borrar y consultar una pequeña cantidad de registros

OnLine Analytical Processing Enfocados al análisis de grandes cantidades

de datos Proporcionan respuestas rápidas y complejas

Tecnologías OLTP vs OLAP

OLTP Orientado a lo operativo

(procesos) Predomina la

actualización Se accede a pocos

registros

Datos altamente normalizados

Estructura relacional

Rápidos tiempos de respuesta.

Estructura estática

OLAP Orientado a temas

Predomina la consulta.Datos históricos

Procesos masivos, se accede a muchos registros

Datos Denormalizados

Estructura multidimensional

Respuesta masiva, no inmediata

Estructura dinámica, abundantes cambios

Agenda

Tecnologías OLTP vs OLAP Business Intelligence Diseño de Datawarehouse y soluciones

OLAP Introducción a Data Mining Introducción al lenguaje MDX y

herramientras cliente OLAP Novedades de OLAP y DTS en

SQL Server 2005

Business Intelligence

“Conjunto de herramientas y servicios destinado a la gestión eficiente del conocimiento y la información en empresas y organizaciones”

Nos permite: Convertir los datos en información Tomar mejores decisiones rápidamente Utilizar un método razonable para la gestión

empresarial

SQL Server 2000

© Fernando G. Guerrero (Solid Quality Learning)

SQL Server 2000Relational Database

Engine

Analysis Services

HTTP SupportReplication

Reporting Services

Full-Text Search *Notification Services

Data Transformation Services

Solución Microsoft BI

Colaboración BI Visualización

AnálisisGeoespacial

AnálisisVentas y Márketing

Creación de SitiosB2B y B2C

Gestión de Proyectos

Análisis de datos

Agenda

Tecnologías OLTP vs OLAP Business Intelligence Diseño de Datawarehouse y soluciones

OLAP Introducción a Data Mining Introducción al lenguaje MDX y

herramientras cliente OLAP Novedades de OLAP y DTS en

SQL Server 2005

Componentes

Herramientas de análisis y reporting

ERP

CRM ETL ETL

OLTP Area intermedia Area presentación Acceso a datos

Aplicación 1

Aplicación 2

Aplicación N

DataWarehouse

Cubos

Analysis Services

Datawarehouse

Almacén de datos procedentes de los sistemas OLTP. Repositorio colectivo

Almacén de datos centralizado Datos organizados en grupos temáticos Los datos son:

Consistentes Depurados Históricos (no volátiles)

Suministra datos rápida y eficientemente Datamarts

DTS herramienta ETL

Conjunto de servicios para ETL Modelo de objetos e interfaces gráficos Pueden ser utilizados desde entornos de

desarrollo que soporten COM Incluyen asistentes que facilitan las tareas Diseñador de paquetes

DTS herramienta ETL

Permiten operar con los datos: Importación Exportación Transporte Transformación

Los datos pueden proceder de Cualquier fuente Ole Db Cualquier fuente ODBC Ficheros planos

Herramientas de DTS

Asistente para la importación o exportación

Diseñador DTS DTSRun DTSRunUI (interfaz gráfica) SQLAgent

El diseñador de paquetes

El diseñador de paquetes

ConexionesProveedor OLE DB para SQL ServerData LinkControlador ODBC para Oracle (MS)dBase 5Microsoft AccessArchivo HTML (origen)Microsoft ExcelParadox 5.x

El diseñador de paquetes

ConexionesArchivo de texto (origen)Archivo de texto (destino)Otras conexiones

El diseñador de paquetes

TareasTransformar datosConsulta controlada por datosInserción masivaEjecutar SQLCopia de objetos de SQL ServerTransferencia de base de datosTransferencia de mensajes de errorTransferencia de Inicios de sesiónTransferencia de trabajosTransferencia de proced. almacenados

El diseñador de paquetes

TareasSecuencia de comandos ActiveXPropiedades dinámicasEjecutar paqueteEjecutar proceso (.exe o .bat)FTPCola de mensajesEnviar correoProcesar Analysis ServicesExtracción de datos (consultas de predicción)

DTS

Demo 1 Utilizando los asistentes Utilizando el diseñador de paquetes

Diseño de un DW

Definir objetivos y requerimientos Revisar los informes actuales Investigar todas las posibles fuentes de

información que alimentarán el DW Diseño y modelado

Definir las tablas del área intermedia, que alimentarán dimensiones y tablas de hechos

Realizar los precálculos necesarios Crear índices y vistas Elegir el esquema más adecuado:

Estrella (star) Copo de nieve (snowflacke)

Diseño de un DW

Estrategia de extracción y transformación Decidir en cada caso si se realizará una

extracción completa o incremental Diseño del sistema de extracción incremental Utilizar triggers, replicación u otras técnicas

Transformación de los datos Asegurar la validez, integridad y utilidad de

los datos Comprobar que todas las claves externas

encuentren valores, y evitar claves nulas (ej: sustituirlas por 0 -> ‘Sin familia’)

Diseño de un DW

Demo 2

BBDD Multidimensionales

Son contenedores de cubos relacionados y objetos que comparten

La unidad de almacenamiento es el cubo (en los SGDBR es la tabla)

Opciones de almacenamiento

MOLAP Almacenamiento en estructura

multidimensional de Analysis Services Mayor rapidez de respuesta

ROLAP Almacenamiento en base de datos relacional Para grandes volúmenes de datos

HOLAP Híbrido Respuesta rápida y gran cantidad de datos en

origen

Elementos de la base de datos

Origenes de datos Cubos Dimensiones compartidas Modelos de minería de datos Funciones de base de datos (seguridad)

Sólo autenticación windows

Cubos

Tabla de Hechos Claves externas Medidas

Dimensiones

Producto

Id

Nombre

Tamaño

…

Cliente

Id

Nombre

Provincia

…

Tiempo

Fecha

Año

Mes

Dia

…

Tabla deTabla de

HechosHechos

19982000

1999 Madrid

Sevilla

Producto1

Producto2

Producto 3

19982000

1999 Barcelon

a Madrid

Sevilla

Producto1

Producto2

Producto 3

Dimensiones

Agregaciones Son sumas precalculadas de los datos para

acelerar el tiempo de respuesta Jerarquías y niveles Miembros

Tipos de dimensiones

Regulares Virtuales (a partir de propiedades) Padre-Hijo o Primario-Secundario

Según su ámbito Privadas Compartidas

Medidas y Miembros calculados

Medidas Conjunto de valores de una columna de la

tabla de hechos del cubo Miembros calculados

Medidas calculadas mediante una fórmula MDX

Tipos de cubos

Normales Vinculados

Apuntan a un cubo de otro servidor Virtuales

Se crean a partir de varios cubos existentes(‘equivalentes a JOIN en SGDBR’)

Analysis Manager

Analysis Services

Demo 3

Agenda

Tecnologías OLTP vs OLAP Business Intelligence Diseño de Datawarehouse y soluciones

OLAP Introducción a Data Mining Introducción al lenguaje MDX y

herramientras cliente OLAP Novedades de OLAP y DTS en

SQL Server 2005

Data Mining

Uso de métodos automatizados para obtener tendencias y pautas a partir de los datos del DW

Se componen de Escenarios (entidades a analizar)

Ej: un cliente y sus ventas Atributos (información sobre los escenarios)

Ej: edad, número de hijos, total ventas, …

Una vez elegidos los escenarios y atributos, procesamos el modelo

Data Mining

En Analysis Services disponemos de dos proveedores, diseñados para obtener agrupaciones eficazmente

Microsoft Decision Trees Arbol de clasificación, con diversas ramificaciones

Microsoft Cluster Agrupación de escenarios

Data Mining

Demo 4

Agenda

Tecnologías OLTP vs OLAP Business Intelligence Diseño de Datawarehouse y soluciones

OLAP Introducción a Data Mining Introducción al lenguaje MDX y

herramientras cliente OLAP Novedades de OLAP y DTS en

SQL Server 2005

El lenguaje MDX

MultiDimensional eXpressions(Lenguaje de expresiones multidimensionales)

Es el equivalente a SQL para bases de datos multidimensionales

También tiene una instrucción SELECTSELECT <espec-eje>, <espec-eje>

…FROM <espec-cubo>WHERE <espec-rebanador>

Nos permite crear Miembros calculados y Conjuntos con nombre

Dispone de múltiples funciones avanzadas

El lenguaje MDX

Demo 5

Conectividad

PivotTable Service (PTS) Es el cliente de los servicios OLAP Proporciona la interfaz para conectarnos

desde las aplicaciones ADO MD

(ActiveX Data Objects MultiDimensional) ADO MD .Net

Herramientas cliente OLAP

SQL Server 2000 Reporting Services Microsoft Excel Microsoft Data Analyzer Herramientas desarrolladas por nosotros

AddIn para Excel Aplicaciones asp o asp.net Webparts para Sharepoint

Herramientas de terceros

Microsoft Excel

Consultas ad-hoc Para mostrar datos utiliza

Tablas dinámicas Gráficos dinámicos

Necesita MS Query instalado Se puede publicar en web (OWC)

Microsoft Excel

Demo 6

Microsoft Data Analyzer

Herramienta para usuarios Facilita el análisis de datos de negocio Interfaz muy amigable Consultas ad-hoc Formas de visualización

Gráfico de barras Gráfico de tarta Datos numéricos

Microsoft Data Analyzer

Demo 7

Agenda

Tecnologías OLTP vs OLAP Business Intelligence Diseño de Datawarehouse y soluciones

OLAP Introducción a Data Mining Introducción al lenguaje MDX y

herramientras cliente OLAP Novedades de OLAP y DTS en

SQL Server 2005

Novedades DTS

DTS, ahora ‘Integration Services’ Se ha reescrito todo el código Servicio windows independiente Incrementa su potencia y funcionalidad Incrementa su rendimiento Nuevo entorno de desarrollo

MS Development Evironment

Integrados con la plataforma .Net

Novedades DTS

Nuevo entorno de desarrollo, en vez de crear un paquete DTS, se crean proyectos ‘Data Transformation Project’

MS Development Environment

Ejemplo agregación

Novedades Analysis Services

Nuevas herramientas 2000

Enterprise Manager Query Analyzer Analysis Manager Report Manager

2005 BI Development Studio (BIDS)

Orientado a Soluciones y Proyectos (similar a vs.net) Incluye un Debugger

SQL Server Management Studio

Novedades Analysis Services

Mejoras para el modelado de datos Data Source Views

Tenemos una capa más de abstracción Podemos trabajar de forma desconectada Podemos renombrar entidades

Intellicube, nos facilita la construcción de cubos

el Repository (por defecto en Access), se sustituye por metadatos en XML

Deployment Wizard (facil despliegue)

Novedades Analysis Services

UDM – Unified Dimensional Model Se integran todos los tipos de Data Sources Combina las mejores características de los

modelos relacionales y OLAP Querys interactivas con mejor rendimiento

Alto rendimiento gracias a Proactive Caching

Novedades Analysis Services

KPI Framework

Nuevos tipos de dimensiones Nuevos algoritmos de Data Mining MDX Scripts (servidor) y MDX Debugging

BI Development Studio

Preguntas y dudas

???

www.helpdna.net

webmaster@helpdna.net