La importancia de las relaciones en los modelos tabulares de SQL Server 2012

Una característica esencial de los modelos de datos tabulares pertenecientes a BISM (Business Intelligence Semantic Model), el nuevo paradigma de desarrollo BI incorporado en SQL Server 2012, lo constituyen las relaciones entre las tablas integrantes de un determinado modelo, ya que sin ellas resultaría imposible realizar un adecuado análisis de la información contenida en el mismo.

Pongamos como ejemplo que tenemos una base de datos con el nombre AdvWksDW, creada a partir de algunas de las tablas de la base de datos AdventureWorksDW2012, pero sin las correspondientes relaciones entre las mismas. El siguiente bloque de código muestra las sentencias SQL necesarias para su creación.

CREATE DATABASE AdvWksDW
GO
 
USE AdvWksDW
GO
 
SELECT * INTO FactInternetSales FROM AdventureWorksDW2012.dbo.FactInternetSales
GO
 
SELECT * INTO DimDate FROM AdventureWorksDW2012.dbo.DimDate
GO

 

Para aquellos lectores más familiarizados con el trabajo con modelos de análisis multidimensional, si tuviéramos que desarrollar un cubo OLAP empleando esta base de datos, la tabla FactInternetSales representaría a la tabla de hechos, ya que contiene las columnas a partir de las cuales podemos obtener las medidas o resultados numéricos a analizar; mientras que la tabla DimDate representaría a una tabla de dimensión, empleada para filtrar la información por las fechas existentes en el modelo.

A continuación crearemos un proyecto de análisis de tipo tabular con SQL Server Data Tools (SSDT); el entorno de desarrollo para SQL Server 2012 basado en Visual Studio 2010, conocido en anteriores versiones como Business Intelligence Development Studio.

 

En el modelo de datos de este proyecto incluiremos las tablas de la base de datos que acabamos de crear, añadiendo también una medida que sume los valores de la columna SalesAmount.

 

Para analizar este modelo desde Excel seleccionaremos la opción de menú de SSDT «Model | Analyze in Excel», o bien haremos clic en el botón de la barra de herramientas que desempeña el mismo propósito.

 

Esta acción tendrá como resultado la apertura de una nueva hoja de cálculo que mostrará el modelo de datos en una tabla dinámica, dentro de la cual situaremos la medida «Sum of SalesAmount» de la tabla FactInternetSales en el bloque Valores, y el campo CalendarYear de la tabla DimDate en el bloque «Etiquetas de fila».

El resultado que necesitaríamos obtener de esta tabla dinámica sería la suma del campo SalesAmount, perteneciente a la tabla FactInternetSales, agrupada por los años de venta, es decir, por el año del campo OrderDateKey. Puesto que contamos con la tabla DimDate como catálogo de fechas, si combinamos en una consulta SQL ambas tablas por los campos de fecha adecuados, obtendríamos las cifras esperadas.

 

Sin embargo el resultado obtenido en la tabla dinámica es muy distinto, ya que todas sus celdas muestran el mismo valor: el total de la suma del campo SalesAmount. Esto es debido a la inexistencia de una relación entre los campos OrderDateKey y DateKey de las tablas FactInternetSales y DimDate respectivamente, lo cual produce que el motor del modelo tabular no sepa cómo aplicar la medida «Sum of SalesAmount».

 

Relaciones en el modelo tabular

En escenarios como el que acabamos de describir es donde nos damos cuenta de la importancia que cobra un adecuado establecimiento de relaciones entre las tablas de nuestro modelo, a fin de conseguir un óptimo análisis de la información.

Un modelo tabular obtiene, en el caso de que existan, las relaciones directamente de la fuente de datos origen durante la ejecución del asistente de importación de datos. A las relaciones obtenidas de esta forma las denominaremos relaciones automáticas.

Si el modelo carece de relaciones, o bien necesitamos relaciones adicionales a las ya existentes, podemos crearlas mediante la ventana de administración disponible a tal efecto en el entorno de desarrollo de Visual Studio 2010; llamaremos a este tipo relaciones manuales.

En nuestra situación actual, para que la información del modelo resulte coherente, necesitamos, como hemos indicado antes, crear una relación entre las tablas FactInternetSales y DimDate, por los campos OrderDateKey y DateKey; para ello seleccionaremos la opción de menú «Table | Create Relationships», que abrirá la ventana en la que realizaremos dicha operación, seleccionando las tablas y campos integrantes de la relación.

 

Una vez creada la relación volveremos a la tabla dinámica de Excel en la que estamos analizando el modelo, y después de hacer clic en la opción Actualizar (grupo Datos de la pestaña Opciones, contenida en la pestaña de nivel superior «Herramientas de tabla dinámica») se producirá una nueva lectura de los datos del modelo por parte de la tabla dinámica, refrescando su contenido, y haciendo que las cifras de ventas por año se muestren ya correctamente.

 

El estado de la relación. Relaciones activas e inactivas

En un modelo de datos puede suceder que una tabla en la que hemos definido medidas, varios de sus campos se relacionen con un mismo campo de otra tabla que vamos a utilizar para filtrar los datos de la primera. Cuando esto ocurre, ¿cómo podemos saber qué campo de la tabla de medidas se está relacionando con la tabla de filtro al analizar el modelo por ambas tablas? Para aquellos lectores con experiencia en el desarrollo de cubos OLAP, la pregunta a formular sería: ¿qué campo de la tabla de hechos se está relacionando con la tabla de dimensión?

La respuesta a esta pregunta la hallamos en el estado de la relación (activa o inactiva), y la mejor manera de ilustrar este concepto es a través de un ejemplo. Por consiguiente, vamos a crear un nuevo proyecto tabular en SSDT, cuya fuente de datos sea esta vez la base de datos AdventureWorksDW2012, importando en el modelo, al igual que en el ejemplo anterior, las tablas FactInternetSales y DimDate; y definiendo igualmente una medida con la suma de la columna SalesAmount.

Al analizar el modelo en Excel, situando el campo CalendarYear en las etiquetas de fila, los números resultantes corresponderán a las ventas realizadas por fecha de factura.

 

Que serán las mismas cifras que obteníamos de la consulta SQL presentada en un apartado anterior.

 

Llegados a este punto es donde el lector se preguntará con toda seguridad: «¿Y cómo sabe el modelo que yo quiero obtener la suma del campo SalesAmount en base al campo OrderDateKey, y no en base a DueDateKey o ShipDateKey, que también son de tipo fecha?»

Realmente, el modelo no lo sabe, sino que se limita a utilizar la relación activa del modelo. Expliquemos esto con mayor detalle:

Si observamos el diseñador del modelo en vista de diagrama, veremos que desde la tabla FactInternetSales parten tres relaciones hacia la tabla DimDate. De las flechas que indican visualmente las relaciones, la que tiene el trazo de línea continuo corresponde a la relación activa, y por lo tanto, será la que se utilizará de forma predeterminada cuando se consulte el modelo empleando el campo CalendarYear de la tabla DimDate. Al hacer clic en dicha relación, la ventana de propiedades del entorno de desarrollo de Visual Studio mostrará el detalle de la misma. Nótese que la propiedad Active tiene el valor True.

 

También podemos averiguar cuál es la relación activa, y por ende, las inactivas, seleccionando en Visual Studio la opción de menú «Table | Manage Relationships», que abrirá la ventana de administración de las relaciones del modelo, donde la  columna Active nos informará de este aspecto en particular.

 

Consultando el modelo a través de una relación inactiva

La cuestión a plantear ahora sería cómo podríamos interrogar al modelo, de forma que nos proporcionara la suma del campo SalesAmount, pero utilizando alguno de los otros dos campos de fecha (DueDateKey o ShipDateKey) que también están relacionados con el campo DateKey de la tabla DimDate. Dicho con otras palabras, lo que necesitamos es alterar el contexto de filtro predeterminado actualmente activo.

La solución pasa por crear una nueva medida, que también sume el campo SalesAmount, pero indicando de alguna manera que el campo de agrupación va a ser uno distinto del predeterminado. Por ejemplo, si queremos obtener los resultados de ventas por el campo ShipDateKey, usaremos la siguiente expresión DAX.

VentasPorFechaEnvio :=
CALCULATE (
    SUM ( [SalesAmount] ),
    USERELATIONSHIP ( FactInternetSales[ShipDateKey], DimDate[DateKey] )
)

 

Para añadir esta medida al modelo de datos, dentro del área reservada a las medidas haremos clic en una celda vacía debajo de la columna SalesAmount, y escribiremos la anterior expresión en el panel de introducción de fórmulas/expresiones.

 

Analicemos esta expresión por partes: debemos emplear la función SUM(), ya que nuestro objetivo principal es sumar el campo SalesAmount, pero como queremos que al agrupar la suma, se utilice el campo ShipDateKey, mediante la relación de éste con el campo DateKey de la tabla DimDate, tenemos que especificar explícitamente dicha relación porque no es la activa, y esto es lo que hacemos con la función USERELATIONSHIP(). Ahora bien, precisamente por el hecho de utilizar una relación que no es la activa, necesitamos alterar el contexto de filtro actual, lo que conseguimos usando la función CALCULATE().

 

Como hemos hecho en ejemplos anteriores, si trasladamos esta situación a una consulta SQL, para comprobar que los valores obtenidos son los mismos, la sentencia quedaría tal y como vemos a continuación.

 

Conclusiones

En el presente artículo hemos realizado una introducción a las relaciones en los modelos tabulares del nuevo modelo semántico para Business Intelligence incorporado a SQL Server 2012. Dicha característica representa una pieza fundamental en la construcción de sistemas de análisis empleando esta tecnología, por lo que confiamos en que este artículo sirva de ayuda al lector para iniciarse en el desarrollo de sus propios modelos de análisis utilizando BISM.