Vamos a terminar el ejemplo que comenzamos la vez anterior. Preparamos los datos para crear una tabla de Tiempos, como tabla maestra que nos permita analizar los datos y aplicar BI con filtros de tiempo.

Hoy vamos a crear columnas calculadas, relacionar tablas en la ventana PowerPivot, marcar la tabla tipo Date y verlo desde la tabla dinámica… empezamos !!!

Columnas calculadas con DAX

DAX es el lenguaje de fórmulas del que disponemos en PowerPivot. Hoy vamos a utilizar una de las tantas funciones integradas. Vamos a crear una columna calculada utilizando la función Year() de DAX, queremos obtener los años de cada día en la tabla Tiempo.

=YEAR([Fecha])

Lo mismo hacemos para la tabla SalesOrderHeader, donde agregamos la nueva columna AñoOrderDate que muestra los valores de los años y nos sirve para describir el dato, por ejemplo TotalDue.

DAX

Permítanme un comentario antes de continuar con nuestro ejemplo.

Queda todo por ver en cuanto a DAX, especialmente en lo relacionado con Time Intelligence o inteligencia de tiempos, para cumplir con las necesidades de análisis de BI. Estas funciones permiten manipular los datos utilizando períodos de tiempo incluyendo días, meses, trimestres, y años, así como comparar cálculos sobre estos períodos. Nos iremos acercando a DAX en la medida que vayamos mejorando nuestro modelo.

Algunos avances sobre estas funciones tenemos en el blog La biblioteca de PowerPivot, de los amigos de SolidQ.

Tenemos además, documentación sobre funciones DAX en español y también en inglés.

Relaciones entre fechas

Una vez asignada la columna correcta, creamos las relaciones entre esta tabla y las columnas, de tipo Date que encontramos, por ejemplo en SalesOrderHeader, todas las relaciones tiran contra esta tabla de tiempos, como está recomendado por MS.

Desde la vista Diagrama es muy fácil crear las relaciones.

Aquí tenemos las 3 relaciones desde SalesOrderHeader hacia Tiempo, algo que no podíamos en la versión anterior de PowerPivot.

Podemos ver el estado de las relaciones, y ver que tenemos una única relación activa.

Mark as Date Table

Marcamos la tabla Tiempo como tipo Date y lo que hay que hacer es definir cuál es la columna adecuada para identificar la tabla. Tal y como vemos en el mensaje ha de ser una columna con datos tipo Date y con valores únicos. La columna Fecha cumple muy bien estos requerimientos.

Si intentamos asignar una columna que no cumpla con estos requerimientos, PowerPivot devuelve un error.

Filtro de tiempos en tabla dinámica

En la tabla dinámica veamos la diferencia de comportamiento entre una tabla seleccionada como Date y una que no.

Si seleccionamos la columna AñoOrderDate de la tabla SalesOrderHeader e intentamos filtrar las opciones son las siguientes:

Sin embargo, si seleccionamos la columna Año de la tabla Tiempo y realizamos la misma acción, las opciones son las siguientes:

Como vemos hay posibilidad de analizar por rangos de fechas.

Un ejemplo de la utilización de estos filtros lo tenemos en las imágenes siguientes:

Hemos terminado este ejemplo y con ello el recorrido por la ficha Design.

En la próxima entrada vamos a utilizar DAX para crear columnas calculadas aplicando relaciones entre tablas. Y esto lo haremos en el marco de la revisión de la ventana PowerPivot para SQL Server 2012, en este caso trataremos la ficha Advanced.