程式師世界 >> 數據庫知識 >> SqlServer數據庫 >> 關於SqlServer >> SSAS：實現Cube 以及角色扮演維度，度量值格式化和計算成員的創建

SSAS：實現Cube 以及角色扮演維度，度量值格式化和計算成員的創建

編輯：關於SqlServer

在熟悉完下面這三種維度的創建方式之後，就可以開始創建我們的第一個 Cube 了。

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