Back in May I did a post called “Updating AdventureWorksDW2012 for Today”. In that post I included a script that would take the AdventureWorksDW2012 sample database, available on CodePlex, and convert it to use current dates.
As part of my job at PragmaticWorks I teach classes on SQL Server Analysis Services. My upcoming class is using SQL Server 2008R2, and as such I wanted to update the 2008R2 version of AdventureWorksDW. I did find some subtle differences between the 2008R2 and 2012 versions of the Adventure Works Data Warehouse that I wanted to post an updated version of the script to use with 2008R2.
If you don’t have the AdventureWorksDW2008R2 data warehouse database, you can obtain it from http://msftdbprodsamples.codeplex.com/releases/view/59211 or go to CodePlex and search for SQL Server 2008 Sample Databases. Note there are several versions of Adventure Works here. The file for the data warehouse is either “AdventureWorks 2008R2 DW Script”, which is a T-SQL script that creates and populates the 2008R2 data warehouse, or “AdventureWorksDW2008R2 Data File” which is a database file you’ll need to reattach.
The structure of the script is basically the same as the one in the first post, so I’ll let you refer back to it for the explanations. Without further ado, here is the script for AdventureWorksDW2008R2, which will create a new AdventureWorksDW2013R2 database.
/*-----------------------------------------------------------------------------------------------*/
/* Updating AdventureWorks2008R2 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 AdventureWorks2008R2DW sample */
/* database already being installed. It won't change AdventureWorksDW2008R2 in anyway. */
/* */
/* Be warned, if AdventureWorksDW2013R2 exists, it will be deleted as part of this process. */
/* */
/*-----------------------------------------------------------------------------------------------*/
PRINT 'Updating AdventureWorksDW2008R2 for Today - Starting'
GO
/*-----------------------------------------------------------------------------------------------*/
/* Step 1 - Make a copy of AdventureWorksDW2008R2 and restore as AdventureWorksDW2013 */
/*-----------------------------------------------------------------------------------------------*/
SET NOCOUNT ON
USE [master]
-- Step 1.1. Make a backup of AdventureWorksDW2008R2 ----------------------------------------------
PRINT 'Backing up AdventureWorksDW2008R2'
GO
BACKUP DATABASE [AdventureWorksDW2008R2]
TO DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL2008R2\MSSQL\Backup\AdventureWorksDW2008R2.bak'
WITH NOFORMAT,
INIT,
NAME = N'AdventureWorksDW2008R2-Full Database Backup',
SKIP,
NOREWIND,
NOUNLOAD,
STATS = 10
GO
-- Step 1.2. Delete the database AdventureWorksDW2013 if it exists ------------------------------
PRINT 'Deleting AdventureWorksDW2013R2, if it exists'
GO
IF (EXISTS (SELECT 1
FROM master.dbo.sysdatabases
WHERE name = 'AdventureWorksDW2013R2' )
)
EXEC msdb.dbo.sp_delete_database_backuphistory @database_name = N'AdventureWorksDW2013R2'
GO
IF (EXISTS (SELECT 1
FROM master.dbo.sysdatabases
WHERE name = 'AdventureWorksDW2013R2' )
)
DROP DATABASE [AdventureWorksDW2013R2]
GO
-- Step 1.3. Restore the database to a new copy -------------------------------------------------
PRINT 'Restoring AdventureWorksDW2008R2 to AdventureWorksDW2013R2'
GO
RESTORE DATABASE [AdventureWorksDW2013R2]
FROM DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL2008R2\MSSQL\Backup\AdventureWorksDW2008R2.bak'
WITH FILE = 1,
MOVE N'AdventureWorksDW2008R2_Data'
TO N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL2008R2\MSSQL\DATA\AdventureWorksDW2013R2_Data.mdf',
MOVE N'AdventureWorksDW2008R2_Log'
TO N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL2008R2\MSSQL\DATA\AdventureWorksDW2013R2_log.ldf',
NOUNLOAD, STATS = 5
GO
PRINT 'Done Creating AdventureWorksDW2013R2'
GO
/*-----------------------------------------------------------------------------------------------*/
/* Step 2. Create a helper function to convert dates to a YYYYMMDD format Date Id. */
/*-----------------------------------------------------------------------------------------------*/
USE [AdventureWorksDW2013R2]
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
-- Some dates for 2010 are already there so we have to work around it in two passes
-- FiscalDate will be set six months into the future from the CurrentDate
DECLARE @FiscalDate AS DATE
-- Pass 1
DECLARE @FromDate AS DATE = '2010-01-01'
DECLARE @ThruDate AS DATE = '2010-10-31'
DECLARE @CurrentDate AS DATE
-- CurrentDate will be incremented each time through the loop below.
SET @CurrentDate = @FromDate
-- 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
, CASE WHEN MONTH(@CurrentDate) < 7
THEN 1
ELSE 2
END AS CalendarSemester
, DATEPART(q, @FiscalDate) AS FiscalQuarter
, YEAR(@FiscalDate) AS FiscalYear
, CASE WHEN MONTH(@FiscalDate) < 7
THEN 1
ELSE 2
END 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
-- Pass 2
-- 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.
SET @FromDate = '2010-12-01'
SET @ThruDate = '2013-12-31'
-- CurrentDate will be incremented each time through the loop below.
SET @CurrentDate = @FromDate
-- 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
, CASE WHEN MONTH(@CurrentDate) < 7
THEN 1
ELSE 2
END AS CalendarSemester
, DATEPART(q, @FiscalDate) AS FiscalQuarter
, YEAR(@FiscalDate) AS FiscalYear
, CASE WHEN MONTH(@FiscalDate) < 7
THEN 1
ELSE 2
END 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
WHERE [OrderDateKey] = 20080229
UPDATE [dbo].[FactInternetSales]
SET [DueDateKey] = 20080228
WHERE [DueDateKey] = 20080229
UPDATE [dbo].[FactInternetSales]
SET [ShipDateKey] = 20080228
WHERE [ShipDateKey] = 20080229
-- Now update the rest of the days.
UPDATE [dbo].[FactInternetSales]
SET [OrderDateKey] = [OrderDateKey] + 50000
, [DueDateKey] = [DueDateKey] + 50000
, [ShipDateKey] = [ShipDateKey] + 50000
-- 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
WHERE [OrderDateKey] = 20080229
UPDATE [dbo].[FactResellerSales]
SET [DueDateKey] = 20080228
WHERE [DueDateKey] = 20080229
UPDATE [dbo].[FactResellerSales]
SET [ShipDateKey] = 20080228
WHERE [ShipDateKey] = 20080229
-- Now update the table
UPDATE [dbo].[FactResellerSales]
SET [OrderDateKey] = [OrderDateKey] + 50000
, [DueDateKey] = [DueDateKey] + 50000
, [ShipDateKey] = [ShipDateKey] + 50000
-- 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
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 AdventureWorksDW2008R2 for Today - Completed'
GO
Arcane Code 