Modelos tabulares en SQL Server 2012 Analysis Services

La reciente aparición oficial de SQL Server 2012 (también conocido por el nombre clave Denali) viene acompañada, como es habitual en toda nueva versión, por una serie de interesantes mejoras, entre las cuales, en el presente artículo, destacaremos aquellas relacionadas con el proceso de creación y explotación de un Modelo Tabular de análisis, una de las piezas integrantes del Modelo Semántico de Inteligencia de Negocio (BISM, Business Intelligence Semantic Model), el nuevo paradigma para el desarrollo de soluciones de Inteligencia de Negocio (BI, Business Intelligence), basadas en los Servicios de Análisis pertenecientes a SQL Server 2012 (SSAS, SQL Server Analysis Services).

Como ya adelantábamos en un artículo anterior, la incorporación de BISM a los servicios de análisis de SQL Server, convierte a estos últimos en una de las soluciones más potentes dentro del panorama actual de BI. No obstante, desde los primeros anuncios hechos públicos acerca de BISM hasta ahora, se han desvelado nuevos detalles acerca de la arquitectura de dicha tecnología, que nos permiten ser más precisos al hacer la descripción de la misma.

 

BISM. Un modelo único para todas las necesidades analíticas

El nuevo BISM se establece como el modelo único para el desarrollo de soluciones de Inteligencia de Negocio, con el que se atenderán los requerimientos de los diversos tipos de usuarios y aplicaciones para el análisis de información.

BISM representa una evolución del anterior Modelo Dimensional Unificado (UDM, Unified Dimensional Model), hacia un modelo combinado, que ofrece todas las funcionalidades del desarrollo multidimensional que ya tenía UDM, más nuevas características basadas en un diseño y motor de análisis relacional; lo que enriquece la actual oferta de SQL Server dentro del campo de los servicios de análisis. Por este motivo, a partir de ahora, el término UDM queda reemplazado en favor de BISM, cuando hagamos mención al modelo de desarrollo utilizado por Analysis Services. De igual manera, cuando actualicemos un proyecto UDM a SQL Server 2012, será considerado a todos los efectos como un proyecto BISM.

Esta integración en las filosofías de trabajo, y por ende, en las tecnologías subyacentes (multidimensional y relacional), da como resultado una arquitectura distribuida en tres capas, con capacidad para dar respuesta a usuarios con necesidades muy dispares en todo lo referente a las tareas relacionadas con el análisis de información.

 

ModelosTabularesSQLServer2012AnalysisServices_01

 

Si observamos el flujo de funcionamiento de esta arquitectura, partiendo de las fuentes de datos nos encontramos, en primer lugar, con la capa de Acceso a datos, que es la encargada de realizar la extracción de la información residente en los orígenes de datos a los que nos conectamos, existiendo dos mecanismos para llevarla a cabo: por caché y con acceso directo (passthrough) a la fuente de datos.

El mecanismo de caché obtiene los datos de la fuente original, y los almacena en una estructura de datos basada en un algoritmo de compresión optimizado para su acceso a gran velocidad. A su vez, dentro de este modo en caché tenemos que optar por dos motores distintos de almacenamiento: MOLAP o xVelocity.

MOLAP es la vía utilizada tradicionalmente en Analysis Services como sistema de almacenamiento intermedio, y como su nombre indica, está optimizado para su uso en el desarrollo de modelos multidimensionales (cubos OLAP).

xVelocity es un novedoso sistema introducido con BISM para su empleo en modelos tabulares, consistente en un motor de almacenamiento de datos en memoria basado en columna, que aúna sofisticados algoritmos de compresión y búsqueda para ofrecer, sin necesidad de utilizar índices o agregaciones, un rendimiento excelente en la velocidad de respuesta a nuestras consultas.

Con respecto a la modalidad de acceso directo, tal y como su nombre indica, lo que hace es enviar la consulta directamente al motor de la fuente de datos origen, para que ambas operaciones: procesamiento de datos y lógica de negocio, sean realizadas   allí. En este caso también existen dos modalidades de ejecución: ROLAP y DirectQuery.

ROLAP es el modo habitualmente empleado por Analysis Services, que utiliza la   propia fuente de datos origen, para procesar las consultas efectuadas contra cubos pertenecientes a modelos multidimensionales.

DirectQuery se utiliza en modelos tabulares para procesar igualmente las consultas en el origen de datos.

En la capa Lógica de negocio incluiremos las consultas en los lenguajes MDX o DAX, según trabajemos respectivamente contra un modelo multidimensional o tabular, con las que implementaremos la lógica de nuestra solución.

Para terminar, en el Modelo de datos encontramos la capa conceptual, en la que utilizando SQL Server Data Tools (antiguo Business Intelligence Development Studio y actual entorno de desarrollo basado en Visual Studio 2010) construiremos nuestro modelo empleando alguna de las plantillas de proyecto disponibles a tal efecto: Analysis Services Multidimensional and Data Mining Project o Analysis Services Tabular Project.

Cabe mencionar que también resulta posible utilizar PowerPivot como herramienta de desarrollo de un modelo tabular, aunque para llevar a cabo la implementación de éste en el servidor tendremos necesariamente que usar SQL Server Data Tools.

 

Instalación de Analysis Services. Un modelo semántico, dos modos de ejecución

Dadas las particulares características de cada uno de los modelos de datos pertenecientes a BISM, en el programa de instalación de SQL Server 2012, al llegar al paso correspondiente a Analysis Services, debemos elegir qué modo de ejecución del servidor de análisis queremos instalar: Multidimensional o Tabular, ya que no es posible la instalación simultánea de ambos en la misma instancia. Por este motivo, el procedimiento recomendado consiste en instalar cada modo en instancias separadas. No obstante, en nuestro caso, bastará con instalar únicamente el modo tabular para el desarrollo del ejemplo.

ModelosTabularesSQLServer2012AnalysisServices_02

 

Manos a la obra. Creación de un modelo tabular

Una vez expuestos todos aquellos aspectos necesarios para que el lector pueda ubicarse conceptualmente dentro del nuevo modelo semántico, pasemos a la parte práctica del artículo, en la que nos ocuparemos de desarrollar un proyecto de análisis de tipo tabular.

La base de datos que emplearemos para el ejemplo será AdventureWorksDWDenali, mientras que el objetivo del modelo a crear consistirá en analizar los gastos de envío de los pedidos realizados por los clientes a través de Internet, así como la cantidad de pedidos emitida, todo ello en función del territorio de residencia del cliente y los detalles de oferta o promoción en que estuvieran los artículos comprados.

En primer lugar iniciaremos SQL Server Data Tools, seleccionando un nuevo proyecto basado en la plantilla Analysis Services Tabular Project, al que daremos el nombre PruebaModeloTabular. Según veremos en el Explorador de Soluciones, este proyecto estará formado por un archivo con el nombre Model.bim, que representa al diseñador del modelo tabular.

ModelosTabularesSQLServer2012AnalysisServices_03

 

El siguiente paso consistirá en añadir al modelo las tablas que formarán parte del mismo, para lo cual seleccionaremos la opción de menú “Model | Import From Data Source”, que iniciará el asistente de importación de datos. Tras elegir el tipo de origen de datos, la instancia de SQL Server correspondiente y la base de datos, llegaremos al paso del asistente en el que deberemos especificar las credenciales para poder conectarnos a la fuente de datos y proceder a su extracción.

En este punto utilizaremos nuestra cuenta de Windows (en caso de que tenga los permisos de acceso suficientes) o bien elegiremos la opción Service Account, que utilizará la cuenta NT SERVICE\MSOLAP$SQL2012TAB, asociada al servicio de Analysis Services. Si empleamos esta última posibilidad, necesitaremos otorgar permiso de acceso y lectura a dicha cuenta sobre la base de datos AdventureWorksDWDenali.

ModelosTabularesSQLServer2012AnalysisServices_04

 

Para asignar el mencionado permiso, en SQL Server Management Studio, haremos clic derecho en el nodo Logins, perteneciente a su vez al nodo Security, eligiendo la opción New Login, que abrirá el cuadro de diálogo para añadir un nuevo login al servidor, donde introduciremos el nombre de la cuenta de Analysis Services. En el apartado User Mapping marcaremos la base de datos a la que daremos acceso.

ModelosTabularesSQLServer2012AnalysisServices_05

 

Continuando con el asistente de importación llegaremos a la selección de tablas, donde marcaremos DimSalesTerritory, DimPromotion y FactInternetSales, procediendo a iniciar el proceso de importación.

ModelosTabularesSQLServer2012AnalysisServices_06

 

Finalizado el proceso de importación de datos, el diseñador mostrará las tablas en forma de cuadrícula, organizándolas en pestañas.

ModelosTabularesSQLServer2012AnalysisServices_07

 

Este modo de visualización del modelo puede alternarse con un modo en diagrama, que será más aconsejable si queremos observar determinadas características, tales como las relaciones entre tablas. Para cambiar este modo de vista utilizaremos los botones situados a tal efecto en la parte inferior derecha de la ventana del diseñador.

ModelosTabularesSQLServer2012AnalysisServices_08

 

Creación de medidas

A pesar de haber importado las tablas al modelo, todavía no podemos realizar un análisis adecuado sobre el mismo, ya que carecemos de los cálculos (medidas o métricas en el contexto de BI) encargados de proporcionar los resultados numéricos, imprescindibles en cualquier sistema de estas características, como por ejemplo cubos OLAP.

Para crear una medida dentro del modelo, en primer lugar, la visualización del diseñador deberá estar establecida en cuadrícula (modo predeterminado). Una vez hecho este ajuste, observaremos que cada tabla muestra dos secciones: la superior, que contiene las filas de la propia tabla; y la inferior, reservada para las medidas calculadas que incorpore el desarrollador al modelo.

Seguidamente, elegiremos la tabla del modelo que contiene aquellas columnas susceptibles de ser utilizadas en la obtención de resultados numéricos (también denominada tabla de hechos dentro del contexto del diseño de modelos multidimensionales), y que en este proyecto será FactInternetSales.

A continuación utilizaremos la columna Freight para crear la primera de nuestras medidas: una suma de los valores de dicha columna para todas las filas de la tabla. En la zona inferior de la cuadrícula seleccionaremos una celda libre debajo de la columna mencionada, y después haremos clic en el botón Sum de la barra de herramientas, que aplicará a esta columna la fórmula “SUM([Freight])”, perteneciente al lenguaje DAX.

Fruto de esta operación será la obtención de la medida calculada Sum of Freight, cuyo nombre, asignado automáticamente por el entorno de desarrollo, cambiaremos en su ventana propiedades a GastosTransporte.

ModelosTabularesSQLServer2012AnalysisServices_09

 

Observando la cifra resultante nos percataremos de que existe un problema de formato con el valor obtenido (7.339.696.091,00 €), ya que el resultado correcto es 733969,6091, lo cual podemos comprobar ejecutando desde SQL Server Management Studio la consulta del siguiente código. No obstante, este escollo solamente lo encontraremos en el ámbito del diseñador del modelo, ya que como veremos en el siguiente apartado, al analizarlo desde una herramienta externa, los valores se visualizarán correctamente.

 


SELECT SUM(Freight) FROM FactInternetSales

 

Seguidamente crearemos la medida que calculará el número pedidos emitidos por la empresa, para lo cual necesitaremos aplicar sobre la columna SalesOrderNumber una operación de recuento de valores distintos (Distinct Count), ya que la tabla FactInternetSales puede tener más de un registro para el mismo pedido.

El botón Sum de la barra de herramientas de Visual Studio puede desplegarse para crear algunos de los cálculos más habituales, Distinct Count entre ellos, aunque en esta ocasión crearemos manualmente la medida FacturasEmitidas, escribiendo la expresión DAX “FacturasEmitidas:=DISTINCTCOUNT([SalesOrderNumber])” en la barra de fórmulas.

 

Analizando el modelo desde Excel

Si hemos instalado Excel 2010 en nuestra máquina, podremos emplearlo como herramienta de análisis para el modelo que estamos desarrollando. Todo lo que tenemos que hacer es seleccionar la opción de menú “Model | Analyze in Excel” de Visual Studio, que abrirá Excel y cargará el modelo en una tabla dinámica.

Una vez situados en Excel, en el panel “Lista de campos de tabla dinámica” marcaremos la medida GastosTransporte, que deberá quedar situada en el bloque Valores. De igual forma procederemos con el campo SpanishPromotionCategory de la tabla DimPromotion, pero en esta ocasión lo situaremos en el bloque Etiquetas de columna. Para terminar, los campos SalesTerritoryGroup y SalesTerritoryCountry serán colocados en el bloque Etiquetas de fila, de forma que podamos observar esta información en orden jerárquico.

ModelosTabularesSQLServer2012AnalysisServices_10

 

Al mismo tiempo, podemos analizar la cantidad de facturas que la empresa ha emitido por los artículos vendidos, añadiendo la medida FacturasEmitidas a la tabla dinámica, de modo que también obtendremos esta información por los campos actualmente situados en las filas y columnas o bien por otros pertenecientes a las tablas del modelo.

Aquellos lectores que hayan tenido la oportunidad de utilizar PowerPivot, encontrarán interesantes similitudes con este modo de trabajo, ya que en ambos casos, la tecnología subyacente es la misma.

 

Conclusiones

Los modelos de análisis tabular, pertenecientes al nuevo paradigma en la construcción de soluciones de Inteligencia de Negocio, BISM, introducido en el recién aparecido SQL Server 2012, representan una estupenda herramienta con la que desarrollar potentes sistemas de información, utilizando los Servicios de Análisis de SQL Server. En el presente artículo hemos realizado una introducción a esta interesante tecnología, mostrando al lector cómo puede empezar a obtener partido de la misma.

Deja un comentario

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *