SSAS: Implementing Cube and Role playing dimensions, measure formatting, and calculating member creation

Once you have familiarized yourself with the creation of the following three dimensions, you are ready to start creating our first Cube.

SSAS Series-Custom Date dimension design

SSAS Series-Dimension design based on snowflake model

SSAS series-about the design of parent-child dimensions

We'll use the following scripts to create some dimension tables and fact tables with the source of AdventureWorksDW2012, but because there are so many data columns, I've streamlined some tables and customized the Dimdate tables.

Use biwork_ssis go to SET NOCOUNT on IF object_id (' factinternetsales ', ' U ') are not NULL DROP TABLE factinternetsales IF OBJECT_ID (' FactResellerSales ', ' u ') is not a NULL DROP TABLE factresellersales IF object_id (' DimEmployee ', ' u ') is not NU LL 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 ') are not null drop table Dimproductsu Bcategory IF object_id (' dimproductcategory ', ' U ') is not a NULL DROP table dimproductcategory go CREATE table Dimda Te (datekey INT PRIMARY KEY, Shortdatename NVARCHAR () not NULL, Fulldatename NVARCHAR () not NULL, D Aynumberofweek TINYINT NOT NULL, Daynameofweek NVARCHAR (a) not NULL, daynumberofmonth TINYINT NOT NULL, Daynu Mberofyear SMALLINT NOT NULL, weeknumberofyear TINYINT NOT NULL, Isweekend NVARCHAR (7) is not NULL, isleapyear BIT not NULL, MONTHKEY INT NOT NULL, monthnumberofyear TINYINT NOT NULL, Monthnameofyear NVARCHAR (TEN) not NULL, monthnamewithyear NVARCHAR is not NULL, Calendarquarterkey INT isn't null, calendarquarternumber TINYINT NOT NULL, Calendarqua Rternamewithyear NVARCHAR NOT NULL, calendarsemesternumber TINYINT NOT NULL, Calendaryearkey SMALLINT not NUL 
    L, calendaryearname NVARCHAR not NULL, Fiscalquarterkey INT, fiscalquarternumber TINYINT not NULL, Fiscalquartername NVARCHAR, fiscalsemester TINYINT not NULL, Fiscalyearkey SMALLINT NOT NULL, Fiscalyearn
       Ame NVARCHAR,) DECLARE @StartDate datetime DECLARE @EndDate datetime SELECT @StartDate = ' 2005-01-01 ',
        @EndDate = ' 2013-12-31 ' while (@StartDate <= @EndDate) BEGIN inserts into Dimdate (DateKey,
        Shortdatename, Fulldatename, Daynumberofweek, Daynameofweek, Daynumberofmonth, 
  Daynumberofyear,      Weeknumberofyear, Isweekend, Isleapyear, Monthkey, Monthnumberofyear, Month Nameofyear, Monthnamewithyear, Calendarquarterkey, Calendarquarternumber, Calendarquart Ernamewithyear, Calendarsemesternumber, Calendaryearkey, CalendarYearName, Fiscalquarte Rnumber, Fiscalsemester, Fiscalyearkey) SELECT CAST (CONVERT (VARCHAR (8), @StartDate, 112) as INT) As ' DateKey ', convert (VARCHAR, @StartDate,) as ' Shortdatename ', convert (VARCHAR (2), Datename (D
                    D, @StartDate)) + ' + datename (MM, @StartDate) + ' + CONVERT (CHAR (4), DATEPART (YY, @StartDate)) as ' Fulldatename ',-1 July DATEPART (DW, @Sta  rtdate) 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 ((@StartDate)% 4 = 0) and (@StartDate)%!= 0 OR year (@StartDat e)% = 0) THEN 1 ELSE 0 end as ' isleapyear ', DATEPART (yy,@ StartDate) * + DATEPART (mm, @StartDate) as ' Monthkey ',--200507 DATEPART (mm, @StartDate) as ' Monthnumberofye Ar ', datename (mm, @StartDate) as ' Monthnameofyear ', datename (mm, @StartDate) + ' + CONVERT (CHAR (4), D Atepart (yy, @StartDate)) as ' monthnamewithyear ',--July DATEPART (yy, @StartDate) * + 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 ' C
                    Alendarquarternamewithyear ',--CY Qtr 3 case DATEPART (MM, @StartDate) BETWEEN 1 and 6 THEN 1 ELSE 2 end as ' CalendarSemester ', DATEPART (YY, @StartDate) as ' Calendar Yearkey ', ' cy ' + CONVERT (CHAR (4), DATEPART (YY, @StartDate)) as ' CalendarYearName ',--Cy-C CA  SE 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 * + Fiscalquarternumbe R,--200601 fiscalyearname = ' FY ' + CONVERT (CHAR (4), Fiscalyearkey),--FY 2006       
           Uartername = ' FY ' + convert (char (4), Fiscalyearkey) + ' Qtr ' + CONVERT (char (1), Fiscalquarternumber)--FY 2006 1 WHERE DateKey = CONVERT (Int,convert (VARCHAR (8), @StartDate, 112)) SET @StartDate = @Start Date + 1 End SELECT EmployeeKey, Parentemployeekey, Employeenationalidalternatekey.
       NULL (MiddleName, ') = ' THEN FirstName + ' + LastName ELSE FirstName + ' + middlename + ' +lastname
       End as FullName, Title to DimEmployee from AdventureWorksDW2012.dbo.DimEmployee SELECT ProductKey, Productalternatekey, Productsubcategorykey, Englishproductname, StandardCost, Color, Sa Fetystocklevel, LIstprice, Class, Size, StartDate, EndDate, [Status], Productalternatekey + ' (' + CONVERT (Char, StartDate,) + ') ' as ProductID into DimProduct from AdventureWorksDW2012.dbo.DimProduct Sele CT Productsubcategorykey, Productsubcategoryalternatekey, Englishproductsubcategoryname, ProductCateg
       Orykey into DimProductSubcategory from AdventureWorksDW2012.dbo.DimProductSubcategory SELECT Productcategorykey, Productcategoryalternatekey, englishproductcategoryname into Dimproductcategory from ADVENTUREWORKSDW2012.DBO.D Improductcategory SELECT ProductKey, Orderdatekey, EmployeeKey, Salesorderlinenumber, Sa Lesordernumber, UnitPrice, Productstandardcost, salesamount into FactResellerSales from Adventurework SDW2012.dbo.FactResellerSales SELECT ProductKey, Orderdatekey, Duedatekey, Shipdatekey, S
       Alesordernumber,Salesorderlinenumber, Orderquantity, UnitPrice, salesamount into FactInternetSales from Adventurework SDW2012.dbo.FactInternetSales-------------------------------------------------------------------------------- Add Primary Key Constraint------------------------------------------------------------------------------ALTER Table DimEmployee Add CONSTRAINT pk_employeekey PRIMARY KEY CLUSTERED (employeekey) ALTER TABLE dimproduct ADD CONS Traint pk_productkey PRIMARY KEY CLUSTERED (productkey) ALTER TABLE dimproductsubcategory ADD CONSTRAINT Pk_subcatego Rykey PRIMARY KEY CLUSTERED (productsubcategorykey) ALTER TABLE dimproductcategory ADD CONSTRAINT Pk_categorykey ARY key CLUSTERED (productcategorykey) ALTER TABLE factinternetsales ADD CONSTRAINT pk_internetsales PRIMARY KEY clus  tered (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 (Pr Oductkey) 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) refer Ences dimproduct (ProductKey) ALTER TABLE factinternetsales ADD CONSTRAINT fk_internet_orderdatekey FOREIGN KEY (orde Rdatekey) 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 (Dateke 

Y) ALTER TABLE dimproduct ADD CONSTRAINT fk_product_subcatetorykey FOREIGN KEY (productsubcategorykey) REFERENCES DimProductSubcategory (Productsubcategorykey) ALTER TABLE dimproductsubcategory ADD CONSTRAINT fk_subcategory_catet Orykey FOREIGN KEY (productcategorykey) REFERENCES dimproductcategory (Productcategorykey) SELECT * from Dimemploye E select * FROM Dimdate SELECT * FROM dimproduct SELECT * to dimproductsubcategory SELECT * from Dimproductcategory SE Lect * from FactResellerSales SELECT * from FactInternetSales