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