Arcane Code

/*-----------------------------------------------------------------------------------------------*/
/* Updating AdventureWorks2012 for Today                                                         */
/*                                                                                               */
/* Robert C. Cain, http://arcanecode.com @ArcaneCode                                             */
/*                                                                                               */
/* Script Copyright (c) 2013 by Robert C. Cain                                                   */
/* AdventureWorks database Copyright (c) Microsoft.                                              */
/*                                                                                               */
/* This script will make a backup of the AdventureWorks2012DW database, then copy and restore it */
/* as AdventureWorksDW2013. It will then update it for current dates. 2008 now becomes 2013,     */
/* 2007 is now 2012, and so forth. This script is dependent on the AdventureWorks2012DW sample   */
/* database already being installed. It won't change AdventureWorksDW2012 in anyway.             */
/*                                                                                               */
/* Be warned, if AdventureWorksDW2013 exists, it will be deleted as part of this process.        */
/*                                                                                               */
/*-----------------------------------------------------------------------------------------------*/

PRINT 'Updating AdventureWorks2012 for Today - Starting'
GO

/*-----------------------------------------------------------------------------------------------*/
/* Step 1 - Make a copy of AdventureWorksDW2012 and restore as AdventureWorksDW2013              */
/*-----------------------------------------------------------------------------------------------*/
SET NOCOUNT ON

USE [master]

-- Step 1.1. Make a backup of AdventureWorksDW2012 ----------------------------------------------
PRINT 'Backing up AdventureWorksDW2012'
GO

BACKUP DATABASE [AdventureWorksDW2012] 
    TO DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Backup\AdventureWorksDW2012.bak' 
  WITH NOFORMAT, 
       INIT,  
       NAME = N'AdventureWorksDW2012-Full Database Backup', 
       SKIP, 
       NOREWIND, 
       NOUNLOAD,  
       STATS = 10
GO


-- Step 1.2. Delete the database AdventureWorksDW2013 if it exists ------------------------------
PRINT 'Deleting AdventureWorksDW2013, if it exists'
GO

IF (EXISTS (SELECT 1 
              FROM master.dbo.sysdatabases 
             WHERE name = 'AdventureWorksDW2013' )
   )
   EXEC msdb.dbo.sp_delete_database_backuphistory @database_name = N'AdventureWorksDW2013'
GO

IF (EXISTS (SELECT 1 
              FROM master.dbo.sysdatabases 
             WHERE name = 'AdventureWorksDW2013' )
   )
   DROP DATABASE [AdventureWorksDW2013]
GO

-- Step 1.3. Restore the database to a new copy -------------------------------------------------
PRINT 'Restoring AdventureWorksDW2012 to AdventureWorksDW2013'
GO

RESTORE DATABASE [AdventureWorksDW2013] 
   FROM  DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Backup\AdventureWorksDW2012.bak' 
   WITH  FILE = 1,  
   MOVE N'AdventureWorksDW2012_Data' 
     TO N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\AdventureWorksDW2013_Data.mdf',  
   MOVE N'AdventureWorksDW2012_Log' 
     TO N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\AdventureWorksDW2013_log.ldf',  
        NOUNLOAD,  STATS = 5

GO

PRINT 'Done Creating AdventureWorksDW2013'
GO



/*-----------------------------------------------------------------------------------------------*/
/* Step 2. Create a helper function to convert dates to a YYYYMMDD format Date Id.               */
/*-----------------------------------------------------------------------------------------------*/

USE [AdventureWorksDW2013]
GO

IF EXISTS (SELECT [name] FROM [sys].[all_objects] WHERE [name] = 'DateToDateId')
  DROP FUNCTION [dbo].[DateToDateId];
GO

CREATE FUNCTION [dbo].[DateToDateId]
(
  @Date DATETIME
)
RETURNS INT
AS
BEGIN

  DECLARE @DateId  AS INT
  DECLARE @TodayId AS INT

  SET @TodayId = YEAR(GETDATE()) * 10000
               + MONTH(GETDATE()) * 100
               + DAY(GETDATE())         

  -- If the date is missing, or a placeholder for a missing date, set to the Id for missing dates
  -- Else convert the date to an integer
  IF @Date IS NULL OR @Date = '1900-01-01' OR @Date = -1
    SET @DateId = -1  
  ELSE
    BEGIN
      SET @DateId = YEAR(@Date) * 10000
                  + MONTH(@Date) * 100
                  + DAY(@Date)         
    END  
  
  -- If there's any data prior to 2000 it was incorrectly entered, mark it as missing
  IF @DateId BETWEEN 0 AND 19991231 
    SET @DateId = -1

  -- Commented out for this project as future dates are OK
  -- If the date is in the future, don't allow it, change to missing
  -- IF @DateId > @TodayId 
  --   SET @DateId = -1

  RETURN @DateId

END

GO




/*-----------------------------------------------------------------------------------------------*/
/* Step 3. Add new dates to the dbo.DimDate table.                                               */
/*-----------------------------------------------------------------------------------------------*/
PRINT 'Adding new dates to dbo.DimDate'
GO

SET NOCOUNT ON

-- Later we will be writing an INSERT INTO... SELECT FROM to insert the new record. I want to 
-- join the day and month name memory variable tables, but need to have something to join to. 
-- Since everything is calculated, we'll just create this little bogus table to have something
-- to select from.
DECLARE @BogusTable TABLE
  ( PK TINYINT)

INSERT INTO @BogusTable SELECT 1;


-- Create a table variable to hold the days of the week with their various language versions
DECLARE @DayNameTable TABLE
  ( [DayNumberOFWeek]      TINYINT
  , [EnglishDayNameOfWeek] NVARCHAR(10)
  , [SpanishDayNameOfWeek] NVARCHAR(10)
  , [FrenchDayNameOfWeek]  NVARCHAR(10)
  )

INSERT INTO @DayNameTable
SELECT DISTINCT 
       [DayNumberOFWeek]      
     , [EnglishDayNameOfWeek] 
     , [SpanishDayNameOfWeek] 
     , [FrenchDayNameOfWeek]  
  FROM dbo.DimDate

-- Create a month table to hold the months and their language versions.
DECLARE @MonthNameTable TABLE
  ( [MonthNumberOfYear] TINYINT
  , [EnglishMonthName]  NVARCHAR(10)
  , [SpanishMonthName]  NVARCHAR(10)
  , [FrenchMonthName]   NVARCHAR(10)
  )

INSERT INTO @MonthNameTable
SELECT DISTINCT
       [MonthNumberOfYear] 
     , [EnglishMonthName]  
     , [SpanishMonthName]  
     , [FrenchMonthName]   
  FROM dbo.DimDate

-- This is the start and end date ranges to use to populate the 
-- dbo.DimDate dimension. Change if it's 2014 and you run across this script.
DECLARE @FromDate AS DATE = '2011-01-01'
DECLARE @ThruDate AS DATE = '2013-12-31'

-- CurrentDate will be incremented each time through the loop below.
DECLARE @CurrentDate AS DATE
SET @CurrentDate = @FromDate

-- FiscalDate will be set six months into the future from the CurrentDate
DECLARE @FiscalDate  AS DATE

-- Now we simply loop over every date between the From and Thru, inserting the
-- calculated values into DimDate.
WHILE @CurrentDate <= @ThruDate
BEGIN

  SET @FiscalDate = DATEADD(m, 6, @CurrentDate)

  INSERT INTO dbo.DimDate
  SELECT [dbo].[DateToDateId](@CurrentDate)
       , @CurrentDate
       , DATEPART(dw, @CurrentDate) AS DayNumberOFWeek
       , d.EnglishDayNameOfWeek
       , d.SpanishDayNameOfWeek
       , d.FrenchDayNameOfWeek
       , DAY(@CurrentDate) AS DayNumberOfMonth
       , DATEPART(dy, @CurrentDate) AS DayNumberOfYear
       , DATEPART(wk, @CurrentDate) AS WeekNumberOfYear
       , m.EnglishMonthName
       , m.SpanishMonthName
       , m.FrenchMonthName
       , MONTH(@CurrentDate) AS MonthNumberOfYear
       , DATEPART(q, @CurrentDate) AS CalendarQuarter
       , YEAR(@CurrentDate) AS CalendarYear
       , IIF(MONTH(@CurrentDate) < 7, 1, 2) AS CalendarSemester
       , DATEPART(q, @FiscalDate) AS FiscalQuarter
       , YEAR(@FiscalDate) AS FiscalYear
       , IIF(MONTH(@FiscalDate) < 7, 1, 2) AS FiscalSemester
    FROM @BogusTable
    JOIN @DayNameTable d
      ON DATEPART(dw, @CurrentDate) = d.[DayNumberOFWeek]
    JOIN @MonthNameTable m
      ON MONTH(@CurrentDate) = m.MonthNumberOfYear

  SET @CurrentDate = DATEADD(d, 1, @CurrentDate)
END
GO

-- If you want to verify you can uncomment this line.
-- SELECT * FROM dbo.DimDate WHERE DateKey > 20110000

PRINT 'Done adding new dates to dbo.DimDate'
GO





/*-----------------------------------------------------------------------------------------------*/
/* Step 4. Update the Fact Tables with the new dates.                                            */
/*-----------------------------------------------------------------------------------------------*/


PRINT 'Update Fact Tables'
GO

SET NOCOUNT ON

-- To move forward five years, we simply add 50,000 to the date key

-- 4.1 FactFinance ------------------------------------------------------------------------------
PRINT '  FactFinance'
GO

UPDATE [dbo].[FactFinance]
   SET [DateKey] = [DateKey] + 50000;


-- 4.2 FactInternetSales ------------------------------------------------------------------------
PRINT '  FactInternetSales'
GO

-- There are a few rows where the due date is on leap year. Update these to back off a day 
-- so the date add works OK
UPDATE [dbo].[FactInternetSales]
   SET [OrderDateKey] = 20080228
     , [OrderDate] = '2008-02-28'
 WHERE [OrderDateKey] = 20080229

UPDATE [dbo].[FactInternetSales]
   SET [DueDateKey] = 20080228
     , [DueDate] = '2008-02-28'
 WHERE [DueDateKey] = 20080229

UPDATE [dbo].[FactInternetSales]
   SET [ShipDateKey] = 20080228
     , [ShipDate] = '2008-02-28'
 WHERE [ShipDateKey] = 20080229

-- Now update the rest of the days. 
UPDATE [dbo].[FactInternetSales]
   SET [OrderDateKey] = [OrderDateKey] + 50000
     , [DueDateKey] = [DueDateKey] + 50000
     , [ShipDateKey] = [ShipDateKey] + 50000
     , [OrderDate] = DATEADD(yy, 5, [OrderDate])
     , [DueDate] = DATEADD(yy, 5, [DueDate])
     , [ShipDate] = DATEADD(yy, 5, [ShipDate])


-- 4.3 FactResellerSales ------------------------------------------------------------------------
PRINT '  FactResellerSales'
GO

-- As with Internet Sales, there are rows where the due date is on leap year. 
-- Update these to back off a day so the date add works OK
UPDATE [dbo].[FactResellerSales]
   SET [OrderDateKey] = 20080228
     , [OrderDate] = '2008-02-28'
 WHERE [OrderDateKey] = 20080229

UPDATE [dbo].[FactResellerSales]
   SET [DueDateKey] = 20080228
     , [DueDate] = '2008-02-28'
 WHERE [DueDateKey] = 20080229

UPDATE [dbo].[FactResellerSales]
   SET [ShipDateKey] = 20080228
     , [ShipDate] = '2008-02-28'
 WHERE [ShipDateKey] = 20080229

-- Now update the table
UPDATE [dbo].[FactResellerSales]
   SET [OrderDateKey] = [OrderDateKey] + 50000
     , [DueDateKey] = [DueDateKey] + 50000
     , [ShipDateKey] = [ShipDateKey] + 50000
     , [OrderDate] = DATEADD(yy, 5, [OrderDate])
     , [DueDate] = DATEADD(yy, 5, [DueDate])
     , [ShipDate] = DATEADD(yy, 5, [ShipDate])

-- 4.4 FactSalesQuota ---------------------------------------------------------------------------
PRINT '  FactSalesQuota'
GO

UPDATE [dbo].[FactSalesQuota] 
   SET [DateKey] = [DateKey] + 50000

-- 4.5 FactSurveyResponse -----------------------------------------------------------------------
PRINT '  FactSurveyResponse'
GO

UPDATE [dbo].[FactSurveyResponse]
   SET [DateKey] = [DateKey] + 50000

-- 4.6 FactCallCenter ---------------------------------------------------------------------------
PRINT '  FactCallCenter'
GO

-- All the rows in call center have a 2010 date, just add 3 years to make these 2013
UPDATE [dbo].[FactCallCenter]
   SET [DateKey] = [DateKey] + 30000


-- 4.7 FactCurrencyRate -------------------------------------------------------------------------
PRINT '  FactCurrencyRate'
GO

-- Because the DateKey is part of the PK, we have to drop the key before we can update it
ALTER TABLE [dbo].[FactCurrencyRate] DROP CONSTRAINT [PK_FactCurrencyRate_CurrencyKey_DateKey]
GO

-- Shift the 2008 Leap Year days to 2012 Leap Year
UPDATE [dbo].[FactCurrencyRate]
   SET [DateKey] = 20120229
 WHERE [DateKey] = 20080229

-- Update everything except the leap year we fixed already
UPDATE [dbo].[FactCurrencyRate]
   SET [DateKey] = [DateKey] + 50000
 WHERE [DateKey] <> 20120229

-- Add the PK back
ALTER TABLE [dbo].[FactCurrencyRate] 
  ADD CONSTRAINT [PK_FactCurrencyRate_CurrencyKey_DateKey] PRIMARY KEY CLUSTERED 
      ( [CurrencyKey] ASC,
        [DateKey] ASC
      )
 WITH ( PAD_INDEX = OFF
      , STATISTICS_NORECOMPUTE = OFF
      , SORT_IN_TEMPDB = OFF
      , IGNORE_DUP_KEY = OFF
      , ONLINE = OFF
      , ALLOW_ROW_LOCKS = ON
      , ALLOW_PAGE_LOCKS = ON
      ) ON [PRIMARY]
GO


-- 4.8 FactProductInventory ---------------------------------------------------------------------
PRINT '  FactProductInventory'
GO

-- As with the previous step, the date is part of the primary key, so we need to drop it first.
ALTER TABLE [dbo].[FactProductInventory] DROP CONSTRAINT [PK_FactProductInventory]
GO

-- Shift the 2008 Leap Year days to 2012 Leap Year
UPDATE [dbo].[FactProductInventory]
   SET [DateKey] = 20120229
 WHERE [DateKey] = 20080229

-- Update everything except the leap year we fixed already
UPDATE [dbo].[FactProductInventory]
   SET [DateKey] = [DateKey] + 50000
 WHERE [DateKey] <> 20120229
 
-- Add the PK back
ALTER TABLE [dbo].[FactProductInventory] 
  ADD CONSTRAINT [PK_FactProductInventory] PRIMARY KEY CLUSTERED 
      (  [ProductKey] ASC
      , [DateKey] ASC
      )
 WITH ( PAD_INDEX = OFF
      , STATISTICS_NORECOMPUTE = OFF
      , SORT_IN_TEMPDB = OFF
      , IGNORE_DUP_KEY = OFF
      , ONLINE = OFF
      , ALLOW_ROW_LOCKS = ON
      , ALLOW_PAGE_LOCKS = ON
      ) ON [PRIMARY]
GO

PRINT 'Done updating the Fact tables'
GO



/*-----------------------------------------------------------------------------------------------*/
/* Step 5. Cleanup, remove the helper function we added earlier.                                 */
/*-----------------------------------------------------------------------------------------------*/
PRINT 'Removing Helper Function'
GO

IF EXISTS (SELECT 1 FROM [sys].[all_objects] WHERE [name] = 'DateToDateId')
  DROP FUNCTION [dbo].[DateToDateId];
GO

/*-----------------------------------------------------------------------------------------------*/
/* All done!                                                                                     */
/*-----------------------------------------------------------------------------------------------*/
PRINT 'Updating AdventureWorks2012 for Today - Completed'
GO