Tratamiento de fechas desconocidas en PowerPivot y DAX

La práctica totalidad de los modelos de datos que desarrollamos en PowerPivot necesitan en algún momento el empleo, tanto en las tablas de datos como de búsqueda, de columnas de tipo fecha, que nos permitan analizar la información aplicando filtros basados en rangos temporales.

Sin embargo no todas las fuentes de datos utilizadas en un modelo proporcionan las fechas cumplimentadas como sería deseable. En muchos casos se produce una carencia de valor en las columnas de fecha que causan inconsistencias entre las tablas relacionadas. Esto nos conduce a la necesidad de aplicar operaciones de corrección durante el desarrollo del modelo, encaminadas a conseguir una adecuada coherencia en la información ofrecida por el sistema.

 

El desarrollo de un escenario de ejemplo

Como ejemplo ilustrativo de esta situación crearemos una base de datos con el nombre FechasPrueba y dos tablas: VentasInternet y Fechas, que representarán respectivamente a la tabla de datos (Data Table) y búsqueda (Lookup Table) del modelo. En primer lugar cargaremos la tabla VentasInternet a partir de la información existente en las tablas DimTime y FactInternetSales de la base de datos AdventureWorksDW.

 

CREATE DATABASE FechasPrueba
 
CREATE TABLE VentasInternet(
NumeroPedido int NULL,
NumeroPedidoLinea tinyint NULL,
FechaPedido datetime NULL,
ImporteVenta money NULL
);
 
CREATE TABLE Fechas(
Fecha datetime NULL,
Anualidad smallint NULL,
Mes tinyint NULL,
Dia tinyint NULL,
);
 
INSERT INTO VentasInternet (
NumeroPedido,
NumeroPedidoLinea,
FechaPedido,
ImporteVenta
)
SELECT
CONVERT(int,RIGHT(fis.SalesOrderNumber,5)),
fis.SalesOrderLineNumber,
dt.FullDateAlternateKey,
fis.SalesAmount
FROM AdventureWorksDW.dbo.FactInternetSales AS fis
LEFT JOIN AdventureWorksDW.dbo.DimTime AS dt
ON fis.OrderDateKey = dt.TimeKey;

 

Para cargar la tabla Fechas emplearemos una consulta que devuelva los distintos valores de la columna FechaPedido, correspondiente a la tabla VentasInternet.

 

INSERT INTO Fechas (
Fecha,
Anualidad,
Mes,
Dia
)
SELECT DISTINCT
FechaPedido,
YEAR(FechaPedido),
MONTH(FechaPedido),
DAY(FechaPedido)
FROM VentasInternet;

 

 

Creación del modelo de datos en PowerPivot

A continuación ya podemos crear en Excel un modelo de datos de PowerPivot, donde relacionaremos los campos de fecha de ambas tablas.

 

Seguidamente añadiremos un campo calculado (medida) con el recuento de las filas de la tabla VentasInternet mediante la siguiente expresión DAX.

 

NumFilas:=COUNTROWS(VentasInternet)

 

 

Para analizar los resultados del trabajo desarrollado hasta el momento, seleccionaremos en la ventana de PowerPivot la opción Tabla dinámica del menú Inicio, arrastrando el campo Anualidad de la tabla Fechas hasta el área Filas.

 

Cuidado con las medidas predeterminadas

Observemos ahora un detalle importante: si en lugar de arrastrar y soltar el campo Anualidad en el área Filas, simplemente hubiéramos marcado la casilla que hay junto al mismo, el resultado sería muy distinto.

 

En lugar de mostrarnos cada año de venta con su número de filas correspondiente, la tabla dinámica ha sumado los valores del campo Anualidad de la tabla Fechas. Esto es debido a que Anualidad es un campo de tipo numérico, y al ser seleccionado de esa forma en la tabla dinámica, el comportamiento por defecto dicta que se genere una medida predeterminada (con la operación de suma como agregado) en vez de mostrar sus distintos valores como literales, que es lo que necesitamos.

Como ya hemos visto anteriormente, si arrastramos el campo Anualidad al área correspondiente (Filas o Columnas) evitamos la operación de suma por defecto, pero en un campo de estas características, cuya finalidad será utilizarse como literal, quizá sería interesante evitar efectos no deseados como este que acabamos de describir.

Por ello, vamos a retroceder algunos pasos hasta el momento de la creación de la tabla Fechas, pero en esta ocasión vamos a crear dicha tabla añadiendo algunas columnas que representarán las versiones en modo textual de cada partícula de fecha así como de la propia fecha. También volveremos a cargar dicha tabla a partir de las fechas existentes en VentasInternet.

 

CREATE TABLE Fechas(
FechaID datetime NULL,
Fecha varchar(20) NULL,
AnualidadID smallint NULL,
Anualidad varchar(20) NULL,
MesID tinyint NULL,
MesNombre varchar(15) NULL,
DiaID tinyint NULL,
Dia varchar(20) NULL
);
 
INSERT INTO Fechas (
FechaID,
Fecha,
AnualidadID,
Anualidad,
MesID,
MesNombre,
DiaID,
Dia
)
SELECT DISTINCT
FechaPedido,
FORMAT(FechaPedido,'dd/MM/yyyy'),
YEAR(FechaPedido),
FORMAT(FechaPedido,'yyyy'),
MONTH(FechaPedido),
DATENAME(month,FechaPedido),
DAY(FechaPedido),
FORMAT(FechaPedido,'dd')
FROM VentasInternet;

 

 

Volviendo a nuestro modelo de datos en PowerPivot, debido a la relevancia de los cambios realizados, eliminaremos del modelo la actual tabla Fechas y volveremos a importar la nueva versión de esta tabla, creando otra vez la relación entre las dos tablas del modelo. En esta ocasión, el campo de la tabla Fechas a relacionar será FechaID. La selección ahora en la tabla dinámica del campo Anualidad (tanto al marcar su casilla como al arrastrar el campo) tendrá el comportamiento que esperamos, visualizando en las filas los años de venta.

 

Ocultando columnas del modelo al usuario

Para evitar posibles confusiones a la hora de trabajar con el modelo, una práctica recomendable reside en ocultar al usuario aquellas columnas con las que no necesite interaccionar. El procedimiento para llevar a cabo esta operación consiste en hacer clic derecho en el título de una columna, eligiendo la opción Ocultar en herramientas cliente. A partir de ese momento, desde la tabla dinámica, dicha columna ya no aparecerá entre las disponibles del panel Campos de tabla dinámica.

 

Dentro del ejemplo que estamos desarrollando, en lo que respecta a la tabla de datos, ocultaríamos todas las columnas excepto el campo calculado NumFilas; mientras que en la tabla de búsqueda, serían la columna de tipo fecha y las de tipo numérico que representan por separado a cada una de las partículas de la fecha. Estas columnas ocultas se aprecian visualmente en el modelo mostrándose con un tono grisáceo.

 

El panel Campos de tabla dinámica se mostrará ahora más despejado de elementos, y lo que resulta muy interesante: la tabla VentasInternet mostrará el icono que la identifica como tabla de medidas (o campos calculados), al haber dejado solamente accesible al usuario la medida NumFilas. Esta es una práctica recomendable, dado que uno de los principales propósitos a los que debería destinarse este tipo de tabla es como contenedor de medidas del modelo.

 

Ordenación de datos en el modelo

De modo adicional, también resulta aconsejable ordenar, en función de nuestros requerimientos, aquellas columnas que hemos dejado visibles a las herramientas cliente, en el caso de que tras utilizarlas comprobemos que no se muestran en el orden previsto.

Un ejemplo lo representaría la columna MesNombre, que por defecto se muestra ordenada alfabéticamente.

 

Pero como es lógico, necesitamos que los nombres de mes se muestren según el orden natural del calendario. Esto lo conseguiremos seleccionando en el menú Inicio la opción Ordenar por columna, y dentro de la ventana del mismo nombre, que se abrirá a continuación, seleccionaremos en la lista desplegable Ordenar la columna MesNombre; y en la lista Por la columna MesID.

 

De esta manera los nombres aparecerán ahora ordenados por el número de mes.

 

Para el resto de columnas de la tabla Fechas que estén en la misma situación ordenaremos según vemos en la siguiente tabla.

Columna para ordenar	Columna de ordenación
Fecha	FechaID
Anualidad	AnualidadID
Dia	DiaID

 

Buscando huecos de datos (Data Gaps) en el modelo

Si pensamos utilizar dentro de nuestro modelo de datos las funciones de inteligencia temporal incluidas en el lenguaje DAX, deberíamos asegurarnos de que todas las fechas son consecutivas, no existiendo “huecos” desde la primera hasta la última.

Al realizar un análisis somero de los valores de la tabla Fechas, puede parecer que todo está correcto, y que todas las fechas son correlativas, pero es recomendable asegurarse realizando una comprobación mediante algún medio que nos permita identificar huecos de datos (Data Gaps) en los valores de una tabla.

La técnica que proponemos a continuación pertenece al libro Microsoft SQL Server 2012 High-Performance T-SQL Using Window Functions, escrito por Itziq Ben-Gan, y una vez adaptada a nuestro caso, demuestra cómo podemos detectar la falta de fechas utilizando la función LEAD de Transact-SQL.

LEAD es una función con una finalidad eminentemente analítica, que permite, desde una fila de un conjunto de resultados, obtener un valor perteneciente a la siguiente fila del mismo conjunto. Veamos un sencillo ejemplo sobre nuestra tabla Fechas.

 

SELECT 
FechaID AS FechaActual,
LEAD(FechaID) OVER(ORDER BY FechaID) AS FechaSiguiente
FROM Fechas
ORDER BY FechaActual;

 

 

 

Basándonos en esta sentencia, construimos una nueva en la que se calcule la diferencia en días entre la fecha actual y la siguiente, y gracias a este pequeño truco descubrimos que hay tres fechas inexistentes en la secuencia de la tabla Fechas.

 

WITH
cteCalcularHuecos AS
(
    SELECT
    FechaID AS FechaActual,
    LEAD(FechaID) OVER(ORDER BY FechaID) AS FechaSiguiente
    FROM Fechas
)
SELECT
DATEADD(day,1,FechaActual) AS FechaNoExistente
FROM cteCalcularHuecos
WHERE DATEDIFF(day,FechaActual,FechaSiguiente) > 1;

 

 

 

Este problema revela que la técnica utilizada para cargar la tabla Fechas no ha resultado todo lo efectiva que podía esperarse, por lo que tendremos que recurrir a un modo diferente de rellenar dicha tabla.

 

Utilización de una tabla numérica auxiliar para rellenar la tabla de búsqueda

La estrategia a la que hacemos mención consiste en utilizar una tabla numérica auxiliar (Tally Table) como soporte en la inserción de valores en la tabla fechas.

El concepto de tabla numérica (ver artículos 1 y 2 en este mismo blog) es extremadamente simple, residiendo su potencia precisamente en dicha sencillez. Se trata de una tabla que contiene una única columna numérica con valores secuenciales, en una cantidad de filas a determinar según nuestros requerimientos.

Aplicada en un contexto como el que nos ocupa actualmente, evita la necesidad de utilizar un bucle para rellenar la tabla Fechas con un rango de valores consecutivo, lo que supone una importante optimización de los recursos que debe emplear el motor de datos en la realización de esta tarea.

Crearemos por tanto una tabla con el nombre AUXNumeros, que contenga alrededor de dos mil registros, cantidad suficiente para nuestro ejemplo, y a continuación escribiremos una sentencia, en la que combinando dos expresiones de tabla, calcularemos la fecha mínima y máxima en las que se han realizado ventas, así como el número de días transcurridos entre ambas, cifra que emplearemos para generar la secuencia de fechas.

 

WITH
cteCalculoDias AS
(
    SELECT 
    DATEDIFF(
        day,
        (SELECT MIN(FechaPedido) FROM VentasInternet),
        (SELECT MAX(FechaPedido) FROM VentasInternet)
    ) AS NumDias
)
,cteFechas AS
(
    SELECT
    Numero,
    DATEADD(day,Numero,(SELECT MIN(FechaPedido) FROM VentasInternet)) as Fecha
    FROM AUXNumeros
    WHERE Numero BETWEEN 0 AND (SELECT NumDias FROM cteCalculoDias)
)
INSERT INTO Fechas (
FechaID,
Fecha,
AnualidadID,
Anualidad,
MesID,
MesNombre,
DiaID,
Dia
)
SELECT
Fecha,
FORMAT(Fecha,'dd/MM/yyyy'),
YEAR(Fecha),
FORMAT(Fecha,'yyyy'),
MONTH(Fecha),
DATENAME(month,Fecha),
DAY(Fecha),
FORMAT(Fecha,'dd')
FROM cteFechas;

 

Asignación de valores nulos a la tabla de datos

Como hemos mencionado anteriormente, el escenario que estamos preparando con el problema a resolver, consiste en la existencia de una serie de filas sin valor en las columnas de fecha de la tabla de datos del modelo. Para reproducir esta situación, asignaremos en la tabla VentasInternet el valor NULL a la columna FechaPedido de forma aleatoria sobre aproximadamente el 0,5 % de los registros, utilizando una técnica mostrada en MSSQLTips (los resultados obtenidos por el lector pueden variar ligeramente).

 

WITH cteVentasAleatorio AS 
(
    SELECT
    DISTINCT NumeroPedido
    FROM VentasInternet
    WHERE 0.005 >= CAST(CHECKSUM(NEWID(),NumeroPedido) & 0x7fffffff AS float)
    / CAST (0x7fffffff AS int)
)
UPDATE VentasInternet
SET FechaPedido = NULL
WHERE NumeroPedido IN (
    SELECT NumeroPedido
    FROM cteVentasAleatorio
);

 

A continuación actualizaremos los datos en el modelo de PowerPivot, y situaremos el campo Anualidad en las filas de la tabla dinámica, donde comprobaremos que los registros de la tabla VentasInternet que no encuentran correspondencia en la tabla Fechas se clasifican automáticamente con la etiqueta “(en blanco)”.

 

Sin embargo, en lugar de esa etiqueta automática podría resultar más conveniente mostrar un texto personalizado con un literal similar a “Desconocido”, “Sin fecha”, etc.

 

Personalizar la etiqueta de valores no encontrados

Una posible solución a este problema consistiría en asignar en ambas tablas una fecha que, previo acuerdo con los usuarios, se considerara la fecha desconocida en el sistema; por ejemplo, la fecha siguiente a la más reciente que exista en la tabla VentasInternet. Procedamos en primer lugar con dicha tabla.

 

UPDATE VentasInternet
SET FechaPedido = (
    SELECT DATEADD(day,1,MAX(FechaPedido))
    FROM VentasInternet
)
WHERE FechaPedido IS NULL;

 

Y continuemos con la tabla de búsqueda, pero con la particularidad en este caso de que al tratarse de la fecha desconocida, en las columnas de literales asignaremos la cadena que utilizaremos para clasificar este tipo de fecha (Desconocida, Desconocido).

 

WITH cteFechaMax AS
(
    SELECT MAX(FechaPedido) AS FechaMax
    FROM VentasInternet
)
INSERT INTO Fechas (FechaID,Fecha,AnualidadID,Anualidad,MesID,MesNombre,DiaID,Dia)
SELECT
FechaMax,
'Desconocida',
YEAR(FechaMax),
'Desconocido',
MONTH(FechaMax),
'Desconocido',
DAY(FechaMax),
'Desconocido'
FROM cteFechaMax;

 

Tras una nueva actualización del modelo, comprobaremos cómo la tabla dinámica ahora etiqueta con nuestro literal personalizado la cantidad de filas que hemos marcado como desconocidas en la fuente de datos. Sin embargo, la ordenación sigue sin funcionar correctamente, ya que no sitúa al final los datos desconocidos.

 

Puesto que en la tabla Fechas del modelo estamos ordenando las diferentes partículas de fecha por las columnas numéricas que las representan, vamos a modificar el modo de añadir a esta tabla el registro con la fecha desconocida, de manera que en los campos utilizados para la ordenación situaremos un valor que obligue a colocar al final cada partícula. Esto también será de utilidad si creamos una jerarquía para la tabla (previamente borraremos el registro actual con la fecha desconocida).

 

DELETE FROM Fechas 
WHERE FechaID=(SELECT MAX(FechaPedido) FROM VentasInternet);
 
INSERT INTO Fechas (FechaID,Fecha,AnualidadID,Anualidad,MesID,MesNombre,DiaID,Dia)
SELECT
(SELECT MAX(FechaPedido) FROM VentasInternet),
'Desconocida',
9999,
'Desconocido',
99,
'Desconocido',
99,
'Desconocido';

 

Actualizando nuevamente los datos del modelo, tendremos las fechas desconocidas situadas al final.

 

Al crear una jerarquía en esta tabla basada en los campos de literales, el comportamiento en lo que respecta a la ordenación funcionará igualmente.

 

Una vez llegados a este punto damos por concluido el presente artículo, en el que hemos abordado algunas técnicas para el tratamiento de las fechas desconocidas que podamos encontrar durante el desarrollo de un modelo de datos en PowerPivot.