En mi experiencia, todos los datawarehouse que he diseñado incluyen la dimensión de tiempo. El análisis por fechas es básico en la mayoría de organizaciones que analizan sus datos
La dimensión tiempo permite establecer los niveles de análisis temporal y las jerarquías de tiempo para la agregación de los hechos. Los niveles y jerarquías pueden variar según las necesidades de análisis y la granularidad de las métricas, pero casi siempre son los mismos: Semanas, meses, años; trimestres y semestres
Al construir esta dimensión, se puede enriquecer las posibilidades de análisis ofreciendo diferentes valores a cada atributo de la fecha. Así podemos analizar en base al número de mes o el número de semana. O podemos agrupar usando el nombre del día de la semana o el nombre corto del mes. O podemos contar con el nombre del mes en varios idiomas si el diseño es para una corporación internacional
Bueno, aquí les compartimos una función de valor de tabla, de tipo multi-sentencia, que calcula varios atributos de una fecha, para poblar la dimensión tiempo de un datawarehouse. Hemos preferido utilizar una función porque se puede invocar mediante la sentencia SELECT, de modo que podemos listar solo las columnas que nos resulten útiles; además podemos aplicar un filtro con la sentencia WHERE si queremos listar solo ciertos registros que cumplan con alguna condición
Por diseño, a esta función se le pasa como parámetro el año del cual queremos obtener las fechas, y mediante una Expresión Común de tabla o CTE, del tipo recursivo, barremos las fechas desde un valor inicial (1 de enero) a un valor final (31 de diciembre)
Ojo: este script no crea la tabla de dimensión. Asumimos que la tabla ya existe y que vamos a usar la función para llenar dicha tabla
Pues aquí va:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 |
/* PROPOSITO | Generar una secuancia de fechas en base a una fecha inicial y una final de un Año | Retorna la lista de fechas diarias y el nombre del día de la semana ----------------------------------------------------------------------------------------------- PARAMETROS DE ENTRADA| @Año - Año del cual se desea generar las fechas ----------------------------------------------------------------------------------------------- CREADO POR | Alberto De Rossi - http://dblearner.com FECHA CREACION | 30/01/2012 ----------------------------------------------------------------------------------------------- PRUEBA SET LANGUAGE SPANISH; SELECT * FROM dbo.SecuenciaFechas('2017'); */ ALTER FUNCTION [dbo].[SecuenciaFechas] (@Año Char(4)) RETURNS @SecuenciaFechas TABLE ( Fecha date, Año smallint, Mes tinyint, Dia tinyint, DiaDelAño smallint, SemanaDelAño tinyint, Trimestre tinyint, Semestre tinyint, NombreMes varchar(10), NombreMesCorto char(3), NombreDia varchar(10), NombreDiaCorto char(3)) AS BEGIN --Declara variables de fecha de inicio y fin de la secuencia DECLARE @FechaDesde DATE = @Año + '0101'; DECLARE @FechaHasta DATE = @Año + '1231'; --CTE recursivo para generar las fechas WITH SecuenciaFechas(Fecha, Dia) AS (SELECT @FechaDesde, DATEPART(dw,@FechaDesde) as Fecha UNION ALL SELECT DATEADD(DAY, 1, Fecha), DATEPART(dw, DATEADD(DAY, 1, Fecha)) FROM SecuenciaFechas WHERE Fecha < @FechaHasta) --Consulta final en base al CTE para obtener la secuencia INSERT INTO @SecuenciaFechas ( Fecha, Año, Mes, Dia, DiaDelAño, SemanaDelAño, Trimestre, Semestre, NombreMes, NombreMesCorto, NombreDia, NombreDiaCorto) SELECT Fecha, YEAR(Fecha) AS año, MONTH(Fecha) AS Mes, DAY(Fecha) AS Dia, DATENAME(dayofyear, Fecha) AS DiaDelAño, DATENAME(week, Fecha) AS SemanaDelAño, DATENAME(quarter, Fecha) AS Trimestre, CAST(ROUND(DATENAME(quarter, Fecha)/2.0,0) AS tinyint) AS Semestre, DATENAME(month, Fecha) AS NombreMes, UPPER(LEFT(DATENAME(month, Fecha),3)) AS NombreMesCorto, DATENAME(WEEKDAY, Fecha) AS NombreDia, UPPER(LEFT(DATENAME(WEEKDAY, Fecha),3)) As NombreDiaCorto FROM SecuenciaFechas OPTION (MAXRECURSION 366) RETURN END; GO |
Prestar atención al CTE Recursivo, y a la opción MAXRECURSION. Por defecto SQL Server permite hasta 100 niveles de recursividad. Pero como aquí estamos calculando valores para todo el año, se le asigna esta opción con el valor 366, para calcular todos los días, incluyendo los años bisiestos
Para obtener las fechas del año 2017, ejecutamos una consulta a la función, de la siguiente manera:
|
1 2 3 |
SET LANGUAGE SPANISH; SELECT * FROM dbo.SecuenciaFechas('2017'); GO |
Con SET LANGUAGE nos aseguramos que los textos aparezcan en castellano. El resultado parcial es este:
Esperamos que les sea útil en la implementación de sus proyectos de BI o cualquier otro que necesite atributos de fecha
Hasta la próxima