BISM, Business Intelligence, Data Warehouse, DAX, Excel, PowerPivot, SQL Server, SQL Server 2012, Tabular Model

Creación de consultas para modelos tabulares en SQL Server 2012 (y 2)

En la primera parte de este artículo explicábamos la forma de crear un modelo tabular en SQL Server 2012 para su posterior consulta utilizando algunas de las técnicas que ilustraremos en esta segunda entrega.

 

Consultas DAX

Todos aquellos lectores que hayan tenido la oportunidad de trabajar con PowerPivot conocerán en mayor o menor medida el lenguaje DAX y la capacidad que otorga al usuario de construir, en un modelo de datos, expresiones en forma de columnas y medidas calculadas para obtener los resultados analíticos requeridos.

En lo que respecta al desarrollo de modelos tabulares, a las funcionalidades que acabamos de mencionar se suma ahora la posibilidad de escribir consultas con las que obtener conjuntos de resultados a partir de las tablas del modelo.

Para ello haremos uso de la instrucción EVALUATE que representa la pieza fundamental en la construcción de consultas DAX contra un modelo tabular, acompañando a la misma el nombre de la tabla a obtener, tal y como vemos en el siguiente ejemplo.


EVALUATE
DimStore

Para poder apreciar el resultado de la ejecución de esta sentencia iniciaremos SSMS y nos conectaremos a Analysis Server. A continuación haremos clic en el modelo ContosoTabular recién desplegado en el servidor, que aparece como nodo dependiente de la instancia de Analysis Server, y seleccionaremos la opción de menú «File | New | Query with Current Connection», que abrirá una ventana de consultas MDX que en este caso utilizaremos para escribir sentencias DAX. Antes de proseguir aclararemos al lector que cuando nos encontramos trabajando con modelos tabulares, el editor de consultas de SSMS, a pesar de guardar los archivos de sentencias con la extensión MDX, soporta tanto consultas DAX como MDX.

CreacionConsultasModelosTabularesSQL2012_13

 

Si ya hemos consultado la documentación oficial que sobre la instrucción EVALUATE existe, veremos que es posible ordenar el conjunto de resultados obtenido mediante la cláusula ORDER BY, de tal forma que si modificamos nuestra consulta de la manera mostrada en la siguiente sentencia, los datos aparecerán ordenados por la columna StoreManager.


EVALUATE
DimStore
ORDER BY [StoreManager]

Podemos combinar varias columnas a la hora de ordenar, así como alterar el sentido de la ordenación con los modificadores ASC y DESC.


EVALUATE
DimStore
ORDER BY
    [StoreManager] ASC,
    [StoreKey] DESC

 

CreacionConsultasModelosTabularesSQL2012_14

 

Pero aún hay más respecto a la ordenación, ya que usando la subcláusula START AT conseguiremos que los datos comiencen a mostrarse a partir de un determinado valor, perteneciente a la columna(s) que participa en el orden.


EVALUATE
DimStore
ORDER BY [StoreManager]
START AT 75

Dedicaremos un próximo artículo a profundizar en el uso de EVALUATE y el resto de instrucciones DAX especialmente desarrolladas para el trabajo con modelos tabulares.

 

Elementos calculados. Medidas y columnas

En un modelo de datos tabular, además de la información que obtenemos de las columnas de una tabla, podemos encontrarnos con columnas y medidas calculadas que no forman parte de la estructura original de dicha tabla, sino que son producto de una expresión/fórmula DAX. En este apartado haremos una primera aproximación a este tipo de elementos de un modelo.

Pongamos por ejemplo que para la tabla FactSales queremos saber cuánto ha supuesto el total de ventas, por lo que tendremos que aplicar una operación de suma sobre la columna SalesAmount.

En primer lugar nos situaremos en dicha tabla dentro del diseñador. Como podemos comprobar, la cuadrícula de datos se divide en dos zonas: la superior, correspondiente a los propios registros de la tabla; y la inferior, reservada a las medidas calculadas, y que actualmente se encuentra vacía.

Para crear la medida que sume las filas de la columna SalesAmount haremos clic en una celda vacía de la zona inferior de dicha columna, y a continuación escribiremos la siguiente expresión en la barra de fórmulas del diseñador.


TotalVentas :=
SUM ( [SalesAmount] )

De esta forma obtendremos una medida con el nombre TotalVentas, que mostrará el resultado de la operación de suma sobre la columna.

CreacionConsultasModelosTabularesSQL2012_15

 

También conseguiremos el mismo resultado mediante la opción de menú «Column | AutoSum | Sum», con la diferencia de que el nombre de la medida (Sum of SalesAmount) será asignado automáticamente, no obstante, siempre podemos recurrir a la ventana de propiedades de la medida y cambiarle el nombre en su propiedad Measure Name.

Pasemos seguidamente a la creación de una columna calculada, donde vamos a suponer que necesitamos una nueva columna para la tabla FactSales, en la que se realice una operación sobre la columna SalesAmount, que arroje como resultado el valor de dicha columna para la fila actual menos el 10%. Esto lo conseguiremos haciendo clic en una celda de la primera columna vacía disponible en la tabla y escribiendo la siguiente sentencia en la barra de fórmulas.


=
[SalesAmount]
    - ( [SalesAmount] * 0.1 )

A continuación asignaremos el nombre a esta nueva columna haciendo doble clic en su cabecera y escribiendo el valor VentasConDescuento.

CreacionConsultasModelosTabularesSQL2012_16

 

Tras la creación de estos miembros calculados proseguiremos con otro de los modos de consulta a nuestra disposición: las consultas MDX.

 

Consultas MDX

Aunque MDX fue desarrollado como lenguaje de consulta para cubos OLAP (modelos multidimensionales), en SQL Server 2012 ha sido adaptado para poder interaccionar también con modelos tabulares, de manera que cuando lo empleamos dentro de este contexto, el nombre del modelo (archivo bim del proyecto en SSDT) representa al cubo de datos, mientras que las tablas identifican a las dimensiones. Las medidas que hayamos calculado en las diferentes tablas quedarán agrupadas en un nodo aparte, funcionando como una dimensión especial, lo que vemos al abrir el modelo en SSMS.

CreacionConsultasModelosTabularesSQL2012_17

 

Para hacer una consulta MDX contra el modelo emplearemos la instrucción SELECT, donde al  igual que si consultáramos un cubo OLAP, especificaremos, mediante la cláusula ON COLUMNS, la dimensión que ubicaremos en el eje de las columnas, y que en este caso será la medida TotalVentas, ya que como hemos mencionado, las medidas del modelo también pueden comportarse como una dimensión al crear la consulta.

Con la cláusula ON ROWS indicamos que la dimensión utilizada para las filas será DimGeography, visualizando el nombre de las provincias o estados de los países con la función Children de MDX aplicada al atributo StateProvinceName.

SELECT
  {
    [Measures].[TotalVentas]
  } ON COLUMNS,
  {
    [DimGeography].[StateProvinceName].children
  } ON ROWS
FROM [Model] 

 

CreacionConsultasModelosTabularesSQL2012_18

 

Agregando la cláusula WHERE a la consulta podremos aplicar filtros al resultado. Por ejemplo, si queremos que las cifras de ventas se circunscriban solamente a las ventas de productos de color azul (tabla DimProduct, atributo ColorName), emplearemos la siguiente consulta.

SELECT
  {
    [Measures].[TotalVentas]
  } ON COLUMNS,
  {
    [DimGeography].[RegionCountryName].children
  } ON ROWS
FROM [Model]
WHERE (
        [DimProduct].[ColorName].&[Azul]
      )

 

CreacionConsultasModelosTabularesSQL2012_19

 

Podemos desplazar la medida TotalVentas a la cláusula WHERE y poner en las columnas una selección de los años en los que se realizaron las ventas. Para especificar dichos años como un rango, simplemente hemos de poner el primer y último elemento del mismo separados por dos puntos, tal y como vemos a continuación.

SELECT
  {
    [DimDate].[CalendarYear].&[2007] : [DimDate].[CalendarYear].&[2010]
  } ON COLUMNS,
  {
    [DimGeography].[RegionCountryName].children
  } ON ROWS
FROM [Model]
WHERE (
        [Measures].[TotalVentas]
      )

 

CreacionConsultasModelosTabularesSQL2012_20

 

Excel como herramienta de consulta

Y llegamos a Excel, el tercero de los tipos de consulta contra modelos tabulares que estamos presentando en este artículo.

Cuenta como gran ventaja con una potente interfaz de usuario, lo que nos permite construir informes de gran riqueza ya que pone a nuestra  disposición un amplio abanico de elementos visuales tales como tablas dinámicas, gráficos, estilos, formatos, etc.

Una vez iniciado Excel, para acceder al contenido de un modelo tabular haremos clic en la pestaña Datos de la cinta de opciones, y a continuación, dentro del grupo «Obtener datos externos», haremos clic en la opción «De otras fuentes» seleccionando en la lista desplegable el valor «Desde Analysis Services».

CreacionConsultasModelosTabularesSQL2012_21

 

Como resultado se abrirá un asistente en el que especificaremos el servidorinstancia a la que queremos conectarnos.

CreacionConsultasModelosTabularesSQL2012_22

 

En el siguiente paso seleccionaremos la base de datos que contiene el modelo tabular (ContosoTabular) junto al propio modelo, que en el asistente estará identificado como un cubo.

CreacionConsultasModelosTabularesSQL2012_23

 

En el último paso guardamos la información de conexión en un archivo.

CreacionConsultasModelosTabularesSQL2012_24

 

Como resultado de la ejecución del asistente se establecerá una conexión contra el modelo tabular, y en la hoja de cálculo se creará una tabla dinámica que nos servirá como vehículo para presentar los datos del modelo. En el panel de campos de la tabla dinámica tendremos a nuestra disposición las tablas y medidas que hayamos definido en el modelo, que arrastraremos, bien directamente a la tabla o a los bloques denominados Valores, Etiquetas de fila/columna y Filtro de informe, hasta componer un informe similar al que podemos ver a continuación, donde analizamos las ventas de productos de color azul en función de los años y el país en que se ha producido la venta.

CreacionConsultasModelosTabularesSQL2012_25

 

Además podemos añadir un gráfico sincronizado con los valores numéricos desde la opción Gráfico dinámico, perteneciente a la pestaña Opciones de la pestaña de nivel superior Herramientas de tabla dinámica. En dicho gráfico también es posible aplicar filtros al igual que desde la tabla dinámica.

CreacionConsultasModelosTabularesSQL2012_26

 

Y llegados a este punto damos por concluida esta introducción dedicada a la elaboración de consultas contra los nuevos modelos tabulares de SQL Server 2012, espero que resulte de ayuda a todos los que quieran empezar a dar sus primeros pasos con esta interesante tecnología.

1 Comentario

  1. anonymous

    Como ya explicábamos en el artículo sobre creación de consultas para modelos tabulares

Deja un comentario

Tema creado por Anders Norén