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. Los ejemplos elaborados a lo largo del artículo están
disponibles en este enlace.