En la entrada anterior recordamos algunas de las características de PowerPivot y Modelo Tabular dentro de SSAS 2012.

El lenguaje de consultas que tendremos dentro de estas herramientas es el lenguaje de expresiones DAX.  El trabajo con este lenguaje, tiene como objetivo fundamental facilitar el análisis de datos al usuario final y para conseguirlo, se ha creado un lenguaje parecido a Excel, que si se utiliza con conocimientos de BBDD relacionales y tablas dinámicas Excel, nos brindará tremendas funcionalidades.

Componentes

Según el concepto planteado antes, las expresiones DAX están formadas por: funciones, operadores y constantes. Las constantes son, como siempre, valores que no cambian, veamos los otros dos componentes:

Por un lado, las funciones son fórmulas integradas en el lenguaje para manipular más fácilmente los datos, la gran mayoría viene de Excel como SUM, AVERAGE y otras, son propias de DAX, que permiten extender funcionalidades, como es el caso de las funciones dedicadas al BI de Tiempos, por ejemplo: OPENINGBALANCEYEAR, CLOSINGBALANCEYEAR, DATESYTD, ENDOFYEAR, PREVIOUSYEAR, NEXTYEAR, SAMEPERIODLASTYEAR, todas estas tienen sus equivalentes para meses y trimestres y una función muy importante en el mundo BI PARALLELPERIOD. Además hay funciones muy útiles y que estaremos usando con mucha frecuencia como RELATED y RELATEDTABLE que trabajan con columnas y tablas relacionadas y CALCULATE que nos va a permitir evaluar una expresión en un contexto que puede ser modificado mediante filtros, esto lo veremos. Tendremos ayuda para dar los primeros pasos, como en Excel.

Los operadores, por su parte, son los habituales, aquí sin novedades: + – * / …

Las fórmulas DAX siempre comienzan con signo igual (=). Cuando estamos creando una medida entonces escribimos el nombre de la medida, dos puntos (:) e igual (=). Ejemplo: SumaCantidad:=SUM(Cantidad)

DAX vs Excel

Aunque existen algunas diferencias, la sintaxis, es muy parecida a la de Excel, todo el conocimiento previo ayuda, aunque no podemos pegar DAX en Excel ni fórmula Excel en PowerPivot.

Como valores de entrada a las funciones se referencian Tablas y/o Columnas enteras, nunca celdas ni rangos de ellas. Las tablas de PowerPivot siempre deben tener el mismo número de columnas en cada fila y todas las filas de una columna deben contener el mismo tipo de datos. En caso de necesitar valores individuales o conjuntos, necesitamos emplear funciones de filtro, DAX es altamente competente en estos casos.

Los tipos de datos soportados por DAX y Excel no son los mismos, al convertir se produce o se intenta producir conversiones implícitas de los datos. Si no se consigue devuelve error. DAX no admite el tipo de datos variant de Excel. Como no me voy a detener en este aspecto, les dejo el enlace a Tipo de datos DAX del MSDN de MS.

Como resultado de una función podemos obtener una tabla o un valor, esta tabla puede ser a su vez, valor de entrada de otra función que devuelva un resultado escalar. La tabla no la vamos a poder «ver ni tocar», tendremos que pensar como una tabla temporal, que se crea para poder ejecutar correctamente la expresión DAX.

Las funciones de fecha y hora de DAX devuelven un tipo de datos datetime. En contraste, las funciones de fecha y hora de Excel devuelven un entero que representa una fecha como un número de serie.

Salvo raras excepciones las funciones requieren uno o más parámetros de entrada o argumentos, que pueden ser, como hemos dicho, valores de tabla o escalar, columnas o expresiones. Las expresiones, a su vez pueden contener otras funciones con otros parámetros incluidas otras expresiones. Hay que tener cuidado con la complejidad y el anidado de expresiones.

Una misma fórmula puede devolver resultados diferentes si se aplica a una columna o a  una medida, incluso puede devolver error en un caso y ser válida en otro, según el contexto. Sobre los contextos en DAX, y ya es la segunda vez que menciono el tema hoy, vamos a hablar en otro momento. Lo primero que estudié sobre estos conceptos fue la Biblioteca PowerPivot, un blog muy interesante que llevan los amigos de SolidQ, que les recomiendo y además, MSDN.

Tanto las columnas calculadas como las medidas van a pertenecer a una tabla en concreto.

DAX proporciona además, unas funciones de búsqueda nuevas que requieren que se establezca una relación entre las tablas.

Nombres de elementos

En la ventana PowerPivot o el Modelo Tabular SQL Server 2012, vamos a poder trabajar con múltiples tablas, con nombre único. En cada tabla, hay columnas con nombre único dentro de cada tabla; pero que pueden repetirse para diferentes tablas.

La forma completa de nombrar una columna es Tabla.[Columna]

Para pasar como parámetro una columna en una expresión de columna calculada, es frecuente no utilizar el nombre de la Tabla, cuando la columna utilizada en la expresión pertenece a la misma tabla en la que se está creando la nueva columna calculada. Esto se hace, sobre todo, por no agrandar excesivamente la expresión. Digamos que estamos en una tabla DetalleOrden donde tenemos dos columnas Cantidad  y Precio y queremos obtener una columna calculada que devuelva el resultado de Cantidad por Precio. Es válido escribir la expresión =[Cantidad]*[Precio]

Cuando hablamos de una medida, lo más sano es nombrar completamente la columna, sobre todo por «curarse en salud» ante posibles cambios, que podrían ser, por ejemplo: que cambie la tabla donde se aloja la medida, o que se agregue una columna en otra tabla con el mismo nombre.

Los nombres no distinguen entre mayúsculas y minúsculas, así es que la tabla productos es la misma que Productos o PRODUCTOS.

Relaciones entre tablas

Regresaremos a este tema con ejemplos, sólo quiero dejar introducida la idea de que DAX requiere que las tablas estén relacionadas para realizar muchos de los cálculos. Es fácil crear relaciones, tanto en PowerPivot como en Modelo Tabular, aunque tenemos que tener en cuenta algunos aspectos como:

Control o tratamiento de la relación activa. Se pueden crear múltiples relaciones a partir de una columna; pero sólo una estará activa.

Cuando las tablas están conectadas por una relación, PowerPivot no aplica la integridad referencial, por lo que es posible tener valores no coincidentes en una columna de clave y sin embargo crear una relación y esto, viniendo del relacional, no es lo que esperamos.

Aquí lo dejamos, en la próxima vamos a crear el entorno de trabajo importando las tablas necesarias y eliminando las columnas que no vamos a necesitar. De esta forma estaremos listos para empezar a trabajar con ejemplos de uso del Lenguaje de expresiones DAX.