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

SSAS：实现Cube 以及角色扮演维度，度量值格式化和计算成员的创建2014-03-23 cnblogs BIWORK在熟悉完下面这三种维度的创建方式之后，就可以开始创建我们的第一个 Cube 了。
SSAS 系列 - 自定义的日期维度设计
SSAS 系列 - 基于雪花模型的维度设计
SSAS系列 - 关于父子维度的设计
我们将使用下面的这些脚本来创建一些维度表和事实表，数据源的来源是 AdventureWorksDW2012，但由于数据列太多因此我精简了一些表并且自定义了 DimDate 表。
USE BIWORK_SSISGOSET NOCOUNT ONIF OBJECT_ID（"FactInternetSales","U"） IS NOT NULLDROP TABLE FactInternetSalesIF OBJECT_ID（"FactResellerSales","U"） IS NOT NULLDROP TABLE FactResellerSalesIF OBJECT_ID（"DimEmployee","U"） IS NOT NULLDROP TABLE DimEmployeeIF OBJECT_ID（"DimDate","U"） IS NOT NULLDROP TABLE DimDate IF OBJECT_ID（"DimProduct","U"） IS NOT NULLDROP TABLE DimProduct IF OBJECT_ID（"DimProductSubcategory","U"） IS NOT NULLDROP TABLE DimProductSubcategoryIF OBJECT_ID（"DimProductCategory","U"） IS NOT NULLDROP TABLE DimProductCategoryGOCREATE 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 DATETIMEDECLARE @EndDate DATETIMESELECT @StartDate = "2005-01-01", @EndDate = "2013-12-31"WHILE （@StartDate <= @EndDate）BEGININSERT 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 1ELSE 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 6THEN 1ELSE 2 END AS "CalendarSemester", DATEPART（YY,@StartDate） AS "CalendarYearKey", "CY " + CONVERT（CHAR（4）,DATEPART（YY,@StartDate）） AS "CalendarYearName", -- CY 2005CASE WHEN DATEPART（MM,@StartDate） BETWEEN 1 AND 6 THEN DATEPART（QQ,@StartDate） + 2ELSE DATEPART（QQ,@StartDate） - 2 END AS "FiscalQuarter", CASE WHEN DATEPART（MM,@StartDate） BETWEEN 1 AND 6THEN 2ELSE 1 END AS "FiscalSemester", CASE WHEN DATEPART（MM,@StartDate） BETWEEN 1 AND 6THEN DATEPART（YY,@StartDate） ELSE DATEPART（YY,@StartDate） + 1 END AS "FiscalYear" UPDATE DimDateSET 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 + 1ENDSELECT EmployeeKey, ParentEmployeeKey, EmployeeNationalIDAlternateKey, CASE WHEN ISNULL（MiddleName,""） = ""THEN FirstName +" "+ LastNameELSE FirstName +" "+ MiddleName +" "+LastName END AS FullName, Title INTO DimEmployeeFROM AdventureWorksDW2012.dbo.DimEmployeeSELECT ProductKey, ProductAlternateKey, ProductSubcategoryKey, EnglishProductName, StandardCost, Color, SafetyStockLevel, ListPrice, Class, Size, StartDate, EndDate, [Status], ProductAlternateKey + " （" + CONVERT （Char（10）, StartDate, 120） + "）" AS ProductIDINTO DimProductFROM AdventureWorksDW2012.dbo.DimProductSELECT ProductSubcategoryKey, ProductSubcategoryAlternateKey, EnglishProductSubcategoryName, ProductCategoryKey INTO DimProductSubcategoryFROM AdventureWorksDW2012.dbo.DimProductSubcategorySELECT ProductCategoryKey, ProductCategoryAlternateKey, EnglishProductCategoryNameINTO DimProductCategoryFROM AdventureWorksDW2012.dbo.DimProductCategory SELECT ProductKey, OrderDateKey, EmployeeKey, SalesOrderLineNumber, SalesOrderNumber, UnitPrice, ProductStandardCost, SalesAmountINTO FactResellerSalesFROM AdventureWorksDW2012.dbo.FactResellerSalesSELECT ProductKey, OrderDateKey, DueDateKey, ShipDateKey, SalesOrderNumber, SalesOrderLineNumber, OrderQuantity, UnitPrice, SalesAmountINTO FactInternetSalesFROM 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 FactResellerSalesADD CONSTRAINT FK_Reseller_EmployeeKey FOREIGN KEY（EmployeeKey） REFERENCES DimEmployee（EmployeeKey）ALTER TABLE FactResellerSalesADD CONSTRAINT FK_Reseller_ProductKey FOREIGN KEY（ProductKey） REFERENCES DimProduct（ProductKey）ALTER TABLE FactResellerSalesADD CONSTRAINT FK_Reseller_OrderDateKey FOREIGN KEY（OrderDateKey） REFERENCES DimDate（DateKey） ALTER TABLE FactInternetSalesADD CONSTRAINT FK_Internet_ProductKey FOREIGN KEY（ProductKey） REFERENCES DimProduct（ProductKey）ALTER TABLE FactInternetSalesADD CONSTRAINT FK_Internet_OrderDateKey FOREIGN KEY（OrderDateKey） REFERENCES DimDate（DateKey）ALTER TABLE FactInternetSalesADD CONSTRAINT FK_Internet_ShipDateKey FOREIGN KEY（ShipDateKey） REFERENCES DimDate（DateKey）ALTER TABLE FactInternetSalesADD 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 DimProductSELECT * FROM DimProductSubcategorySELECT * FROM DimProductCategorySELECT * FROM FactResellerSalesSELECT * FROM FactInternetSales