Durante el desarrollo de un modelo Bi es indispensable contar con una tabla fecha. Aun en las diferentes teorías de bi la importancia de una tabla que nos congregue día a día las filas con columnas de años, mes, semana, trimestre, etc. a disponibilidad de uso es crítico para un buen desarrollo de tableros o end user bi. Hoy en día, en microsoft particularmente, los modelos tabulares son un lugar indispensable para tener una tabla fecha. Los mismos traen gran capacidad de funciones de fecha para cálculos complejos en dax. Estos cálculos los denominan Time Intelligence. Para no desviarnos más con el tema vamos a ver como y donde puedo crear esta tabla.
SQL Server
Podemos generar un procedimiento almacenado que corra para reconstruirla durante las noches o en cada llamada de la actualización del modelo tabular.
Tengamos en cuenta que el SELECT solo retorna una tabla de una columna con fechas desde hasta que hayamos seteado. Vamos a ver el panorama completo, luego cada quien separará en un procedimiento almacenado o lo usará de consulta directa.
CREATE TABLE dim_date
(
[date] DATE PRIMARY KEY,
[year] AS DATEPART(YEAR, [date]),
[month] AS DATEPART(MONTH, [date]),
[MonthName] AS DATENAME(MONTH, [date]),
[day] AS DATEPART(DAY, [date]),
FirstOfMonth AS CONVERT(DATE, DATEADD(MONTH, DATEDIFF(MONTH, 0, [date]), 0)),
FirstOfYear AS CONVERT(DATE, DATEADD(YEAR, DATEDIFF(YEAR, 0, [date]), 0)),
[week] AS DATEPART(WEEK, [date]),
[ISOweek] AS DATEPART(ISO_WEEK, [date]),
[DayOfWeek] AS DATEPART(WEEKDAY, [date]),
[quarter] AS DATEPART(QUARTER, [date]),
[datekey] AS CONVERT(CHAR(8), [date], 112),
[dmy] AS CONVERT(CHAR(10), [date], 101)
);
Procedimiento:
TRUNCATE TABLE dim_date
SET DATEFORMAT dmy;
SET LANGUAGE Spanish;DECLARE @StartDate DATE = '20000101'
DECLARE @CutoffDate DATE = GETDATE()+1 INSERT #dim([date])
SELECT d
FROM
(
SELECT d = DATEADD(DAY, rn - 1, @StartDate)
FROM
(
SELECT TOP (DATEDIFF(DAY, @StartDate, @CutoffDate))
rn = ROW_NUMBER() OVER (ORDER BY s1.[object_id])
FROM sys.all_objects AS s1
CROSS JOIN sys.all_objects AS s2
-- on my system this would support > 5 million days
ORDER BY s1.[object_id]
) AS x
) AS y;SELECT * FROM dim_date
Power Query
No siempre tendremos la posibilidad de nutrirnos de un acceso de escritura a una base de datos para poder armar la tabla fecha de la primera forma mencionada, sino que a veces vamos a tener que terminar el data modeling incorporando la fecha en la etapa de capa semántica junto al modelo tabular. Éste método es el más usado en mi opinión si construimos Bi como servicio a entidades. Lo más atractivo es tener una tabla fecha resulta antes de cargar y aplicar el modelo tabular.
Tengamos en cuenta que la fecha de inicio esta seteada a mano hasta fecha actual igual que en SQL.
= let
//Set start date
StartDate = #date(2010, 1, 1),
EndDate = DateTime.Date(DateTime.LocalNow()),
//Get the number of dates that will be required for the table
GetDateCount = Duration.Days(EndDate - StartDate),
//Take the count of dates and turn it into a list of dates
GetDateList = List.Dates(StartDate, GetDateCount,
#duration(1,0,0,0)),
//Convert the list into a table
DateListToTable = Table.FromList(GetDateList,
Splitter.SplitByNothing(), {"Fecha"}, null, ExtraValues.Error),
// Convert to Date
ConvertToDate = Table.TransformColumns(DateListToTable,{{"Fecha", Date.From, type date}}),
// Date Key
DateKey = Table.AddColumn(ConvertToDate, "IdFecha", each Number.FromText(Text.From(Date.Year([Fecha])) & Date.ToText([Fecha],"MM") & Date.ToText([Fecha],"dd") ) , Int32.Type ),
//Add Year Column
YearNumber = Table.AddColumn(DateKey, "Año",
each Date.Year([Fecha]), Int32.Type),
//Add Quarter format QX
Quarter = Table.AddColumn(YearNumber, "Trimestre",
each Text.Combine({"Q", Text.From(Date.QuarterOfYear([Fecha]))}), type text),
//Add Month Number Column
MonthNumber = Table.AddColumn(Quarter , "IdMes",
each Date.Month([Fecha]), Int32.Type),
//Add Month Name Column
MonthName = Table.AddColumn(MonthNumber , "Mes",
each Date.ToText([Fecha],"MMMM", "es-es"), type text),
//Add Period
Periodo = Table.AddColumn(MonthName , "Periodo",
each Text.From([Año]) & Text.PadStart(Text.From([IdMes]),2,"0"), type text),
//Add YearQ
TrimestreAño = Table.AddColumn(Periodo, "TrimestreAño",
each Text.Combine({Text.From([Año]), [Trimestre]}), type text ),
//Add Day Number Column
DayNumber = Table.AddColumn(TrimestreAño , "Dia",
each Date.Day([Fecha]), Int32.Type),
//Add Day Name Column
DayName = Table.AddColumn(DayNumber , "Nombre Dia",
each Date.ToText([Fecha], "dddd"), type text)
in
DayName
DAX
Al igual que el paso anterior las opciones de modelado puede acotarse más según el desarrollo y los permisos que nos estén concediendo. Entonces no debemos dejar de lado la posibilidad de construir una tabla fecha una vez ya existente el modelo tabular. La principal ventaja de éste método es construir la tabla fecha a partir de fechas del mismo modelo creado y no necesariamente predefinidas de la forma (hoy y últimos N días/meses/años). De esta forma puedo crearla a partir de la mínima y máxima fecha de mi tabla de hecho.
En caso de dax podemos nutrir la fecha a partir de nuestra tabla de hecho o seteada a mano con la función DATE() y actual con NOW()
Date =
VAR MinYear = YEAR ( MIN ( TablaHecho[Fecha] ) )
VAR MaxYear = YEAR ( MAX ( TablaHecho[Fecha] ) )
RETURN
ADDCOLUMNS (
FILTER (
CALENDARAUTO( );
AND ( YEAR ( [Date] ) >= MinYear; YEAR ( [Date] ) <= MaxYear )
);
"Year Number"; YEAR ( [Date] );
"Month Name"; FORMAT ( [Date]; "mmmm" );
"Month Number"; MONTH ( [Date] );
"Day"; Day ( [Date] )
)
Para este tipo de casos yo soy de la frase "Mientras más atrás se resuelva, mejor". Si tenemos la posibilidad de construirlo en el origen de datos porque existe un data warehouse o tiene la posibilidad mejor. Sino intentaría siempre llevarlo por Power Query y de última instancia en DAX. No deberíamos construir modelos, ni aunque sean pequeños Power Bi de pequeñas necesidades, sin la tabla fecha. Ustedes, ¿usan tabla fecha?