Business Intelligence

Creación de una base de datos para una solución de inteligencia empresarial

Derek Comingore

Descarga de código disponible en: SampleCode2009_08.exe(151 KB)

Un vistazo:

• Sigue el modelo dimensional para generar un puesto de datos
• Desarrollar procesos ETL y datos asigna
• Uso de Integration Services para ETL
• Crear un proyecto de Integration Services en BIDS

Contenido

Requisitos de conocimientos
Utilizar el modelo dimensional
Crear una matriz de bus
Creación de un mapa de datos
Creación de Data Mart
Implementar el Data Mart
Desarrollar procesos ETL
Crear un proyecto SSIS en BIDS
Crear conexiones de datos común
Desarrollar el ETL para dimensiones
Definir un flujo de datos para extraer y carga
Desarrollo de paquetes adicionales
Desarrollar el paquete de tabla de hechos de ventas Internet
Pasos finales

Datos es la parte más importante de cualquier solución de business intelligence (BI). Tal y como se explicó en Stacia Misner " Planeamiento de primera solución BI de Microsoft"obtención de datos en la solución de inteligencia empresarial y mantenimiento de una vez que hay varios pasos. Los profesionales de BI consulte estos pasos como extraer, transformar y carga (ETL) de procesos. Incluso si no piensa perseguir una carrera centrado en BI, todavía pueden sacar partido de ETL técnicas y herramientas para administrar los datos que necesita para informar a las decisiones diarias que tomar en su trabajo. En este artículo, mostraré cómo diseñar y construir un puesto de datos simple para ilustrar cómo puede utilizar SQL Server 2008 Integration Services (SSIS) para realizar la ETL de su propia solución de BI.

Requisitos de conocimientos

Como con cualquier proyecto de TI, la mejor manera de iniciar un proyecto ETL consiste en comprender los requisitos generales de solución de inteligencia empresarial que desea generar y a continuación, resulte volver a los datos para determinar la mejor manera de admitir los requisitos. El primer artículo de la serie establece la fase de la solución BI compañía ficticia Adventure Works necesita describiendo los requisitos de Contabilidad analíticos como varias preguntas de que la compañía necesita para responder. Estas preguntas resalte la necesidad de Adventure Works comprender sus ventas de producto desde perspectivas diferentes: rentabilidad por canal de distribución (revendedores o Internet), el cambio en la demanda de productos a lo largo del tiempo y la diferencia entre ventas reales y previsiones de ventas por producto, vendedor, zona geográfica y el tipo de distribuidor. Las respuestas a estas preguntas le ayudará a decidir el canal de distribución de la empresa debe centrarse en beneficios de aumento, cómo adaptar sus procesos de fabricación para satisfacer mejor la petición y cómo los cambios en estrategia de ventas pueden ayudarle la compañía cumplir sus objetivos de ventas de Adventure Works. Verá cómo los datos permite responder a estas preguntas empresariales al agregar SQL Server Reporting Services (SSRS) a la solución BI.

Antes de empezar a diseñar el data mart para aventura con estos requisitos en mente, desea modelo necesario de la información desde una perspectiva empresarial. En otras palabras, diseño de un puesto de datos debe basarse en la forma que los usuarios le preguntará sus preguntas en lugar de la forma de los datos procede de los orígenes de datos.

Necesitará la base de datos SQL Server 2008 Adventure Works OLTP de ejemplo para los ejemplos de código encontró en este artículo.

Utilizar el modelo dimensional

Un puesto de datos normalmente se construye mediante un diseño de modelo dimensional, que es un esquema de base de datos adecuado para su análisis. (Un recurso excelente para conocer los modelos dimensionales es kimballgroup.com.) Un modelo dimensional presenta los datos de forma que es familiar para los usuarios y ayuda a crear estructuras de datos que están optimizadas para consultar grandes volúmenes de datos. Puede conseguir esta optimización por denormalizing los datos, que ayuda al motor de base de datos seleccione rápidamente y agregado eficientemente una gran cantidad de datos en tiempo de consulta. En el esquema sin normalizar para la solución AdventureWorks, incluirá dos tipos de tablas: hechos y dimensiones. Una tabla de dimensiones almacena información sobre las entidades empresariales y objetos, como los distribuidores o productos. Una tabla de hechos, que usaré para los datos numéricos de ventas, se necesita para agregar, contiene medidas numéricas y las claves relacionadas con hechos las tablas de dimensiones. Explicaré más sobre tablas de hechos más adelante en este artículo.

Puede implementar las tablas de un modelo dimensional en dos tipos de esquemas: estrella y copo de nieve. En términos sencillos, un esquema en estrella utiliza una tabla para cada dimensión, que una consulta a su vez está relacionado con una tabla de hechos con una única combinación. Un esquema de copo de nieve utiliza dos o más tablas para cada dimensión y, por lo tanto, requiere más combinaciones en una consulta para ver todos los datos. Esta colección de combinaciones en cascada significa que las consultas a menudo ejecutan más despacio en un esquema de copo de nieve que en un esquema en estrella. Para propósitos de este artículo, le complicar el diseño y utilice un esquema en estrella.

Crear una matriz de bus

Como parte del proceso de modelado dimensional, crear una matriz de bus para ayudar a identificar las dimensiones de ventas, el enfoque de la solución de inteligencia empresarial de Adventure Works. Recuerde que Adventure Works tiene dos canales de ventas: por mayor de ventas a distribuidores y ventas individuales a través de Internet. Use también la matriz de bus para identificar la relación de cada dimensión para uno o ambos tipos de ventas. figura 1 muestra mi matriz de bus de ejemplo Adventure Works Sales.

Mi siguiente paso consiste en determinar las medidas para la solución. Medidas son los valores numéricos necesarios para el análisis. Se puede provenir directamente del origen, como ventas dólares o costos de producto, o obtenerse a través de un cálculo, como multiplicar una cantidad por importe en dólares durante un período extendido de venta. También debe decidir qué atributos incluir con cada dimensión. Atributos están los elementos individuales de una dimensión (correspondiente a las columnas de una tabla) que puede utilizar para agrupar o filtrar datos para análisis, como el país en la dimensión de la zona de ventas o el año en la dimensión Date. No detalle cada medida identificado o atributo de dimensión en este artículo, sólo se da cuenta que debe realizarse el proceso de identificación.

Creación de un mapa de datos

Antes de crear tablas físicas del data mart., es necesario realizar cierta planificación adicional. Específicamente, se necesita para elaborar un documento de mapa de datos para asignar cada columna de destino en el esquema del puesto de datos a las columnas en el sistema de Adventure Works OLTP de origen (la base de AdventureWorks2008 datos que puede descargar e instalar como se describe en artículo de Stacia Misner en p. 31). Puede utilizar diversas aplicaciones para crear un mapa de datos. El formato no es tan importante como el contenido. Me gusta desarrollar asignaciones de datos en Microsoft Office Excel. figura 2 muestra la ficha DimProduct creado en el mapa de datos. También se crear datos DimCustomer y FactInternetSales asigna. Cada hoja del libro representa una de las tablas en mi data mart. En cada hoja, simplemente hay dos columnas: uno para la columna de origen y otro para la columna de destino.

Figura 2 datos DimProduct asignación ficha

Cada tabla de dimensiones (excepto para la dimensión Date) tiene una clave principal que se conoce como una clave suplente (suele ser una columna de identidad). Una ventaja de utilizar las claves suplentes es que puede combinar datos procedentes de varios sistemas sin riesgo de claves duplicadas.

Las tablas de dimensiones también tienen una columna de clave alternativa. Estas claves alternativas representan claves naturales, a veces denominadas claves empresariales. Estos son los identificadores del sistema de origen. Por ejemplo, la columna CustomerAlternateKey en la dimensión de cliente se asigna al campo de base de datos OLTP de Adventure Works AccountNumber en la tabla Sales.Customer. Al almacenar estas claves en la tabla de dimensiones, tienen una forma hacer coincidir los registros ya en la dimensión con registros extraídos el origen siempre que se ejecuta un proceso ETL para cada dimensión.

Casi cada puesto de datos incluye una dimensión fecha, porque los análisis de negocio a menudo comparan los cambios en medidas por fecha, semana, mes, trimestre o año. La dimensión Date casi nunca procede de un sistema de origen, por lo que no se aplican las razones para utilizar una clave de SQL Server IDENTITY–based. En su lugar, usaré lo que se denomina clave inteligente con formato AAAAMMDD almacenada como una columna Integer de SQL Server. Una clave inteligente es una clave que se genera desde lógica o secuencias de comandos en oposición a que una clave incremento automático como una columna IDENTITY de SQL Server.

Tenga en cuenta que la dimensión Date no asigna normalmente a una tabla de origen. En su lugar, usaré una secuencia de comandos para generar los datos para cargar los registros en la tabla.

Dado que los procesos ETL necesarios para mi esquema pequeña son bastante sencillos, mi mapa de datos sirve como está. En un proyecto real, podría anotar el mapa de datos para señalar cuando son necesarias las transformaciones complejas.

Creación de Data Mart

Ahora que modelos lógicos está completado, es necesario crear las tablas físicas que cargarán los procesos ETL y una base de datos de host para estas tablas. Usaré un script de T-SQL básico para crear mi base de datos y sus dimensiones asociadas y tablas de hechos. Puede encontrar el script T-SQL completo en la descarga que acompaña para la solución de inteligencia empresarial de ejemplo en ( Descargas de código de 2009).

Para propósitos de este artículo, crear sólo un subconjunto del esquema de puesto de datos de ventas completo para que pueda cubrir todo el proceso ETL de SSIS. En la versión más pequeña del esquema, incluyen sólo las medidas OrderQuantity y SalesAmount para la tabla de hechos de ventas de Internet. Además, Mi esquema menor incluye una versión simplificada de las tablas de dimensión de cliente, producto y fecha.

Implementar el Data Mart

Para implementar el data mart, simplemente es necesario ejecutar T-SQL que escribí anteriormente para crear instancias de las nuevas tablas en una instancia de SQL Server. Para ejecutar el T-SQL, inicie SQL Server Management Studio (SSMS) haciendo clic en Start\All Programs\Microsoft SQL Server 2008\SQL Server Management Studio. Una vez abierto SSMS, escriba el nombre de mi instancia designada de SQL Server y haga clic Conectar mediante autenticación de Windows en el cuadro de diálogo de conexión. Utilice SQL Server Management Studio para abrir el archivo TECHNET_AW2008SalesDataMart.sql y ejecutar la secuencia de comandos.

Desarrollar procesos ETL

Diseñar y desarrollar procesos ETL es el siguiente paso para crear una solución de inteligencia empresarial. Para revisar, ETL incluye todos los procesos tecnológicos que se extraen datos de orígenes de datos, transformar y, a continuación, se cargan en un repositorio de destino. Normalmente, los procesos ETL en soluciones de BI extraen datos de archivos planos y bases de datos operativas de OLTP, modificar los datos para ajustarse a un modelo dimensional (por ejemplo, un esquema de estrella) y, a continuación, cargar los datos resultantes en un puesto de datos.

Crear un proyecto SSIS en BIDS

El primer paso para desarrollar un proceso ETL es para crear un nuevo proyecto en Business Intelligence Development Studio (BIDS). BIDS se incluye con SQL Server 2008 y se instala al seleccionar la opción de componentes de la estación de trabajo durante el proceso de instalación. BIDS incluye plantillas de proyecto para SSIS, SSAS y SSRS. También admite integración de control de origen, igual que Visual Studio.

Para iniciar BIDS, vaya a Inicio\Programas\Microsoft SQL Server 2008\Business Intelligence Development Studio y seleccione proyecto Archivo\Nuevo. Debería ver la plantilla de New Project que se muestra en Figure 3 .

Figura 3 nueva plantilla de proyecto en BIDS 2008

Seleccione proyecto de Integration Services en el panel Plantillas, escriba ssis_TECHNET_AW2008 en el cuadro de texto nombre y, a continuación, haga clic en Aceptar. BIDS ahora debería mostrar un proyecto SSIS abierto.

Crear conexiones de datos común

Otra característica interesante en SSIS 2008 es la capacidad para crear conexiones de origen de datos fuera de los paquetes individuales. Puede definir una conexión de origen de datos una vez y referencia a él, a continuación, en uno o más paquetes SSIS dentro de la solución. Para obtener información adicional sobre crear orígenes de datos de BIDS, consulte" Cómo: definir un origen de datos mediante el Asistente de origen de datos (Analysis Services) ".

Crear dos nuevas conexiones de origen de datos: uno para la base de datos TECHNET_AW2008SalesDataMart y otro para la base de datos OLTP AdventureWorks2008. Nombre de las conexiones de origen de datos AW_DM.ds y AW_OLTP.ds, respectivamente.

Desarrollar el ETL para dimensiones

El ETL para cargar la dimensión Product es muy sencillo. Es necesario extraer los datos de la tabla de Adventure Works Production.Product y cargar los datos en la base de datos TECHNET_AW2008SalesDataMart. En primer lugar, es necesario cambiar el nombre el paquete predeterminado que BIDS creado para mi proyecto SSIS. (Un paquete es un contenedor para todos los pasos en el flujo de trabajo que SSIS se ejecutará). Haga clic con el botón secundario en el paquete predeterminado de explorador de soluciones y seleccione Cambiar nombre. Escriba DIM_PRODUCT.dtsx y, a continuación, presione ENTRAR.

A continuación, es necesario crear administradores de conexión con los orígenes de datos pregenerado de paquete local. Crear dos nuevos administradores de conexión hace referencia a los orígenes de datos creados anteriormente.

Definir un flujo de datos para extraer y carga

SSIS incluye una tarea de flujo de datos que encapsula todo lo que necesita para implementar el ETL para una dimensión simple. He arrastre una tarea de flujo de datos desde el cuadro de herramientas superficie del Diseñador de flujo de control y cambie el nombre la tarea EL (para extraer y carga). Haga clic con el botón secundario en la tarea en el Diseñador de flujo de datos y seleccione Modificar. BIDS ahora muestra el Diseñador de flujo de datos.

La parte de extraer el paquete de dimensión de producto debe consultar la tabla AdventureWorks2008 Production.Product. Para configurar esta tarea, arrastre un componente de origen de OLE DB desde el cuadro de herramientas la superficie de diseñador de flujo de datos y cambie el nombre el componente de origen de OLE DB para AW_OLTP.

A continuación, definiré la parte de carga de Mi paquete para cargar en el data mart. Simplemente arrastra una instancia nueva del componente de destino de OLE DB a la superficie Diseñador de flujo de datos y cámbiele AW_OLTP. A continuación, haga clic en el componente OLE DB origen (AW_OLTP) y arrastre la flecha verde que aparece en el origen de OLE DB para el componente de destino de AW_DM OLE DB para conectar los dos componentes.

En este momento, he agregado los componentes necesarios para el flujo de datos, pero aún Necesito configurar cada componente de modo que SSIS sabe cómo van a extraer y cargar los datos. Haga clic con el botón secundario en el componente de destino de AW_DM OLE DB y seleccione Modificar. Con el OLE DB destino editor abierta, asegúrese de que AW_DM está seleccionado como el Administrador de conexión OLE DB. A continuación, expanda el nombre de la tabla con lista desplegable y seleccione la tabla dbo.DimProduct. Por último, haga clic en la ficha asignaciones para confirmar que las asignaciones son correctas. Hacer clic en Aceptar para confirmar las asignaciones. Este proceso es mucho más fácil cuando haya un se asignan los listo para la referencia, datos, especialmente si trabaja con tablas grandes. Paquete ETL de la dimensión de producto ahora finalizada.

Puede ejecutar el paquete de BIDS fácilmente. Para probar el paquete de dimensión de productos, abra el paquete y presione F5.

Desarrollo de paquetes adicionales

Creo el paquete de dimensión de cliente en la misma forma que lo hizo el paquete de producto. No recuerde los pasos que debe seguir para crear este nuevo paquete. Debe intentar producir propia. Observe que este paquete utiliza una columna de tipo de datos XML (Person.Person.Demographics) en el origen, que requiere analizar los atributos relacionados con demográfica individuales. Para analizar los valores individuales de una columna de tipo de datos XML de SQL Server, puede aprovechar una XQuery con método del tipo de datos XML nativo valor (). Nombre del paquete terminado DIM_CUSTOMER.dtsx.

Desarrollar un paquete SSIS para la dimensión Date es opcional. Porque esta dimensión normalmente no tiene datos de origen, la forma más sencilla de cargar se está usando un script de T-SQL básico. Puede encontrar la secuencia de comandos utilizado en la solución completa.

Desarrollar el paquete de tabla de hechos de ventas Internet

El paquete de tabla de hechos Internet ventas consultas para todas las ventas de Internet y devuelve las ventas desglosadas por producto, cliente y fecha (es decir, fecha de pedido). A diferencia de un paquete de dimensión, un paquete de tabla de hechos requiere un paso adicional para buscar los suplentes y inteligentes claves en las tablas de dimensión correspondiente antes para cargar los datos en la tabla de hechos. Puede crear un nuevo paquete y asígnele el nombre FACT_INTERNET_SALES.dtsx.

Debe consultar la base de datos de OLTP AdventureWorks2008 mediante el código de T-SQL que se muestra en la parte de extraer el paquete Figura 4 .

Código de T-SQL de la figura 4 para Internet de ventas por producto, fecha y cliente

SELECT
       P.ProductID
       ,CONVERT(INT,CONVERT( CHAR(4), YEAR(H.OrderDate) ) + RIGHT('0'+ CONVERT(VARCHAR(3), 
            MONTH(H.OrderDate) ),2) + RIGHT('0'+ CONVERT( VARCHAR(3), 
DAY(H.OrderDate) ),2)) AS OrderDateKey
       ,C.AccountNumber 
       ,SUM(D.OrderQty) AS OrderQuantity
       ,SUM(D.LineTotal) AS SalesAmount
FROM
       [Sales].[SalesOrderDetail] D
INNER JOIN
       [Sales].[SalesOrderHeader] H ON (D.SalesOrderID = H.SalesOrderID)
INNER JOIN
       [Production].[Product] P ON (D.ProductID = P.ProductID)
INNER JOIN
       [Sales].[Customer] C ON (H.CustomerID = C.CustomerID)
WHERE
        H.OnlineOrderFlag = 1
GROUP BY
        P.ProductID
        ,H.OrderDate 
        ,C.AccountNumber

Crear una nueva tarea de flujo de datos en la superficie de flujo de control del paquete. Abra el Diseñador de flujo de datos y crear un componente de origen de OLE DB. Nombre del componente AW_OLTP y, utilice la consulta en la figura 4 como su origen. Esta consulta produce una agregación (suma) para las medidas OrderQuantity y SalesAmount que se encuentra en las tablas de ventas de Adventure Works.

Ahora deberá configurar una transformación de búsqueda. Arrastre dos nuevas instancias del componente de transformación de búsqueda desde el cuadro de herramientas a la superficie de diseñador de flujo de datos y asignarles un nombre técnico y clientes. Configurar primero (producto) para buscar ProductKey en la tabla de dimensiones product combinando AlternateKey de la tabla de dimensiones en el campo ProductID entrante de la consulta de origen AW_OLTP.

Configurar el segundo (cliente) para buscar ClaveCliente en la tabla de dimensión de cliente al unirse a AlternateKey de la tabla de dimensiones en el campo AccountNumber entrante de la consulta de origen AW_OLTP uno.

Pasos finales

El paso final consiste en cargar los datos en la tabla de hechos FactInternetSales, reemplazando las claves naturales para cada dimensión con las claves suplentes se encuentra la transformación de búsqueda. Arrastre una instancia nueva del componente de destino de OLE DB y denomínelo AW_DM. Modifique el componente de destino de OLE DB y seleccione el Administrador de conexión AW_DM. A continuación, seleccione la tabla dbo.FactInternetSales y haga clic en la ficha asignaciones. Asegúrese de que las asignaciones de aspecto en la figura 5 . Haga clic en Aceptar para completar la lógica del paquete.

Figura 5 asignaciones de destino de OLE DB para la tabla de hechos Internet SalesFact

Para probar el paquete de hechos de ventas de Internet, abra el paquete en BIDS y presione F5.

Comprender los fundamentos de modelado dimensional y la creación de paquetes diseñado ETL con SSIS. En el tercer artículo de esta serie, aprenderá a utilizar un puesto de datos rellenada para crear dimensiones y cubos de una base de datos SSAS. Una vez generado un cubo, a continuación, puede desarrollar un paquete SSIS para actualizar estos objetos continuamente en la base de datos SSAS que se agregan cada vez nuevos datos para el puesto de datos. SSIS incluso puede preparar datos para mostrar en un informe de SSRS cuando no se cumplen los requisitos de informe con una única consulta. Como puede ver, SSIS puede hacer mucho más para ayudarle a administrar la solución de inteligencia empresarial que simplemente procesamiento ETL.

Derek Comingore es un director con ComFrame. Como un MVP de SQL Server y un profesional certificado en varias tecnologías de Microsoft, incluidos BI de SQL Server, habla en grupos de usuario de SQL Server locales y nacionales y es un autor publicado sobre temas de SQL Server. Derek se centra en crear y entregar el almacenamiento de datos de clase empresarial y soluciones de BI con SQL Server junto con otros productos de software de inteligencia empresarial de Microsoft. Puede ponerse a Derek en dcomingore@comframe.com.