SQL Server Business Intelligence Development Studio 中的 Analysis Services 开发工具提供了一个自动生成日期维度的功能,能够很方便的生成带有层次结构的日期维度。但是若想扩展已有日期维度中的时间范围则无法通过开发工具完成。以下提供一个存储过程用于生成日期维度数据。
说明:由于语言环境以及所生成的日期维度结构可存在较大差异,因此本脚本仅供思路参考。在实际应用时应根据具体情况进行改动。
SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: onelittlefox -- Create date: 2011-01-06 -- Description: Fill date dim for SSAS -- ============================================= ALTER PROCEDURE [dbo].[FillDateDim] -- Add the parameters for the stored procedure here @startDate datetime, @endDate datetime, @operation int = 0 AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; -- Insert statements for procedure here DECLARE @CurrentDate Datetime; SET @CurrentDate = @startDate; SET LANGUAGE english; IF @operation = 1 BEGIN WHILE @CurrentDate < @endDate BEGIN INSERT INTO dbo.Dim_Date ( [PK_日期], [日期_名称], [年], [年_名称], [季度], [季度_名称], [月份], [月份_名称], [每年的某一日], [每年的某一日_名称], [每个季度的某一日], [每个季度的某一日_名称], [每月的某一日], [每月的某一日_名称], [每年的某一月], [每年的某一月_名称], [每个季度的某一月], [每个季度的某一月_名称], [每年的某一季度], [每年的某一季度_名称] ) VALUES ( @CurrentDate, DATENAME(DW, @CurrentDate)+ ', ' + DATENAME(MM, @CurrentDate) + ' ' + right('00' + CONVERT(char(2), DATEPART(D, @CurrentDate)), 2) + ' ' + CONVERT(char(4), DATEPART(YYYY, @CurrentDate)), DATEADD(YYYY, DATEDIFF(YYYY, 0, @CurrentDate), 0), 'Calendar ' + CONVERT(char(4), DATEPART(YYYY, @CurrentDate)), DATEADD(QQ, DATEDIFF(QQ, 0, @CurrentDate), 0), 'Quarter ' + CONVERT(char(1), DATEPART(QQ, @CurrentDate)) + ', ' + CONVERT(char(4), DATEPART(YYYY, @CurrentDate)), DATEADD(MM, DATEDIFF(MM, 0, @CurrentDate), 0), DATENAME(MM, @CurrentDate) + ' ' + CONVERT(char(4), DATEPART(YYYY, @CurrentDate)), DATEPART(DY, @CurrentDate), RTRIM('Day ' + CONVERT(char(3), DATEPART(DY, @CurrentDate))), DATEDIFF(D, DATEADD(QQ, DATEDIFF(QQ, 0, @CurrentDate), 0), @CurrentDate) + 1, RTRIM('Day ' + CONVERT(char(3),DATEDIFF(D, DATEADD(QQ, DATEDIFF(QQ, 0, @CurrentDate), 0), @CurrentDate) + 1)), DATEDIFF(D, DATEADD(MM, DATEDIFF(MM, 0, @CurrentDate), 0), @CurrentDate) + 1, RTRIM('Day ' + CONVERT(char(2),DATEDIFF(D, DATEADD(MM, DATEDIFF(MM, 0, @CurrentDate), 0), @CurrentDate) + 1)), DATEPART(MM, @CurrentDate), RTRIM('Month ' + CONVERT(char(2),DATEPART(MM, @CurrentDate))), DATEDIFF(MM, DATEADD(QQ, DATEDIFF(QQ, 0, @CurrentDate), 0), @CurrentDate) + 1, RTRIM('Month ' + CONVERT(char(2), DATEDIFF(MM, DATEADD(QQ, DATEDIFF(QQ, 0, @CurrentDate), 0), @CurrentDate) + 1)), DATEPART(QQ, @CurrentDate), RTRIM('Quarter ' + CONVERT(char(1), DATEPART(QQ, @CurrentDate))) ); SET @CurrentDate = DATEADD(D, 1, @CurrentDate); END END ELSE BEGIN CREATE TABLE #tmp( [PK_日期] [Datetime] NOT NULL, [日期_名称] [nvarchar](50) NULL, [年] [Datetime] NULL, [年_名称] [nvarchar](50) NULL, [季度] [Datetime] NULL, [季度_名称] [nvarchar](50) NULL, [月份] [Datetime] NULL, [月份_名称] [nvarchar](50) NULL, [每年的某一日] [int] NULL, [每年的某一日_名称] [nvarchar](50) NULL, [每个季度的某一日] [int] NULL, [每个季度的某一日_名称] [nvarchar](50) NULL, [每月的某一日] [int] NULL, [每月的某一日_名称] [nvarchar](50) NULL, [每年的某一月] [int] NULL, [每年的某一月_名称] [nvarchar](50) NULL, [每个季度的某一月] [int] NULL, [每个季度的某一月_名称] [nvarchar](50) NULL, [每年的某一季度] [int] NULL, [每年的某一季度_名称] [nvarchar](50) NULL ); WHILE @CurrentDate < @endDate BEGIN INSERT INTO #tmp ( [PK_日期], [日期_名称], [年], [年_名称], [季度], [季度_名称], [月份], [月份_名称], [每年的某一日], [每年的某一日_名称], [每个季度的某一日], [每个季度的某一日_名称], [每月的某一日], [每月的某一日_名称], [每年的某一月], [每年的某一月_名称], [每个季度的某一月], [每个季度的某一月_名称], [每年的某一季度], [每年的某一季度_名称] ) VALUES ( @CurrentDate, DATENAME(DW, @CurrentDate)+ ', ' + DATENAME(MM, @CurrentDate) + ' ' + right('00' + CONVERT(char(2), DATEPART(D, @CurrentDate)), 2) + ' ' + CONVERT(char(4), DATEPART(YYYY, @CurrentDate)), DATEADD(YYYY, DATEDIFF(YYYY, 0, @CurrentDate), 0), 'Calendar ' + CONVERT(char(4), DATEPART(YYYY, @CurrentDate)), DATEADD(QQ, DATEDIFF(QQ, 0, @CurrentDate), 0), 'Quarter ' + CONVERT(char(1), DATEPART(QQ, @CurrentDate)) + ', ' + CONVERT(char(4), DATEPART(YYYY, @CurrentDate)), DATEADD(MM, DATEDIFF(MM, 0, @CurrentDate), 0), DATENAME(MM, @CurrentDate) + ' ' + CONVERT(char(4), DATEPART(YYYY, @CurrentDate)), DATEPART(DY, @CurrentDate), RTRIM('Day ' + CONVERT(char(3), DATEPART(DY, @CurrentDate))), DATEDIFF(D, DATEADD(QQ, DATEDIFF(QQ, 0, @CurrentDate), 0), @CurrentDate) + 1, RTRIM('Day ' + CONVERT(char(3),DATEDIFF(D, DATEADD(QQ, DATEDIFF(QQ, 0, @CurrentDate), 0), @CurrentDate) + 1)), DATEDIFF(D, DATEADD(MM, DATEDIFF(MM, 0, @CurrentDate), 0), @CurrentDate) + 1, RTRIM('Day ' + CONVERT(char(2),DATEDIFF(D, DATEADD(MM, DATEDIFF(MM, 0, @CurrentDate), 0), @CurrentDate) + 1)), DATEPART(MM, @CurrentDate), RTRIM('Month ' + CONVERT(char(2),DATEPART(MM, @CurrentDate))), DATEDIFF(MM, DATEADD(QQ, DATEDIFF(QQ, 0, @CurrentDate), 0), @CurrentDate) + 1, RTRIM('Month ' + CONVERT(char(2), DATEDIFF(MM, DATEADD(QQ, DATEDIFF(QQ, 0, @CurrentDate), 0), @CurrentDate) + 1)), DATEPART(QQ, @CurrentDate), RTRIM('Quarter ' + CONVERT(char(1), DATEPART(QQ, @CurrentDate))) ); SET @CurrentDate = DATEADD(D, 1, @CurrentDate); END SELECT * FROM #tmp; DROP TABLE #tmp; END END