POWERPIVOR Y EL LENGUAJE DAX Funciones de Inteligencia de Tiempo para el Business Intelligence 25/02/2013 Autor: Juan JosĂŠ Macias Vidal www.linkedin.com/in/juanjosemaciasvidal
POWERPIVOT es un complemento de Excel gratuito para el análisis de datos. Para este fin usa las herramientas de tablas y gráficos dinámicos que proporciona Excel. Aunque las tablas y gráficos dinámicos de Excel son una herramienta excelente para el análisis de datos, presentan como mayor inconveniente el hecho de trabajar únicamente sobre una tabla de datos. Si deseamos analizar datos que se encuentran en una o varias bases de datos que a su vez están integradas por multitud de tablas, sólo lo podremos hacer a través Powerpivot. Además, una de las grandes ventajas de este complemento es que se pueden crear relaciones entre las tablas, lo que me permitirá realizar análisis de datos que no están contenidos en la tabla actual sobre la que trabajamos sino en una tabla segundaria con la que crea una relación. Por último, otras de las características de PowerPivot es que nos permite manejar millones de filas en las tablas. Puede trabajar con archivos de hasta 2GB e incluso guarda en memoria hasta 4GB de datos. Hemos de destacar para finalizar, y a diferencia de las tablas dinámicas de Excel, en PowerPivot no se pueden realizar agrupaciones de datos. Por ejemplo agrupar por años, trimestres, meses…etc., sin embargo, si podremos utilizar el lenguaje DAX para realizar estas agrupaciones y creando una tabla de fechas. Las fórmulas de Excel hacen referencia a celdas o rangos de celdas, e incluso matrices. Las fórmulas de Excel no nos servirán para trabajar en PowerPivot, ya que este trabaja sobre tablas y columnas de la tabla actual o de la relacionada, no sobre celdas. Es por ello que es necesario un nuevo lenguaje de fórmulas denominado El lenguaje DAX (Expresiones de análisis de datos) que es un lenguaje de fórmulas que permite a los usuarios definir cálculos personalizados en tablas de PowerPivot (columnas calculadas) y en tablas dinámicas de Excel (medidas). DAX incluye algunas de las funciones que se usan en fórmulas de Excel y funciones adicionales que están diseñadas para trabajar con datos relacionales y realizar agregaciones dinámicas. Dentro del Lenguaje DAX tenemos los siguientes grupos de funciones: • • • • • • • •
Funciones de Texto Funciones de Información Funciones Lógicas Funciones Matemáticas Funciones Estadísticas Funciones de Filtro Funciones de Fecha y hora Funciones de Inteligencia del Tiempo
Las funciones de inteligencia de tiempo cubren las necesidades de análisis de Business Intelligence, permitiéndole manipular datos mediante los períodos de tiempo, incluidos días, meses, trimestres y años, y, a continuación, compilan y comparan los cálculos a lo largo de estos períodos. En total existen 35 funciones de inteligencia de tiempo, pero como ya veremos a lo largo de este artículo, algunas de estas funciones pueden ser sustituidas por otras, con lo que se reducen considerablemente su número. Para comprender el artículo expuesto es necesario descargar el archivo que utilizaremos para la realización de los cálculos: Ejercicio Contoso. El ejercicio está realizado con Excel 2.010. Con versiones anteriores sólo se podrá abrir la hoja Excel pero no la hoja de datos de PowerPivot. La versión instalada
1
Autor: Juan José Macias Vidal
de PowerPivot es la de enero 2.013. Con cualquier otra versión anterior no permitirá abrir el libro de datos, sólo la hoja de Excel. Podemos dividir las funciones de inteligencia de tiempo en tres grandes grupos: • • •
Aquellas funciones que devuelven una fecha Aquellas funciones que devuelven una tabla de fechas que se utilizarán como argumento para otras expresiones o funciones de cálculo. Aquellas funciones que evalúan expresiones en periodos de tiempo determinados.
Cuando un usuario aborda por primera vez las funciones del Lenguaje DAX, y en concreto, las de las funciones de inteligencia de tiempo, puede ser un poco difícil entender su funcionamiento. Sin embargo, creo que a través del ejercicio facilitado para su descarga y del presente artículo, puede ser mucho más fácil comprender estas funciones, de vital importancia para el Business Intelligence. Veamos a continuación cada uno de los grupos mencionados. Funciones que devuelven una fecha Dentro de este grupo tenemos dos subgrupos de funciones, aquellas que devuelven una fecha al principio o fin del período, y aquellas que devuelven una fecha al inicio o fin de mes, trimestre y año. En el primer subgrupo tenemos las funciones Firstdate y Lastdate. Estas devuelven la primera fecha o última de la columna Dates (Fechas). Si observamos la Hoja F 1.1-1 del libro de ejemplo utilizamos en el cálculo la tabla y columna DimDate[DateKey]. Si en la tabla dinámica, en la etiqueta de filas insertamos como fecha el campo día, el valor de Firstdate y Lastdate son iguales para el día. Si en la etiqueta de filas insertamos el campo mes, nos dará el primer día y último día del mes actual. Si en la etiqueta de filas insertamos el campo trimestre, nos dará el primer día y último del trimestre, y si insertamos el campo año, nos dará el primer día y último del año. Si utilizamos estas funciones como argumento de filtro de la función Calculate(Expresión, filter,..) y para la expresión [Ventas], expresión que suma las ventas de la tabla FactSales, Ventas:=Sum(FacSales[SalesAmount]), obtendremos: • Para el año, las ventas del primer día y último día del año. • Para el trimestre, las ventas del primer y último día del trimestre. • Para el mes, las ventas del primer día y último del mes. • Para el día, serían exactamente iguales. Podemos ver los resultados en la tabla F 1.1. En cuanto al segundo grupo, lo componen las funciones StartOfMonth, StartOfQuarter, StartOfYear y EndOfMonth, EndOfQuarter y EndOfYear. Estas funciones darán como resultado tal y como podemos observar en la tabla 1.2.-1: • StartOfMonth y EndOfMonth nos da como valor la primera y última fecha del mes actual si en la etiqueta de filas tenemos el campo meses. Si tenemos el campo trimestre nos dará la primera fecha del primer mes del trimestre actual y última fecha del último mes del trimestre actual, y si tenemos el campo
2
Autor: Juan José Macias Vidal
año, la primera fecha del primer mes del año actual y la última fecha del último mes del año actual. Nos dará los mismos valores que Firstdate y Lastdate para las etiquetas de filas meses, trimestres y años. En la etiqueta de fila no tiene sentido utilizar el campo días. • StartOfQuarter y EndOfQuarter nos dará la primera y última fecha del trimestre actual. En la etiqueta de filas sólo se indicará los campos trimestres y años. Para los meses no tendría sentido como podemos observar en la tabla. Si en la etiqueta de filas tenemos año, nos dará la primera fecha del primer trimestre del año actual y la última fecha del último trimestre del año actual. • StartOfYear y EndOfYear nos da como valor la primera y última fecha del año. En la etiqueta de filas sólo utilizaremos el campo años, ya que el resto como podemos observar no tendría sentido. Si utilizamos estas funciones como argumentos de filtro para la función CALCULATE y la expresión [Ventas], tal y como podemos observar en la tabla 1.2, obtendríamos las ventas para el primer día y último día de mes actual, para el primer y último día del trimestre actual, y para el primer día y último del año actual. Funciones que devuelven tablas de fechas Podemos dividir estas funciones en tres subgrupos: • Las que calculan un período anterior o siguiente. • Las que calculan un período hasta la fecha. • Las que cambian un conjunto de fechas por otro conjunto de fechas. Dentro del primer grupo tenemos las siguientes funciones: • PreviousDay, PreviousMonth, PreviousQuarter y PreviousYear que calculan períodos anteriores a día, mes, trimestre y año. • NextDay, NextMonth, NextQuarter y NextYear que calculan períodos posteriores a día, mes, trimestre y año. Vamos a utilizar estas funciones como argumento de la función CALCULATE y para la expresión [Ventas] y analizar los resultados obtenidos en la Hoja 2.1. PreviousYear nos da para el año actual el total de ventas del año anterior, y NextYear, para el año actual nos da el total de ventas del año siguiente. PreviousQuarter nos dará para el año actual el total de ventas del trimestre anterior. En el primer trimestre del año actual tomo el total del último trimestre del año anterior. Si en la etiqueta de filas tenemos el campo años, tomara el total de ventas del último trimestre del año anterior. En cuanto a NextQuarter, nos dará el total de ventas del trimestre siguiente. En el último trimestre del año actual tomará el total de ventas del primer trimestre del año siguiente. Si en la etiqueta de filas tenemos el campo años, nos dará el total de ventas del último trimestre del año actual. PreviousMonth nos dará el total de ventas del mes anterior. Observar que el primer mes del año toma las ventas del último mes del año anterior. Si en las etiquetas de filas tenemos el campo trimestre, nos dará el total de ventas del último mes del trimestre anterior. En el primer trimestre tomará las correspondientes a la del último mes del último trimestre del año anterior. Si en la etiquetas de filas tenemos el campo año, tomará las ventas del último mes del trimestre del año anterior. En cuanto a
3
Autor: Juan José Macias Vidal
NextMonth, nos dará el total de ventas del mes siguiente, que en el último mes del año actual será la del primer mes del año siguiente. Si en la etiquetas de filas tenemos el campo trimestres, nos dará el total de ventas del primer mes del trimestre siguiente. En el último trimestre del año, tomará el del primer trimestre del año siguiente. Si en las etiquetas de filas tenemos el campo año, tomará el total de ventas del primer mes del año siguiente. PreviousDay tomará el total de ventas del día anterior. En el primer día del año, tomará las del último día del año anterior. Si en la etiquetas de filas tenemos el campo mes, tomará las ventas del último día del mes anterior. Si en la etiquetas de filas tenemos el campo trimestres, tomará las ventas del último día del trimestre anterior. Y si en las etiquetas de filas tenemos el campo año, tomará las ventas del último día del año anterior. NextDay tomará el total de ventas del día siguiente. Para el último día del año, tomará las del primer día del año siguiente. Si en las etiquetas de filas tenemos el campo meses, tomará las del primer día del mes siguiente, que en el último mes del año será la correspondiente al primer día del primer mes del año siguiente. Si en las etiquetas de filas tenemos el campo trimestres, tomará como ventas las del primer día del primer mes del trimestre siguiente, que en el último del año, será el del primer trimestre del año siguiente. Si en las etiquetas de filas tenemos el campo año, tomará las del primer día del primer mes del año siguiente. Estas ochos funciones pueden sustituirse para el cálculo por la función DateADD como veremos más adelante tomando como número de intervalos +1 o -1 según sea posterior o anterior, y como intervalo, day, month, quarter o year según el caso. La diferencia es que en los subtotales de la tabla dinámica me tomará la suma de las ventas de cada día para el subtotal de mes, la suma de las ventas de los meses correspondientes al trimestre para el subtotal de trimestre, y la suma de las ventas de los meses para el subtotal del año. Lo que tiene más sentido si estamos creando un Cuadro de Mando. El segundo grupo está formado por las siguientes funciones: • DatesMTD, DatesQTD y DatesYTD. • SamePeriodLastYear Las tres primeras calculan como vemos en la tabla de la Hoja 2.2 las ventas acumuladas. DatesMTD indican las ventas acumuladas hasta la fecha mes, es decir, la suma de las ventas de cada día del mes actual. Si en la etiquetas de filas indicamos el campo días y meses veremos claramente cómo se acumulan las ventas. Si en la etiquetas de filas indicamos el campo trimestres, tomará como importe el del último mes del trimestre actual, y si indicamos el campo año, tomará la del último mes del año actual. DatesQTD suman las ventas mensuales de los meses correspondientes al actual trimestre. Si colocamos en la etiquetas de filas el campo meses y trimestres lo veremos claramente. Si indicamos en la etiquetas de filas el campo años, tomará la suma de ventas del último trimestre del año. DatesYTD indica las ventas acumulas hasta la fecha año, es decir, las suma de las ventas de todos los meses del año. Si en la etiquetas de filas colocamos el campo meses y trimestres observamos cómo se van acumulando las ventas. SamePeriodLastYear indica las ventas del rango del período anterior. Si observamos la tabla de la Hoja 2.2 los resultados son los idénticos a Ventas pero desplazados un período. Idéntico resultado obtendríamos si utilizamos DateADD (Dates, -1, year). El último grupo de funciones me cambian el conjunto de fechas por otro conjunto de fechas. DatesADD como hemos comentado anteriormente puede sustituir a las ochos funciones que calculan un período
4
Autor: Juan José Macias Vidal
anterior y siguiente. Por ejemplo, =Calculate([Ventas];DATEADD(DimDate[Datekey];-1;MONTH)) es idéntica a PreviousMonth. Como podemos observar en la sintaxis de la función DateADD, (Dates; Número de Intervalos; Intervalos), podemos desplazar hacia delante o atrás n intervalos las fechas, con signo positivo hacia delante y con signo negativo hacia atrás. Intervalo corresponde a day, month, quarter y year. Podemos calcular por ejemplo las ventas desplazadas 3 días, 8 meses, 2 años hacia delante o atrás, o en cualquier otro período. DatesbetWeen y DatesinPeriod son funciones idénticas, aunque la primera se suele utilizar para cálculos de períodos personalizados y la segunda para períodos correspondientes a meses, trimestres y años. Como observamos en la Hoja 2.3, podemos calcular las ventas de un año, trimestre o mes concreto. También podemos calcular las ventas de un período de días, por ejemplo, del 10 de enero al 17 de enero. Hay que tener en cuenta que las fechas son inclusivas, es decir, se incluyen en la suma. Por ejemplo, las ventas entre el 10 de enero y el 17 del mismo mes se incluiría las ventas del día 10 y 17. Si quiero calcular a fecha 01.01.2008 las ventas del mes anterior, diciembre de 2.007, e indicamos: =CALCULATE ([VENTAS];DATESINPERIOD (DimDate[Datekey];DATE(2008;01;01);-1;month)) estaremos tomando las ventas del 2 de diciembre al 1 de enero porque son inclusivas y estaremos cometiendo un error. Tendremos que indicar en Date (2007;12;31) Por último, Parallelperiod dan como resultado una tabla de fechas que son un paralelo de las fechas de la columna indicada, desplazadas un intervalo anterior o posterior, ya sea, day, month, quarter y year. Si indicamos =Calculate([Ventas];PARALLELPERIOD(DimDate[Datekey];-1;year)) los resultados serían idénticos a SamePeriodLastYear y a DateADD(dates;-1;year). Funciones que evalúan expresiones en un período de tiempo El primer subgrupo de fórmulas serían TotalMTD, TotalQTD y TotalYTD. Estas funciones evalúan una expresión, en nuestro ejemplo [Ventas], para el período de fechas mes, trimestre y año. Las fórmulas dan el mismo resultado que el emplear las funciones DatesMTD, DatesQTD y DatesYTD. En definitiva, lo que hacen estas funciones es simplificar el cálculo al eliminar la necesidad de utilizar la función CALCULATE. Por ejemplo, en vez de usar la fórmula =CALCULATE ([Ventas];DateMTD([DimDate[DateKey]) directamente podemos calcularla como =TotalMTD([Ventas];DimDate[DateKey]). El siguiente y último grupo de fórmulas se refieren a la apertura y cierre de Balances. El balance de apertura para cualquier período es lo mismo que el saldo de cierre durante el período anterior. El balance de cierre incluye todos los datos hasta el final del período, mientras que el saldo inicial no incluye ningún dato de dentro del período actual. Estas funciones siempre devuelven el valor de una expresión evaluada para un punto específico en el tiempo. El punto en el tiempo que nos importa es siempre el último valor de fecha posible en un período de calendario.
5
Autor: Juan José Macias Vidal
En la hoja 3.2 tenemos los distintos resultados obtenidos. OpeningBalanceMonth me dará como resultado en el mes actual las ventas del último día del mes anterior. Por ejemplo, para febrero me dará las ventas a 31 de enero. ClosingBalanceMonth me dará como resultado las ventas del último día del mes actual. Para el de febrero me dará las ventas del día 28 de febrero. Si en la etiquetas de filas hemos incluido el campo trimestre, la primera me dará como resultado las ventas del último día del trimestre anterior, y la segunda, las ventas del último día del trimestre actual. Si en la etiquetas de filas hemos incluido el campo años, la primera dará como resultado las ventas del último día del año anterior, y la segunda, las del último día del año actual. OpeningBalanceQuarter nos dará como resultado las ventas del último día del trimestre anterior. Si en las etiquetas de filas hemos incluido el campo años, nos dará para el año, las ventas del último día del último trimestre del año anterior. ClosingOpeningBalance dará las ventas del último día del trimestre actual. Si hemos incluido el año en las etiquetas de fila, dará las ventas del último día del último trimestre del año actual. OpeningBalanceYear nos mostrará como resultados las ventas del último día del año anterior y ClosingBalanceYear las ventas del último día del año actual.
6
Autor: Juan José Macias Vidal