Dealing with the Date Dimension Deployment Dilemma

For those of you who have routines that load up a date dimension, you know that it can be a little slow to run. There are times when I am deploying my database project (hey, you are using SSDT SQL Server Database Tools SQL Server Projects aren’t you???) and I want to recreate the database from scratch. While it is a great opportunity to grab another cup of coffee, tea, or read through a fantastic blog like this one, after doing this a few times you wind up with a bad case of caffeine jitters.

I had a date dimension to load today that was particularly challenging. They legitimately needed a couple hundred years worth of dates, plus there were some special calculated columns that needed to be populated. All total it took about 25 minutes to populate, on a reasonably decent sized server. Ouch. So I came up with an alternate solution that I thought I’d share with you, my adoring public (at least is seems like it from the spam comments that appear as love letters lol).

I created a second database project as part of my same solution. You can name it something generic, like “DateData”, or if you are using a date routine specific to each project, you can name it after your project database with something like “Dates” or “DateData” at the end.

This new project has two files. One is the create table script to create the only table, DimDate. The second is a post deployment script, in which I just copied over the original date dimension population script from the original project. I was able to deploy this, waiting the 25 minutes or so for it to populate my date dimension.

Back in the main database project, I deleted the code in the DimDate population post deployment script, and simply put in an INSERT statement to insert the rows in the DateData’s DimDate into the main projects DimDate. Load time was maybe 50 seconds, although it may have been shorter as I looked away for a moment.

The beauty of this is I only had to do a real load of DimDate once, since the date dimension isn’t likely to change during development, or generally at any point. Once your project is complete and on-line, you can even delete the DateData database. Should you need it again you can simply recreate it from the DateData project that was part of your solution. I can now do a full drop and create deployment (aka Publish in SSDT) as often as I want without risking caffeine poisoning or being condemned to staying awake all night doing a Phineas and Ferb marathon (which actually sounds kinda fun, now I wish I hadn’t fixed this issue. Oh well.)

I’m Speaking! SQL Saturday Nashville and PowerShell Saturday Atlanta

Just wanted to let folks know I’ll be doing presentations at two upcoming events.

The first is SQL Saturday #145 in Nashville. That’s this weekend, October 13th. I’ll be giving my “Introduction to Data Warehousing / Business Intelligence” presentation. Here is the slide deck I’ll be using: introtodatawarehousing.pdf

My second presentation will be October 27 in Atlanta at PowerShell Saturday #003. Yep, the PowerShell guys are taking the Saturday concept and kicking off a series of PowerShell Saturdays. This is only the third, but I see many more coming in the future.

At PowerShell Saturday I’ll be presenting “Make SQL Server Pop with PowerShell”. I’ll cover both the SMO and SQL Provider during this session.

Looks like it’ll be a busy October, but I’d hurry as both events are filling up so don’t wait and get registered now!

Intro to DW/BI at SQL Saturday #167 Columbus GA

Today, September 8th 2012, I’m presenting at SQL Saturday #167 in Columbus, GA. My slides for this presentation can be found at:

http://arcanecode.files.wordpress.com/2012/08/introtodatawarehousing.pdf

The shortcuts from the slides are:

 

Thanks for attending!

devLink 2012–Intro to Data Warehousing / Business Intelligence

Today I’m presenting at devLink 2012. My slides for this presentation can be found at:

http://arcanecode.files.wordpress.com/2012/08/introtodatawarehousing.pdf

The shortcuts from the slides are:

SQL Saturday #111–Atlanta

Today I’m presenting not one but two sessions at the Atlanta SQL Saturday. I wanted to provide copies of my slide decks here.

Configuring SQL Server 2012 Reporting Services

The Decoder Ring to Data Warehousing / Business Intelligence

Hope you enjoyed the sessions, and thanks for coming.

Follow

Get every new post delivered to your Inbox.

Join 93 other followers