Monday, January 13, 2014

Adventure Works Year Update

Adventure Works - Our favorite fictional company.
There are many times when we must rely on a sample database to develop our QlikView applications.  I use these DB's for POC's, training, for testing a particular technique, creating examples, blog posts and other situations.

My standard "Go To" has got to be the Adventure Works database that comes with MSSQL.

But the problem with ANY sample database is that the data tends to grow stale in regards to the date columns.  It seems that too quickly, data that felt so fresh in 2008 doesn't make much sense in 2014 or beyond.

So I developed a QlikView application that will take my favorite sample database and update all the date fields to the maximum year of my choosing.  This allows me to get a few more years out of my Adventure Works database without having to make excuses for the age of the data.

This particular application extracts all the tables from AdventureWorksDW2008R2, transforms the date columns in needed tables and then saves all the tables in the schema to the directory of your choice.

  1. Download the qvw here
  2. On the first page of script, create a new connection to match your SQL instance.
  3. Adjust the value for vMaxYear to the highest year you want to appear in the sales data.
  4. Adjust the value of vQVDPath to the fully qualified path where you wish the transformed qvd's to be deposited.

  • The script is specifically for AdventureWorksDW2008R2.  But feel free to adjust to the version you are using or another db.
  • The DimData table is omitted since I usually create my own calendars in QlikView.  All other schema tables are extracted and stored to qvd.

No comments:

Post a Comment