SSAS：实现Cube 以及角色扮演维度，度量值格式化和计算成员的创建 - MSSQL教程

SSAS 系列 - 自定义的日期维度设计

SSAS 系列 - 基于雪花模型的维度设计

SSAS系列 - 关于父子维度的设计

我们将使用下面的这些脚本来创建一些维度表和事实表，数据源的来源是 AdventureWorksDW2012， 但由于数据列太多因此我精简了一些表并且自定义了 DimDate 表。

USE BIWORK_SSIS
GO
SET NOCOUNT ON
IF OBJECT_ID('FactInternetSales','U') IS NOT NULL
DROP TABLE FactInternetSales
    
IF OBJECT_ID('FactResellerSales','U') IS NOT NULL
DROP TABLE FactResellerSales
    
IF OBJECT_ID('DimEmployee','U') IS NOT NULL
DROP TABLE DimEmployee
    
IF OBJECT_ID('DimDate','U') IS NOT NULL
DROP TABLE DimDate 
    
IF OBJECT_ID('DimProduct','U') IS NOT NULL
DROP TABLE DimProduct 
    
IF OBJECT_ID('DimProductSubcategory','U') IS NOT NULL
DROP TABLE DimProductSubcategory
    
IF OBJECT_ID('DimProductCategory','U') IS NOT NULL
DROP TABLE DimProductCategory
GO
    
CREATE TABLE DimDate
(
    DateKey INT PRIMARY KEY,
    ShortDateName NVARCHAR(12) NOT NULL,
    FullDateName NVARCHAR(20)  NOT NULL, 
    DayNumberOfWeek TINYINT NOT NULL,
    DayNameOfWeek NVARCHAR(10) NOT NULL,
    DayNumberOfMonth TINYINT NOT NULL,
    DayNumberOfYear SMALLINT NOT NULL, 
    WeekNumberOfYear TINYINT NOT NULL,
    IsWeekend NVARCHAR(7) NOT NULL, 
    IsLeapYear BIT NOT NULL,
    MonthKey INT NOT NULL,
    MonthNumberOfYear TINYINT NOT NULL,
    MonthNameOfYear NVARCHAR(10) NOT NULL,
    MonthNameWithYear NVARCHAR(20) NOT NULL, 
    CalendarQuarterKey INT NOT NULL, 
    CalendarQuarterNumber TINYINT NOT NULL, 
    CalendarQuarterNameWithYear NVARCHAR(20) NOT NULL, 
    CalendarSemesterNumber TINYINT NOT NULL,
    CalendarYearKey SMALLINT NOT NULL, 
    CalendarYearName NVARCHAR(20) NOT NULL, 
    FiscalQuarterKey INT,
    FiscalQuarterNumber TINYINT NOT NULL, 
    FiscalQuarterName NVARCHAR(20),
    FiscalSemester TINYINT NOT NULL,
    FiscalYearKey SMALLINT NOT NULL, 
    FiscalYearName NVARCHAR(20),
)
    
DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME
    
SELECT @StartDate = '2005-01-01',
       @EndDate = '2013-12-31'
    
WHILE (@StartDate <= @EndDate)
BEGIN
    INSERT INTO DimDate 
    (
        DateKey,
        ShortDateName,
        FullDateName, 
        DayNumberOfWeek,
        DayNameOfWeek,
        DayNumberOfMonth,
        DayNumberOfYear, 
        WeekNumberOfYear,
        IsWeekend,
        IsLeapYear, 
        MonthKey,
        MonthNumberOfYear,
        MonthNameOfYear, 
        MonthNameWithYear,  
        CalendarQuarterKey,
        CalendarQuarterNumber,
        CalendarQuarterNameWithYear,
        CalendarSemesterNumber,
        CalendarYearKey, 
        CalendarYearName,  
        FiscalQuarterNumber, 
        FiscalSemester,
        FiscalYearKey 
    )
    SELECT CAST(CONVERT(VARCHAR(8),@StartDate,112) AS INT) AS 'DateKey',
           CONVERT(VARCHAR(20), @StartDate,106) AS 'ShortDateName',
           CONVERT(VARCHAR(2),DATENAME(DD,@StartDate)) 
                    + ' '
                    + DATENAME(MM,@StartDate) 
                    + ' '
                    + CONVERT(CHAR(4), DATEPART(YY,@StartDate)) AS 'FullDateName', -- 1 

July 2005  
           DATEPART(DW,@StartDate) AS 'DayNumberOfWeek',
           DATENAME(DW,@StartDate) AS 'DayNameOfWeek',
           DATENAME(DD,@StartDate) AS 'DayNumberOfMonth',
           DATENAME(DY,@StartDate) AS 'DayNumberOfYear',
           DATEPART(WW,@StartDate) AS 'WeekNumberOfYear',
           CASE WHEN DATEPART(DW,@StartDate) IN (1,7)  
                    THEN 'Weekend'
                ELSE 'Weekday'
           END AS 'IsWeekend',
           CASE WHEN ((YEAR(@StartDate) % 4 = 0) AND (YEAR(@StartDate) % 100 != 0 OR YEAR

(@StartDate) % 400 = 0))
                    THEN 1
                ELSE 0
           END AS 'IsLeapYear', 
           DATEPART(YY,@StartDate) * 100 + DATEPART(MM,@StartDate) AS 'MonthKey', -- 200507
           DATEPART(MM,@StartDate) AS 'MonthNumberOfYear',
           DATENAME(MM,@StartDate) AS 'MonthNameOfYear',
           DATENAME(MM,@StartDate) + ' ' + CONVERT(CHAR(4),DATEPART(YY,@StartDate)) AS 

'MonthNameWithYear',  -- July 2005  
           DATEPART(YY,@StartDate) * 100 + DATEPART(QQ,@StartDate) AS 'CalendarQuarterKey', 

 -- 200503
           DATEPART(QQ,@StartDate) AS 'CalendarQuarterNumber',
           'CY ' + CONVERT(CHAR(4),DATEPART(YY,@StartDate)) 
                 + ' Qtr '
                 + CONVERT(CHAR(1), DATEPART(QQ,@StartDate)) AS 

'CalendarQuarterNameWithYear', -- CY 2005 Qtr 3  
           CASE WHEN DATEPART(MM,@StartDate) BETWEEN 1 AND 6
                    THEN 1
                ELSE 2
           END AS 'CalendarSemester',
           DATEPART(YY,@StartDate) AS 'CalendarYearKey',  
           'CY ' + CONVERT(CHAR(4),DATEPART(YY,@StartDate)) AS 'CalendarYearName', -- CY 

2005 
           CASE WHEN DATEPART(MM,@StartDate) BETWEEN 1 AND 6
                     THEN DATEPART(QQ,@StartDate) + 2
                ELSE DATEPART(QQ,@StartDate) - 2
           END AS 'FiscalQuarter',
           CASE WHEN DATEPART(MM,@StartDate) BETWEEN 1 AND 6
                    THEN 2
                ELSE 1
           END AS 'FiscalSemester',
           CASE WHEN DATEPART(MM,@StartDate) BETWEEN 1 AND 6
                    THEN DATEPART(YY,@StartDate) 
                ELSE DATEPART(YY,@StartDate) + 1
           END AS 'FiscalYear'
        
           UPDATE DimDate 
           SET FiscalQuarterKey = FiscalYearKey * 100 + FiscalQuarterNumber,  -- 200601
               FiscalYearName = 'FY ' + CONVERT(CHAR(4), FiscalYearKey),   -- FY 2006
               FiscalQuarterName =  'FY ' + CONVERT(Char(4), FiscalYearKey) + ' Qtr ' + 

CONVERT(CHAR(1), FiscalQuarterNumber) -- FY 2006 Qtr 1      
           WHERE DateKey = CONVERT(INT,CONVERT(VARCHAR(8),@StartDate,112))
            
    SET @StartDate = @StartDate + 1
END
    
SELECT EmployeeKey,
       ParentEmployeeKey,
       EmployeeNationalIDAlternateKey,
       CASE WHEN ISNULL(MiddleName,'') = ''  THEN FirstName +' '+ LastName  
            ELSE FirstName +' '+ MiddleName +' '+LastName
       END AS FullName,
       Title 
INTO DimEmployee
FROM AdventureWorksDW2012.dbo.DimEmployee
    
SELECT ProductKey,
       ProductAlternateKey,
       ProductSubcategoryKey,
       EnglishProductName,
       StandardCost,
       Color,
       SafetyStockLevel,
       ListPrice,
       Class,
       Size,
       StartDate,
       EndDate,
       [Status],
       ProductAlternateKey + ' (' + CONVERT (Char(10), StartDate, 120) + ')' AS ProductID
INTO DimProduct
FROM AdventureWorksDW2012.dbo.DimProduct
    
SELECT ProductSubcategoryKey,
       ProductSubcategoryAlternateKey,
       EnglishProductSubcategoryName,
       ProductCategoryKey 
INTO DimProductSubcategory
FROM AdventureWorksDW2012.dbo.DimProductSubcategory
    
SELECT ProductCategoryKey,
       ProductCategoryAlternateKey,
       EnglishProductCategoryName
INTO DimProductCategory
FROM AdventureWorksDW2012.dbo.DimProductCategory 
    
SELECT ProductKey,
       OrderDateKey,
       EmployeeKey,
       SalesOrderLineNumber,
       SalesOrderNumber,
       UnitPrice,
       ProductStandardCost,
       SalesAmount
INTO FactResellerSales
FROM AdventureWorksDW2012.dbo.FactResellerSales
    
SELECT ProductKey,
       OrderDateKey,
       DueDateKey,
       ShipDateKey,
       SalesOrderNumber,
       SalesOrderLineNumber,
       OrderQuantity,
       UnitPrice,
       SalesAmount
INTO FactInternetSales
FROM AdventureWorksDW2012.dbo.FactInternetSales
    
------------------------------------------------------------------------------
-- Add Primary Key Constraint
------------------------------------------------------------------------------  
ALTER TABLE DimEmployee 
ADD CONSTRAINT PK_EmployeeKey PRIMARY KEY CLUSTERED (EmployeeKey)
    
ALTER TABLE DimProduct 
ADD CONSTRAINT PK_ProductKey PRIMARY KEY CLUSTERED(ProductKey)
    
ALTER TABLE DimProductSubcategory 
ADD CONSTRAINT PK_SubcategoryKey PRIMARY KEY CLUSTERED(ProductSubcategoryKey)
    
ALTER TABLE DimProductCategory 
ADD CONSTRAINT PK_CategoryKey PRIMARY KEY CLUSTERED(ProductCategoryKey)
    
ALTER TABLE FactInternetSales 
ADD CONSTRAINT PK_InternetSales PRIMARY KEY CLUSTERED

(SalesOrderNumber,SalesOrderLineNumber)
    
ALTER TABLE FactResellerSales 
ADD CONSTRAINT PK_ResellerSales PRIMARY KEY CLUSTERED

(SalesOrderNumber,SalesOrderLineNumber)
    
------------------------------------------------------------------------------
-- Add Primary Key Constraint
------------------------------------------------------------------------------
ALTER TABLE FactResellerSales
ADD CONSTRAINT FK_Reseller_EmployeeKey FOREIGN KEY(EmployeeKey) REFERENCES DimEmployee

(EmployeeKey)
    
ALTER TABLE FactResellerSales
ADD CONSTRAINT FK_Reseller_ProductKey FOREIGN KEY(ProductKey) REFERENCES DimProduct

(ProductKey)
    
ALTER TABLE FactResellerSales
ADD CONSTRAINT FK_Reseller_OrderDateKey FOREIGN KEY(OrderDateKey) REFERENCES DimDate

(DateKey) 
    
ALTER TABLE FactInternetSales
ADD CONSTRAINT FK_Internet_ProductKey FOREIGN KEY(ProductKey) REFERENCES DimProduct

(ProductKey)
    
ALTER TABLE FactInternetSales
ADD CONSTRAINT FK_Internet_OrderDateKey FOREIGN KEY(OrderDateKey) REFERENCES DimDate

(DateKey)
    
ALTER TABLE FactInternetSales
ADD CONSTRAINT FK_Internet_ShipDateKey FOREIGN KEY(ShipDateKey) REFERENCES DimDate(DateKey)
    
ALTER TABLE FactInternetSales
ADD CONSTRAINT FK_Internet_DueDateKey FOREIGN KEY(DueDateKey) REFERENCES DimDate(DateKey) 
    
ALTER TABLE DimProduct 
ADD CONSTRAINT FK_Product_SubcatetoryKey FOREIGN KEY (ProductSubcategoryKey) REFERENCES 

DimProductSubcategory (ProductSubcategoryKey)
    
ALTER TABLE DimProductSubcategory 
ADD CONSTRAINT FK_Subcategory_CatetoryKey FOREIGN KEY (ProductCategoryKey) REFERENCES 

DimProductCategory (ProductCategoryKey)
    
SELECT * FROM DimEmployee 
SELECT * FROM DimDate 
SELECT * FROM DimProduct
SELECT * FROM DimProductSubcategory
SELECT * FROM DimProductCategory
SELECT * FROM FactResellerSales
SELECT * FROM FactInternetSales

（编辑：佛山站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!