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. 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. Try it with your next example it may save you a lot of time.Like many of my fellow MVPs and Presenters, I use the Adventure Works sample data from Microsoft to do my presentations. Instead of spending hours on creating sample databases, I will use the AdventureWorks database whenever I need just a database for my samples. To navigate around you should download the AdventureWorks OLTP Database Diagram for Visio: The file size is small, but there are many tables inside this database. Extended data warehouse (DW_Ext): with a lot more data (883 MB).Lightweight (LT): with a smaller set of data (7 MB).You can download a backup of the database for your version of SQL Server as part of the SQL Server Samples Repository. Thanks to the liberal license ( MIT license), you are allowed to use this database for whatever you like. With nearly 20.000 customers, over 70.000 orders and 500 products there is enough data for your examples. That stopped a few releases ago, but the database still does exist. Microsoft created a long time ago the fictitious multinational manufacturing company called Adventure Works and shipped the AdventureWorks database as part of SQL Server. They would work with any table, as long as there is just enough test data. However, most examples do not require handcrafted tables. The more time I spend with the setup, the less time I have to write the blog posts. Whenever I want to explain something that requires a database, I have to spend a lot of time to create the tables and test data before I even can start with the part I want to show.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |