Ordenación de fechas en PowerPivot

El tratamiento de fechas es un aspecto del desarrollo de un sistema de información que puede provocarnos más de un quebradero de cabeza. PowerPivot, como herramienta orientada al análisis de datos, permite, entre todos los tipos de datos de que dispone, el manejo de fechas. En el presente artículo abordaremos el uso de fechas desde PowerPivot, focalizando nuestros esfuerzos en el apartado dedicado a la ordenación de las mismas.

Todo proceso de desarrollo de un sistema de información implica, en mayor o menor grado, el trabajo con elementos temporales. Tomando como ejemplo un sistema Business Intelligence, si observamos los cubos de datos de que está compuesto, nos percataremos de que la mayoría dispone de dimensiones de fecha, destinadas al análisis de la información desde una perspectiva temporal.

PowerPivot, como ya explicamos en un artículo anteriormente publicado en este mismo blog, es una herramienta self-service BI, que puede utilizarse tanto en las fases iniciales del desarrollo de un sistema de inteligencia de negocio, como para realizar un análisis rápido, a partir de un conjunto de resultados obtenidos del propio sistema de información corporativo, o de fuentes externas a éste.

 

Creación de la fuente de datos

Situemos nuestro escenario de trabajo en este último supuesto que acabamos de mencionar: nos entregan una tabla con datos de ventas, perteneciente a una base de datos externa a nuestro sistema de información, sobre la que tenemos que realizar diversos análisis; algunos de ellos implican el uso de uno de los campos de fecha disponibles en la tabla.

Como motor de datos emplearemos SQL Server 2008 R2, mientras que como fuente de datos origen utilizaremos AdventureWorksDW2008, copiando la tabla FactInternetSales a una nueva base de datos con el nombre FechasPowerPivot.

USE master
GO
CREATE DATABASE FechasPowerPivot
GO

USE FechasPowerPivot
GO
SELECT * INTO FactInternetSales FROM AdventureWorksDW2008.dbo.FactInternetSales
GO

 

Aunque acabamos de indicar que los análisis que necesitaremos realizar sobre la tabla FactInternetSales requerirán el uso de un campo de fecha, al observar la estructura de la tabla FactInternetSales, advertiremos que los campos cuyos nombres sugieren que su contenido puede contener tal información (OrderDateKey, DueDateKey y ShipDateKey), son realmente de tipo int, teniendo sus valores un formato que podemos identificar con las diferentes partes de una fecha, ya que los cuatro primeros dígitos representan el año, los dos siguientes el mes, y los dos últimos el día.

El motivo de este diseño de tabla se debe a que en AdventureWorksDW2008, la base de datos origen, estos campos de la tabla FactInternetSales, así como del resto de tablas, se relacionan con el campo DateKey de la tabla DimDate, la cual constituye el catálogo de fechas de la base de datos; donde los campos de cada registro, que representa una fecha, identifican los diferentes elementos de la misma: año, mes, nombre de mes, día de la semana, etc.

Podríamos añadir la tabla DimDate a la base de datos FechasPowerPivot, lo que facilitaría enormemente nuestra labor. Sin embargo, hay ocasiones en las que por determinadas restricciones, sólo disponemos de un único conjunto de resultados al que debemos acomodarnos, así que intentando emular dicho escenario, emplearemos solamente la tabla FactInternetSales.

 

Preparación del modelo de datos en PowerPivot

A continuación iniciaremos Excel, creando un archivo con el nombre OrdenarFechas.xlsx. Seguidamente abriremos la ventana de PowerPivot, donde crearemos un modelo de datos conteniendo la tabla FactInternetSales de la base de datos FechasPowerPivot. El proceso de creación de un modelo de datos se explica aquí.

 

La columna de fecha

Puesto que en el modelo de PowerPivot el tipo de dato de la columna OrderDateKey también es numérico, crearemos, partiendo de dicha columna, una nueva columna calculada cuyo tipo de dato sea fecha.

 

Para ello nos situaremos en la primera columna vacía existente en la ventana de PowerPivot, introduciendo la siguiente expresión DAX en la barra de fórmulas.

=DATE(
  LEFT(FactInternetSales[OrderDateKey],4), 
  MID(FactInternetSales[OrderDateKey],5,2), 
  RIGHT(FactInternetSales[OrderDateKey],2)
)

 

La función DATE devuelve un valor de tipo fecha a partir de los tres números que recibe como parámetro y que corresponden al año, mes y día de la fecha a crear. Estos números son obtenidos de la columna OrderDateKey mediante el uso de las funciones de cadena LEFT, MID y RIGHT. Aunque el tipo de dato de dicha columna es numérico, las mencionadas funciones realizan una conversión de tipo automática a cadena para extraer la subcadena correspondiente en cada caso; siendo transformados   nuevamente en números al ser empleados en la función DATE. De esta forma habremos creado una nueva columna calculada, a la que daremos el nombre FechaPedido editando el título de su cabecera.

 

 

La jerarquía de fecha

El siguiente paso consistirá en crear tres nuevas columnas calculadas, que representen las diferentes partes de las fechas incluidas en la columna FechaPedido. Para ello utilizaremos las funciones de manipulación de fechas incluidas en el lenguaje DAX de PowerPivot que vemos a continuación.

=YEAR([FechaPedido])
=MONTH([FechaPedido])
=DAY([FechaPedido])

 

 

A continuación, en la cinta de opciones de la ventana de PowerPivot, haremos clic en la opción “PivotTable” del grupo “Informes”, creándose de esta manera una tabla dinámica en la ventana de Excel, que nos permitirá analizar la información del modelo de datos de PowerPivot.

Dentro del panel “Lista de campos de PowerPivot” arrastraremos, desde el bloque de selección de campos hasta el bloque “Etiquetas de fila”, los campos que acabamos de crear, formando una jerarquía de fecha de tres niveles (año, mes y día), por la que podremos navegar, expandiendo y replegando sus elementos, para obtener diferentes grados de detalle de los datos, en base a las fechas de venta.

 

 

Obteniendo el nombre del mes

A pesar de ser completamente operativa, nuestra jerarquía adolece de un detalle para ser más funcional: mostrar el mes como nombre en lugar de número.

Solucionaremos este problema desplazándonos a la ventana de PowerPivot, y creando una nueva columna calculada llamada MesNombre, que contenga la expresión =FORMAT([FechaPedido],»mmmm»), en la que como podemos ver, aplicaremos un formato para obtener el nombre del mes de la columna de fecha. Posteriormente sustituiremos, en la tabla dinámica, el campo Mes por MesNombre.

 

 

Observemos, no obstante, que en la tabla dinámica, las etiquetas con los nombres de mes han quedado ordenadas alfabéticamente, en lugar de mantener el orden de fecha, como sería más apropiado.

Para resolver este problema haremos clic en la opción “Ordenar de A a Z” de la cinta de opciones de Excel, perteneciente al grupo “Ordenar y filtrar” de la ficha “Opciones”, que a su vez está contenida en la pestaña de nivel superior “Herramientas de tabla dinámica”. Como resultado, el orden de los nombres de mes cambiará para adaptarse a la disposición del calendario.

 

 

¿Dónde reside la magia que hace posible esta alteración en el comportamiento de la ordenación? La respuesta se halla en las listas personalizadas de Excel, una característica que, como su nombre indica, nos permite la creación de una lista de literales en un orden particular, que no tiene por qué ajustarse al orden predeterminado utilizado por el motor de la hoja de cálculo.

Para acceder a estas listas personalizadas haremos clic en el elemento “Opciones”, perteneciente a la pestaña “Archivo” de la cinta de opciones de Excel. En esta ventana de opciones, dentro del apartado “Avanzadas”, haremos clic en el botón “Modificar listas personalizadas”, que abrirá su ventana de mantenimiento, donde comprobaremos que ya se encuentra presente la lista que acabamos de utilizar con los nombres de meses.

 

 

Para comprobar su funcionamiento, vamos a añadir en la ventana de PowerPivot, una columna calculada que genere los nombres de los meses en un idioma distinto del local, utilizando de forma combinada las funciones IF y MONTH del lenguaje DAX. Llamaremos a esta columna MesNombre2.

=IF(MONTH([FechaPedido])=1, "Janvier",
IF(MONTH([FechaPedido])=2, "Février",
IF(MONTH([FechaPedido])=3, "Mars",
IF(MONTH([FechaPedido])=4, "Avril",
IF(MONTH([FechaPedido])=5, "Mai",
IF(MONTH([FechaPedido])=6, "Juin",
IF(MONTH([FechaPedido])=7, "Juillet",
IF(MONTH([FechaPedido])=8, "Août",
IF(MONTH([FechaPedido])=9, "Septembre",
IF(MONTH([FechaPedido])=10, "Octobre",
IF(MONTH([FechaPedido])=11, "Novembre",
"Décembre")
))))))))))

Si utilizamos esta nueva columna como nombre de mes en la jerarquía de fechas, los nombres se ordenaran alfabéticamente, y en esta ocasión permanecerán así aunque seleccionemos la opción “Ordenar de A a Z”.

Agreguemos pues una nueva lista personalizada a Excel, compuesta por los nombres de la columna MesNombre2, y volvamos a ordenar los meses.

 

 

Comprobaremos que ahora ya sí se ordenan tal y como necesitamos, lo que nos proporciona una idea acerca del funcionamiento de estas listas y su aplicación en tablas dinámicas.

 

 

Las listas personalizadas no funcionan en segmentadores

En el caso de que en la tabla dinámica necesitemos utilizar un segmentador basado en el campo del nombre del mes, volveremos a encontrarnos nuevamente con el problema de la ordenación, ya que los segmentadores no soportan la ordenación en base a las listas personalizadas.

 

 

En esta ocasión podemos solucionar el problema creando en PowerPivot otra columna calculada, que llamaremos MesNumeroNombre, con una cadena de formato que en primer lugar contenga los dígitos del mes, seguidos del nombre: =FORMAT([FechaPedido],»mm – mmmm»).

Al utilizar esta columna como campo de la tabla dinámica en un segmentador, el orden mostrado por sus elementos corresponderá al que se espera de un tipo de dato fecha. Adicionalmente podemos retocar la expresión DAX de esta columna para hacer que la primera letra del nombre del mes se muestre en mayúscula.

=REPLACE(
  FORMAT([FechaPedido],"mm - mmmm"), 
  6, 
  1, 
  UPPER(MID(FORMAT([FechaPedido],"mm - mmmm"),6,1))
)

 

 

SQL Server 2012. La solución

En el caso de que trabajemos con (Denali) SQL Server 2012 (Denali), no será necesario que utilicemos las técnicas relatadas anteriormente, ya que PowerPivot para SQL Server 2012 permite solucionar este problema de una forma mucho más sencilla y elegante, tal y como explicamos a continuación.

Para empezar, volvamos al momento del desarrollo de nuestro ejemplo en el que añadíamos la columna calculada MesNombre con la función FORMAT. Continuando en la ventana de PowerPivot, en su cinta de opciones seleccionaremos, dentro del grupo “Ordenar y filtrar”, la opción “Sort by Column”. En la ventana del mismo nombre elegiremos, en sendos desplegables, la columna que queremos ordenar, MesNombre, y la columna por la que se realizará la ordenación de la primera, Mes.

 

 

Al actualizar estos cambios en la tabla dinámica, el campo MesNombre ya se ordenará correctamente tanto en la propia tabla como en el segmentador empleando únicamente el nombre del mes, lo que nos evita el uso de las técnicas artificiosas explicadas anteriormente.

 

Conclusiones

En la primera versión de PowerPivot, la ordenación de ciertas partes de una fecha, tales como el nombre del mes, precisa de la utilización de ciertos trucos para poder llevarla a cabo correctamente. La próxima aparición de SQL Server 2012, ya disponible en Release Candidate, conlleva aparejada una nueva versión de PowerPivot, que simplifica en gran medida este aspecto, facilitando el trabajo a todos aquellos usuarios de esta potente herramienta de análisis de información.