BIDATA 201A: Assignment09: Database Script

/************************************************************************************************

Dev: RRoot

Date: 11/24/2014

Change Log:

       11/30/2014, RRoot,

       Fixed FactCustomersPersonsAddresses to include the AddressTypeKey instead of the AddressTypeID.

       Changed spelling of SCDStarDate to SCDStartDate.

       Changed       name of column Option2.DimCustomerAccounts,[CustomerPersonKey]

              to Option2.DimCustomerAccounts,[CustomerPersonID].

       Changed SCDStartDate to use -1 int instead of GetDate() datetime

       Removed the Identity spec. from the DimDates table

       Update Null specs. on DimDates to make all columns Not Null

       Changed the Name and Datatype of  Option2.CustomerCountryRegionID int

       to Option2.CustomerCountryRegionCode varchar

************************************************************************************************/

USE [master];

Go

SET ANSI_NULLS ON

-- Trick: if ANSI Nulls are on the tables will not fill with data in this script.

-- This is because I am using Null = Null in my Where clauses. 

Go

If Exists (Select name from SysDatabases Where Name = 'AW_Project01_DW_DB')

       Begin

              ALTER DATABASE [AW_Project01_DW_DB] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

              DROP DATABASE [AW_Project01_DW_DB];

       End

Go

Create Database [AW_Project01_DW_DB];

Go

Use [AW_Project01_DW_DB]; -- Source DB

Go

/* Consistency is very important so

all dimension table designs will follow this pattern:

       Dimension Surrogate Key Column

       Dimension Original Key Column

       Dimenson Name Column

       Additional Attribute Columns (0 to N)

       Foriegn Key to Snowflake Tables Columns

       SCD Columns (3)

*/    

------------------------------------------------------------------------------------

/************-- Option 1 (Close to original, with snowflake design) --************/

------------------------------------------------------------------------------------

Create Schema Option1;

Go

-------------------------------------------------

--** Customers Dimension (Regular Dimenison) **--

-- These are ALL the Customer we have. --

-------------------------------------------------

       -- 1) DimCustomerAccounts (First level from FactSales)

       SELECT Distinct

         Identity(int, 1,1) AS CustomerAccountKey --Surrogate Key

       , 0 AS [CustomerID] -- Original Key

       , AccountNumber AS [CustomerAccountNumber] -- Name Column

       , 0 AS [CustomerPersonKey] -- FK to Snowflake

       , -1 AS SCDStartDate -- SCD Type 2 column

       , Null AS SCDEndDate -- SCD Type 2 column

       , 'Yes' AS IsCurrent -- SCD Type 2 column

       Into [AW_Project01_DW_DB].Option1.[DimCustomerAccounts]

       FROM [AW_Project01_OLTP_DB]..Customer

       WHERE Null = Null;

       Go

              -- 2) DimCustomerPersons (Second level Snowflake of DimCustomers )

              SELECT Distinct

                Identity(int, 1,1) AS CustomerPersonKey --Surrogate Key           

              , 0 AS [PersonId] -- Original Key

              , FirstName + '' + LastName AS [CustomerPersonName] -- Name Column

              , -1 AS SCDStartDate -- SCD Type 2 column

              , Null AS SCDEndDate -- SCD Type 2 column

              , 'Yes' AS IsCurrent -- SCD Type 2 column

              Into [AW_Project01_DW_DB].Option1.[DimCustomerPersons]

              FROM [AW_Project01_OLTP_DB]..Person

              WHERE Null = Null;

              Go

--------------------------------------------------------------------------------

--** [AddressTypes Dimension (Many To Many Dimension to DimCustomers)] **--

-- These are ALL the Address Types we use. --

--------------------------------------------------------------------------------

       -- 1) DimAddressTypes (First level from FactCustomersPersonsAddresses)

       SELECT Distinct

         Identity(int, 1,1) AS AddressTypeKey --Surrogate Key              

       , 0 AS AddressTypeID -- Original Key

       , Name AS [AddressName] -- Name Column

       , -1 AS SCDStartDate -- SCD Type 2 column

       , Null AS SCDEndDate -- SCD Type 2 column

       , 'Yes' AS IsCurrent -- SCD Type 2 column

       Into [AW_Project01_DW_DB].Option1.[DimAddressTypes]

       FROM [AW_Project01_OLTP_DB]..AddressType

       WHERE Null = Null;

       Go

-------------------------------------------------------------------------------------

--** [CustomerAddresses Dimension (Many To Many Dimension to DimCustomers)] **--

-- These are ONLY Address for our customers, not all know address. --

-------------------------------------------------------------------------------------

       -- 1) DimCustomerAddresses (first level from FactCustomersPersonsAddresses)

       SELECT Distinct

         Identity(int, 1,1) AS CustomerAddressKey --Surrogate Key                

       , 0 AS [CustomerAddressID] -- Original Key

       , City AS [CustomerAddressCityName] -- Name Column

       , 0 AS [CustomerStateProvinceKey] -- FK to Snowflake

       , -1 AS SCDStartDate -- SCD Type 2 column

       , Null AS SCDEndDate -- SCD Type 2 column

       , 'Yes' AS IsCurrent -- SCD Type 2 column

       Into [AW_Project01_DW_DB].Option1.[DimCustomerAddresses]

       FROM [AW_Project01_OLTP_DB]..Address

       WHERE Null = Null;

       Go

              -- 2) DimCustomerStateProvinces (Second level Snowflake of DimCustomerAddresses)

              SELECT Distinct

                Identity(int, 1,1) AS CustomerStateProvinceKey --Surrogate Key          

              , 0 AS [CustomerStateProvinceID] -- Original Key

              , Name AS [CustomerStateProvinceName] -- Name Column

              , 0 AS [CustomerCountryRegionKey] -- FK to Snowflake

              , -1 AS SCDStartDate -- SCD Type 2 column

              , Null AS SCDEndDate -- SCD Type 2 column

              , 'Yes' AS IsCurrent -- SCD Type 2 column

              Into [AW_Project01_DW_DB].Option1.[DimCustomerStateProvinces]

              FROM [AW_Project01_OLTP_DB]..StateProvince

              WHERE Null = Null;

              Go

                     -- 3) DimCustomerCountryRegions (Third Level Snowflake of DimCustomerAddresses)

                     SELECT Distinct

                       Identity(int, 1,1) AS CustomerCountryRegionKey --Surrogate Key          

                     , 0 AS [CustomerCountryRegionID] -- Original Key

                     , Name AS [CustomerCountryRegionName] -- Name Column

                     , -1 AS SCDStartDate -- SCD Type 2 column

                     , Null AS SCDEndDate -- SCD Type 2 column

                     , 'Yes' AS IsCurrent -- SCD Type 2 column

                     Into [AW_Project01_DW_DB].Option1.[DimCustomerCountryRegions]

                     FROM [AW_Project01_OLTP_DB]..CountryRegion

                     WHERE Null = Null;

                     Go

-------------------------------------------------------------------------------------

--** [Products Dimension (Regular)] **--

-- These are ALL the products we have. --

-------------------------------------------------------------------------------------

       -- 1) DimProducts (first level from FactSales) --

              SELECT Distinct

                Identity(int, 1,1) AS ProductKey --Surrogate Key      

              , 0 AS ProductID -- Original Key

              , Name AS [ProductName] -- Name Column

              , ListPrice AS [ProductListPrice] -- Additonal Attribute

              , 0 AS ProductSubcategoryKey -- FK to Snowflake

              , -1 AS SCDStartDate -- SCD Type 2 column

              , Null AS SCDEndDate -- SCD Type 2 column

              , 'Yes' AS IsCurrent -- SCD Type 2 column

       Into [AW_Project01_DW_DB].Option1.[DimProducts]

       FROM [AW_Project01_OLTP_DB]..Products

       WHERE Null = Null;

       Go

              -- 2) DimProductSubcategory (Second level Snowflake of DimProducts) --

              SELECT

                Identity(int, 1,1) AS ProductSubcategoryKey --Surrogate Key         

              , 0 AS ProductSubcategoryID -- Original Key

              , Name AS [ProductSubcategoryName] -- Name Column

              , 0 AS ProductCategoryKey -- FK to Snowflake

              , -1 AS SCDStartDate -- SCD Type 2 column

              , Null AS SCDEndDate -- SCD Type 2 column

              , 'Yes' AS IsCurrent -- SCD Type 2 column

              Into [AW_Project01_DW_DB].Option1.[DimProductSubcateories]

              FROM [AW_Project01_OLTP_DB]..ProductSubcategory

              WHERE Null = Null;

              Go

              -- 3) DimProductCategory (Third level Snowflake of DimProduct) --

                     SELECT

                       Identity(int, 1,1) AS ProductCategoryKey --Surrogate Key                   

                     , 0 AS ProductCategoryID -- Original Key

                     , Name AS [ProductCategoryName] -- Name Column

                     , -1 AS SCDStartDate -- SCD Type 2 column

                     , Null AS SCDEndDate -- SCD Type 2 column

                     , 'Yes' AS IsCurrent -- SCD Type 2 column

                     Into [AW_Project01_DW_DB].Option1.[DimProductCategories]

                     FROM [AW_Project01_OLTP_DB]..ProductCategory

                     WHERE Null = Null;

                     Go

-------------------------------------------------------------------------------------

--** [Date Dimension (Regular)] **--

-- These are ALL the Date we have between 2000 and 2010 --

-------------------------------------------------------------------------------------

       -- 1) DimDates (first level from FactSales) --

       CREATE TABLE [Option1].[DimDates](

               [DateKey] [int] NOT NULL --Surrogate Key        

              ,[FullUSADate] [datetime] NOT NULL UNIQUE -- Original Key

              ,[DateName] [nvarchar](50) NOT NULL -- Name Column

              ,[MonthKey] [int] NOT NULL -- Additonal Attribute

              ,[MonthName] [nvarchar](50) NOT NULL -- Additonal Attribute

              ,[QuarterKey] [int] NOT NULL -- Additonal Attribute

              ,[QuarterName] [nvarchar](50) NOT NULL -- Additonal Attribute

              ,[YearKey] [int] NOT NULL -- Additonal Attribute

              ,[YearName] [nvarchar](50) NOT NULL -- Additonal Attribute

              -- NOTE: No SCD columns are needed since we do not allow people to change the data

       );

       Go

-------------------------------------------------------------------------------------

--** [The Sale Fact table] **--

-------------------------------------------------------------------------------------

       -- 1) FactSales (Primary Fact table of the Sales Data Mart)

       SELECT Distinct

         SalesOrderHeader.SalesOrderID

       , SalesOrderDetail.SalesOrderDetailID

       , 0 AS OrderDateKey

       , 0 AS CustomerAccountKey

       , 0 AS ProductKey

       , SalesOrderDetail.OrderQty

       , SalesOrderDetail.UnitPrice

       -- NOTE: No SCD columns are needed IF we do not allow people to change the data

       Into [AW_Project01_DW_DB].Option1.FactSales

       FROM [AW_Project01_OLTP_DB]..SalesOrderDetail

       INNER JOIN [AW_Project01_OLTP_DB]..SalesOrderHeader

         ON SalesOrderDetail.SalesOrderID = SalesOrderHeader.SalesOrderID

       WHERE Null = Null;

       Go

-------------------------------------------------------------------------------------

--** [The Person to Address Fact table] **--

-------------------------------------------------------------------------------------

       -- 2) FactCustomersPersonsAddresses (Bridge of DimCustomers to DimAddresses)

       SELECT Distinct

         0 AS CustomerPersonKey

       , 0 AS CustomerAddressKey

       , 0 AS AddressTypeKey

       -- NOTE: No SCD columns are needed IF we do not allow people to change the data

       Into [AW_Project01_DW_DB].Option1.[FactCustomersPersonsAddresses]

       FROM [AW_Project01_OLTP_DB]..PersonAddress

       WHERE Null = Null;

       Go

-------------------------------------------------------------------------------------

-- Now we will add Primary Keys to the tables

-------------------------------------------------------------------------------------

-- Customers Dimension tables

-- 1)[Option1].[DimCustomers]

Alter Table [AW_Project01_DW_DB].Option1.DimCustomerAccounts

       Add Constraint PK_DimCustomerAccounts Primary Key (CustomerAccountKey);

       -- 2)[Option1].[DimCustomerPersons]

       Alter Table [AW_Project01_DW_DB].Option1.DimCustomerPersons

              Add Constraint PK_DimCustomerPersons Primary Key (CustomerPersonKey);

Go

-- AddressTypes Dimension

-- 1)[Option1].[DimAddressTypes]

Alter Table [AW_Project01_DW_DB].Option1.DimAddressTypes

       Add Constraint PK_DimAddressTypes Primary Key (AddressTypeKey);

Go

-- Addresses Dimension tables

-- 1)[Option1].[DimCustomerAddresses]

Alter Table [AW_Project01_DW_DB].Option1.DimCustomerAddresses

       Add Constraint PK_DimCustomerAddresses Primary Key (CustomerAddressKey);

       -- 2)[Option1].[DimCustomerStateProvinces]

       Alter Table [AW_Project01_DW_DB].Option1.DimCustomerStateProvinces

              Add Constraint PK_DimCustomerStateProvinces Primary Key (CustomerStateProvinceKey);

              -- 3)[Option1].[DimCustomerCountryRegions]

              Alter Table [AW_Project01_DW_DB].Option1.DimCustomerCountryRegions

                     Add Constraint PK_DimCustomerCountryRegions Primary Key (CustomerCountryRegionKey);

Go

-- Products Dimension tables

-- 1)[Option1].[DimProducts]

Alter Table [AW_Project01_DW_DB].Option1.DimProducts

       Add Constraint PK_DimProducts Primary Key (ProductKey);

       -- 2)[Option1].[DimProductSubcateories]

       Alter Table [AW_Project01_DW_DB].Option1.DimProductSubcateories

              Add Constraint PK_DimProductSubcateories Primary Key (ProductSubcategoryKey);

              -- 3)[Option1].[DimProductCategories]

              Alter Table [AW_Project01_DW_DB].Option1.DimProductCategories

                     Add Constraint PK_DimProductCategories Primary Key (ProductCategoryKey);

Go

-- Dates Dimension tables

-- 1)[dbo].[DimDates]

Alter Table [AW_Project01_DW_DB].Option1.DimDates

       Add Constraint PK_DimDates Primary Key (DateKey);

Go

-- Sales Fact table

-- 1) [Option1].[FactSales]

Alter Table [AW_Project01_DW_DB].Option1.FactSales

       Add Constraint PK_FactSales Primary Key (SalesOrderID, SalesOrderDetailID, OrderDateKey, CustomerAccountKey, ProductKey);

-- Person Address Fact Bridge table

-- 1) [Option1].[FactPersonAddresses]

Alter Table [AW_Project01_DW_DB].Option1.FactCustomersPersonsAddresses

       Add Constraint PK_FactCustomersPersonsAddresses Primary Key (CustomerPersonKey, CustomerAddressKey, AddressTypeKey);

-------------------------------------------------------------------------------------

-- Next, we add Foreign Keys to the tables

-------------------------------------------------------------------------------------

-- Sales Fact Table

-- 1)[Option1].[FactSales] to DimDates

ALTER TABLE Option1.FactSales ADD CONSTRAINT

       FK_FactSales_DimDates FOREIGN KEY (OrderDateKey)

              REFERENCES Option1.DimDates(DateKey);

       -- 2) [Option1].[FactSales] to DimCustomerAccounts

       ALTER TABLE Option1.FactSales ADD CONSTRAINT

              FK_FactSales_DimCustomerAccounts FOREIGN KEY (CustomerAccountKey)

                     REFERENCES Option1.DimCustomerAccounts(CustomerAccountKey);

              -- 3) [Option1].[FactSales] to [Option1].[DimProducts]

              ALTER TABLE Option1.FactSales ADD CONSTRAINT

                     FK_FactSales_DimProducts FOREIGN KEY (ProductKey)

                           REFERENCES Option1.DimProducts(ProductKey);

-- Sales Person Addresses Fact Table

-- 1)[Option1].[FactPersonAddresses]to DimCustomerPersons

ALTER TABLE Option1.FactCustomersPersonsAddresses ADD CONSTRAINT

       FK_FactCustomersPersonsAddresses_DimCustomerPersons FOREIGN KEY ([CustomerPersonKey])

              REFERENCES Option1.DimCustomerPersons([CustomerPersonKey]);

       -- 2)[Option1].[FactPersonAddresses]to DimCustomerAddresses

       ALTER TABLE Option1.FactCustomersPersonsAddresses ADD CONSTRAINT

              FK_FactCustomersPersonsAddresses_DimCustomerAddresses FOREIGN KEY ([CustomerAddressKey])

                     REFERENCES Option1.DimCustomerAddresses([CustomerAddressKey]);

       -- 3)[Option1].[FactPersonAddresses]to DimAddressTypes

                     ALTER TABLE Option1.FactCustomersPersonsAddresses ADD CONSTRAINT

                           FK_FactCustomersPersonsAddresses_DimAddressTypes FOREIGN KEY(AddressTypeKey)

                                  REFERENCES Option1.DimAddressTypes(AddressTypeKey);

-- Customers Dimension tables

-- 1)[Option1].[DimCustomerAccounts]To DimCustomerPersons

ALTER TABLE Option1.DimCustomerAccounts ADD CONSTRAINT

       FK_DimCustomerAccounts_DimCustomerPersons FOREIGN KEY (CustomerPersonKey)

              REFERENCES Option1.DimCustomerPersons(CustomerPersonKey);

-- CustomerAddresses Dimension tables

-- 1)[Option1].[DimCustomerAddresses] To DimCustomerStateProvinces

ALTER TABLE Option1.DimCustomerAddresses ADD CONSTRAINT

       FK_DimCustomerAddresses_DimCustomerStateProvinces FOREIGN KEY (CustomerStateProvinceKey)

              REFERENCES Option1.DimCustomerStateProvinces(CustomerStateProvinceKey);

               

       -- 2)[Option1].[DimCustomerStateProvinces] To DimCustomerCountryRegions

ALTER TABLE Option1.DimCustomerStateProvinces ADD CONSTRAINT

       FK_DimCustomerStateProvinces_DimCustomerCountryRegions FOREIGN KEY (CustomerCountryRegionKey)

              REFERENCES Option1.DimCustomerCountryRegions(CustomerCountryRegionKey);

               

-- Products Dimension tables

-- 1)[Option1].[DimProducts] to DimProductSubcateories

ALTER TABLE Option1.DimProducts ADD CONSTRAINT

       FK_DimProducts_DimProductSubcategories FOREIGN KEY

       (ProductSubcategoryKey) REFERENCES Option1.DimProductSubcateories(ProductSubcategoryKey);

       -- 2)[Option1].[DimProductSubcategories] to DimProductCateories

       ALTER TABLE Option1.DimProductSubcateories ADD CONSTRAINT

              FK_DimProductSubcateories_DimProductCategories FOREIGN KEY

              (ProductCategoryKey) REFERENCES Option1.DimProductCategories(ProductCategoryKey);

Go

--------------------------------------------------------------------------------------

/************-- Option 1 (Close to original, with snowflake design) --************/

--------------------------------------------------------------------------------------

--------------------------------------------------------------------------------------

/************-- Option 2 (Close to original, but with star design) --************/

--------------------------------------------------------------------------------------

Go

Create Schema Option2

Go

-------------------------------------------------

--** Customers Dimension (Regular Dimenison) **--

-- These are ALL the Customer we have. --

-------------------------------------------------

       -- 1) DimCustomerAccounts (first level from FactSales)

       SELECT Distinct

                Identity(int, 1,1) AS CustomerAccountKey --Surrogate Key

              , 0 AS [CustomerID] -- Original Key

              , AccountNumber AS [CustomerAccountNumber] -- Name Column

              , 0 AS [CustomerPersonID] -- Original Key

              , FirstName + '' + LastName AS [CustomerPersonName] -- Name Column

              , -1 AS SCDStartDate -- SCD Type 2 column

              , Null AS SCDEndDate -- SCD Type 2 column

              , 'Yes' AS IsCurrent -- SCD Type 2 column

       Into [AW_Project01_DW_DB].Option2.[DimCustomerAccounts]

       FROM [AW_Project01_OLTP_DB]..Customer AS C

       JOIN [AW_Project01_OLTP_DB]..Person AS P

              ON C.PersonID = P.BusinessEntityID

       WHERE Null = Null;

       Go

--------------------------------------------------------------------------------

--** [AddressTypes Dimension (Many To Many Dimension to Customers Dimension)] **--

-- These are ALL the Address Types we use. --

--------------------------------------------------------------------------------

       -- 1) DimAddressTypes (first level from FactCustomersPersonsAddresses)

       SELECT Distinct

                Identity(int, 1,1) AS AddressTypeKey --Surrogate Key              

              , 0 AS AddressTypeID -- Original Key

              , Name AS [AddressName] -- Name Column

              , -1 AS SCDStartDate -- SCD Type 2 column

              , Null AS SCDEndDate -- SCD Type 2 column

              , 'Yes' AS IsCurrent -- SCD Type 2 column

       Into [AW_Project01_DW_DB].Option2.[DimAddressTypes]

       FROM [AW_Project01_OLTP_DB]..AddressType

       WHERE Null = Null;

       Go

-------------------------------------------------------------------------------------

--** [CustomerAddresses Dimension (Many To Many Dimension to Customers Dimension)] **--

-- These are ONLY Address for our customers. --

-------------------------------------------------------------------------------------

       -- 1) DimCustomerAddresses (first level from FactCustomersPersonsAddresses)

       SELECT Distinct

                Identity(int, 1,1) AS CustomerAddressKey --Surrogate Key                

              , 0 AS [CustomerAddressID] -- Original Key

              , City AS [CustomerAddressCityName] -- Name Column

              , SP.StateProvinceID AS [CustomerStateProvinceID] -- Original Key

              , SP.Name AS [CustomerStateProvinceName] -- Name Column

              , C.CountryRegionCode AS [CustomerCountryRegionCode] -- Original Key

              , C.Name AS [CustomerCountryRegionName] -- Name Column

              , -1 AS SCDStartDate -- SCD Type 2 column

              , Null AS SCDEndDate -- SCD Type 2 column

              , 'Yes' AS IsCurrent -- SCD Type 2 column

       Into [AW_Project01_DW_DB].Option2.DimCustomerAddresses

       FROM [AW_Project01_OLTP_DB]..Address AS A

       JOIN[AW_Project01_OLTP_DB]..StateProvince AS SP

              ON A.StateProvinceID = SP.StateProvinceID

       JOIN [AW_Project01_OLTP_DB]..CountryRegion AS C

              ON SP.CountryRegionCode = C.CountryRegionCode

       WHERE Null = Null;

       Go

-------------------------------------------------------------------------------------

--** [Products Dimension (Regular)] **--

-- These are ALL the products we have. --

-------------------------------------------------------------------------------------

       -- 1) DimProducts (first level dimension table) --

              SELECT Distinct

                Identity(int, 1,1) AS ProductKey --Surrogate Key      

              , 0 AS ProductID -- Original Key

              , P.Name AS [ProductName] -- Name Column

              , ListPrice AS [ProductListPrice] -- Additonal Attribute

              , 0 AS ProductSubcategoryID -- Original Key

              , PS.Name AS [ProductSubcategoryName] -- Name Column  

              , 0 AS ProductCategoryID -- Original Key

              , PC.Name AS [ProductCategoryName] -- Name Column     

              , -1 AS SCDStartDate -- SCD Type 2 column

              , Null AS SCDEndDate -- SCD Type 2 column

              , 'Yes' AS IsCurrent -- SCD Type 2 column

       Into [AW_Project01_DW_DB].Option2.DimProducts

       FROM [AW_Project01_OLTP_DB]..Products AS P

       JOIN [AW_Project01_OLTP_DB]..ProductSubcategory AS PS

              ON P.ProductSubcategoryID = PS.ProductSubcategoryID

       JOIN [AW_Project01_OLTP_DB]..ProductCategory AS PC

              ON PS.ProductCategoryID = PC.ProductCategoryID

       WHERE Null = Null;

       Go

-------------------------------------------------------------------------------------

--** [Date Dimension (Regular)] **--

-- These are ALL the Date we have between 2000 and 2010 --

-------------------------------------------------------------------------------------

        --1) DimDates (first level dimension table) --

       CREATE TABLE [Option2].[DimDates](

               [DateKey] [int] IDENTITY(1,1) --Surrogate Key          

              ,[FullUSADate] [datetime] NOT NULL UNIQUE -- Original Key

              ,[DateName] [nvarchar](50) NULL -- Name Column

              ,[MonthKey] [int] NOT NULL -- Additonal Attribute

              ,[MonthName] [nvarchar](50) NOT NULL -- Additonal Attribute

              ,[QuarterKey] [int] NOT NULL -- Additonal Attribute

              ,[QuarterName] [nvarchar](50) NOT NULL -- Additonal Attribute

              ,[YearKey] [int] NOT NULL -- Additonal Attribute

              ,[YearName] [nvarchar](50) NOT NULL -- Additonal Attribute

       );

       Go

-------------------------------------------------------------------------------------

--** [The Sale Fact table] **--

-------------------------------------------------------------------------------------

       -- 1) FactSales (Primary Fact table of the Sales Data Mart)

       SELECT Distinct

         SalesOrderHeader.SalesOrderID

       , SalesOrderDetail.SalesOrderDetailID

       , 0 AS OrderDateKey

       , 0 AS CustomerAccountKey

       , 0 AS ProductKey

       , SalesOrderDetail.OrderQty

       , SalesOrderDetail.UnitPrice

       -- NOTE: No SCD columns are needed IF we do not allow people to change the data

       Into [AW_Project01_DW_DB].Option2.[FactSales]

       FROM [AW_Project01_OLTP_DB]..SalesOrderDetail

       INNER JOIN [AW_Project01_OLTP_DB]..SalesOrderHeader

         ON SalesOrderDetail.SalesOrderID = SalesOrderHeader.SalesOrderID

       WHERE Null = Null;

       Go

-------------------------------------------------------------------------------------

--** [The Person to Address Fact table] **--

-------------------------------------------------------------------------------------

       -- 2) FactCustomersPersonsAddresses (Bridge of DimCustomers to DimAddresses)

       SELECT Distinct

         0 AS CustomerAccountKey

       , 0 AS CustomerAddressKey

       , 0 AS AddressTypeKey

       -- NOTE: No SCD columns are needed IF we do not allow people to change the data

       Into [AW_Project01_DW_DB].Option2.[FactCustomersPersonsAddresses]

       FROM [AW_Project01_OLTP_DB]..PersonAddress

       WHERE Null = Null;

       Go

-------------------------------------------------------------------------------------

-- Now we will add Primary Keys to the tables

-------------------------------------------------------------------------------------

-- Customers Dimension tables

-- 1)[Option1].[DimCustomers]

Alter Table [AW_Project01_DW_DB].Option2.DimCustomerAccounts

       Add Constraint PK_DimCustomerAccounts Primary Key (CustomerAccountKey);

Go

-- AddressTypes Dimension

-- 1)[Option1].[DimAddressTypes]

Alter Table [AW_Project01_DW_DB].Option2.DimAddressTypes

       Add Constraint PK_DimAddressTypes Primary Key (AddressTypeKey);

Go

-- Addresses Dimension tables

-- 1)[Option1].[DimCustomerAddresses]

Alter Table [AW_Project01_DW_DB].Option2.DimCustomerAddresses

       Add Constraint PK_DimCustomerAddresses Primary Key (CustomerAddressKey);

Go

-- Products Dimension tables

-- 1)[Option1].[DimProducts]

Alter Table [AW_Project01_DW_DB].Option2.DimProducts

       Add Constraint PK_DimProducts Primary Key (ProductKey);

Go

-- Dates Dimension tables

-- 1)[dbo].[DimDates]

Alter Table [AW_Project01_DW_DB].Option2.DimDates

       Add Constraint PK_DimDates Primary Key (DateKey);

Go

-- Sales Fact table

-- 1) [Option1].[FactSales]

Alter Table [AW_Project01_DW_DB].Option2.FactSales

       Add Constraint PK_FactSales Primary Key (SalesOrderID, SalesOrderDetailID, OrderDateKey, CustomerAccountKey, ProductKey);

-- Person Address Fact Bridge table

-- 1) [Option1].[FactPersonAddresses]

-- NOTE: When we collaped the Person into the Customer tables

-- we Pimary key switches from CustomerPersonKey to CustomerAccountKey

Alter Table [AW_Project01_DW_DB].Option2.FactCustomersPersonsAddresses

       Add Constraint PK_FactCustomersPersonsAddresses Primary Key (CustomerAccountKey, CustomerAddressKey);

-------------------------------------------------------------------------------------

-- Next, we add Foreign Keys to the tables

-------------------------------------------------------------------------------------

-- Sales Fact Table

-- 1)[Option1].[FactSales] to DimDates

ALTER TABLE Option2.FactSales ADD CONSTRAINT

       FK_FactSales_DimDates FOREIGN KEY (OrderDateKey)

              REFERENCES Option2.DimDates(DateKey);

       -- 2) [Option1].[FactSales] to DimCustomerAccounts

       ALTER TABLE Option2.FactSales ADD CONSTRAINT

              FK_FactSales_DimCustomerAccounts FOREIGN KEY (CustomerAccountKey)

                     REFERENCES Option2.DimCustomerAccounts(CustomerAccountKey);

              -- 3) [Option1].[FactSales] to [Option1].[DimProducts]

              ALTER TABLE Option2.FactSales ADD CONSTRAINT

                     FK_FactSales_DimProducts FOREIGN KEY (ProductKey)

                           REFERENCES Option2.DimProducts(ProductKey);

-- Sales Person Addresses Fact Table

-- 1)[Option1].[FactPersonAddresses]to DimCustomerPersons

-- NOTE: An FK will not work without unique column data so we use the PK of the child table.

  ALTER TABLE Option2.FactCustomersPersonsAddresses ADD CONSTRAINT

         FK_FactCustomersPersonsAddresses_DimCustomerAccounts FOREIGN KEY ([CustomerAccountKey])

              REFERENCES Option2.[DimCustomerAccounts]([CustomerAccountKey]);

       -- 2)[Option1].[FactPersonAddresses]to DimCustomerAddresses

       ALTER TABLE Option2.FactCustomersPersonsAddresses ADD CONSTRAINT

              FK_FactCustomersPersonsAddresses_DimCustomerAddresses FOREIGN KEY ([CustomerAddressKey])

                     REFERENCES Option2.DimCustomerAddresses([CustomerAddressKey]);

                     -- 3)[Option1].[FactPersonAddresses]to DimAddressTypes

                     ALTER TABLE Option2.FactCustomersPersonsAddresses ADD CONSTRAINT

                           FK_FactCustomersPersonsAddresses_DimAddressTypes FOREIGN KEY(AddressTypeKey)

                                  REFERENCES Option2.DimAddressTypes(AddressTypeKey);

Go

-----------------------------------------------------------------------------------

/************-- Option 2 (Close to original, but with star design) --************/

-----------------------------------------------------------------------------------