I’d like to share a networking success story.  Last year, I blogged about my experience at the PASS Summit of 2005, where I was essentially a wallflower and didn’t really do any networking.  Since then, I’ve realized its importance and have embraced professional networking as a key component in a successful career. 

Ever since then I have carried through on the lesson I learned, spending as much time as possible getting to know my colleagues, and lending them a hand whenever possible.  At the PASS Summit this past November, I got the chance to redeem myself from the lack of initiative from my trip four years earlier, and took the opportunity to get to know as many people as I could.  During lunch on the third day of the Summit, I met a fellow Dallas-area business intelligence professional who works for a small consulting firm in my area.  He mentioned that his company was looking to hire one or two more senior BI people, and I hinted that I was considering making a move.

To make a long story short, that encounter led to a few phone calls and a series of meetings with this company, and as of next week, I will be a permanent part of their team!  My new role at Artis Consulting will be as a business intelligence consultant, solving complex business data problems alongside some very sharp coworkers.  I’ve had the opportunity to spend a little time with all of the leadership and several of the staff members, and I’m very excited about this move and the new challenges that it will bring.

So back to the success story… Looking back at the events of the past couple of months, I don’t believe things would have ended up this way without the groundwork I laid through networking.  In the last few years, I’ve spent a good deal of time working with and getting to know the folks in my local SQL Server user group, which in part led to my leadership role within that group.  That leadership position helped me to meet and develop friendships with other SQL Server group leaders, and one of those relationships led directly to a friendly introduction to my initial contact at Artis, resulting in the interviews and eventually the new career with that company.  It's important to note that my new role at this company was not openly advertised as a vacant position, so I would likely not have found this opportunity through a traditional job search.  I do believe that there was a greater comfort level on both sides of the interviewing fence after we came together through a known and trusted common contact.

My recent experience is further proof that building professional relationships through networking is a great strategy for career improvement.  If you’re like I used to be – introverted, a bit shy, perhaps doubting the value of professional networking – I encourage you to take a chance and get to know some of your peers and colleagues.  Find a local user group in your area of expertise, and set a goal to meet X number of people.  Attend a local technology event such as a product launch or a SQL Saturday, and introduce yourself to others there.  Invite a colleague you don’t know to lunch or coffee.  Volunteer to be part of a team in events such as GiveCamp.  There’s nothing to lose!  The very worst thing that can happen is that you’ll meet some people you’ll never see again.  And often, things work out such that your networking contacts work together to change your career for the better.

"Fortune favors the bold.”  -- Virgil

A relatively common requirement in ETL processing is to break records into disparate outputs based on an alphabetical split on a range of letters.  A practical example of this would be a work queue for collections staff based on last name; records would be pulled from a common source and then separated into multiple outputs based on a the Customer Last Name field, with the resulting output going to the person or group responsible for working that alphabetical subset of data.

There are a couple of different ways you can do this.  First is to use separate sources for each range of characters, and specify in your SELECT statement only those values that you want.  This is an effective quick-and-dirty option, but it doesn’t scale well as it requires multiple round trips to the database.  You could also accomplish this task using a simple text comparison for each letter of the alphabet, but this method is a typing-intensive operation.  For example, let’s say you want to group together the records for customers whose last names falls in the A-F range.  Using the Conditional Split transformation, your A-F output expression would look something like the following:
 

Your other groups would contain a similar statement to explicitly define each letter to be included in the group.  Not a complex operation, but one that requires a lot of typing.

An Easier Way

An easier way to do this is to use the relatively obscure CODEPOINT() function.  This method, which is part of the SSIS expression language, returns the numerical Unicode decimal value of the leftmost character of the input string.  The above grouping would be rewritten as follows using the CODEPOINT() function:

The difference is, rather than enumerating each possible starting letter within the range, I’m now evaluating the Unicode value of the first character in my LastName text field, and only including those in the 65 to 70 range (A through F inclusive) in this output.  I’ve saved myself a little typing, and this approach is easier to maintain and troubleshoot in my opinion.  A sample conditional split with four groupings is shown below:

Take It Up A Notch

So you might ask, “That’s great, smart guy, but why go through this just to save myself maybe 5 minutes of typing?”.  I’m glad you asked!  Let’s take our example a little bit further and assume we’re breaking these groupings down into smaller units.  Consider the possibility that, rather than grouping last names together based on the first letter of the last name, we’ve got a sufficient number of outputs that we’re now splitting the records within that first letter; for example, if we were to split the data stream where the last name starts with an M, we might slice our outputs on those starting with MA to MI, then MJ to MR, and finally MS to MZ.  By using the direct comparison method described above, our fully configured conditional split could have up to 26^2 possible permutations, which means we’ve got to do 676 comparisons (assuming all uppercase alpha characters) within the conditional split transformation, which will likely impact your package performance, not to mention the immense amount of typing required to set this up.  Fortunately, some creative use of the CODEPOINT() function can simplify this ETL requirement.

For this example, let’s assume that we need to separate our records within the letter M into three distinct groups as mentioned earlier, since statistically there are a lot of last names beginning with M.  For each “M” output, I’m going to use an direct string comparison to verify that the first letter is an M (since we’re looking for a single match and not a range in the first character), and second, I’ll use CODEPOINT() in conjunction with the SUBSTRING() function to check that the second letter falls within the expected range for each output. 

So for our first M grouping, the MA to MI group, the following expression would be used:

The code above will match records where the first letter is a literal M, and the second character is between A (Unicode 65) and I (Unicode 73) inclusive. Similarly, the MJ to MR expression reads as such:
 

And finally, the MS to MZ expression:

The partially configured conditional split transformation would look similar to the following:

So you can see that you’ve still got a small chunk of code to write (or copy/paste and modify) for each of your outputs, but it’s far less trouble – and better performing, no doubt – than enumerating all of the possible combinations of the first two letters of the LastName field.  The further you go into the string for your split (for example, breaking all the way down to split “McA” to “McF”, “McG” to “McN”, etc.), the more significant your efficiency in using this method over direct comparisons.

One caveat that bears mentioning: You’ll notice that I’ve used the UPPER() function generously in these examples.  The reason for this is twofold: First, a direct string comparison in the SSIS expression language is case sensitive; for example, “M” does not equal “m”.  Second, the same holds true for the Unicode decimal values returned by CODEPOINT().  Uppercase M, or Unicode value 77, does not equal lowercase m, or Unicode value 109.  Use of the UPPER() function helps to ensure that we’re making accurate comparisons regardless of case.

Conclusion

The CODEPOINT() is a rarely used function in the SSIS expression language, but can be an effective tool in your ETL arsenal in some cases.  For alphabetical grouping or splitting of records, it’s a very handy function that helps to reduce a lot of typing at design time.

More information about the CODEPOINT() function can be found at this page on MSDN.

If you’re anywhere from the southwest to the deep south, you’re probably aware of the impending winter weather bearing down on this large area.  Unfortunately, Mother Nature has temporarily claimed a significant victim: This weekend’s SQL Saturday event has been postponed until April 10, 2010.

For more information, visit the official event site.

This weekend, I’ll be headed for the east coast to speak at the SQL Saturday in Richmond, VA on Saturday.  I’ll be presenting my session on SSIS package configurations and expressions as part of the BI track.  As of last week, the event registration was cut off because they’d reached maximum capacity – more proof that the SQL Saturday movement continues to grow!  I think this franchise will continue to benefit the SQL Server community for years to come, and has even inspired similar events (SharePoint Saturday among them).

For those who can’t attend and are interested in my presentation, I’m going to try to do a Camtasia recording of it, something I’ve never done successfully for a live presentation.

Apart from the event itself, I’m looking forward to catching up with some friends and colleagues, including Andy Leonard, Patrick LeBlanc, and Jessica Moss, among others.

So if you make it to this event on Saturday, please stop by my session and say hello.  Hope to see you there!

Last weekend (January 15-17) found me at the third annual GiveCamp Dallas event, known locally as We Are Microsoft.  This event pairs developers and other technical professionals with charities who are long on need but short on funds for technology.  Most if not all of the projects are web-based, and represented a wide variety of organizations and needs.

This is my third year to have participated, but my first where I wasn’t part of a single team – I had a schedule conflict that originally kept me from registering, but after my schedule was cleared for the weekend, registration was already closed.  Since I got in so late, I was assigned to the “Flying Committee”, a small group who went from group to group to help fill in the gaps.  The past two years was a good opportunity to get to know some new people very well, since you end up spending 40+ hours working shoulder-to-shoulder with a small group; this time I met a lot of people, but didn’t really get that foxhole experience of years past.  Still, it was good to help out where I could, and I got to catch up with a few people I worked with last year, including Jay Smith, Todd Stone, and others.  I was also happy to see fellow NTSSUG member Trevor Barkhouse there, since the last 2 years found us short of database people.

If you’ve never done a GiveCamp, I encourage you to check out an event in your area, or organize your own.  It’s a good experience, and more importantly, a great cause.

So for the latest database geek meme, Paul Randal started this thing off and tagged Tom LaRock, who enlisted Grant Fritchey, who finally tagged me.   This one simply asks, “What 3 things or events brought you to where you are today?”

The Eyes

Barely a year out of high school, I was working full time in retail and occasionally attending classes at the local community college.  Through my job I had befriended a local Marine Corps recruiter, SSgt. Tennant.  Doing what recruiters do best, he saw a young man who could use a little direction and discipline, and invited me to lunch to discuss my future.  After a few months of meetings with the staff sergeant, I was convinced that I was to be a United States Marine.  I would enlist and become an MP, pursuing a dream (up to that point, anyway) to be a police officer.

SSgt. Tennant was on vacation on the weekend I was to make it official, so another recruiter drove me to the enlistment station in Dallas, where I underwent a battery of physical exams, blood tests, urine tests, aptitude tests, hearing tests, and a variety of other procedures.  At the end of the second day, we had reached the point of no return – I was called into the CO’s office to put my name on the big contract.  I brought up the specifics of what I would do as a Marine, citing my intention to work as a military police officer, but it was then discovered that my poor eyesight, although corrected to 20/20, would disqualify me from serving as an MP.  I was invited to still join up, but in a different MOS (method of service).

Now in retrospect, had SSgt. Tennant been there to counsel me, I probably would have still enlisted.  But there I was, young and naive, surrounded by strangers and incredibly disappointed that my well-laid plan was not to be.  I spent a couple of hours by myself in the enlistment station, pondering whether to join up or walk away and regroup.  In the end, I chose the latter.  Was it the right choice?  I must have asked myself that a hundred times since.  Whatever the answer, it’s clear that the choice I made helped get me to where I am today.

The Boast

Twelve years ago, I had a friend who was searching for a new career.  He wasn’t quite sure what he wanted to do, and he decided to explore a couple of different options, including taking some vocational training.  Our local community college offered a computer repair course, essentially a CompTIA A+ prep course, and he seemed to enjoy learning the basics about computer hardware and software and such. 

At the end of the course, he took the A+ test and unfortunately did not pass.  I remember bragging that I had always been a computer whiz in high school and would probably excel at such an endeavor, and certainly would have passed the certification exam on the first try.  My embellished tales of brainpower and academic prowess must have reached the maximum BS threshold, and I received a good old-fashioned southern put-up-or-shut-up.  Not to be bested by a challenge, I scraped up the $500 to take the computer maintenance course – and for the record, I did excel in the course, and I did pass both A+ exams on my first try.  More importantly, the bit of experience I gained through the course and exam prep led directly to my first technical job – it wasn’t glamorous, mostly installing white box computers and deploying Ghost images, but it was the foothold I needed to get started in the business.

The Notebook

No, not the sappy chick flick.  Back in the early 2000s (Is that really what we’re going to call the first 10 years of this millennium? Bah.), I was working as part of a 3-person IT team supporting the entire technical infrastructure for a 10-campus, 6000-student school district.  We didn’t even have a ticket tracking system of our own, instead relying on the antiquated system used by our building maintenance department, and because we didn’t own enough licenses for our IT staff to directly access their ticketing system, I had to rely on printed reports to administer our workflow.  We could only open or close tickets by submitting hard-copies of the request forms, and it often took weeks for the maintenance secretary to open or close an IT ticket in the database.

I started keeping these reports and written forms in a three-ring binder that we dubbed The Notebook.  Twice a week I would print out a list of the “current” (yuk yuk) list of tickets, and had a rubber stamp that I would mark those that had been completed but not yet marked as such in the database.  Also stored in The Notebook were copies of the hand-written requests awaiting data entry.  The system worked, but was a time sink; I would often spend 15% or more of my time just keeping up with workflow issues, not to mention the wasted time and opportunity cost for the entire team for lack of having the right information at hand.

So I began quietly keeping track of wasted hours, as well as researching ticket tracking software packages.  I found a package that was affordable and relatively easy to administer, and, with an armload of research data, presented to my boss a software solution to the problem of The Notebook.  After much convincing, my request was fulfilled, with one stipulation: that I learn enough about SQL Server to maintain the back end and create a few reports.  It wasn’t long before that one SQL Server installation helped me find my true calling, and it slowly changed over from a secondary duty to a full time career.  And the rest is history.

So, to keep this little meme going, I’ll tag the following:

Aaron Bertrand (there’s likely to be an amusing story there)

Kendal Van Dyke (those Florida guys can always tell a good story)

Lee Everest (a fellow Dallas-area guy, and I’m curious to know how he got started)

I’m also going to tag Kevin Kline – I know he’s already been tagged, but he was missed during the last meme and was taking it pretty hard.

So the question came up earlier today about the RIGHT() and LEFT() functions in the SSIS expression language.  Like the Transact-SQL functions, one might assume that these functions would exist in SSIS expression language to snatch a specified subset of a string.  That assumption would be only half right.

Don’t go digging for a LEFT() function in the expression language, because it ain’t there.  The RIGHT() function does indeed retrieve a specified number of characters, but strangely enough, there’s no corresponding LEFT() function:

Even though this is a pain for those just learning the expression language syntax, there are a couple of easy workarounds: One could simply use the SUBSTRING() function, with the second parameter – the starting point in the string – set to 1, which yields the same result.  If you want to get really crafty, you could use the RIGHT() combined with the REVERSE() function to simulate the behavior expected.

There’s already a Microsoft Connect item for this issue, and it’s planned to be fixed in a future version.

I got the opportunity to present to the PASS DBA Virtual Chapter today, discussing the properties and practical uses of SSIS expressions and package configurations.  Thanks to Greg Larsen and the other members of this virtual chapter for allowing me to present.  We had a good turnout, about 40 people, which is not bad for a lunchtime presentation.

I’ve published the code samples and slide deck if you’re interested; these can be downloaded here.  The LiveMeeting session was recorded, and should be published on the PASS website soon.

Join me tomorrow at 1:00pm CST as I present "Dynamic SSIS: Using Expressions and Configurations" for the PASS DBA virtual chapter:

In this session, we'll review the use of expressions and configurations that can help make your SSIS packages more dynamic and flexible. We'll cover the basics of the SSIS expression language and will demonstrate some practical uses, and will discuss using package configurations to abstract hard-coded package settings into external resources for maximum flexibility. 

To join the LiveMeeting: https://www.livemeeting.com/cc/usergroups/join?id=75P98G&role=attend

So I’d planned to already have this done and published before the new year rolled around, but life got in the way…  and I mean that in a good way!  I’ve said this before but it bears mentioning again that creating and sharing a list of goals helps to serve as a reminder and a means of accountability for meeting those goals.

So here’s my list for the upcoming year:

Speaking.  I hit a home run with this goal last year, exceeding my goal by 3x.  So for this year, I’m going to set my goal at 10 speaking engagements for the year.  I’ve already got a head start on this, having booked 2 for this month alone with the possibility of a third.

Blogging. I was a bit short of my goal of 104 posts (2 per week) for last year, so I’m going to set that same goal for next year.  One item to work on is to get better at scheduling posts to maintain consistency and avoid peaks and valleys.

Writing Articles: Totally blew it on this goal last year.  I’m going to set a goal of 3 technical articles for this year, which is less ambitious than my goal for last year but is reasonable considering the anticipated busy year.

Training Videos: I’ve done several training videos over at SQLShare.com (formerly Jumpstart TV), and had planned to do many more last year but only completed a handful.  Since these videos are short and narrow in scope, there’s no reason I can’t record 10 of these short videos by the end of the year.

Do More of The Work I Like: This is a less quantifiable goal, and I admit that I’ve borrowed this from Chip Camden, an independent software engineer who frequently blogs about career topics, consulting life, etc.  With this goal, my aim is to “work myself out of a job” for those tasks that I’m less excited about, so I can focus on delivering better business value based on my strengths and areas of passion.

Find 2 People to Mentor: I’ve got several unofficial, casual mentors – and some of them don’t even realize it!  By my definition, a mentor is someone who altruistically shares his/her knowledge with someone else and takes an interest in that person’s career.  It doesn’t have to be a formal relationship, nor does it require exclusivity from either party.  My intent here is to identify a couple of positive individuals who are long on passion and drive and are looking to move to the next step on the career ladder.  I don’t hold myself out to be a career counselor, but I’ve come up through the trenches and would expect that there are some folks who could benefit from my experiences, both bad and good.

It’s my intention to make a realistic evaluation of my progress periodically throughout the year so I don’t end up trying to cram a year’s worth of goals in to the last quarter.  I’ll post back on my blog as I progress.

The new year is less than 24 hours old and I’ve already been blessed with two different honors.  I shared earlier this morning that I received word of my selection as a Microsoft SQL Server MVP for 2010.  Just four hours later, I was notified that I have been elected to the board of the North Texas SQL Server User Group.  I am humbled and honored by both distinctions, and I look forward to continuing my service to the community in both capacities

I received a notification e-mail earlier this morning that I’ve been selected as a Microsoft SQL Server MVP for 2010!  I’m both surprised and honored by this distinction.  This is my first MVP award, so I’m still trying to wrap my head around it, but one thing is for certain – I’m in good company.  The SQL Server community just rocks.

Thanks to the MVP team and to those who nominated me for this award.

Late last year, I blogged about some goals I had set for myself to accomplish during 2009.  No goal-setting session is complete until you spend some time reviewing your completion of, or progress toward, those goals.  To that end, I’ll address those goals and how well I did for each of them during the past 12 months.

Presenting: I give myself highest marks on this one, as it was my big success for the year.  I greatly surpassed my goal of 3 presentations for the year, delivering ten presentations this year.

Blogging: Another success.  I had committed to blog twice per week during the year, for a total of about a hundred posts.  I haven’t tallied up all of the posts for the year, but I’m close to if not at that number for the year.  Action items include being more consistent in blogging (I tend to have bursts of blog posts, where I should be scheduling them ahead of time), and blogging more about specific technical subjects.

Forum Participation: I didn’t do as well on this as I had planned.  I had spikes of forum activity this year, where I’d spend a lot of time answering questions in one month and then drop off the following month.  I was not as consistent with my participation as I had hoped, and I’m going to include this for my improvement goals for 2010 as well.

Spend time with 12 new people: Success.  I committed to have dinner and/or drinks with at least 12 people that I’d never met before, and I went well beyond that goal.  This was made easier by way of my speaking “tour” of 2009.

Publish monthly articles and videos: Goals not met.  I committed to monthly articles and bimonthly training videos for SQLShare.com (formerly JumpstartTV.com), and I fell well short of that goal.

Certify on SQL Server 2008 BI: Goal not met. To be honest, I was not as fired up about this as I was about other goals so I’m not going to beat myself up too much, but I would still like to earn this designation at some point.  I’ve got a lot happening in 2010 so I may or may not add this one in for next year.

That’s it… Overall I give myself a passing grade, but I’ve got a few things that I’m going to be revisiting for next year.  Look for an upcoming blog post with next year’s plan of action.

There are few things more reassuring for a data professional than having clean, consistent data to back up critical business decisions.  The numbers don’t lie, or so they say.  But can the right data lead to wrong conclusions?  Sadly, yes, and I suspect that it happens more often than we’d like to admit.

Recently, as part of a large hospital project I’ve been working on, I’ve been addressing questions around the all-important census, the magic number of patients bedded in a given facility.  This facility converted from an outdated software package to a more modern, SQL-Server based product about a year ago, and one of the key goals with the new system was to “get the census right”.  See, the old system had at least a few dozen census reports, most of which were in disagreement with the others about the true count of in-house patients.  Because the software package was dated, the most common complaint was that “the system” was reporting incorrect information.  However, during my review of the archived reports, it quickly became clear that the reports from the old system were all correct.  The malfunction was not in the answers, but in the questions.

Speaking Different Languages
The root of these types of problems is consistently identifying the performance metrics of a business.  In our census example, one report might include only admitted patients, while another could include those still in triage in the ER.  One report shows the count of inpatients as of the previous midnight, while another provides the same information in real time.  In isolation, each of these metrics is correct, but when held side-by-side with the others, it appears that the output is wrong.  This is, in my experience, a trend on the rise as the movement toward self-service reporting continues to grow: more and more end users are querying their information systems than ever before, and many of them are basing critical decisions on loosely defined standards and definitions.

To avoid these assumptions and half-truths with data, I offer the following preferred practices:

• Clearly define the metrics, entities, and standards that are critical to your business intelligence, and share them with all principals.  Often this involves answering what appear to be silly questions: “What is a day?”, “What is a patient?”, “What is a sale?”, “What is a billable hour?”, etc.  By clarifying these elemental questions, your downstream metrics will be improved because everyone understands what is and is not included in these definitions.  Once those terms are defined, be dogmatic in their use.
• Involve all business units in those standards-setting conversations.  Regardless of your industry, you should include principals from each major facet of your business – sales, marketing, decision support, executives, customer service – to ensure not only a comprehensive understanding of the reporting needs but to also create a feeling of ownership in the process.  If they believe in it, they’ll support it.
• Ask leading questions.  Don’t simply give everyone ad-hoc access to your raw data; use the technical tools available to enable managed self service reporting (security controls, data warehousing, denormalizing views, or more encompassing tools such as PowerPivot) to limit the open-endedness of most user queries.  The data should be versatile enough to answer the important business questions without creating a free-for-all where the results could be made to show almost anything.
• Validate your output.  There should be a formal, mandatory validation process for each new output created, whether it’s a simple report or an entire volume of data.  Having a validation process that crosses business units is highly desirable, as this lends itself to more accurate and versatile (reusable) reports.  Part of this review process should be to confirm that the data retrieved is not already provided by existing outputs.

With the growing trend toward self-service reporting, it’s more important for everyone in a business to keep on the same page.  It’s still going to be a challenge, but by following these few practices, this process should be a little easier.

Next week, I have the honor of presenting two different sessions on SSIS.  These 2 events are the last speaking engagements on my calendar for this year:

On Monday, December 14 at 11:30 CST, I’m presenting a SQL Lunch session to discuss looping logic in SSIS using the For Loop and the For Each Loop.  This will be a working session, consisting almost exclusively of demos.  Thanks to Patrick LeBlanc for yet another opportunity to present to this group.

The following day, Tuesday, December 15th at 11:00 CST, I’ll present “Looping, Moving Files, and Splitting Data Streams: Intermediate SSIS Tasks for the DBA” for the PASS DW/BI virtual chapter.

See you there!