di

Resumen

Este tutorial describe cómo usar SQL Server Data Tools para desarrollar e implementar un proyecto de SQL Server Analysis Services, usando la empresa ficticia Adventure Works Cycles para todos los ejemplos. Este tutorial se corresponde con el Tutorial oficial de Adventure Works de Modelado multidimensional de Microsoft.

Objetivos

  • Crear un proyecto de SQL Server Analysis Services.

  • Definir dimensiones y jerarquías.

  • Definir un cubo.

  • Conocer el proceso de implementación de un proyecto de Analysis Services.

  • Modificar medidas, atributos y jerarquías.

  • Definir propiedades de dimensiones y de atributos avanzados.

  • Definir relaciones entre dimensiones y grupos de medida.

  • Definir cálculos.

  • Definir indicadores clave de rendimiento (KPI).

  • Definir perspectivas y traducciones.

Disponible lista de reproducción en YouTube con videotutoriales sobre el modelado multidimensional con Microsoft Analysis Services.

1. Introducción

El modelado multidimensional es una técnica de diseño de bases de datos que se utiliza para analizar datos de negocios. En lugar de utilizar un modelo relacional orientado a transacciones del negocio, el modelado multidimensional utiliza un modelo de estrella o copo de nieve para organizar los datos del negocio en dimensiones y hechos. Las dimensiones son las categorías de datos que se utilizan para analizar los hechos, que son los datos numéricos que se miden. Por ejemplo, en un caso de estudio de ventas, las dimensiones podrían ser la fecha, el producto, la tienda y el cliente, mientras que los hechos podrían ser las ventas y los beneficios. El modelado multidimensional facilita la creación de informes y análisis de datos, ya que los datos se organizan de una manera que refleja la forma en que las personas piensan sobre ellos.

En este tutorial, se mostrará cómo crear un proyecto de SQL Server Analysis Services (SSAS) y cómo definir dimensiones, jerarquías, cubos, medidas, atributos, relaciones y cálculos. También se mostrará cómo implementar un proyecto de Analysis Services y cómo explorar los datos en un cubo implementado. Este tutorial se basa en el Tutorial oficial de Adventure Works de Modelado multidimensional de Microsoft.

2. Requisitos previos

Para completar este tutorial, necesitarás tener instalado SQL Server Data Tools (SSDT) y SQL Server Management Studio (SSMS). También necesitarás tener instalada la base de datos de ejemplo AdventureWorksDW2019 en tu servidor SQL Server. Toda esta información está disponible en el documento de Configuración del entorno de la asignatura

3. Creación de una solución de Analysis Services para el modelado multidimensional

El escenario de este laboratorio está basado en el Tutorial de Analysis Services de Adventure Works de Microsoft. Se trata de una compañía ficticia llamada Adventure Works Cycles que vende bicicletas y accesorios. La empresa tiene una base de datos de ventas que contiene información sobre los pedidos, los productos, los clientes y las fechas de los pedidos. El objetivo de este laboratorio es crear un cubo multidimensional que permita analizar las ventas de la compañia en distintas situaciones. Para ello, realizaremos las siguientes tareas:

  • Creación de un cubo multidimensional en Analysis Services.

  • Modificación de medidas, atributos y jerarquías.

  • Mejora de la definición de dimensiones y atributos.

De cara al desarrollo del grupo de trabajo de la asignatura, se realizará cada una de estas tareas en una sesión de clase.

3.1. Creación de un cubo multidimensional en Analysis Services

En este apartado se describen los pasos necesarios para crear un proyecto de SSAS, definir una vista del origen de datos, definir una dimensión y un cubo, y explorar el cubo implementado.

  1. Definir una vista del origen de datos en un proyecto de Analysis Services

    1. Crear un proyecto de Analysis Services

      A la hora de crear el proyecto en SSAS puede que nos de a elegir entre un proyecto multidimensional o tabular. Para más información, consultar el siguiente enlace: Modelos tabulares y multidimensionales en Microsoft Analysis Services

    2. Definir un origen de datos

      En el apartado de Información de suplantación (Impersonation Information), seleccionar la opción Utilizar un nombre de usuario y una contraseña de Windows específicos y proporcionar las credenciales de acceso al equipo. Así fue como configuramos el acceso a SQL Server en la instalación.

    3. Definir una vista del origen de datos

    4. Modificar los nombres de tabla predeterminados

  2. Definir e implementar un cubo

    1. Definir una dimensión

    2. Definir un cubo

    3. Agregar atributos a dimensiones

    4. Revisar las propiedades de cubo y dimensión

    5. Implementar un proyecto de Analysis Services

    6. Examinar el cubo

3.1.1. Caso de estudio

Estamos interesados en conocer el comportamiento de las ventas de la empresa Adventure Works Cycles. En concreto, necesitamos saber cuáles han sido las ventas realizadas y las unidades vendidas por clase de producto y género de cliente. Estas ventas se quieren analizar para aquellos pedidos realizados en los años comprendidos entre 2010 y 2014 y que han sido realizados por clientes del estado de California.

Con el Examinador de cubos haremos lo siguiente:

  1. Definir los campos del informe arrastrando a la zona central:

    1. El atributo Class de la dimensión Product y el atributo Gender de la dimensión Customer.

    2. La medida Sales Amount y la medida Order Quantity del cubo Adventure Works.

  2. Filtrar los datos arrastrando a la parte superior:

    1. La dimensión Order Date y seleccionar los años 2010 a 2014 de la jerarquía Order Date.Calendar Year.

    2. La dimensión Customer y seleccionar el estado California de la jerarquía State Province Name.

El Examinador de cubos mostrará un resultado similar al siguiente:

ssas examinador de cubos

3.1.2. Creación de un informe en Power BI

Podemos crear un informe en Power BI sobre el cubo creado en Analysis Services para que ofrezca una forma de interacción más elaborada que la realizada en el caso de estudio anteior. Para ello, haremos lo siguiente:

  1. Abrir Power BI y seleccionar "Obtener datos" y "Base de datos Analysis Services".

  2. Introducir localhost en la dirección del servidor y seleccionar la base de datos y el cubo.

  3. En la pestaña de datos aparecerán las tablas de dimensiones y hechos del cubo. La Vista de modelo mostrará las relaciones entre las tablas.

  4. Crear un informe de ventas por clase de producto y género con varias visualizaciones como el de la figura siguiente. Incluir en el informe los segmentadores para filtrar por año y estado.

powerbi adventure works basico

3.1.3. Creación de un informe en Excel

Excel es una herramienta muy utilizada para la creación de informes y análisis de datos. En este apartado, vamos a conectar Excel con el cubo implementado en Analysis Services. Antes de comenzar, comentar que como aún no tenemos definida ninguna jerarquía en las dimensiones, Excel mostrará los atributos con valores individuales. Cometaremos cómo crear jerarquías en el siguiente apartado. Para conectar Excel con el cubo, haremos lo siguiente:

  1. Abrir Excel y seleccionar la pestaña "Insertar", "Tabla dinámica" y "Origen de datos de tabla dinámica de Analysis Services".

  2. Aparecerá un cuadro de diálogo para elegir la conexión. Como aún no tenemos definida ninguna, pulsaremos "Aceptar" para crear una nueva conexión.

    excel elegir conexion

  3. Aparecerá un cuadro de diálogo que da a elegir entre crear nuevas conexiones con un nuevo origen de datos, con SQL Server o seleccionar entre algunas conexiones recientes. Seleccionar "Conectar con nuevo origen de datos".

    excel elegir origen

  4. Aparecerá un cuadro de diálogo para elegir el tipo de origen de datos. Seleccionar "Microsoft Analysis Services" y pulsar "Aceptar".

    excel asistente para la conexion

  5. En el paso siguiente del asistente hay que introducir los datos para la conexión con el servidor. Introducir localhost en el nombre del servidor y seleccionar "Utilizar autenticación de Windows" para conectar.

    excel conexion con el servidor

  6. En el paso siguiente del asistente, seleccionar la base de datos (p.e. Adventure Works) y el cubo (p.e. Adventure Works).

    excel seleccionar base de datos y cubo

  7. Finalizar el asistente en el paso siguiente para guardar las conexión.

    La conexión creada queda almacenada en Excel y se puede reutilizar en futuras ocasiones.

  8. Una vez creada la conexión, volveremos al paso inicial del asistente y ya aparecerá la conexión creada y seleccionada. Como vamos a crear una tabla dinámica, dejaremos los valores predeterminados y pulsaremos "Aceptar".

    excel crear tabla dinamica

  9. Excel mostrará una tabla dinámica con los campos de la dimensión y las medidas del cubo a la derecha. Basta con arrastrar los campos a las zonas de filas, columnas y valores para crear el informe deseado.

    excel tabla dinamica

Por ejemplo, a modo ilustrativo y para comparar con el caso de estudio anterior, crearemos una tabla dinámica con:

  • Dimensión Product, atributo Product Line en la zona de filas.

  • Dimensión Customer, atributo Gender en la zona de columnas.

  • Medida Sales Amount y medida Order Quantity en la zona de valores.

  • Dimensión Order date, atributo Calendar Year en la zona de filtro y Dimensión Customer, atributo State Province Name en la zona de filtro. Filtrar por los años 2010 a 2014 y por el estado California.

Excel mostrará un resultado similar al siguiente:

excel caso de estudio basico

3.2. Modificación de medidas, atributos y jerarquías

En este apartado se describen los pasos necesarios para modificar medidas, atributos, dimensiones y analizar el cubo implementado.

  1. Modificar medidas

    1. Modificar medidas

    2. Modificar la dimensión Customer

    3. Modificar la dimensión Product

    4. Modificar la dimensión Date

    5. Examinar el cubo implementado

3.2.1. Conexión de Excel con el cubo

Una vez incorporadas las jerarquías y medidas al cubo, podemos conectar Excel con el cubo para realizar análisis de datos. Ahora veremos, que a diferencia del caso anterior, Excel mostrará las jerarquías y no sólo los atributos con varlores individuales. Para ello, haremos lo siguiente:

  1. Abrir Excel y seleccionar la pestaña "Datos".

  2. Seleccionar "Obtener datos", "De una base de datos" y "De Analysis Services".

  3. En el paso "Conectar con el servidor de la base de datos" del asistente, introducir localhost en el nombre del servidor y seleccionar "Usas mis credenciales actuales" para conectar.

  4. En el paso "Seleccionar la base de datos y tabla", seleccionar la base de datos (p.e. Adventure Works) y el cubo (p.e. Adventure Works).

  5. En el paso "Guardar archivo de datos y finalizar", seleccionar "Finalizar".

  6. Excel mostrará una cuadro de diálogo para indicar cómo se quiere visualizar los datos. Seleccionar "Informe de tabla dinámica", situar en la celda que indica en la hoja actual y pulsar "Aceptar".

  7. Excel mostrará una tabla dinámica con los campos de la dimensión y las medidas del cubo a la derecha. Arrastrar los campos a las zonas de filas, columnas y valores para crear el informe deseado.

3.2.2. Caso de estudio

Estamos interesados en conocer el comportamiento de las ventas de la empresa Adventure Works Cycles. En este caso usaremos Excel para resolver el problema. En concreto, necesitamos saber cuáles han sido las ventas realizadas y las unidades vendidas por clase de producto y género de cliente. Estas ventas se quieren analizar para aquellos pedidos realizados en los años comprendidos entre 2010 y 2014 y que han sido realizados por clientes del estado de California.

Con Excel haremos lo siguiente:

  1. Crear una conexión con el cubo Adventure Works implementado en Analysis Services.

  2. Crear una tabla dinámica con los campos de la dimensión y las medidas del cubo.

  3. Definir los campos del informe arrastrando:

    1. a las filas de la tabla dinámica el atributo Product Line de la jerarquía Líneas de modelo de producto de la dimensión Product

    2. a las columnas de la tabla dinámica el atributo Gender de la carpeta de atributos Demographic de la dimensión Customer

    3. a los valores de la tabla dinámica la medida Sales Amount y la medida Order Quantity del cubo Adventure Works.

  4. Filtrar los datos arrastrando a la zona de filtro:

    1. la dimensión Order Date y seleccionar los años 2010 a 2014 de la jerarquía Order Date.Calendar Year.

    2. la dimensión Customer y seleccionar el estado California de la carpeta Location de la dimensión Customer.

Excel mostrará un resultado similar al siguiente:

excel caso de estudio

3.3. Mejora de la definición de dimensiones y atributos

En este apartado se describen los pasos para definir propiedades de dimensiones y de atributo avanzados, definir relaciones entre dimensiones y grupos de medida, definir cálculos, definir indicadores clave de rendimiento (KPI) y definir perspectivas y traducciones.

  1. Definir propiedades de dimensiones y de atributos avanzados

    1. Usar una versión modificada del proyecto Tutorial de Analysis Services

    2. Definir propiedades de atributo primario en una jerarquía de elementos primarios y secundarios

    3. Agrupar miembros de atributo automáticamente

    4. Ocultar y deshabilitar jerarquías de atributo

    5. Ordenar los miembros de atributo en función de un atributo secundario

    6. Especificar relaciones de atributo entre los atributos de una jerarquía definida por el usuario

    7. Definir las propiedades de miembro desconocido y de procesamiento de valores NULL

  2. Definir relaciones entre dimensiones y grupos de medida

    1. Definir una relación referenciada

    2. Definir una relación de hechos

    3. Definir una relación de varios a varios

    4. Definir la granularidad de las dimensiones en un grupo de medida

  3. Definir cálculos

    1. Definir miembros calculados

    2. Definir conjuntos con nombre

  4. Definir indicadores clave de rendimiento (KPI)

    1. Definir y examinar KPI

  5. Definir perspectivas y traducciones

    1. Definir y examinar perspectivas

    2. Definir y examinar traducciones

3.3.1. Caso de estudio

En curso…​

4. Conclusiones

En este tutorial hemos aprendido cómo usar SQL Server Data Tools para desarrollar e implementar un proyecto de SQL Server Analysis Services, usando la empresa ficticia Adventure Works Cycles para todos los ejemplos. Hemos visto cómo crear un proyecto de SSAS, definir dimensiones y jerarquías, definir un cubo, conocer el proceso de implementación de un proyecto de Analysis Services, modificar medidas, atributos y jerarquías, definir propiedades de dimensiones y de atributos avanzados, definir relaciones entre dimensiones y grupos de medida, definir cálculos, definir indicadores clave de rendimiento (KPI), definir perspectivas y traducciones.