Thus I had to first drop the Primary Key, make the changes to the dates, then recreate the Primary Key. Also note that on this and the Product Inventory table, the Date Key was actually part of the Primary Key of the tables. I took a slightly different approach with the Currency Rate fact table, simply shifting the 2008 leap year to 2012 leap year, then omitting February 29th from the rest of the update. To fix those I simply backed those up a day, shifting them to February 28th. The two Sales fact tables had dates on leap year from 2008. For example, in Step 4.1 I just add 50,000 to the date key. With the dates added to DimDate, it was time to move on to the Fact tables. I suppose I could have used CASE statements for each of the names, but this was more fun. This gave me something to join the month and day name tables to. So I simply created a third table variable named BogusTable, and inserted a single row in it. None of my date data though existed in the table, each piece of data was generated from the CurrentDate variable. Of course to do that, I had to have a table to select from. I simply read the existing ones into table variables, then in the SELECT part of the INSERT INTO… SELECT statement do a join to these two table variables.
If you recall, the DimDate table in AdventureWorks has mult-language versions of the month and day names. Inside a WHILE loop I iterate over each date individually, do the calculations to break out the various pieces of a date such as month number, quarter number, etc, and do an INSERT into the DimDate table. The DimDate table already had dates through the end of 2010, so I only had to generate 2011-2013. In step 3 I tackle the biggest task of inserting new rows into the date dimension. Note it also does some bounds checking, etc that really wasn’t needed here, but like I said I did a grab and reuse. I probably could have done this using some version of FORMAT but I already had the routine written so I just grabbed and reused it. I have a handy little routine that converts a traditional datetime data type to an integer, using the traditional YYYYMMDD common for data warehouse date keys. This might be good if you want an easy way to reset your 2013 version, if not alter the script for your needs. Be warned, if you have run this before and AdventureWorksDW2013 exists it will be deleted. I then do a restore, renaming it to AdventureWorksDW2013. Not wanting to mess with the original AdventureWorksDW2012, in Step 1 (these steps are numbered in the script below) I make a backup of the existing 2012 version. Speaking of which, I use the default paths for everything, you’ll need to alter if you used other paths. It could easily be adapted for 2008R2 by tweaking a few paths. Next, please note this script was written with SQL Server 2012 in mind. (I don’t think Wrox will mind, as I and many of my co-workers have written books for them, nice folks.) Instead he suggested the version stored at. When he just grabbed the mdf file and tried to create the database using the attach_rebuild_log option it came out corrupted. He had some issues with the version of AdventureWorksDW2012 located at. A friend and co-worker, Bradley Ball ( | blog ) pointed out one issue which I’ll pass along. First, you will need to have AdventureWorksDW2012 installed on your system. The script, below, turned out to be pretty simple.īefore you begin though, a few prerequisites. Below is a script which will add five years to each date in AdventureWorksDW2012.
ADVENTUREWORKS2012 DATA WAREHOUSE MDF CODEPLEX UPDATE
I scoured the search engines but couldn’t find anyone who had taken time to come up with a way to update the database. This script is for the Data Warehouse version, AdventureWorks DW2012. This is especially irritating when demonstrating features reliant on the current date ( think GETDATE() or NOW() ).īefore you read further, let me stress again this is NOT for the typical AdventureWorks2012 database. I think you’d agree though it’s gotten a little long in the tooth. Being a BI guy, I specifically use the AdventureWorksDW2012 version, the Data Warehouse of Adventure Works. Like many of my fellow MVPs and Presenters, I use the Adventure Works sample data from Microsoft to do my presentations.