Todd McDermid's Blog

PASS Community Summit 2011 Day 3 Keynote

2011-10-14T15:32:00.000-07:00

Dr. DeWitt has spoken to the PASS Summit for the past two years – turning what was otherwise a very forgettable “day 3” keynote slot into a must-see event for anyone going to the Summit.
The past two years Dr. DeWitt took us through the internals of columnstore databases and query optimization. They were fantastically content-rich and dug right to the bottom of the technology. On top of explaining the mechanics of the query optimizer and theory of column-based storage, Dr. DeWitt demonstrated understandable applications of the change in technology to common queries and functions we all enjoy today in SQL Server. He's also a pretty funny presenter.
That brings us to today and the changing world of data. We’re going to get the concept of “big data” funneled into our brains faster than we can process it – but that’s part of why I’m here. I hope to capture some of the content that comes flying at us this morning about Hadoop and distributed data processing. But my other job is to comment on how this movement will affect our industry going forward, changing your data, your job, your server architecture, and even SQL Server internals. I have no doubt that I’ll only scratch the surface of what Dr. DeWitt conveys this morning – and for that reason alone, I recommend you don’t miss his keynote (if they can get him) next year at the PASS Community Summit.
According to Dr. DeWitt, big data is a large collection of data – petabytes – on a large collection of machines. To others, “big data” means NoSQL, but that's not a requirement to Dr. DeWitt. For some sophisticated commentary on Dr. DeWitt’s presentation (and some inspired discussion), I expect to see something on Jeremiah Peschka’s blog – I’d encourage you to go read it if you end up interested in this type of technology.
The data explosion is largely due to automation, a sea change in collecting data. In the past, it was all manually entered, but now cell phones collect location data automatically, clickstreams rule advertising, and on and on. The “old guard” (and Dr. DeWitt freely considers himself part of that) would handle these petabyte data warehouses on a parallel database system. The new guard wants to use NoSQL. In his observations, current implementations of NoSQL take about 5 times the server hardware as parallel technologies. For example, EBay stores 10PB on 256 nodes vs. Facebook’s 20PB on 2700 nodes, and Bing’s 150PB on 27,000 nodes! Of course, parallel DB technologies are typically (for good reason) delivered as appliances of high-quality hardware compared to NoSQL usage of commodity type (possibly-heterogeneous) servers. As with anything to do with databases - "it depends."
NoSQL isn’t all the same – it’s a big tent of technology – but there are a couple major types: Key/value stores (CouchBase, MongoDB, …) and Hadoop. The former are intended primarily for single-record updates/retrievals – you can think of them as the “OLTP NoSQL” technology. Hadoop is designed for processing massive data sets, and is roughly expressible as “data warehouse NoSQL.”

The claim that NoSQL works on unstructured data, and RDBMSs are for structured data is a misconception. NoSQL databases do have structure – it’s just that the structure is applied to the data later in the process than for your traditional RDBMS. Instead of the metadata being stored with the data – in fact being a requirement to actually store the data – NoSQL metadata is “applied” when the analysis function processes the data. I think of it as “compiled” vs. “interpreted” code.

Only eight years ago, Google started in on the big data problem in order to analyze clickstream data. Hadoop is an open-source interpretation of Google’s Map/Reduce+GFS system. The general structure has two parts – a fault-tolerant data storage layer (HDFS) and a fault tolerant processing layer.

The data storage layer provides fault tolerant storage using file redundancy across different nodes in the cluster of servers, protecting against individual server failure as well as entire racks failing. You can think of this like RAID – data is smartly distributed over the cluster. If one node fails, the data exists on other nodes, so it isn’t lost. The main difference here is that RAID only addresses single disk failures, whereas the HDFS protects against query, disk, server, switch, rack, and datacenter failures without necessarily having to restart or recover… using only one “redundancy” algorithm.

The layer that sits on top of the storage is the processing engine – Map/Reduce. Map/Reduce is best at performing aggregations, but it isn’t designed to do joins. Joins take a ton of code to achieve – four full screens of multiple “programs” compared to a quarter-screen of SQL (HiveQL). Dr. DeWitt compared performance of a few TPC-H “structured” queries between Hive and PDW – all showing significant wins by parallel database “old guard” technology. The reason is primarily due to “old technology” being intentionally designed to handle these specific type of queries – even though they’re aggregations that he’d just said perform typically better in NoSQL scenarios. Why? Because of the inherent optimizations possible when the data used is a TPC-H workload, which is already schema’d and loaded. If the data structure wasn’t known ahead of time, a parallel database would not be capable of servicing the query at all – until metadata was “applied” by performing ETL.

There still is a strong use case for NoSQL – and his reasoning follows how I think about the relationship between SSAS (cubes) and PowerPivot (tabular) analysis technologies. In Map/Reduce, the schema is stored in the application that’s doing the analysis itself – not within the data like in SQL. So it’s a lot “easier” and better to use when you’re doing exploratory analysis of data. Much like PowerPivot, you only need to define enough schema to get your particular job done – not enough schema to get any type of query done, or “explain” each piece of data in the set. But at some point, if your exploratory analysis finds value in the data, you’re going to want to move it into an RDBMS to get the query performance out of it, and have a consistent schema applied to avoid two different analysis applications interpreting the data in inconsistent ways. Currently, there are techniques and technologies for moving data between these platforms, but they can be pretty difficult and involved. One of those is called Sqoop.

Dr.DeWitt envisions a better way to share data between SQL and NoSQL worlds than Sqoop. He’s investigating the concept of an “Enterprise Data Manager” that can relate and use unstructured and structured data so that the appropriate engine can traverse and analyze whichever type of data it’s presented with.

A few ideas Dr. DeWitt had for this year that “didn’t make the cut”, but could be a topic for next year’s Summit:

What the “Cloud” means for databases
The impact of Solid State Drives and Phase Change Memory
... whatever you might want to see – send your ideas to PASS!

If you want more, visit David DeWitt and his team on the web, there are links to the PASS talks on the left side. My thanks go to Dr. DeWitt and the team that got him up on stage - I really hope they can do so again next year.

What I Learned at #SPC11

2011-10-07T06:00:00.000-07:00

This week, I attended the Microsoft SharePoint Conference 2011 (#SPC11) in Anaheim. Not my usual beat, to be sure, but our company is starting to use SharePoint more, and we have no internal resources trained on it. We have a "wild west" set of sites already, plan to add more for cross-functional project team sites, want to host BI solutions delivery, and we've got a project on the books to migrate an existing old-tech Intranet to SharePoint. Better late than never, we're taking this opportunity to learn a little more.

I'm A Newb Here

Overall, the conference was pretty good - I learned things I needed to know and got more comfortable with the usage, architecture, and management of SharePoint. Of course, having not known these things prior to our "wild west" deployment, I now know we did it all wrong. Some good news is that even though I may not yet know the "perfect" way to deploy SharePoint for our needs, I know what questions to ask, and generally how to "fix" our current deployment.

There was one more good opportunity I took advantage of at the conference. I found myself in a fairly typical position for other people: I'm an almost complete newbie at the technology, and I didn't know anyone in the community. That sounds eerily familiar to the situation that first-timers have at the SQL PASS Community Summit. I was in the same position with SharePoint that they'll be in with SQL Server. The difference is that I now know what I didn't when I was a first-timer at Summit. I now know a great deal about the technology, the community, and the conference itself. I'm participating as a "big brother" or "guide" for a gaggle of first-timers... Could I put my own "first-timer" experience at the SharePoint conference, contrast it with my "veteran" status at Summit, and help my first-timers get great value out of the Summit? You bet.

And It Wasn't A Great Experience

To start off, I'll describe my experience at SPC11 for you - the discomfort, unfamiliarity, and frustration of it all. Yes, I wasn't terribly comfortable at the conference. Despite what you may assume from reading here or meeting me in person, I'm an introvert. I only exhibit this extrovert type of behaviour when I'm around friends, or in an environment where I'm comfortable with my perceived "expertise." I didn't really want to start conversations with people I ate breakfast and lunch with in the convention dining hall. Saying more than hi - even to the bare extent of offering my name as an introduction was difficult. I forced myself to, but my reluctance was reinforced by the lack of responses I usually got. I hardly acknowledged people sitting next to me in sessions - I often sat two or three seats away from anyone else. Sound like your typical convention-goer? It does to me.

Add to that that I'm completely unfamiliar with the technology. How do I know what sessions to go to, or what vendors to talk to? I know that my organization has issues with SharePoint - not knowing how to use it, manage it, or architect it well. But is there a session that covers that? Not that I could find. One may argue that a convention isn't the place I should be going to find this stuff out... but here I am at SPC11 in exactly this boat, and I'm positive there are other wallflowers bailing water with me. I don't even know the terminology well enough to know where to start! From what I now know about SharePoint and language used to describe it, I missed some helpful sessions. Luckily they'll be online for me to review later. But I won't get the opportunity to talk to the speaker after the session, or meet others who were also up at the podium asking similar questions (making me feel better about being a dummy) or talk to other experts that were trolling the area, offering assistance. Opportunity definitely lost...

Being uncomfortable with the setting and unfamiliar with the technology made me frustrated. I'm not used to being in that position, and the frustration drove me to learn more so I didn't stay in that situation. Unfortunately, there's not much in the way of hand- or foot-holds to help me climb out of that pit... so I got more frustrated!

It wasn't a complete disaster, I did get a lot of useful information out of the conference. Thanks in no small part to Mahmood Jaffer - he helped fill in that basic knowledge I was missing. Yes - it only took one contact to "make things better." But the experience itself was illuminating to me as to what little things could have helped me get so much more out of it. I'm glad to say that PASS' programs - of which the first-timer program is an example - do help. But even though PASS is doing many things "right", I think they could do more, and relatively easily.

Necessity Is The Mother...

Here were my issues on being a first-time conference attendee at the SharePoint Conference:

I couldn't identify "experts". (Perhaps I should clarify that I'm equating "experts" with "people that will help me".) I think speakers had a different badge accent colour - but I'm not sure. Vendors definitely did. MVPs did not. No ribbons either. I spent the entire welcome reception in vain trying to find one - not even vendors knew who or where they were.
I didn't know my (independently reserved) hotel had a shuttle to the conference until I registered.
I didn't know my (independently reserved) hotel internet access was covered until I checked out. (I bought it anyway - had to in order to do office work, and was prepared to expense it.)
Apparently, there was a "birds of a feather" area. The only information I saw on it was on a room directory, but I didn't investigate further. I don't know what kind of "feathers" they were - industry or feature - and I didn't know when it was either.
Apparently, there were Hands-On labs available. Again, I only saw passing reference to rooms on signage, without any description of lab content in the conference registration package or emails. (They did promote certification exams though.)

I think it's also important to note I had the double-whammy of being a product newbie as well as a conference newbie. Here were my issues on being completely unfamiliar to the technology of SharePoint:

I didn't know the product features or purpose - not even enough to know what I didn't know.
I didn't know how important knowing certain things was. Was understanding claims-based security more important than knowing how to leverage content types?
I still don't even know what I don't know...

Even though I'm sure some of the above two lists was in the documentation or registration package, I was more interested in addressing other issues than in reading all that to pull out a nugget or two.

...Of Invention

I think the conference organizers could have helped me out with those problems by:

Delivering better preparatory email to new/all attendees. I was actually shocked by the grand total of two - yes TWO - emails from the conference organizers that only served to help me prepare logistically... minus key shuttle and hotel internet information.
Targeting emails by attendee "type". I don't want a ton of spam either. Separating communications for "newbs" versus "returnees" would help keep content from being inadvertently categorized as spam or irrelevant by the receiver.
Identifying experts. SPC11 didn't have the ribbons all us PASS vets know (and I hope you love as much as I now do). Yes, they had a "badge customization" booth you could go to after registration to "add bling" to your badge (their words, not mine) with fairly unhelpful ribbons like "Internet Sensation" and "Hire Me". (Really?) Summit's ribbons are better, but perhaps they aren't as helpful as they could be. Instead of a ribbon for "MVP" or "Speaker" (can't remember all the ones I've had), they should have one of the colours (say red) with technology words. Pasting a red "Clustering" ribbon on your badge would mean "I know clustering, and I can (and want to) answer your questions about it." SPC11 didn't put those "bling" ribbons in everyone's packet (nor would I want PASS to), they had a free-for-all booth to pick and apply your own. That would work for a set of "I'm here to help with" ribbons (borrowing Andy Leonard's trademark).

SPC11 could have helped a product ignoramus like me more easily by:

Having an experts area manned at all hours of the conference. Heck, "experts" is asking too much. "People with a passing familiarity" would do! I would have LOVED to have sat down with someone at the Welcome Reception who could have:

Found out what I knew about SharePoint and what I didn't
Found out what my learning goals were for the conference
Given those, helped me to identify appropriate sessions/events to attend

Having an "I'm new to SharePoint" track. I know that program selection is a hard job - one of the problems is having appropriately leveled content. If there was a dedicated "I'm new to <product>" track with ONLY 100-level sessions in it, I think a newbie would do quite well, thank you. Yes, as a SQL newb, I might only care about DBA type content, not BI, cloud, and/or modeling content that would (should) be delivered in such a track. But if the first session(s) in that track explained the big picture - the stack, the different tech, AND the typical job descriptions associated with SQL Server, I think that would help newbs a LOT. Follow that up with intro sessions to the sub-products and features. With that taken care of, the program committee could feel more freedom in loading up on 300/400 level sessions.
Alternatively, have an "I'm new to <product>" pre-con to cover that...
Pass out/win "I know <product> <feature>, thanks to <conference>" shirts with "Ask me about <feature>" on the back. Have them at the ATE booths for "experts" to give to attendees they think learned something about the subject... with a soft commitment from attendees to WEAR THE SHIRT during the conference and bring it next year.

Make Conferences Better!
The above lists are directed at the SharePoint Conference - but I hope PASS can take away some nuggets as well. I think most of those points could help make the first-timer and/or newbie experience so much better, and draw attendees into the awesome community that we have already. Since this has turned into an "open letter" to PASS (I'll be drawing their attention to it), I would also encourage members of the PASS board to attend a conference every year that they've never attended before - big, small, on a technology which they're completely new to, doesn't matter - for the express purpose of putting themselves in the shoes of those they want to serve.

A "New Guy" Joins Our Team

2011-10-03T10:57:00.000-07:00

It's a great day for me and Moulding and Millwork (the company I work for). A few weeks back, I let you all know that I'd been given the green light on finding someone to help me out with Data Warehousing. One of the first people I turned to for suggestions on finding someone was Scott Stauffer, the chapter leader of the Vancouver BI PASS chapter, PASS regional mentor, and SQLSaturday organizer. I was hoping Scott could help me find the right person out there to help grow and support the fact-based decision making engine I envisioned working at M&M. Scott has a ton of contacts from being a leader in the community, and having a busy consulting practice in the area for years and years.
Scott thought my news was great - he was really excited for me that what I've been working towards was looking like it was gaining a lot of traction. But then he surprised me. He didn't start by giving me a list of people he knew that would fit the position. Nor did he point me towards recruiters or job boards. The first thing Scott asked was if I thought he would fit the position. That totally threw me for a loop. I was expecting to have either a naive kid to train up completely, or a SSAS geek to learn from. It won't surprise you to find out that I was (after the shock) VERY interested in working out arrangements with Scott. (If you get an MVP interested in working with you... you find out a way to say yes.) On top of being a great community supporter and organizer - which only touches the tip of his interpersonal capabilities and vision for the big picture - Scott is an exceptional technical talent at the whole Microsoft BI stack. Yes, he'll claim that I've got him beat in Integration Services, but I actually don't think so. I'm a hacker (in the classical sense) in SSIS, and only have one "project" to claim on my resume. I've managed to milk that into an MVP award for the third year in a row now (yay!) which I just found out about this past weekend. But Scott has a list longer than my arm of successes in this space that I now get to draw on and use to help our IT department support our business with insightful data to help them make visionary decisions quickly and more confidently than ever before.

With that, I'll be accepting your congratulations to me for landing Scott as a partner in crime here at Moulding and Millwork. It's a real coup for me, and I'm pretty happy with myself at the moment. ;) I'll make sure his badge at PASS Summit is appropriately labeled with his new allegiance. Oh... and I guess you can send your congratulations to him on twitter or in person at the Summit. He'll have to get used to the good-natured ribbing I dole out - welcome to M&M Scott!

Parallelizing Slow Parts of the Data Flow - Part 1 - Preparation

2011-09-06T05:25:00.000-07:00

It's quite common to have parts of your Data Flow that are slow, and there are some techniques you can use to improve performance of those parts. One of them is to try to parallelize the slow operation - but that only works for operations that are parallelizable. Sort isn't one of them - sorry to say. And you really have to know how the data flow works to make sure you don't try fixing a problem that looks "serial" but is, in fact parallel - you'll only make things worse.
Identifying Parallelization Opportunities
So what scenarios does parallelization work for?
Positive Signs
Here are some characteristics that should identify a good candidate for parallelizing a portion of your data flow.

The operation applies independently to single rows, or to an easily identifiable subset of rows.
There's a known-slow resource in use that allows concurrent use (SQL Server, disk, web, ...)

Counter Indications
If your data flow has any of the following characteristics, it's not likely that parallelizing (alone) will help speed anything up.

The server's CPU is at or near capacity during the slow section of the data flow.
Any external resources (SQL Server, file system, network) the slow section of the data flow uses are already at or near capacity.
Some part of the slow section requires all rows at once - an aggregate or sort, for example.

Test, Backup, Change, Test, Backup, Change, ...
After applying a parallelization technique, you'll need to figure out if it's better, right? Then the first thing you have to do is measure the performance of your current design (obviously!). Make sure you measure a few things - time to completion, CPU, RAM, network, and disk utilizations. Run the test at least three times in a row to eliminate caching effects and get a good average.
We measure all those attributes so that we can see what we're trading speed for. The idea is to trade CPU utilization for speed - but we could have to pay RAM and I/O as well - to a degree you might not be comfortable with. That's why we keep an eye on those metrics.
Before you make any changes... make sure you back up your original! Your quest to improve performance using one technique might work somewhat, and you may want to try another technique. If you have a backup of your original package, it'll (probably) be easier to implement the second technique from there, while ensuring your actual logic remains consistent. Do also back up your attempts - you might try a few techniques but decide the first one worked "best".
Make your change to parallelize the package, then double-check the result is the same as your original. It doesn't do any good to make your data go faster if you ruin it in the process.
Be Prepared For Disappointment
Even if your slow section seems to fit the profile I outlined, there's no guarantee applying a parallelization technique will help. You may not be able to squeeze any more time out of the process due to some limiting factor you weren't aware of when you started. Or the trade of speed for some other resource just isn't something you're ready to make.
What follows will be posts on using the Balanced Data Distributor, then a "poor man's" BDD using existing components for those of you that can't install additional software (even when published by Microsoft).

Inserting Records AND Getting The Identity in SSIS - Part 2

2011-08-29T05:59:00.000-07:00

Since I posted a ways back on retrieving the identity value for inserts in SQL Server Integration Services, I've learned many things - one of which is an inline solution for that problem I didn't know back then. Check out that previous post for the business problem, then come back here for an "inline" solution using SSIS - without scripts - just regular SSIS tasks and components.
It's a Love/Hate Relationship
The OLE DB Command component in SSIS is a near-universally hated object due to it's inherent performance problems. But it's so hard to hate when it can actually get the job done when nobody else can.
Yes, our old nemesis is back to solve a problem for us... at the same price it's always asked. Do you want your packages to run fast, but be complicated? Or do you want them to run slow, but be easy to understand? Sadly, there is not much middle ground.
The Keys
It turns out to be pretty easy, and I should have got it into my thick skull before, from all the hints I'd read. A stored procedure with an OUTPUT parameter, and the OLE DB Command are the keys. For this example, assume we're using a table that has only two columns: ID and TEXTVALUE, with ID being the automatically generated identity column.
The Stored Procedure
Unfortunately, this doesn't use much of the GUI power of SSIS, it's old hand-coding. For our simple table, this would be the stored procedure definition:


CREATE PROCEDURE InsertData
  @textvalue AS CHAR(10),
  @id AS INT OUTPUTAS
INSERT INTO IdentityTable 
  (textvalue) 
  VALUES 
  (@textvalue)
SET @id = SCOPE_IDENTITY()

The SSIS Data Flow
Use a Derived Column to generate the ID column with a NULL(DT_I4) value in it. (We need somewhere to place our identity value in.)

Following that, we can work on the OLE DB Command component, using a statement like this:


EXEC InsertData ?, ? OUTPUT

Map the columns as you'd expect to map them.
If you'd like I've got an SSIS 2008 package showing this technique.
The Big Picture

Sure, it works - but here are the drawbacks:

It's going to perform badly. Horribly if the package is run on a different server from the database the stored procedure is on. I can't stress that enough. The OLE DB Command component is making a round-trip call to the database for each row. Even on the same machine, that's an expensive operation.
You're doing an RBAR (row by agonizing row) process which is much, much slower than a set-based process that SQL Server is more suited for. (SSIS is fine with RBAR, it's designed that way.) Neither SQL Server process nor the Integration Services runtime will be working very hard during this part of the data flow - I don't think even one core would get utilized fully due to the wait-states involved.
If you're interested, here's a sample package to show this process. It creates a database on your local machine, creates the sample table, generates one row, performs the insert (and shows the result to you), then tears the database down.
Can we mitigate this RBAR flaw? Stick around or subscribe your RSS reader to find out in an upcoming post.

Anyone Want to Work With Me?

2011-08-22T13:44:00.000-07:00

Let's see... my last post was almost a month ago, and it was a pretty small one. I've been busy!
But it's good to be busy, especially when the housing market has looked so bad in the past few years, and you work for a company that directly serves that industry. My guerilla war about data as (apparently) been successful enough that I'm now looking for someone to help us out, full time.
What Do I Want?
I want your job. Seriously. I'm of two minds on getting another body in here. On the one hand, it should make things happen faster - but on the other, that's work I won't get to do and things I won't get to learn. If I could clone myself, or get by with only a few hours sleep a night like Buck Woody, I'd do it. (If you know me personally, that last sentence is pretty funny.)
What's the Job?
I need some help working on our data warehouse. I feel pretty comfortable with the "first half" of the technical aspects of it - sourcing the data and transforming it into an appropriate dimensional model inside a relational store. What I don't know well is the "last half" - working Analysis Services, a reporting tool (RS, Crescent, Tableau, QlikView, ...), and SharePoint. We have a small team of hardware/OS guys, but they don't know this stuff either, and don't have a massive budget to address our coding inefficiencies.
This Isn't a Backroom Job
If you're not very comfortable talking to "suits," you won't enjoy this job. Without a doubt, you'll be working with C-level executives and VPs from time to time. Even though you may feel most at home in a cave gently washed by LED backlighting, you'll need fantastic communication skills in email, phone, and face-to-face situations all over North America. It won't be the majority of your work, but you'll have to be comfortable with teaching "end users," evangelizing our capabilities, and listening very hard for opportunities and difficulties with data.
Self-Starters Only
Another reason I'm hesitant to move in this direction is that I loathe having to monitor other people... in part because I get distracted by work myself. If you need constant feeding, at some point you'll starve to death because I've unintentionally ignored you. You need to find your own work and check in with others from time to time to make sure we're all part of the same pack, pulling in the same direction.
I'm looking for a flexible mind, a problem solver, and a committed learner. Someone who isn't a 9 to 5er, but understands that's when they'll get called for information. You will also have to feed my desire to learn from you. I want to understand the technology so we can offer the guys who do real work at this company the data and insights they need to keep us on top. I'll reciprocate gladly - anything you want to know about anything you think I know.
Benefits Are Great
I'm not going to go into a lot of details here - because there's only one that should get me the applicants I want, the rest should take care of itself. I like working here because I get to do what I love, and work with people that like what they do. If you're passionate about fact-based decision making, and want to help provide it to a company that's waking up to how valuable that can be, contact me.

Quick Hit: Using XLSM Files With SSIS

2011-07-27T05:14:00.000-07:00

If you've tried to use an Excel 2010 XLSM file - a "macro-enabled" spreadsheet - with Integration Services, you'll have seen the incorrect (and badly worded) message that the "File path contains invalid Excel file. Please provide file with .xls or .xlsx extension." It looks like the SSIS team just didn't know that Excel makes other flavours of files.
How Do You Get Around This Limitation?

Create an Excel Connection Manager to refer to ANY other spreadsheet with a "valid" XLS or XLSX extension. (You don't have to save your XLSM file "as" an XLSX.)
Select the Excel Connection Manager in your Connection Managers pane.
Open the Properties Window (F4).
Change the "Server Name" property to the full pathname of your XLSM file.

Done!

PASS Summit 2011 Content - It's Your Choice

2011-07-19T05:47:00.000-07:00

If you're reading this post and attending the PASS Summit 2011 in Seattle, you absoutely should vote for two things with regards to the Summit: Dr. DeWitt's keynote subject, and the Community Sessions.
Dr. DeWitt's Keynote
I've been to the Summit for the past two years (I think... it's hazy) and one of the absolute highlights has been Dr. DeWitt's presentations. 2009 was on columnar database technology, 2010 was about query optimization. Believe me - he does not dumb things down, and he's not from marketing. There's a reason everyone raves about his presentations - he talks to geeks the way we want to be talked to.
Just like last year, PASS is asking you to help select Dr. DeWitt's topic. The theme this year seems to be parallelism technology - big data or big iron. Would you like the doctor to explain Hadoop and distributed data processing, or describe the coming impact of the increasing number of CPU cores and I/O changes? If you're a DBA, I can see you being interested in the Moore's Law presentation. But since I'm closer to the DBD demographic, I've voted for the Hadoop talk.
Best quote from Dr. DeWitt last year (I'm paraphrasing): "Guys that drop out of query optimizing because it's too hard... go build rockets for NASA."
Community Sessions
Apparently, the PASS Summit Program Committee is taking a day off, because PASS is asking for our help to round out the Summit schedule. (I kid - the PC did a great job this year and deserve a day off... even though I know they're not taking one.) From various sources, the Program Committee has selected 20 candidate sessions that are competing for five slots. Here are my choices, based solely on what I need to learn and want to see:
Robert Cain on Project Juneau - The team I'm with needs to understand databases better. It's really hard to help them get there when the code they write is so very disconnected from the database. As I understand it, Juneau is a step towards integrating code and the database better - which would give me better tools to encourage using better code and a real database.
Wendy Pastrick on Visualizing Indexes - My understanding of index internals is woefully deficient. I certainly think I know the basics, but I could really use more.
Chris Webb on SSAS Security - This one is probably over my head at the moment - but I know it's inevitable that I'll be called upon to deliver it. My org is presently just too paranoid (IMO) about spilling too many secrets to parallel lines of business.
John Welch on Advanced DW Scenarios - Some of what I want to be able to deliver in my DW seems to require what John's talking about here... but I don't know enough to say for sure, that's why I have to go.
Jen Stirrup on Data Visualizations - This topic is both fascinating to me and severely frustrating. I yearn for the ability to get rid of the complex reams of tabular reporting that require significant mental effort to decipher. I've done my best with Excel 2007, but I need to expand my toolset to accomplish transforming my business' expectations.
Vote Now
If you're attending the Summit, please vote for the keynote and session schedule you'd like to see. You've only got until the 20th of July. Please don't make it a popularity contest about the speaker - because that means I'll lose :) Yes, I happen to be listed twice! I have a session on complex SSIS executions and another on processing slowly changing dimensions in the running. If straightforward SQL Agent Jobs can't solve your SSIS execution scenario, I've got some techniques to help. If John Welch's presentation on advanced DW scenarios is a little too advanced, maybe mine fits in your ballpark a little better.
Whatever your interests, please do pick some. It's a shame that so many great sessions are in competition with each other - but I'll be happy to be a loser if it means you get sessions that will do you some good!

Presenting Upserts to PASS AppDev VC

2011-07-18T05:52:00.000-07:00

Hopefully this crowd knows a little about what they're getting into! John Jakubowski (blog|twitter) has graciously asked me to present on upsert strategies with SSIS. What are "upserts"? A not-so-fancy mashup of the words "update" and "insert".
If you struggle with incremental table loading performance - updating rows that already exist, and inserting rows that didn't - then you might want to drop by.
When?
July 26th, 4pmGMT/12pm ET/9am PT
Where?
Go to the PASS AppDev website, and find the LiveMeeting link.
What?
The full abstract:
One of the most sought after techniques in Integration Services is how to perform "upserts" - propagating changes from one system to another that may require new records to be inserted, or existing records to be updated. Integration Services can't accomplish this with a single click, but it does contain all the tools required to construct packages that will achieve this goal. The task of synchronizing data will be broken down into two parts: detecting changes, and updating or inserting. Refinements to use of the Lookup and alternatives to the OLE DB Command transform will be explored.

What is Pipeline Backpressure?

2011-07-07T06:26:00.000-07:00

First - I'll get the "what's a pipeline got to do with Integration Services" question out of the way. The "pipeline" I'm referring to here is the word commonly used to describe the flow of data in the Data Flow Task. That term doesn't seem to show up in any of the MSDN documentation, but the Microsoft bloggers just can't seem to talk enough about it. I suppose it's an internal term that's leaked out into general usage.

The SQL Server Integration Services' Data Flow Task reads data from one or more sources, flows that data through this "pipeline" where some parts of it will get transformed (changed), and then sends the data to one or more destinations. The choice of the word "pipeline" will tend to get people thinking of a liquid in a pipe - and that's an apt metaphor for the phenomenon of backpressure. The same kind of action takes place in SSIS.

Fluid Backpressure
When talking about a liquid, backpressure refers to the resistance that the fluid encounters flowing through a pipe. When the fluid encounters a constricted section of the pipe, it can't flow as fast through that section. But since (most) fluids are incompressible and most pipes are rigid, the reduction in flow rate at this constricted point means that the flow is "backed up" all the way to the start of the pipe. If a kink is stopping the flow of water near the end of the hose, the tap isn't able to inject any more water into the hose - there's simply no space to put it. The rate at which you can inject water into the hose is directly related to two things: how constricted the smallest part of the hose is, and how much the hose can "expand" prior to the blockage. (If the hose can inflate like a balloon, you can still pump water in.)
Data Flow Backpressure
SSIS's Data Flow Task has very similar constraints. The source - or "tap" - wants to inject data into the pipeline as fast as it can. But that data has to flow through the components - the "pipe" - in order to reach the destination - the "end" of the hose. Somewhat like a garden hose, the data flow pipeline can stretch to allow the source to inject more data into the pipeline than is ultimately getting through to the end. But there's a limit, of course, just like with a hose. If it's stretched too much, it will burst.
In SSIS's case, since it's a "fictitious" hose, the nice engineers that built it were able to set limits on how much of a balloon you're allowed to turn your hose into. It's the RAM limit of the process and/or machine you're running your package on. In most cases, taking advantage of SSIS' "stretchy hose" is a very bad thing - you'll easily consume all the available memory on your machine, and start spilling into virtual memory... and that's slow disk!
Backpressure In Action
Here's an SSIS package that demonstrates backpressure quite well.

You can build your own quite easily from what I'll describe here, in case you don't have SSIS 2008. The first task in it is a Data Flow Task that looks like this:

The first component hooks up to your local instance's master database, fetching a cross-joined resultset from sysobjects. (It's the volume of rows that matter here - not the content.) The second component is one of my Data Flow wiretap scripts that will dump timing information to the logging stream that we'll be able to watch in the Output window. The last component is a script destination. Normally, I'd just use a Union All or Derived Column to cap the flow, but those result in a lot of "unused column" warnings that would clutter up the logging stream.

As you can see below, running this flow results in something like this pretty quickly. In my case, under 1.5 seconds, just about as quick as SQL Server can deliver the rows to SSIS. (So fast I didn't bother to capture an "in-flight" picture.)

The next Data Flow Task in the package shows how an "impeded" data flow will behave. In this flow, there's one more script component in there.

The component inserted at the third position is intended to mimic a transformation that takes effort to perform. This is the equivalent of an uncached Lookup, OLE DB Command, Merge Join, etc... What does this look like at runtime? Well, no problem taking a screen capture in mid-execution on this one...

How does this image show the effects of backpressure? It was taken over 20 seconds after the data flow started. Let me explain in more detail.

Our first "free-flowing" data flow loaded the data into SSIS' pipeline in under 1.5 seconds. The second "impeded" data flow hasn't even finished loading the 100K rows after 20 seconds... why not? Because the "sleep" transform is slowing things down. So why isn't the source simply reading as fast as it can, and piling up the data at the slow transform's doorstep? Because if it did that, it would consume memory. The developers of SSIS thought that consuming memory in this situation would be completely unnecessary if they could just tell the source to "slow down" to a manageable level... so they did.

Any "execution tree" (there's another bit of SSIS internals jargon) will experience this backpressure - forcing every component to operate no faster than the slowest. One of the key parts of managing the performance of your packages is understanding pipeline backpressure. It can mislead you about what's performing slowly in your flow, causing you to spend time and effort where it will have no effect. Tweaking the DefaultBufferSize and DefaultBufferMaxRows properties can change some of the effects of backpressure - but doing so only delays the inevitable with sufficiently large data sets, and typically offers little meaningful benefit. The real key is understanding your data flow's behaviour through decomposition.

I have one more example data flow for you in the package, where I add a sort component to the middle of the flow. How will this affect the flow of data? The sleep component will definitely cause the end-to-end flow to be slow, but will the source stream quickly like the "free-flowing" example, or will it be throttled like the "impeded" flow? I'll leave that up to you to explain...

Code Snippet for an SSIS DataFlow Performance Wiretap

2011-06-20T05:56:00.000-07:00

I was recently tweaking a few Data Flows, and settled on a very small, but very useful script to help record execution timing inside the flow.

Decomposition Is Time Consuming

You may know that I do recommend the decomposition technique to help diagnose performance problems in SSIS data flows. That's the technique where you start recording execution times using fewer and fewer components in the data flow in order to get a differential execution time for particular components. Even though it's not perfect, it does account for misleading effects of the pipeline backpressure mechanism. (What I suggest later does not.)

Unfortunately, performing a decomposition analysis can take quite a while. It is time consuming to replace the destination component with a row count, run the flow, record the results, and continue up the flow.
Adding a Performance Wiretap is Easy
Especially when the code is completely copy-and-paste! I couldn't measure any performance impact of including this script code, and didn't expect any. It doesn't use any columns in the data flow, and the operations it does do are very lightweight. You don't even have to change anything in the script itself - the events that are logged include the name you give the component in the data flow (which must be unique) so you can easily identify which of several wiretaps is reporting information.



public class ScriptMain : UserComponent

{

  #region Statistic collectors

  private DateTime _preExecuteTime;

  private DateTime _firstRowTime;

  private DateTime _lastRowTime;

  private int _numRows;

  #endregion



  #region PreExecute

  // This method is called before any rows are read from any sources

  // We reset all statistics here

  public override void PreExecute()

  {

    base.PreExecute();

    this._firstRowTime = DateTime.MinValue;

    this._lastRowTime = DateTime.MinValue;

    this._numRows = 0;

  }

  #endregion



  #region ProcessInput

  // This is NOT a regular method override you normally see in a script component

  // This override is called once for each input buffer (set of rows) that

  // are passed into this script component. We count rows here, and record

  // first and last "row seen times".

  public override void ProcessInput(int InputID, Microsoft.SqlServer.Dts.Pipeline.PipelineBuffer Buffer)

  {

    base.ProcessInput(InputID, Buffer);

    #region Record the time the first row was seen

    if (this._firstRowTime == DateTime.MinValue)

    {

      this._firstRowTime = DateTime.Now;

    }

    #endregion

    #region If this buffer has any rows (the last buffer typically does NOT), record the time

    if (Buffer.RowCount > 0)

    {

      this._lastRowTime = DateTime.Now;

    }

    #endregion

    #region Add the number of rows in this buffer to the row count statistic

    this._numRows += Buffer.RowCount;

    #endregion

  }

  #endregion



  #region PostExecute

  public override void PostExecute()

  {

    bool fireAgain = true;

    base.PostExecute();

    #region Output statistics to package logging, as Information events

    ComponentMetaData.FireInformation(0, "",

      ComponentMetaData.Name + " first row seen at " + this._firstRowTime.ToString("hh:mm:ss.fff"),

      "", 0, ref fireAgain);

    ComponentMetaData.FireInformation(0, "",

      ComponentMetaData.Name + " last row seen at " + this._lastRowTime.ToString("hh:mm:ss.fff"),

      "", 0, ref fireAgain);

    TimeSpan duration = (this._lastRowTime - this._firstRowTime);

    ComponentMetaData.FireInformation(0, "",

      ComponentMetaData.Name + " watched " + this._numRows.ToString("###,###,##0") + " rows pass by in " + duration.TotalMilliseconds.ToString() + "ms",

      "", 0, ref fireAgain);

    #endregion

  }

  #endregion

}

So what does it do? It records the time the first row passes it, the number of rows that go by, and the time the last row passes it.
How Can This Help Diagnose Performance?
If you want to know which section of your Data Flow takes the longest duration - place some of these scripts at various places in the flow. After you run your Data Flow, you'll see how long each section takes - keepin gin mind their execution times will overlap each other. (If you use two scripts, the second script's "first row" may very well be seen before the "last row" passes through the first script.)
If you're wondering what kind of impact a certain change has in your Data Flow - place this script before and after the affected area. Run your flow before you make the change, and again after. The results will show you how much impact your change had (but remember it won't account for hidden backpressure effects!)
If you're wondering which of several "branches" of a Data Flow that are combined using a Union All is the slowest, you can use these wiretaps to instrument each one.
Once again - please don't think this is a replacement for correct use of the decomposition technique. Component performance in the Data Flow depends heavily on other components coming before and after the area you're interested in - and even flows occurring in parallel in the same (or other) Data Flow.

How Do You Deal with Excel in SSIS?

2011-06-06T09:16:00.000-07:00

This Friday June 10th, I'll be presenting to the Vancouver BI PASS chapter about Excel. I've got a TON of problems, solutions, resources and tips to compile together - Excel can be a real finicky beast to deal with from an automation standpoint.

Given all the problems with connectivity, data types, formatting, and so on... why don't ETL developers just proclaim that they won't take data in the form of Excel spreadsheets? Because then we'd have no work to do! It's pretty uncommon to hear of a data integration project that doesn't involve Excel in part of it. The source OLTP system probably isn't a set of spreadsheets - but quite a lot of supporting metadata, reference information, or budgets and forecasts come from the ubiquitous Excel. It's the business' most comfortable vehicle for storing data - I constantly hear it referred to as a "database" (by business folks). Well, we can't snigger from on high in our ivory tower about how rediculous it is to call Excel spreadsheets databases - we have to deal with this data!

The presentation will be in the Microsoft offices in downtown Vancouver - 1111 West Georgia, 11th floor at 10:30 PDT. Come early to chat - I'll be there around 10am, depending on how long a conference call goes. I hope to see you there if you're a local - but if not, you can still tune in virtually. Scott will have the RSVP and LiveMeeting links up on the Vancouver BI PASS website shortly.
And for those that are really paying attention - if there's a specific issue you're interested in solving between SSIS and Excel, ask away during the presentation - or be nice to me and leave a comment here prior to Friday so I come prepared!
UPDATE 2011-06-22: The recording, samples, and slide deck are now available.

Use Connections Properly in an SSIS Script Task

2011-05-31T05:47:00.000-07:00

There are lots of scenarios in SSIS where you'd want to use a connection within a Script Task. Unfortunately, the closest help documentation - the comments inside the Script Task itself - give you exceptionally poor advice:
To use the connections collection use something like the following:
ConnectionManager cm = Dts.Connections.Add("OLEDB");
cm.ConnectionString = "Data Source=localhost;Initial Catalog=AdventureWorks;Provider=SQLNCLI10;Integrated Security=SSPI;Auto Translate=False;";
Why is this advice bad? Let me count the ways:

The connection string is hardcoded inside the script.
The connection can't be used with Package Configurations.
The connection string (and name) imply that OLEDB connections can be used.
As presented, the connection won't participate in MSDTC transactions.

(And seriously, "use something like the following"? Hey - I write the vague blog posts here. They're supposed to be writing documentation. ;) )
The poor advice boils down to the fact that it appears to create a "managed" connection, but doesn't truly do so. In fact, if you attempt to use code in the comment as is - you're only halfway there. How do you actually use this "ConnectionManager" object anyway? There's no "Open" method, or Command object - just weird methods and properties that would be entirely unfamiliar to you even if you know .Net data access! And to add insult to injury, even if you did know where to go from here, the example provided will only cause an error, and will never work.
What is a Managed Connection?

Lets start with describing what a "managed" connection is (in the context of SQL Server Integration Services). It is simply a connection that's defined and controlled by an entry in the "Connection Managers" window at the bottom of the design surface in BIDS.

Managed connections have the following benefits:

Their properties (primarily the connection string) can be manipulated with Package Configurations.
Their properties (again, the connection string) can be manipulated with Property Expressions.
They can participate in SSIS (MSDTC) transactions.
The connection is tested by default when the package is loaded, before any (potentially unrecoverable) work is started.

But perhaps most importantly, using a managed connection within a script (properly) informs someone maintaining the package that a connection to an external resource is being used.
How is the Advice Wrong?
The comment in the Script Task counsels you to use Dts.Connections.Add to get a reference to a ConnectionManager object. This is the correct type of object - it's a managed connection. Unfortunately, using that syntax, you're creating a new one, not using one of the managed connections you've already defined in the package. Since it's an entirely new connection, you have to set up the connection string. You could pass the connection string in to the script using an SSIS variable, or read it from a file - but these are both non-standard practices. Another ETL developer or administrator familiar with SSIS won't anticipate your decision to implement a connection that way.
Additionally, since it's a brand new object, none of the regular design time facilities are available to you; Package Configurations, Property Expressions, the TransactionOption property, and runtime validation don't work.
The Right Way to Use Managed Connections
Ignore the comments in the Script Task - use managed connections properly by following these steps.
1. Create an ADO.Net Connection
Add a Connection Manager to your package the same way you'd make one for use in a Data Flow or Execute SQL Task. Right-click in the Connection Managers pane and add a new ADO.Net connection. You can use OLEDB type connections and other connection types - but they're more difficult to use and have fewer capabilities inside a Script.
2. Retrieve a Reference to the Connection
Inside your Script Task, instead of using Dts.Connections.Add, retrieve a reference to an existing connection manager by using the Dts.Connections indexer. For example, if you happen to have a connection manager named "(local).master", then use this code to retrieve a reference to the ConnectionManager object:
ConnectionManager cm = Dts.Connections["(local).master"];
3. For Connections with Providers, Acquire a Connection
(A "provider" means any connection that has code supporting and managing the connection - like an ADO, OLEDB, or Excel connection, but unlike a Flat File connection.)
Even though you now have a reference to the ConnectionManager object, you need to acquire an actual connection to the resource, and request that connection be established in a way that SSIS can manage it. This step is necessary to ensure that your work with the connection participates in transactions and connection pooling (or lack of it).
The specific details for each connection type vary, but the general method is consistent.
// For an ADO.Net ConnectionManager using a SqlClient provider
System.Data.SqlClient.SqlConnection sqlConn = (System.Data.SqlClient.SqlConnection)cm.AcquireConnection(Dts.Transaction);
// For an ADO.Net ConnectionManager using an ODBC provider
System.Data.Odbc.OdbcConnection odbcConn = (System.Data.Odbc.OdbcConnection)cm.AcquireConnection(Dts.Transaction);
Be aware that it is NOT necessary (or advisable) to use the Open method on the connection object you just retrieved... it's already open. (Check the connection's State property if you don't believe me.)
4. Use the Connection
At this point, you can use the connection to retrieve datasets, execute commands, or do just about anything else you'd want to do with your connection.
5. Release the Connection Properly
Once you've finished your work, you need to clean up after yourself - but NOT by using the Close method on the connection! You'll need to use the ReleaseConnection method of the ConnectionManager instead.
How Hard Was That?
Hopefully not so hard as to make you want to go the other direction and use completely unmanaged connections in your scripts! To recap, here's a complete code listing of how to correctly use an ADO.Net managed connection for a SqlClient provider within a Script Task:
bool fireAgain = true;
ConnectionManager cm;
System.Data.SqlClient.SqlConnection sqlConn;
System.Data.SqlClient.SqlCommand sqlComm;
int rowsAffected;
// Retrieve the reference to the managed connection
cm = Dts.Connections["(local).master"];
// Request an open connection
sqlConn = (System.Data.SqlClient.SqlConnection)cm.AcquireConnection(Dts.Transaction);
Dts.Events.FireInformation(0, "", "Connection is: " + sqlConn.State.ToString(), "", 0, ref fireAgain);
// Do your work
sqlComm = new System.Data.SqlClient.SqlCommand("UPDATE YourTable SET YourColumn = 'SomeValue'", sqlConn);
rowsAffected = sqlComm.ExecuteNonQuery();
// Inform SSIS you're done your work
cm.ReleaseConnection(sqlConn);

Dts.Events.FireInformation(0, "", rowsAffected.ToString() + " rows updated.", "", 0, ref fireAgain);

Dts.TaskResult = (int)ScriptResults.Success;

Idiot's Guide to SSAS Attribute Relationships

2011-05-24T05:35:00.000-07:00

Yes, that's me - and this is a reference post because frankly, this stuff is too complicated to keep in my head for how frequently I use it.
The Problem

The SSAS Idiot

When you get the "Errors in the OLAP storage engine: The attribute key cannot be found when processing." error returned when you're trying to process an SSAS cube, what does that really mean? I have a previous post where I went into some more technical reasons that can cause this error, but there's usually a pretty simple cause related to how you've structured your dimension in the designer... your attribute relationships. Attribute Relationships
Specifying good attribute relationships are something that the experts say are a very basic and necessary step to configuring a cube that performs well. Attribute relationships tell SSAS that it can take some shortcuts when processing your cube. What kind of shortcuts? Something like telling it that since "British Columbia", "Alberta", "Saskatchewan", and all the other provinces are always in "Canada", if you (or a user) asks for a total sales number for Canada, it doesn't have to add up all the invoice line items for Canada - it can go add up the numbers it's stored already for total sales in each province.
So yes, it's very good to specify attribute relationships! It can turn a million row operation into a ten row operation, which results in better query performance, reduced storage requirements, faster cube processing, and more. It's a best practice to relate attributes that you've placed in a dimension Hierarchy - because they're going to be used in aggregations very often.
How Are Attribute Relationships Related to the Problem? (Pun Intended)
In the example of provinces and countries I provided, it seems like you'd never have a problem - it's straightforward and simple, right? Not so fast. You could have bad data... or not such a simple relationship. We'll look at both in turn.
What do I mean by "bad data"? I mean that your data warehouse could have business entities that have bad address information delivered to it by your OLTP system(s). (You're shocked, I know.) Bad how? Perhaps address information is incomplete - it only has the country, they didn't bother to fill in the street, city, or province. Bad, bad, OLTP data entry validation! But it happens... all too often. This means that a "blank" province could be located in multiple countries.
What do I mean by "not a simple relationship"? Let's take another example - cities. There's only one Vancouver, right? The one in British Columbia, Canada. Not so fast... Vancouver, WA would be having words about that assertion. Other city names are much more prevalent, and easily demonstrate this particular issue.
Deciphering SSAS's Error Message
OK - so you understand the problem when I explain it with my example... but how do you decipher SSAS's message to figure out how your attribute relationships and your data are causing problems? Here's the long explanation - I'll cover it again more briefly at the conclusion of the post. My error message looks like this:
Errors in the OLAP storage engine: A duplicate attribute key has been found when processing: Table: 'dbo_vDIM_CUSTOMER', Column: 'ship_to_city', Value: 'Greenland'. The attribute is 'Ship To City'.
To genericize it, I'll replace the actual values with placeholder names that I'll use going forward:
Errors in the OLAP storage engine: A duplicate attribute key has been found when processing: Table: 'DimensionTable', Column: 'AttributeColumn', Value: 'AttributeValue'. The attribute is 'AttributeName'.
Find the "Source Attribute"
Breaking it down, this message means that the attribute in your SSAS dimension called AttributeName, and that's where the problem is. We'll refer to this as the Source Attribute.
Find the "Related Attribute"
Open BIDS open the dimension that didn't process, and switch to the Attribute Relationships tab - the one with the graph that looks like this (based on my example):

(Now, why the "Ship To City" attribute is in a separate box from the "Dim Customer Key" attribute, but the "Ship To State Province Code" isn't is not something I can explain, so I won't.) Looking at the list view (down below), I see this:

The important things to notice is that the Source Attribute (in my case) was Ship To City, and it's being "rolled up" or "is contained in" the Ship To State Province Code. In your case, you need to find the Source Attribute in your relationship graph or list, and find out what other attributes it's related to. (There might be more than one relationship where your Source Attribute is on the left-hand-side of an arrow. You'll have to investigate each relationship individually.)
Find the "Source Column" in the Dimension Table
Your Source Attribute came from somewhere in your underlying dimensional database - find out where. You're given a hint in the error message - the AttributeColumn.
Find the "Related Column" in the Dimension Table
Just like the Source Attribute, the Related Attribute comes from a dimensional database table - find out where. You'll likely have to switch to the Dimension Structure tab of the dimension editor, select the Related Attribute, open the Properties window (F4), and look at the KeyColumns property.
Now you're ready to query your data.
Querying Your Data
You now have three critical pieces of information: the Source Column, Related Column, and AttributeValue. In my case, the Source Column was "ship_to_city" in my vDIM_CUSTOMER view, the Related Column was "ship_to_state_province_code" in the same view, and the offending AttributeValue was 'Greenland'. To find out why SSAS is having a problem, I need to run this query:

SELECT ship_to_city, ship_to_state_province_code, COUNT(*)

FROM vDIM_CUSTOMER

WHERE ship_to_city = 'Greenland'

GROUP BY ship_to_city, ship_to_state_province_code

To genericize the query and relate it to those placeholder names we've been using, it should look like this:

SELECT [Source Attribute], [Related Attribute], COUNT(*)

FROM [DimensionTable]

WHERE [Source Attribute] = AttributeValue

GROUP BY [Source Attribute], [Related Attribute]

When I run that, I got 2 rows back - it shows quite clearly what my problem was:

Yep - OLTP addresses where sometimes the city was specified on a customer record, but the state or province wasn't. In your case, you should get at LEAST two rows from your query. If you don't - then this isn't your problem... maybe you have one of these problems instead.
Knowing Is Half The Battle
Fantastic, now that you know where the problem is, you can fix it. How? One of two ways:

Decide it's a "data problem" and rectify the underlying data. Tell a business user to fix the offending address, tell the OLTP developer to add validation to customer address entry, and/or change your ETL process to clean the data.
Decide the data will never get "clean" - because nobody will "own" the dirt, or because it's a valid part of the business. This happens very often.

In my case, the data you see is dirty. But the offending business data is very old, and not worth cleaning up. If I did spend the time to clean up the blanks, I would still be unable to process my cube, because I would find "Springfield" in multiple states, etc... So even though it is dirty, it is also a business reality that cities don't uniquely belong to state/provinces.
The Other Half - Fixing The Attributes
So if I can't clean the data because it is (mostly) how it's supposed to be... how can I model it in SSAS? I recognize that certain cities are related to multiple states and provinces... but I still want to (and should) help SSAS optimize it's processing.
I'm sure there are several ways (there always are) - but the following works for me. If an SSAS expert would like to chime in, please do!
Recognize That "AttributeColumn" Isn't Unique
We have to recognize that the Source Attribute doesn't uniquely identify the Reference Attribute. Just because I say "Vancouver" doesn't always mean I'm referring to the city in British Columbia. I'm going to have to uniquely identify cities in order to roll them up into states and provinces, just like you'll have to uniquely identify Source Attributes in order to roll them up into Reference Attributes.
How do we do that?
Inside BIDS, open the Dimension and select the Dimension Structure tab. Select the Source Attribute from the list and open the Properties window (F4).

Select the KeyColumn property, and click the ellipsis button (...) to open the Key Column selection dialog.

Pick another column that together with the Source Attribute will uniquely identify a Reference Attribute. I've chosen to select the Reference Attribute column itself. You may choose to select another column or columns that will uniquely identify the Reference Attribute.
Since we've just identified two columns to be the key for this attribute, SSAS can't use the default "name" for attribute members (the KeyColumn itself). It doesn't know how to display specific members of this attribute - you've got to tell it by editing the NameColumn property - just under the KeyColumns property we just changed. You may decide to simply choose the Source Attribute column (as I did).
The dimension will now successfully process... unless you've got another "duplicate attribute"!
Success! Let's Recap
The short version of what we just did was:
We deconstructed the error message to identify the Source Attribute
We examined the Attribute Relationship tab of the dimension to identify the Reference Attribute
We verified that there was a many-to-one relationship between the Source and Reference Attribute
We made the Source Attribute map one-to-one to the Reference Attribute by including the reference column in the KeyColumns for the Source Attribute.
What I don't know is whether this is the "best" general advice to give, or if there's a better rule of thumb for resolving this issue. Until I do know, I'll be referring back to this step-by-step quite often, I'm sure...

How To: Use the Same Data Flow Column Twice As a Parameter in SSIS

2011-05-18T05:22:00.000-07:00

Every once in a while, you'll have a slightly more complex UPDATE statement in an OLE DB Command or Destination. You'll use an UPDATE statement or a destination table that needsto use data from one column in your data flow several times. A typical example is a range update:

UPDATE table1

  SET column1 = ?

  WHERE datecolumn1 <= ?

    AND datecolumn1 > ?

Yes, best practices say that you should probably avoid using the OLE DB Command entirely, but if you aren't updating many records and you don't mind the poor interface, it does get the job done.
What You Can't Do
The OLE DB Command interface asks for the statement (like above) that has question marks as placeholders for the values you're going to pass in. The OLE DB and ADO.Net Destinations allow you to identify tables, or craft a SELECT statement to identify a table. They also have a interface for associating those numbered parameters or destination columns to the columns you have available in the data flow. One of the many faults of this interface is that it doesn't permit two parameters/destination columns to be associated with one column in the data flow - you just can't do it.

Another Splice Won't Hurt...

What You Can Do So if the interface only allows 1:1 associations of parameters in your statement to columns in the data flow... then you just need to duplicate a column in the data flow. Add a Copy Column transform, or a Derived Column transform to the design surface just prior to the OLE DB Command or Destination transform to create a second column that contains the same data. Now you've got what you need to configure your OLE DB Command or Destination properly.

Runtime Interaction with SSIS Packages - Confirming Actions

2011-05-10T05:34:00.000-07:00

In developing my data warehouse ETL processes, there are some packages and SQL Agent jobs that are both necessary to the process of deploying a new version of the ETL system - and yet are extremely dangerous. These packages "reset" certain control infomation to a known state, which is a destructive operation I want to make sure I think about twice before executing - especially in a QA or production environment. If I executed this kind of package in development on my local machine, I could use Script Tasks to pop up MessageBoxes to ask for this kind of confirmation.

Are You Sure?

Of course, you can't do that when you're executing packages on a server! There are also use cases beyond initial data loads. You may allow your SSIS packages to be callable by a wide range of people or systems... but you may want some oversight or confirmation before those packages are actually run. Here's one way to balance convenience of access and execution with some protection against unintended execution.
The Design
My basic thought process in finding an alternative to "confirmation" message boxes was:

I can't have a popup... how else can the server talk to me?
Ah - it can send me an email. Bonus - it doesn't matter who kicks off the job, I can hardcode an email address in there so I get the message, or I can look up an email in a "responsibility" table so the email gets sent to a designated "process owner."
Now how do I talk back to the server? I can't reply to the email - it doesn't have a mailbox.
Ah - it can watch a share for a file to appear that contains a confirmation to continue.
So how do I make sure that I don't confuse the package, inadvertently acknowledging confirmation for "request #2" when I intended to confirm "request #1"? You know how that happens every once in a while - you're editing a document, and some other program "pops up" to ask you a question... but you don't stop typing in time, and your keystrokes end up selecting something and dismissing the dialog!
Ah - I can require the file contents to be something... special... and unique to that package execution. Some kind of code that would be unlikely to mistype unless I spent some serious brain power on it. Like a GUID.
But I don't want to have to navigate to the share, create a new text file, and hand-transcribe a GUID. I'll probably type it in wrong and have to restart the process again. (Faulty humans!)
Ah - I can attach the file to the email, so all I have to do is save it in the required location.

Yeah - OK already. So it takes me a while to figure this stuff out...
The Implementation
I'd put up a sample package - but I'm too ~~lazy~~ busy to strip out the sensitive and custom stuff from one of my packages, so I'll just describe it.
Task 1: Add a Share
Find a location on your network and create a share that you give permission to both you (as the package execution confirmer) as well as the account you're going to be executing the SSIS package under.
Task 2: Script - Generate the Confirmation File Contents
Add an SSIS variable called "Confirmation File Name" that contains the UNC path (not driver letter path) to where you want the confirmation file placed.
Pass that variable into the Script Task, along with the MachineName and ExecutionInstanceGUID system variables. I've chosen the ExecutionInstanceGUID as my "confirmation code" - you can choose something else entirely: a passphrase, or another GUID generated by Guid.NewGuid. (It's more secure if you generate another GUID rather than use the execution instance - someone could guess your implementation and subvert the email step if they have access to the sysssislog table.)
Paste in code somewhat like this:



System.IO.StreamWriter confirmationFile 

  

    (string)Dts.Variables["User::ConfirmationFilePathname"].Value);

confirmationFile.WriteLine(

  Dts.Variables["System::ExecutionInstanceGUID"].Value);

confirmationFile.Close();

Dts.TaskResult = (int)ScriptResults.Success;= System.IO.File.CreateText(

As you'll see in a bit, I've built the file in the exact spot that I intend to look for it... we'll take care of that.
Task 3: Send the Confirmation
Slap a Send Mail Task down, set it up to send you an email, and attach the file using the ConfirmationFilePathname variable.
Task 4: Delete the Confirmation File
As you pointed out earlier, if I "waited" for you to place that file in the location we want, it wouldn't work - because the file is already there! So I need to delete it, making sure that I've got a clean spot for you to place your confirmation.
(You could have created the file in a separate location so this isn't an issue - but deleting it was simpler to set up.)
Task 5: Wait... and Watch
You can use a Script to do this entirely - I happen to have used my File Properties Task instead. It's up to you, but in this step, you'll be waiting for the file to reappear. How long you wait, and how often you check is also up to you - but I suggest you don't wait indefinitely. Have your package fail if a certain timeout is reached. If you use a Script to watch for the file, you'll be using System.IO.File.Exists and System.Threading.Thread.Sleep to do that.
Task 6: Read the File
If you receive your email, decide to confirm the action, and save it to the right share, your package will arrive at this step. It's now got to open the file and check the contents with another Script Task to make sure you're responding to the proper request. Here's one with an appropriate level of error handling:

bool fireAgain = true;
string confirmationFileName =
(string)Dts.Variables["User::ConfirmationFilePathname"].Value;
Dts.TaskResult = (int)ScriptResults.Failure;string contents = "";
try

{

  System.IO.StreamReader confirmationFile;

  confirmationFile = System.IO.File.OpenText(confirmationFileName);

  Dts.Events.FireInformation(0, "",

"Confirmation file opened.",

    "", 0, ref fireAgain);

  contents = confirmationFile.ReadLine();

  Dts.Events.FireInformation(0, "",

"Confirmation file contents read.",

    "", 0, ref fireAgain);

  confirmationFile.Close();

}
catch (Exception ex)

{

  Dts.Events.FireError(0, "",

    "Unable to open '" + confirmationFileName + "' to "

    + "check contents: " + ex.Message,

"", 0);
}



if (contents == (string)Dts.Variables["System::ExecutionInstanceGUID"].Value)

{

  Dts.Events.FireInformation(0, "",

"Confirmation file contents validated.",

    "", 0, ref fireAgain);

  Dts.TaskResult = (int)ScriptResults.Success;

}
else

{

  Dts.Events.FireError(0, "",

"Confirmation file contents failed to "
+ "match expected code.", "", 0);
}

If the confirmation file contains the right code, it will return success, otherwise it will fail with an appropriate message.
Task 7+: Do Your Dirty Work
Your package just got the green light to trash the place (or do whatever critical action was requested)!

Iterating Over Columns in the SSIS Script Component

2011-05-03T05:15:00.000-07:00

Just recently a question was posed in the MSDN SSIS Forums about how to create a "generic" script component that would read all columns for each row passed through it in the Data Flow. The standard impetus for this kind of behaviour is a logging or auditing step in the Data Flow. Now - my first reaction is that you should probably be looking to code a custom component so that you aren't copying and pasting script code everywhere...
But I know that not everyone is comfortable coding a custom component from scratch - you have to be a code addict like me to find joy in that. So here's a little "gateway drug" for those of you who are itching for the capability. Perhaps a little taste of it will expose you to the script's shortcomings, and lead you to build your own lab to cook up a custom component to do this job properly.
Location, Location, Location
The big key here, as mentioned by Darren Green in an earlier post on the subject, is where to put code to do this. It's not in one of the automatically generated stubs - you'll have to tread into slightly less familiar territory - the ProcessInput method override. The standard stubs (PreExecute, PostExecute, and Input0_ProcessInputRow) are all useless for our purposes - you can safely delete them or ignore them. Why? PreExecute doesn't yet have any information about the buffer, and PostExecute is too late (obviously). Input0_ProcessInputRow is called once per row... but with a carefully wrapped row object that presents the columns in nicely named properties. There's no way to access the collection of columns. Only ProcessInput has access to the buffer in a condition that allows iterating over the columns.
Paste This Code

public override void ProcessInput(int InputID, PipelineBuffer Buffer)

{

  bool fireAgain = true;

  ComponentMetaData.FireInformation(0, "", 

    Buffer.ColumnCount.ToString() + " columns", 

    "", 0, ref fireAgain);



  while (Buffer.NextRow())

  {

    for (int columnIndex = 0; 

      columnIndex < Buffer.ColumnCount; 

      columnIndex++)

    {

      string columnData = null;

      if (Buffer.IsNull(columnIndex))

      {

        columnData = "is NULL";

      }

      else

      {

        BufferColumn columnInfo = Buffer.GetColumnInfo(columnIndex);

        switch (columnInfo.DataType)

        {

          case DataType.DT_BOOL:

            columnData = Buffer.GetBoolean(columnIndex).ToString();

            break;



          case DataType.DT_WSTR:

            columnData += Buffer.GetString(columnIndex);

            break;



          // add code to support more data types here



          default:

            columnData = "";

            break;

        }

      }

      ComponentMetaData.FireInformation(0, "", 

        "Column " + columnIndex.ToString() + ": " + columnData, 

        "", 0, ref fireAgain);

    }

  }

  base.ProcessInput(InputID, Buffer);

}

What's Happening In There?
It doesn't take much explanation - but every little bit helps.
Initially, a loop has to be constructed to iterate over all the rows we're given. Do remember that this isn't ALL the rows coming through your Data Flow - it's just one buffer's worth. This method will get called several times (unless you have very few rows in your flow).
The column then gets checked to see if it's NULL... because NULLs cause quite a problem when you don't expect to see them.
After that, a little inquiry is made to ask for some information about the column. This bit of code can definitely be optimized out of the loop - it is a burden on the system to ask for it for each and every row!
The reason for getting a little information about the column is apparent in the next block - the switch statement that handles different datatypes differently. You can extend the code to handle the data types you expect.
Finally, after the loop, don't forget to call the base ProcessInput method. Why? Press F12 on that call and you'll see - the base code handles marking the processing as being complete when you've finished seeing all the rows.
Variations On This Theme
The above code isn't the only way to get this done. You can do away with manually detecting the column data type and simply call ToString on the buffer's indexer - as in:
string columnData = Buffer[columnIndex].ToString();
But do still beware of nulls and other odd results. Using the "Get" methods specific to the data type do perform faster, and are safer in the long run.

Transforming SSIS' sysssislog Entries Into Something More Readable

2011-04-26T16:15:00.000-07:00

The format of sysssislog in SSIS 2005 and 2008 isn't great for human consumption, so what follows is an attempt to reformat it for easier digestion. It isn't perfect - and here's hoping that the new Denali (the next version of SQL Server) server-side toolset will improve the situation it as much as I think it will.
Raw sysssislog Table Listing
My difficulty is with the nature of the logging - it "bubbles up" the same message to each containing task. The result is that the same message is reported multiple times in the log, which doesn't make it very concise (for reading). There's no doubt that it does make it easier to see everything that happened to or within a specific task - but that's not typically how I read the logs. Issuing a simple query such as:
SELECT id, event, starttime, source, message FROM sysssislog ORDER BY id
Results in a rowset like this (click to enlarge):

My difficulty with the above is that (in this case) there are two rows per "message" - and that's just a simple demo package. More typically, I've got five or more nested containers, resulting in a lot of repetition.
The Distilled Query
My desire was to distill the log records such that there was only one row per "message" instead of several. You can't simply group by the message, of course. You have to group only adjacent entries and also (preferably) still show the involvement of each level of the Task hierarchy, because that context information is still valuable.
What I've arrived at is the following, which will distill the last 1000 entries in the log into something less wordy. It's still not ideal - tasks executing in parallel get mashed together. Those of you who are experts in T-SQL and windowing functions will no doubt be able to optimize this. Please post a comment if you do!

WITH 

  LOG_SUBSET AS

  (

    SELECT TOP 1000 id, REPLACE(event, 'User:', '') AS event, starttime, source, message

      FROM sysssislog

      ORDER BY id DESC

  ),

  ORDERED_LOG_SUBSET AS

  (

    SELECT TOP 1000 id, event, starttime, source, message 

      FROM LOG_SUBSET

      ORDER BY id

  ),

  EVENTTYPED_LOG AS

  (

    SELECT id, event, starttime, source, message,

      HASHBYTES('SHA1', CAST(starttime AS CHAR(20)) + event + message) AS eventtype_group_key

    FROM ORDERED_LOG_SUBSET

  ),

  CONTIGUOUS_EVENT_GROUPED_LOG AS

  (

    SELECT id, event, starttime, source, message, eventtype_group_key, 

      ROW_NUMBER() OVER (ORDER BY id) AS s1,

      ROW_NUMBER() OVER (PARTITION BY eventtype_group_key ORDER BY id) AS s2,

      ROW_NUMBER() OVER (ORDER BY id) - ROW_NUMBER() OVER (PARTITION BY eventtype_group_key ORDER BY id) AS sequence

    FROM EVENTTYPED_LOG

  ),

  GROUPED_LOG AS

  (

    SELECT id, event, starttime, source, message, eventtype_group_key, s1, s2, sequence,

      HASHBYTES('SHA1', CAST(eventtype_group_key AS CHAR(36)) + CAST(sequence AS CHAR(10))) AS event_group

    FROM CONTIGUOUS_EVENT_GROUPED_LOG

  )SELECT MIN(id) AS id, event, starttime, message,

    SUBSTRING(REPLACE(REPLACE(

      (

        SELECT ' < ' + source

           FROM GROUPED_LOG AS Y

          WHERE X.event_group = Y.event_group

          GROUP BY source

          ORDER BY MIN(id) DESC

            FOR XML PATH('')

      ), 

      '&lt;', '<'), '&amp;', '&'), 3, 2000) AS source_list

  FROM GROUPED_LOG AS X

  GROUP BY event, starttime, message, event_group

  ORDER BY MIN(id)

A sample of the resulting rowset (click to enlarge):

It's not fantastic, but it does improve my diagnostic tracing a little bit.
Thanks to information from Boneist and Tom Cooper on grouping, and Anith Sen on pivoting.

More Changes for the Dimension Merge SCD?

2011-03-14T06:27:00.000-07:00

A while back, I ran a contest to find a new name for the SCD component hosted on CodePlex. The results were the best "lemons into lemonade" situation I could have expected. Now I've got some other news I can share that should extend the reach and capabilities of every Data Warehouse ETL developer that uses Integration Services.
This Time, No Lemons... Porkbellies Instead

One of the problems I'd always had with offering a component on CodePlex was that it meant some enthusiastic would-be users couldn't end up using it in their production environments. The reasons they were unable to use the component weren't technical, they were business-related:

Open-source project software was avoided due to (valid) longevity concerns.
In-house development capabilities weren't sufficient to provide support, should a problem occur.
"Untrusted" vendor code wasn't permitted on production servers.

The "easy" solution? Offer support for the component myself, building up that trust level and support network. But I've got a very nice day job, thank you, and not nearly enough free time - not "easy" for me, I'm afraid. The bottom line is that offering support myself doesn't make sense for me, or a good business proposition for those potential users. I just don't have the economies of scale to work with, or the track record.
So if I can't do it, who can turn those porkbellies into tasty bacon?
Heat Up the Frying Pan
One of the component's ardent enthusiasts for a few years now has been Joe Salvatore. He's helped with testing and has provided feedback on improvements for several releases. Just recently, he started with a new consulting company, and apparently the guy is pretty vocal. ;) I don't know the whole story, but I can imagine Joe did a demonstration or two, some quick talking, and twisted someone's arm. Questionable karaoke footage could have factored into the equation as well.
So who's attention did Joe attract that could smoke and cure a little raw porkbelly into something you can actually sink your teeth into? Brian Knight and his crew at Pragmatic Works, that's who. This is the Brian Knight that's written quite a few books, has a successful consulting company, has contributed voluminous amounts of time to the community by serving on the PASS board, co-founding SQL Saturdays and SQLServerCentral.com. You know Brian's all about the bacon for the SQL community.
Soft or Crispy?

If you're looking for a dimension table loading experience in Integration Services that's fast, flexible, and maintainable, you can't look to in-the-box Microsoft tools today. They're continuing to stand pat with what they have in that department - arguably a very servicable tool in the SCD Wizard. But if that's not good enough for you, and you're not keen on re-inventing the wheel, you now have very viable choices. How do you like your 3rd party components? Open-source or commercial? Community forums or paid support? This partnership gets you what you need, the way you want it.
Pragmatic Works is working on improving the component in ways that work for them and their customers; they'll be including it in their Task Factory set of tools. That's a great option for the more "conservative" organizations out there. But if that's not you, you're not left out in the cold. The free-to-use open-source project on CodePlex is independent, and will continue on its own - that's the beauty of open source and business working together. Brian knows there's a real gap in the Dimension Merge SCD story today - the lack of vendor support. Even if he didn't change a stitch of code, providing that support adds value for his customers that they're willing to pay for. There's no need for any kind of exclusivity in this arrangement - the value is self-evident.
Order Up!
I'd like to give a hearty thanks to Brian Knight and everyone that works with him. I'll be helping them out as much as I can to explain the spaghetti code they're trying to understand, but I'm confident they'll be providing a Dimension Merge SCD that works even better and easier than what I've made so far - and they'll stand behind it. Brian didn't have to even talk to me to get this done - he could have freely downloaded the code and used it as he wished. But he's a community-minded guy. He reached out to me for my support and I really appreciate it. Thanks, Brian!

Say Hi to the Dimension Merge SCD Component

2011-03-09T05:40:00.000-08:00

Last week was a great week all around. It started with Canada's first SQL Saturday in Vancouver - packed with over 300 attendees watching five tracks of local speakers and MVP designated imports. I had the privilege of hosting one of those rooms and watching Donabel Santos (blog|twitter) elucidate on SSRS, John Welch (blog|twitter) go 2-for-2 on exposing SSIS internals, and Bill Pearson (blog) delivering an introduction to MDX in his fine southern accent. I suggest you download their code and slides from the SQL Saturday session list, or their blogs. I've just uploaded my slide deck and demo package for the talk I did on Data Warehouse ETL with SSIS - thanks again for all those that attended that last session of the day!
MVP Summit 2011
Of course, SQL Saturday #65 was scheduled on February 26th very deliberately - in order to benefit from proximity to the Microsoft MVP Summit being held the following week in Bellevue/Redmond. A fantastic experience (again), and definitely one I hope to repeat many more times. Not only was it an amazing opportunity to connect with the development crew of SQL Server Integration Services directly - it's also a spectacular opportunity to interact with the best of the best, the other MVPs. After last week, I have a list as long as my arm of things to investigate - it's almost depressing to have a real job!
The Contest Results
But the real action last week was, of course - my contest to find a new name for the better-than-decent Slowly Changing Dimension component I've been hosting on CodePlex for the last few years. I was absolutely blown away by the response - you could see the comments on the original post, but there was also quite a few submissions via twitter and email. A huge thank-you has to go out to Steve Jones and the SQLServerCentral crew that highlighted the contest on their weekly newsletter to their million-plus membership. There was a massive response from that exposure - nothing like I could have ever expected.
Down to brass tacks! The title of this post told you the new name of the component: the "Dimension Merge SCD" - so congratulations go out to Merrill Aldritch (blog), a fellow SQL blogger. There were some other names that I was really attracted to - it wasn't an easy call! In the end, Merrill's was the most descriptive and fit in with what I was looking for. So congratulations to Merrill - I'll be sending off a Windows 7 Pro to him, even though I promised an Ultimate. Purchasing limits and prior commitments conspired against me on that - so I've also included a little something extra to make up for it.
More To Come
Obviously, there's more in store for the Dimension Merge SCD - I have to release v1.6 into the wild, and those of you with a finger on the pulse of the community may have an inside track on another bit of news I'll be shouting from the rooftops in short order. Stay tuned...

Changes to the SSIS Slowly Changing Dimension Component

2011-02-25T05:35:00.000-08:00

Probably not the kind of change you're expecting. I've recently been contacted by the Kimball Group, and they've asked me to stop using the word "Kimball" to identify the component. They have every right to make sure nobody is confused about who they are and what they do, or try to capitalize unfairly on their reputation. I never intended to do anything of the sort, and certainly hope I've never given that impression. Work is (slowly) underway to strip it out of the project materials on CodePlex.
Lemonade, Anyone?
So if I can't identify the component by the name of the methodology it follows... I've got to find something else for it's title - because "SCD component" is just boring, and will easily be confused by the component of the same name (but definitely inferior capability IMO) included within SQL Server Integration Services.
I could come up with a label, but it would probably be really lame. Anything I can think of to tack on to "SCD Component" seems trite and overused in the world of 3rd party objects. Your typical adjectives of "ultimate", "fast", "improved", "enhanced" don't seem to be able to capture what I'm looking for.
I Need Your Help
This component turned into a real community experience for me - 50,000 site visits,13,000 downloads, 600 discussion posts, and 100 bug reports and suggestions. Best of all (for me) were 4 four-star and 10 five-star reviews (nothing lower) and the expressions of support.
So that's why I'm turning to you - the people who know what this component does better than anyone! I'd like to run a little contest to come up with a new name for the project - something that speaks to the impact that it has for you in processing incremental dimension loads in SSIS.
Think Out of the Box
Don't restrict yourselves to looking through the thesaurus to find an adjective to tack on to "SCD Component" like I did. Think unique. A descriptor that's funny, insightful, or just plain accurate will do - but not something you'd hear everyday. Send me your idea for the full name of the component, as well as something shorter if you get out of breath saying the full name. (An obvious nickname would be the acronym... but you can do better, right?)
Leave your ideas (and lobby for your favourites) in the comments, send an email to toddmcdermid at live.ca, tweet to @Todd_McDermid, or tell me in-person at SQL Saturday #65.

The Incentive

Seriously - you need an incentive other than contributing to the community? Of course you don't - but I have one for you anyway. The winner - chosen at my sole discretion - will get a boxed Windows 7 Ultimate x64. You'll probably have to patch it to SP1 by yourself... but you guys develop SSIS ETL packages, so I know you're used to a little manual hackery. (I may also have a little something for the funniest entry - I'm talkin' shooting-milk-out-the-nose funny...)
The Time Limit
All good things must come to an end - and this contest will too. You've got one week - until midnight Pacific Time on Friday, March 4th to send me the moniker that should emblazon the best SCD option for SSIS. Stay tuned for the results!

Windows Phone 7 and OneNote

2011-02-20T16:09:00.000-08:00

I'd never used OneNote before I picked up my Windows Phone 7 - but it's extremely handy, even more so than the Notes or Tasks that I'd used for the same purposes on my BlackBerry. The only downside was that they (still) don't have notifications attached, which means OneNote notebooks aren't a replacement for Outlook Tasks. That said, the ability to synchonize to the cloud allows better access - from any computer anywhere - rather than just from computers that I have my Outlook client installed on. In addition, the formatting capabilities of OneNote are very useful compared to Outlook notes or tasks. I find myself recording more information in my notebooks - because it lends itself to better organization, and it's easier to get at.
What's Possible Now
The Windows Phone how-to pages include information on how to set up a "default" synchronizing notebook called "Personal (Web)" on your SkyDrive. This (inexplicably) shows up on the phone with a section called "Unfiled Notes" and you're warned against changing it. This prevents a useful organizational feature of OneNote - the ability to file notes into sections, and even into different notebooks.
If you happen to have an internet-accessible corporate SharePoint 2010 server, you can "keep" a copy of those notebooks on the phone and synchronize them with the server. I don't have a SharePoint 2010 server architecture lying about, and my organization is probably not looking to spend a ton to get one. I really wanted to segregate information and realize some of the benefits of multiple sections and notebooks.
Officially, That's It
As far as Microsoft's documentation goes, that's all you can do. They don't mention anything about using more than one notebook. In fact, in all the news I've read on the internet about what's coming in a future update (the Mango, 2011H2 update) is "better" Office integration.
So you're stuck, right?
Pushing the Boundaries
It turns out you can put any SkyDrive notebook on the phone - although Microsoft doesn't explain how, or tout this capability. It's relatively easy to do, but there are limitations, which is why you aren't hearing this from Microsoft. It's so easy that I didn't realize how seamless it was until I broke it and re-enabled it by accident.
Quite simply, create any OneNote notebook on your SkyDrive, then use the phone's Internet Explorer to navigate to your Live account to find the document in SkyDrive, and open on it. If you did this on your desktop's IE, it would open in OneNote's WebApp. But on the phone, it switches immediately to the Office Hub, opening the notebook on the phone, and syncs the sections.
But wait - I said I "broke" this and then fixed it. How did I do that? I changed my phone's Internet Explorer settings to look at the pages in "desktop" mode, not mobile. (Some sites have way too limited "mobile" versions, don't they?) Seems that if you surf to your OneNote notebook in "desktop" mode, it'll fire up the OneNote WebApp, but if you hit it in "mobile" mode, it knows to download and sync it with the Office Hub. Who knew?
Getting Rid of "Personal (Web)"
Great! You can put any OneNote notebook on the phone... so why settle for a hobbled notebook called "Personal (Web)" with an "Unfiled Notes" section that you can't change?
Only one thing - if you create a new note (page), it needs a notebook to get saved in, and that's the "Unfiled Notes" section of the "Personal (Web)" notebook. Again, the documents don't say this - but you can change that default. Open the Office Hub and tap the "all" button. Tap the "notebooks" pivot heading. Try to tap the garbage can icon, then pick the "Personal (Web)" notebook - the phone will complain, and give you instructions on how to change the default. Just to save you from having to do that, press "OK" and the back button to get back to the notebook screen, and tap and hold the notebook section (not the notebook) you want to set as the default.
Life Isn't Perfect Yet
But of course, there are some issues. It seems like the phone reads different "section" information than the WebApp. When you add any notebook to the phone using what I've described, the first section in the notebook is always "Untitled". No matter what you do, you can't rename that. In fact, any section renaming you do on the web using the WebApp doesn't EVER get reflected on the phone. Moreover, if you try to change page names on the phone, you get a sync error. You can view the sync errors on the web, but your only option is to copy the data out of the changed page, because the only resolution you have to sync errors is to delete the version the phone made.
It'll Do Until Mango
I could (and will) ask for more, but this will do. I can now have whatever notebook pages I want on my phone, synced so I can work on them offline, in very high-fidelity. I can structure my notebooks' sections and pages any way I want... as long as I do it right the first time, and delete the automatically added first section. I can even add voice notes, pictures, and video to the pages from the phone. On top of that, with a little tap-and-hold, I can pin any notebook page right to the start menu.
Personally, I don't see how the one limitation here - section and page naming and syncing - is taking a year to fix.
Recap
Create your notebook "properly" using the WebApp

Create the notebook
Add a new section
Delete the original "Untitled Section"
Construct your notebook sections and pages as you wish - named perfectly on creation.

Link the notebook on the phone

Surf in IE "mobile" mode to the SkyDrive
Open the notebook

Limitations

Notebook, section, and page names can NOT be changed. If you want to rename a section or page, create a new one, move the data in the pages, and delete the original (old) named section/page.

To the cloud!

How To: Load Dimension Tables with Integration Services - Introduction

2011-02-16T06:00:00.000-08:00

When I first started down the business intelligence and data warehousing road, I needed to learn the foundations of dimensional design. Ralph Kimball's methods seemed appropriate at the time, and they still are for me. The next step was learning how to make Microsoft's BI tool stack follow that methodology, and after that, making the technology work as easily, reliably, and as fast as possible.
There Are Knowledge Gaps
Over the last year, I've noticed that although there's a lot of interest in the last step in that process - making things go fast - there seems to still be a lot of confusion over the first steps. Quite a few interactions I've had with the community lead me to believe that there's a significant number of people skipping over understanding the dimensional model, and how ETL (of any brand or style) should be used to manage loading one.
I see questions in the MSDN forums asking why the OLE DB Command isn't inserting rows into their dimension table... which clearly demonstrates two problems the asker has. First, they don't understand that the OLE DB Command doesn't insert rows - that's not its purpose. But the second and more fundamental issue is that they don't understand why it doesn't. They aren't grasping the concept of new row versions and updating old rows. If they understood that, they wouldn't wonder why the OLE DB Command didn't insert rows, because they'd know adding rows to their dimension table shouldn't happen at that point.
I Sucked Too
This also came up quite clearly in my side-project of the Kimball Method SCD component. I assumed (and you know what that means) that people would know what to do with the "New" and "Expired" outputs I'd labeled on the component. I held on to that belief so fundamentally that I created a video intending to demonstrate the performance of the component, but completely ignoring the dimensional modeling accuracy of the demo. In fact, the video showed exactly what not to do when inserting/updating rows in the dimension table.
But Then, So Did AdventureWorks
I tried to find a fairly simple example of dimension loading that clearly showed "data in" and "data out" and how that mapped to the SSIS SCD Wizard or any other tool. I expected to find that in some Microsoft samples - like the AdventureWorks Integration Services samples. Unfortunately, the writers of this example also assumed too much about the readers' knowledge of dimensional modeling. The AdventureWorks samples contain an SSIS package specifically named AWDataWarehouseRefresh - implying that it can be used to "refresh" AdventureWorks dimension tables. But it loads dimension tables into SQL Server from a CSV file. A completely populated dimension table - a snapshot in time. No use of the SCD Wizard. No incremental loading. I really can't understand why they thought it would be an accurate demonstration of loading a table in a data warehouse specifically - fact tables OR dimensions. That example applies to any kind of table, data warehouse or not, and provides no help at all for the specific problem of loading dimension tables on an incremental basis.
A New Step-By-Step
So here's my second attempt at doing it better the first time. I've made a few videos and other resources to try to better explain how to incrementally load dimension tables with SSIS, and I'll be posting them in a series here on my blog. The videos were originally intended to better explain my Kimball Method SCD component and are already available there, but they seem to fit this bill as well. Please do comment on specifics that make them easy or hard to understand. I've made comments through my channels to Microsoft about improving the information available for this kind of thing - but your comments can help me confirm to Microsoft that this isn't all a figment of my imagination.
The next post in this series will provide and describe the sample data stored in our data warehouse, and the source system that updates will come from.

Speed Up SSIS - February Talks

2011-02-05T16:04:00.000-08:00

Improving the performance of packages is always a focus of ETL developers. It should go without saying that your first duty is to make the package work... after which you can then make it work fast. In an effort to help you out with the latter, I've got two presentations I'm set to deliver this month. The first is to Colin Stasiuk's (blog|twitter) Edmonton PASS chapter, and the second is at SQL Saturday #65 in Vancouver.
Diagnosing and Addressing Performance in SSIS
On February 16th, the Edmonton PASS chapter is hosting me virtually to talk on the subject of optimizing SSIS packages. You too can attend this webcast even if you're not there - I can guarantee that, because if it's not broadcast, I won't be able to deliver it, because I'm not going to be there in person either.
I'll be covering one of the essential steps that too often seems to get overlooked in trying to make SSIS packages "go fast"... and that's knowing how to find out where they're actually "going slowly." If you don't know why this is important, or don't know there are techniques that can help you do this, you should probably make time to sign up.
I'll have to make a dig or two at Colin's expense during the webcast. He trimmed my bio down on his site... he must not appreciate my humour. Please feel free to feed me some fodder in the comments.
Data Warehouse ETL with Integration Services
On February 26th, the famous SQL Saturday franchise will make its first stop in Vancouver at the Pinnacle Mariott. If you're anywhere close to the area and touch SQL Server, you need to be here. The following week is the Microsoft MVP Summit in Redmond, which the organizers of Vancouver's SQL Saturday exploited to the fullest. A ton of SQL MVPs will arrive a little sooner than normal to the Summit just to be able to make the short trip up to Vancouver. Some of them will be presenting - for example, SQL rock stars like Brent Ozar and Brad McGehee. Others couldn't get a speaking slot, but they're coming anyway, and they're all open to conversation.
I was fortunate enough to leverage my "local" credentials to get a session on performing data warehouse ETL with SSIS. I'll start by describing the multiple ways to fill dimension tables with SSIS, leaving you with solid guidelines to pick a technique that performs in your scenario. I'll finish up with the relatively simple techniques involved in fact table population, as well as some optimization techniques to speed the whole process up.

Two Weeks of #SQLAwesomeSauce Over - With More To Come

2010-11-22T05:57:00.000-08:00

What a stellar two week jolt of SQL Server goodness. SQL Connections in Las Vegas the first week of November, then the "big kahuna" of SQL conferences - the PASS Summit in Seattle. It was fantastic to run into people I'd met at the last Summit (in both places!) and meet some new people interested in SQL Server and Integration Services. It's taken me this long to recover and get back (almost) on top of "regular" work.

SQL Connections Fall 2010

Of course, the main reason I was in Vegas was to do some presenting, and overall was very pleased with the turnout and participation of the audience. My third session (on Script components) really felt too rushed to me - and it won't surprise me at all if that's my lowest rated. For those of you who do want to see the slide decks and sample code from those sessions, here they are:

Data Warehouse ETL with Integration Services: slides, sample
In-Depth with the SSIS Script Task: slides, sample
In-Depth with the SSIS Script Component: slides, sample

PASS Community Summit 2010
The following week was the pinnacle event of the SQL Server world, and was my "best Summit ever." Among a ton of things, some highlights for me were the Birds of a Feather lunch, and building towers with the SSIS Dev team with post-it notes. Of course, it wasn't all play, or that's what they tried to tell me. I had a chalk talk on debugging SSIS packages, and participated in a very fun Lightning Talk session with six other fabulous speakers, including the comedian of the SQL world, Rob Farley. Kendal Van Dyke was also very gracious in letting a bunch of us "help him out" with his speaking and blogging session. I hope we didn't derail him too much...
The chalk talk has a slide deck with a few suggestions on it - we only got to investigate a very few - 40 minutes is SHORT. Take from it what you will, it's not your typical deck. Also very atypical was my lightning talk deck - 23 slides, seven words... I guess you had to be there.
Coming Up Next...
This Tuesday, November 23rd, the BI Virtual Chapter of PASS is hosting my session on Loading Dimension Tables with SSIS. Very similar to what I did at SQL Connections, but focusing only on dimension table loading. Brush up on slowly changing dimensions before you attend - I'm presuming you've experienced the joy and pain of loading them prior. I'll walk through alternatives to the SCD Wizard, giving you a good feel of what you should try to get by its limitations. (UPDATE 2011-11-30: the recording is now available on their website.)
Just around the corner from that - SQL Saturday #65 in Vancouver on February 26th! I don't have any sessions submitted yet - but I'll get to that. It's looking like a very special SQL Saturday - Scott Stauffer brilliantly picked the weekend before the MVP Summit in Redmond. Just a couple weeks after announcing the date, MVPs Bill Pearson, John Welch, Denny Cherry, Gail Shaw, and Aaron Bertrand have submitted multiple sessions each, as well as MCM Robert Davis from Microsoft. I expect that there will be many more MVPs and Microsoft folks presenting and attending, not to mention the fantastic speakers that don't (yet) have those letters to tag after their names (but ought to).
See you on a monitor, or in-person soon!