Como Construir la dimension tiempo en SQL SERVER
Hola,
Tablas y vistas:
-- ---
-- Table 'Dia'
--
-- ---
DROP TABLE Mercurio_Datawarehouse.dbo.TDWH_DIA ;
CREATE TABLE Mercurio_Datawarehouse.dbo.TDWH_DIA (
NMSEC_DIA DATE PRIMARY KEY,
DIA_MES INTEGER,
FECHA_INT INTEGER,
NMSEC_MES INTEGER
);
Create View Dbo.VTDWH_DIA
(NMSEC_DIA,DIA_MES,NMSEC_MES, FECHA_INT) AS
Select NMSEC_DIA,DIA_MES,NMSEC_MES,FECHA_INT
From Mercurio_Datawarehouse.Dbo.TDWH_DIA with(nolock)
;
-- ---
-- Table 'Mes'
--
-- ---
DROP TABLE Mercurio_Datawarehouse.Dbo.TDWH_MES;
CREATE TABLE Mercurio_Datawarehouse.Dbo.TDWH_MES (
NMSEC_MES INTEGER PRIMARY KEY,
DSNOMBRE_MES VARCHAR(20),
NMSEC_ANO INTEGER
);
Create View Dbo.VTDWH_MES
(NMSEC_MES,DSNOMBRE_MES,NMSEC_ANO) AS
Select NMSEC_MES,DSNOMBRE_MES,NMSEC_ANO
From Mercurio_Datawarehouse.Dbo.TDWH_MES with(nolock)
;
-- ---
-- Table 'Ano'
--
-- ---
DROP TABLE Mercurio_Datawarehouse.Dbo.TDWH_ANO;
CREATE TABLE Mercurio_Datawarehouse.Dbo.TDWH_ANO (
NMSEC_ANO INTEGER PRIMARY KEY,
);
Create View Dbo.VTDWH_ANO
(NMSEC_ANO) AS
Select NMSEC_ANO
From Mercurio_Datawarehouse.Dbo.TDWH_ANO with(nolock)
;
Proceso para llenar el modelo:
USE [Bodega_Datos]
GO
/****** Object: StoredProcedure [dbo].[PCN_LLENAR_TIEMPO] Script Date: 03/29/2012 22:26:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: DIEGO APARICIO
-- Create date: 2012/03/12
-- Description: Para el llenado de tiempo
-- =============================================
ALTER PROCEDURE [dbo].[PCN_LLENAR_TIEMPO]
@FEINICIO NUMERIC(8)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
--SE CREA UNA TABLA TEMPORAL PARA CALCULAR TIEMPO
CREATE TABLE #DIM_TIEMPO(
NMSEC_DIA DATE,
DIA_MES INTEGER,
FECHA_INT INTEGER,
NMSEC_MES INTEGER,
DSNOMBRE_MES VARCHAR(20),
NMSEC_ANO INTEGER
)
--VARIABLES
DECLARE @DIAS NUMERIC(4)
DECLARE @CONT NUMERIC(4)
DECLARE @FEAUX DATETIME
SET @DIAS = 500
SET @CONT = 0
SET @FEAUX = (SELECT CASE
WHEN MAX(NMSEC_DIA) IS NULL THEN
CAST('20110101' AS DATETIME)
ELSE
MAX(NMSEC_DIA)
END
FROM Bodega_Datos.DBO.TDWH_DIA)
WHILE @CONT < @DIAS
BEGIN
/*TDWH_DIA*/
INSERT INTO #DIM_TIEMPO
--INSERT INTO Bodega_Datos.dbo.DIM_TIEMPO
(NMSEC_DIA,DIA_MES,FECHA_INT,NMSEC_MES,DSNOMBRE_MES,NMSEC_ANO)
SELECT Cast( Cast( (year(@FEAUX)*10000+month(@FEAUX)*100+day(@FEAUX)) as varchar(10)) as DATE) NMSEC_DIA,
year(@FEAUX)*10000+month(@FEAUX)*100+day(@FEAUX) as FECHA_INT,
DAY(@FEAUX) as DIA_MES,
year(@FEAUX)*100+month(@FEAUX) as NMSEC_MES,
(DATENAME(MONTH,Cast( Cast( (year(@FEAUX)*10000+month(@FEAUX)*100+day(@FEAUX)) as varchar(10)) as DATE) ) ) + ' de ' + CAST(year(@FEAUX) AS VARCHAR(4)) DSNOMBRE_MES,
year(@FEAUX) as NMSEC_ANO
SET @CONT = ( @CONT + 1)
SET @FEAUX = (SELECT @FEAUX + 1)
END
INSERT INTO Bodega_Datos.dbo.TDWH_DIA
(NMSEC_DIA,FECHA_INT,DIA_MES,NMSEC_MES)
SELECT DISTINCT NMSEC_DIA, FECHA_INT, DIA_MES, NMSEC_MES
FROM #DIM_TIEMPO
INSERT INTO Bodega_Datos.Dbo.TDWH_MES
(NMSEC_MES,DSNOMBRE_MES, NMSEC_ANO)
SELECT DISTINCT NMSEC_MES,DSNOMBRE_MES, NMSEC_ANO
FROM #DIM_TIEMPO
INSERT INTO Bodega_Datos.Dbo.TDWH_ANO
(NMSEC_ANO)
SELECT DISTINCT NMSEC_ANO
FROM #DIM_TIEMPO
DROP TABLE #DIM_TIEMPO
END
Quería traer a consideración una forma de crear la dimensión de tiempo, en una topología en copo de nieve. Si quieren modificar algo o ven algún error les agradezco que me comuniquen el error y en la mayor brevedad lo podré organizar:
Tablas y vistas:
-- ---
-- Table 'Dia'
--
-- ---
DROP TABLE Mercurio_Datawarehouse.dbo.TDWH_DIA ;
CREATE TABLE Mercurio_Datawarehouse.dbo.TDWH_DIA (
NMSEC_DIA DATE PRIMARY KEY,
DIA_MES INTEGER,
FECHA_INT INTEGER,
NMSEC_MES INTEGER
);
Create View Dbo.VTDWH_DIA
(NMSEC_DIA,DIA_MES,NMSEC_MES, FECHA_INT) AS
Select NMSEC_DIA,DIA_MES,NMSEC_MES,FECHA_INT
From Mercurio_Datawarehouse.Dbo.TDWH_DIA with(nolock)
;
-- ---
-- Table 'Mes'
--
-- ---
DROP TABLE Mercurio_Datawarehouse.Dbo.TDWH_MES;
CREATE TABLE Mercurio_Datawarehouse.Dbo.TDWH_MES (
NMSEC_MES INTEGER PRIMARY KEY,
DSNOMBRE_MES VARCHAR(20),
NMSEC_ANO INTEGER
);
Create View Dbo.VTDWH_MES
(NMSEC_MES,DSNOMBRE_MES,NMSEC_ANO) AS
Select NMSEC_MES,DSNOMBRE_MES,NMSEC_ANO
From Mercurio_Datawarehouse.Dbo.TDWH_MES with(nolock)
;
-- ---
-- Table 'Ano'
--
-- ---
DROP TABLE Mercurio_Datawarehouse.Dbo.TDWH_ANO;
CREATE TABLE Mercurio_Datawarehouse.Dbo.TDWH_ANO (
NMSEC_ANO INTEGER PRIMARY KEY,
);
Create View Dbo.VTDWH_ANO
(NMSEC_ANO) AS
Select NMSEC_ANO
From Mercurio_Datawarehouse.Dbo.TDWH_ANO with(nolock)
;
Proceso para llenar el modelo:
USE [Bodega_Datos]
GO
/****** Object: StoredProcedure [dbo].[PCN_LLENAR_TIEMPO] Script Date: 03/29/2012 22:26:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: DIEGO APARICIO
-- Create date: 2012/03/12
-- Description: Para el llenado de tiempo
-- =============================================
ALTER PROCEDURE [dbo].[PCN_LLENAR_TIEMPO]
@FEINICIO NUMERIC(8)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
--SE CREA UNA TABLA TEMPORAL PARA CALCULAR TIEMPO
CREATE TABLE #DIM_TIEMPO(
NMSEC_DIA DATE,
DIA_MES INTEGER,
FECHA_INT INTEGER,
NMSEC_MES INTEGER,
DSNOMBRE_MES VARCHAR(20),
NMSEC_ANO INTEGER
)
--VARIABLES
DECLARE @DIAS NUMERIC(4)
DECLARE @CONT NUMERIC(4)
DECLARE @FEAUX DATETIME
SET @DIAS = 500
SET @CONT = 0
SET @FEAUX = (SELECT CASE
WHEN MAX(NMSEC_DIA) IS NULL THEN
CAST('20110101' AS DATETIME)
ELSE
MAX(NMSEC_DIA)
END
FROM Bodega_Datos.DBO.TDWH_DIA)
WHILE @CONT < @DIAS
BEGIN
/*TDWH_DIA*/
INSERT INTO #DIM_TIEMPO
--INSERT INTO Bodega_Datos.dbo.DIM_TIEMPO
(NMSEC_DIA,DIA_MES,FECHA_INT,NMSEC_MES,DSNOMBRE_MES,NMSEC_ANO)
SELECT Cast( Cast( (year(@FEAUX)*10000+month(@FEAUX)*100+day(@FEAUX)) as varchar(10)) as DATE) NMSEC_DIA,
year(@FEAUX)*10000+month(@FEAUX)*100+day(@FEAUX) as FECHA_INT,
DAY(@FEAUX) as DIA_MES,
year(@FEAUX)*100+month(@FEAUX) as NMSEC_MES,
(DATENAME(MONTH,Cast( Cast( (year(@FEAUX)*10000+month(@FEAUX)*100+day(@FEAUX)) as varchar(10)) as DATE) ) ) + ' de ' + CAST(year(@FEAUX) AS VARCHAR(4)) DSNOMBRE_MES,
year(@FEAUX) as NMSEC_ANO
SET @CONT = ( @CONT + 1)
SET @FEAUX = (SELECT @FEAUX + 1)
END
INSERT INTO Bodega_Datos.dbo.TDWH_DIA
(NMSEC_DIA,FECHA_INT,DIA_MES,NMSEC_MES)
SELECT DISTINCT NMSEC_DIA, FECHA_INT, DIA_MES, NMSEC_MES
FROM #DIM_TIEMPO
INSERT INTO Bodega_Datos.Dbo.TDWH_MES
(NMSEC_MES,DSNOMBRE_MES, NMSEC_ANO)
SELECT DISTINCT NMSEC_MES,DSNOMBRE_MES, NMSEC_ANO
FROM #DIM_TIEMPO
INSERT INTO Bodega_Datos.Dbo.TDWH_ANO
(NMSEC_ANO)
SELECT DISTINCT NMSEC_ANO
FROM #DIM_TIEMPO
DROP TABLE #DIM_TIEMPO
END
Comentarios
Publicar un comentario