Fame! Fortune! Featured Blog!

I just received an email notifying me that my BI Polar blog has been featured in the "Bloggers Spotlight" section on the TechEd Bloggers web site. This site aggregates a bunch of technical blogs from bloggers who participated in Microsoft's TechEd conference. I've had a bunch of my posts (not the ones about fine dining and finer music, just the technical ones) show up there but it's a nice surprise to have the whole blog featured.

On a related note, I definitely do continue to blog on BI and SSIS topics (and all of the other random stuff that justifies the "BI Polar" title) after I join Microsoft next month. I'm sure my post frequency will continue to be erratic, or even if but the blog will continue to be hosted here on blogspot, but rest assured the madness will continue. I see several interesting places to apply BI technologies to the challenges waiting for me at Microsoft, and if I know me (this is not guaranteed, but it does seem likely) I'll find the time to blog about it here and there...

Losing My MVP

Since the beginning of the year I've had the Microsoft Most Valuable Professional (MVP) logo on my blog. I've been delighted to be honored with the MVP award, because is is an acknowledgment of the effort I've made to give back to the Microsoft technical community. This is what Microsoft has to say about the MVP award:

"The Microsoft MVP Award Program recognizes and thanks outstanding members of technical communities for their community participation and willingness to help others. The MVP Award is given to exceptional technical community leaders who foster the free and objective exchange of knowledge by actively sharing their real-world expertise with technology users. The MVP Award celebrates the most active community members from around the world who provide invaluable online and offline expertise that enriches the community experience and makes a difference in technical communities that feature Microsoft products."

What a cool thing to have Microsoft say about you, right? But in another three weeks I will no longer be an MVP - Microsoft is taking the award away from me.

Why would they do that, you ask?

Well, since I haven't been violating any NDAs lately, that narrows down the field. Have you guessed yet?

That's right - I'm going to be joining Microsoft as a full time employee. MVPs are "independent experts" and Microsoft employees cannot be MVPs.

Starting October 6th I will be joining Microsoft Learning as a Senior Program Manager/Quality Architect. I will be working with Microsoft Learning's Courseware Development Group (CDG) to improve the quality of Microsoft Official Curriculum (MOC) courseware. This is a challenge that is very close and dear to my heart. I've been a Microsoft Certified Trainer (MCT) since 1996, and have taught many MOC courses over the years. The quality of the courseware is a big factor that affects trainers (and their students) around the world, and being in a position to make such a big difference to such a large audience is an opportunity I simply could not refuse.

And I get one of those cool blue badges - how cool is that? ;-)

My Interview With Chris Shaw

Chris Shaw is the conference director for the SSWUG vConferences that I have been blogging about for the past few months. Last week he sent out a set of interview questions to the various conference chairs[1] asking questions about careers and technology and such. I replied, and today Chris posted the interview on his blog, here:

Business Intelligence with Matthew Roche

Some of the questions[2] were quite interesting, so if you have a spare minute or two you should feel free to check it out.

 

[1] Not the things you sit in, but so far as I know this is the accepted gender-neutral form of "chairmen" as opposed to "chairpeople" or something...

[2] As opposed to the answers, of course

Business Intelligence vConference Update

If you've been following my posts about the SSWUG Business Intelligence vConference, you know that it's an online conference with 10 world-class speakers and 30+ sessions that cover the breadth of the Microsoft SQL Server BI stack.

But it just got even better better.

Based on popular demand from conference attendees, the BI vConference and its sister SharePoint, SQL Server and .NET vConferences will be consolidated into a single SSWUG Ultimate Conference[1] that includes all 120+ sessions from 40 industry-leading speakers

And you get all this for the $100 price tag of the BI-only conference.

The new conference dates are November 5, 6 and 7, so you also have some extra time to prepare for the show. Tell your friends, and I'll see you all there.

[1] I voted for the name of "ÜberCon" but got vetoed on this one...

Elemental, My Dear Watson

This is a completely non-technical post, but since I know that quite a few people who read my blog also live in the Seattle area, so there is no way I could not post it. You see, I had the most amazing dinner last night...

My friends Ken and Susan had each independently recommended the restaurant, Elemental@Gasworks[1] to me and I'd been wanting to check it out for some months now. And since I was in Seattle and free last night, I had my chance. And let me tell you, my expectations were sky-high, and I was not disappointed.

I arrived around 4:15. I'd heard that the place was hard to find so I wanted to give myself some extra time, but between my GPS and the verbal instructions I'd received from Ken I did ok. I took advantage of the opportunity to visit the Gasworks Park which is just south of the restaurant, and took my seat around 5:00 when the doors opened[2] for business. There was a cocktail hour from 5:00 to 6:00, but since I was driving I couldn't partake in the hard stuff, and instead sipped some iced wine and cucumber water[3] while snacking on my truffle popcorn.

Instead of ordering from the menu I took the prix fixe option. I'd heard enough good things about the chef to trust her judgment, and what followed was an exercise in culinary delight. Let's see...

• We started with stuffed calamari (the body of the squid was stuffed with rice and other goodness, and the tentacles were on the side) cooked in red wine,
• Which was followed by a blue cheese and pear bread pudding, which was savory and sweet at the same time,
• And then a dish of lamb tartare with a quail egg yolk on top, served with crispy salty cheese cracker triangles,
• Followed by a green salad with a truffle vinaigrette dressing,
• And a salmon filet cooked with bacon and served on some sort of wilted greens and pear tomatoes,
• And finished up with a roast quail stuffed with something delicious - by that point it was too dark for me to tell just what it was.
• Of course "finished up" is something of a misnomer, because I opted to get the cheese course as well, which included four types of amazing cheese, three varieties of wine, some bread, slices of apple, and a date stuffed with nuts and other goodness.
• And by then there was no way I could say no to the "sweets," which included three different desserts and a pair of dessert wines.

Oh my goodness - was the best meal I've had in months, and perhaps the best meal I've ever had in the US. Yes, it was that good.

I just wish I had a designated driver, so I could have done more than sip my wine. The wines were generous and paired perfectly with the courses, but since I was driving I had to limit my intake to just a taste here and a taste there.

And the last thing that blows my mind - the meal cost $87 in total. Now I know this is not a cheap meal, but this includes the prix fixe menu ($40) plus the wine pairings ($20) plus the cheese course ($12) plus desserts ($15) and includes taxes. And they don't accept tips, so when I say "total" that's exactly what I mean. I honestly don't know how they do it. I've paid twice as much for meals that were far inferior.

If you're in the Seattle area, definitely check out this restaurant. You need to show up early (doors open at 5:00 and every table was full by 5:15 or so, and at 9:00 I was the first one to leave) or show up late (to get a table when the first round of diners is done) but definitely show up. I know that I'll be back...

[1] As I write this the restaurant web site is down, so I'm linking to a Seattle Times review instead. Normally you can find them online here http://www.elementalatgasworks.com.

[2] Figuratively - the doors were physically open the whole time.

[3] That's right - there was a little slice of cucumber in the carafe of ice water on the table, and it gave the water a very subtle cucumber flavor. It was a little weird, but it worked.

Views as "Interfaces" for SSIS

This is a technique that I have used for quite some time, and while I have mentioned it to others often enough, I have never blogged on it. A recent conversation on the MVP newsgroups made me think of it again, and hopefully I'll get the chance to type it all up before I get pulled back to the real world.

Here's the scenario:

• An SSIS package is extracting data from a 3rd party database.
• The package is deployed to multiple dev/test/prod environments.
• The package uses configurations so that deploying to multiple environments does not require changes to the package file.
• The database schema of the various environments is "identical." [1]
• The package works in one environment, but when deployed to another environment, it fails validation with the dreaded "VS_NEEDSNEWMETADATA" error.
• After various debugging and diagnostic steps are completed, the package suddenly works again, even though "nothing has been changed."[2]

Hey wait - if the database schemas are identical, you shouldn't get this error, right? And since nothing has changed, the error shouldn't just disappear. What's going on here? Obviously SSIS shouldn't behave like this.

Of course, the real world story here is that someone was changing the schema of the test environment and had not communicated that change to anyone. Once this is known, the solution is pretty obvious, and it's clear that SSIS is behaving as desired, enforcing strict typing in the data flow.

But if this was a simple "user error" scenario it wouldn't be appropriate blog fodder, would it?

But unfortunately, this is not a rare, one-off scenario, and the solution generally involves more communication than it involves technology. But before looking at a solution, let's see if we can summarize the problem. These are the significant problem factors that I see when I look at this scenario:

• The SSIS data flow is incredibly strict when it comes to data types. This strictness is closely related to interfaces in object oriented programming in that the names, order and data types of the columns being consumed cannot change in any way once a client (the SSIS data flow) exists that is built to use the interface.
• When the "interface" of the source data changes, any data flows that reference it will break with the "VS_NEEDSNEWMETADATA" error mentioned above.
• Database administrators are generally pretty good about not changing column names and data types on tables when they know that other database objects rely on those tables.[3]
• There is no simple/easy/straightforward way for a DBA to tell if an SSIS package uses a given table. Solutions generally rely upon documentation that is external to the database, or just waiting until something breaks.

So how does this problem description help us reach a solution? Take a look at the last two bullets and the solution practically presents itself: Use database views as an "interface layer" within the database itself.

But what does this mean? In short, it means that:

• The SSIS data flows should extract views from views and not from base tables.
• The views should "self-documenting" to DBAs because they are database objects with concrete dependencies with the base tables from which they draw their data.
• The views should explicitly CAST each column to an explicit data type and column alias - even when the alias name and data type exactly match the base table - to provide an additional layer of insulation against changes to the base tables.

The key here comes from the second and third bullets, above. Having the views as "clients" to the base tables should make it more obvious to people who are changing table schemas that the the tables in question are being used. And the explicit data type casting in the view definitions will define the "interface" required by the SSIS package clients in a way that is familiar to DBAs.

Now obviously this isn't always an option - many DBAs will not allow the creation of new database objects (such as views) but when this is viable it is a technique with which I have had great success. And when it's not, you can still get some of the same benefits (namely the data type change protection) by basing your data flow on a source query that explicitly casts and aliases each column in the SELECT statement. Then, so long as data type changes to the underlying tables can still support an explicit cast to the data type required by the SSIS data flow, the package still won't break.

So there you have it - a technique to isolate SSIS data flows from changes in source databases, and to reduce the likelihood of those changes happening in the first place.

What do you think? I'd love to hear if anyone has used similar techniques...

[1] Yes, this is in quotes for a reason

[2] Same thing here

[3] This is a pretty broad statement, I know...