Recurro muy seguido a este script de SQL para llenar mi tabla de tiempo. Casi siempre lo hago y lo vuelvo a hacer una vez al mes. Todavía no comprendo como no demonios lo guardo en alguna parte…bueno, esperemos que al publicarlo lo tenga en un solo lado.
/* Llena la tabla de tiempo Haciendo cubos: Adrian Ceballos Solo para asegurarnos que esto corre en cualquier servidor independientemente del idioma ademas de especificar en que idioma saldran los nombres de los dias de la semana */ SET LANGUAGE spanish /*Ponemos que el primer dia de la semana 1 es Lunes. Para el Domingo usar 7*/ SET DATEFIRST 1 declare @fi datetime, @ff datetime set @fi = '2004-01-01' Set @ff = '2004-31-12' while @fi < = @ff begin insert into Tiempo (TimeID, Fecha, Anio, MesID, Mes, Cuarto, Semana, NumDiaSemana, DiaSemana) select year(@fi)*10000+month(@fi)*100+day(@fi) as TimeId, @fi as Fecha, year(@fi) as Anio, year(@fi)*100+month(@fi) as MesID, case when Month(@fi)Â <Â 10 then datename(year,@fi)+'-0'+convert(varchar,month(@fi)) else datename(year,@fi)+'-'+convert(varchar,month(@fi)) end as Mes, year(@fi)*10+datepart(q,@fi) as Cuarto, datename(year,@fi)+datename(ww,@fi) as Semana, datepart( dw, @fi) as NumDiaSemana, datename( dw, @fi) as DiaSemana set @fi = @fi + 1 end
Solo hay que darle copy-paste y ¡voilá!…No olviden sustituir los valores fechas para el rango que quieren se genere en la tabla de tiempo. El detalle de las columnas aquí.
El concepto es el mismo para cualquier marca de base de datos pero habrá que traducir el script. El script para crear y llenar la tabla de tiempo puede bajar aquí.
Technorati tags: Data Warehouse, Cubos
Advertisement
Adrian, me parece interesante la dimension tiempo que utilizas … sobre todo el blog que es muy interesante … para los que recien nos iniciamos en esta nueva aventura de BI
, Amigos ,les envio un script para que no se preocupen de actualizar la dimension tiempo constantemente(podria ser en la tarea de carga diaria) 
… Muchos exitos..
slds fabio..
—- DimTiempo —-
– Inserta Nuevos Valores —-
Insert into DimTiempo (IdTiempo, Fecha, Año, MesID, Mes, Cuarto, Semana, NumDiaSemana, DiaSemana)
SELECT TOP 1 year(getdate())*10000+month(getdate())*100+day(getdate()) as TimeId,
getdate() as Fecha,
year(getdate()) as Año,
year(getdate())*100+month(getdate()) as MesID,
case when Month(getdate()) < 10
then datename(year,getdate())+’-0′+convert(varchar,month(getdate()))
else datename(year,getdate())+’-'+convert(varchar,month(getdate()))
end as Mes,
year(getdate())*10+datepart(q,getdate()) as Cuarto,
datename(year,getdate())+datename(ww,getdate()) as Semana,
datepart( dw, getdate()) as NumDiaSemana,
datename( dw, getdate()) as DiaSemana
FROM SARR_DM.dbo.DimTiempo
WHERE year(getdate())*10000+month(getdate())*100+day(getdate()) not in (Select IdTiempo from SARR_DM.dbo.DimTiempo)
Hola Fabio,
no encuentro palabras para agradecerte tu colaboración. Estoy seguro que será de gran utilidad ya que este post es uno de los que más se leen buscando ayuda.
Gracias ! Saludos !
Adrian
Gracias por la buena descripcion de como construir los BI, con relacion a este script, si utilizas SQL puedes ubicarlo como procedimiento, y luego pueden despues programarlo para que se ejecute como una tarea. lo que me gustaria saber que herraenta estas utilizando segun la imagen donde podes hacer un drilldown y si es gratis?
Hola Eduardo,
tienes razon, normalmente lo modificamos un poco para que quede como procedimiento y poder usarlo cada vez quese requiera.
La herramienta que usamos para el drill down es Artus y es de la compañia BITAM = http://www.bitam.com
Saludos
Excelente el que compartas codigo , al menos yo voy comenzando
en esto y me es dificil encontrar respuestas a mis grandisimas dudas..
pero gracias a foros como este y colaboradores como tu seguimos existiendo
Gracias ¡
Hola Adalid,
Mil gracias por tus comentarios. Sientete como en casa
Sadluso,
Adrián
Buenas.
Primero de todo agradecer infinitamente la existencia de ésta página, ya que para todos los que empezamos en esto de los cubos nos viene genial. Gracias Adrian y gracias tb a toda la gente que ayuda con sus post.
Y segundo, dejaros el script de llenado de la tabla tiempo modificado para que funcione en Oracle, por si alguno lo necesitase.
Un saludo
DECLARE
fi DATE := to_date(’01-01-2008′,’DD-MM-YYYY’);
ff DATE := to_date(sysdate,’DD-MM-YYYY’);
BEGIN
WHILE (fi <= ff) LOOP
INSERT INTO Tiempo_DIM (TIME_ID,FECHA, ANIO, MONTH_ID, MES, QUARTER, WEEK, DAY_OF_WEEK_NUM, DAY_OF_WEEK)
SELECT to_number(to_char(fi,’YYYY’))*10000+to_number(to_char(fi,’MM’))*100+to_number(to_char( fi,’DD’)) as TIME_ID,
fi as FECHA,
to_number(to_char(fi,’YYYY’)) as ANIO,
to_number(to_char(fi,’YYYY’))*100+ to_number(to_char(fi,’MM’)) as MONTH_ID,
to_char(fi,’YYYY’) || ‘-’ || to_char(fi,’MM’) as MES,
to_number(to_char(fi,’YYYY’))*10+ to_number(to_char(fi,’Q')) as QUARTER,
to_number(to_char(fi,’YYYY’))+to_number(to_char(fi,’WW’)) as WEEK,
to_number(to_char( fi,’D')) as DAY_OF_WEEK_NUM,
to_char( fi,’DAY’) as DAY_OF_WEEK
FROM DUAL;
fi := fi + 1;
END LOOP;
END;
Vaya, muchos paréntesis se han cambiado por la carita!! ) ))
Solo tenéis que cambiar cara por parénteis.
….vamos a pensar que es un script feliz
Saludos!
Aqui les dejo el script funcionando para MySql
Saludos desde Rosario Argentina
DELIMITER $$
DROP PROCEDURE IF EXISTS `dw_entrepids`.`aa`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `aa`()
BEGIN
set @fi = '2000-01-01',@ff = '2000-12-31';
while (@fi <= @ff) DO
insert into Tiempo (TiempoID, Fecha, Año, MesID, Mes, Cuarto, Semana, NumDiaSemana, DiaSemana)
select year(@fi)*10000+month(@fi)*100+day(@fi) as TimeId,
@fi as Fecha,
year(@fi) as Año,
year(@fi)*100 + month(@fi) as MesID,
case when Month(@fi) < 10
then CONCAT(year(@fi), '-0' , CAST(month(@fi) as char))
else CONCAT(year(@fi),'-',CAST(month(@fi) as char))
end as Mes,
year(@fi)*10+quarter(@fi) as Cuarto,
CONCAT(year(@fi),week(@fi)) as Semana,
DAYOFWEEK(@fi) as NumDiaSemana,
dayname(@fi) as DiaSemana;
set @fi = DATE_ADD(@fi, INTERVAL 1 DAY);
END WHILE;
END$$
DELIMITER ;
Jemarroyo gracias por su codigo, pero no se como usarlo, lo ejecuto con phpmyadmin y me crea la rutina aa pero no me pobla la tabla, tengo que hacer algo mas???? esto es lo que me aparece
Su consulta se ejecutó con éxito
consulta SQL:
DROP PROCEDURE `aa`//# MySQL ha devuelto un valor vacío (i.e., cero columnas). CREATE DEFINER=`root`@`localhost` PROCEDURE `aa`() BEGIN set @fi = ’2010-01-01′,@ff = ’2012-12-31′; while (@fi <= @ff) DO insert into tiempo (TiempoID, Fecha, Año, MesID, Mes, Cuarto, Semana, NumDiaSemana, DiaSemana) select year(@fi)*10000+month(@fi)*100+day(@fi) as TimeId, @fi as Fecha, year(@fi) as Año, year(@fi)*100 + month(@fi) as MesID, case when Month(@fi) < 10 then CONCAT(year(@fi), '-0' , CAST(month(@fi) as char)) else CONCAT(year(@fi),'-',CAST(month(@fi) as char)) end as Mes, year(@fi)*10+quarter(@fi) as Cuarto, CONCAT(year(@fi),week(@fi)) as Semana, DAYOFWEEK(@fi) as NumDiaSemana, dayname(@fi) as DiaSemana; set @fi = DATE_ADD(@fi, INTERVAL 1 DAY); END WHILE; END # MySQL ha devuelto un valor vacío (i.e., cero columnas).
me respondo a mi mismo es necesario hacerlo por consola no se puede desde el phpmyadmin, ya lo probe y perfecto.
Genial!!
Mil gracias! Saludos!
Buenos días Adrian, antes de hacer mi consulta, quiero agradecerte por esta pagina,la cual consulto constantemente y la cual me ha aportado bastante.
Mi pregunta es la siguiente, estamos haciendo el rediseño de nuestra base de datos en la cual tengo una tabla que crece bastante, esta tabla tiene una columna DATETIME, la cual ademas incluye la hora y minutos, me gustaria me aconsejaras si debo manejar las horas y los minutos en la dimension de tiempo o segun tu experiencia que me recomiendas para este caso.
De antemano gracias por tu respuesta.
Hola Sandra,
yo tendría 2 columnas
La de la fecha:
http://haciendocubos.com/2007/07/02/la-dimensin-tiempo/
http://haciendocubos.com/2007/07/03/la-dimensin-tiempo-los-otros-campos/
http://haciendocubos.com/2007/07/18/llenando-la-tabla-de-tiempo/
y la del tiempo:
http://haciendocubos.com/2007/10/16/la-dimensin-minutos/
Claro, dejaria como quiera algun lugar la columna original,
Gracias por la visita y comentarios.
Saludos