Have you voted yet for my sessions your favorite sessions for SQL Rally?  If not, this is a friendly reminder to hop on over and vote now.  You have to have a PASS login to vote, but don’t worry – it’s free to get one and takes about 2 minutes.

Out of the 60 total sessions to be presented at the event, 20 of those will be chosen by popular vote.  To avoid potential conflict of interest, those of us who were part of “the crew” for evaluating and selecting SQL Rally sessions were not eligible to be selected as part of the general selection. Therefore, both of the sessions that I submitted are up for vote in the community voting process.

I’ve submitted two sessions for this event:

Getting Started with the EzAPI for SSIS

The SQL Server Data Tools (formerly BIDS) environment is a very capable platform for developing SQL Server Integration Services packages.  However, there are times when ETL needs require more flexibility and dynamic behavior than what SSDT provides.  In this session, we will discuss the EzAPI for SSIS, which is a framework for dynamically altering package elements at runtime.  We'll briefly review the capabilities exposed in the EzAPI, and will walk through a few practical examples of using this framework.

Introduction to Data Quality Services

In this session, we will take a quick tour of the new data quality tool released with SQL Server 2012. With SQL Server Data Quality Services, data professionals now have an easy-to-use framework with which they can analyze and maintain data quality. This session will serve as an introduction to this new product - we will discuss DQS concepts and architecture, review the server and client components of DQS, and will demonstrate the DQS component for SSIS.

I Want Your Vote

So if you want to see me present one or both of these sessions (and you know that you do), get out and vote!  I hope to see you there.

I’d like to share with you a laziness efficiency method I use when developing reports in SQL Server Reporting Services.  I’ve found that it’s quite common to work on reports wherein the requirements dictate the following:

• There are numerous parameters, and
• The parameters will not use default values

Both are valid business requirements, to be sure.  However, from the report developer’s perspective, the above requirements can translate into a time sink.  When developing a report, a common method is to test the report very frequently as changes are made, which could eventually add up to hundreds or perhaps thousands of clicks just to set the default parameters during the various testing steps.  Over the development life cycle of a single report, all that clicking around could literally add hours to the project!

Ideally, it’s best to eliminate this redundant selection of parameters for each test iteration.  Certainly one could specify default parameters during the development cycle and simply remove the defaults, but this presents the issue of a) remembering to remove the default values from the report before deployment, and b) adding the defaults back to the parameters, should the report require additional work after deployment.

However, I like to use a method for conditionally setting default parameters that does not require predeployment changes.  By leveraging a little VBScript in your parameter defaults, you can easily eliminate the need to manually set parameter values on each test execution of the report. 

The method I’ll describe uses the following methodology:

• Set up a Boolean control variable defining whether default values should be used
• Set up default values for each parameter, and make the use of those values dependent on the control variable

The control variable essentially gives permission to each of the rest of the parameters to use the specified default values.  If the control variable is not set to True, then no defaults will be used.

Demonstration

To illustrate this method, I’m going to create a report using a modified version of the AdventureWorks database.  My test report is configured with 7 different parameters, all but one of which is required to have a value selected:

If I click really quickly (and the server is responsive), I can set all of the parameters to the values I want to use for testing in about 25 seconds.  It doesn’t sound like much until you consider that, if the report logic or layout is complex, I might test the report in the SSRS preview window 50 times or more in a day as I make incremental changes.  That’s about 20 minutes worth of clicking in a single day.  Personally, I’d rather use that 20 minutes for something more productive!

Configure the Control Parameter

To save time (and potentially a carpal tunnel procedure), we’re going to add an additional parameter to the report.  I’m going to call this parameter pUseDefaultValues, and configure it as a Boolean value:

You’ll notice that I’ve set the visibility to Internal, meaning that this new variable cannot be set externally.  You might also consider setting this to Hidden if you choose to pass the value of the control parameter to the report (such as through a URL parameter), but for demonstration purposes we’re going to leave it as Internal and configure the logic to set the value at runtime.

To set the value of this variable, I’m going to use its Default Values tab.  I’ll create a new default value for this control variable, in which I will set the value to True if a certain control condition is met.  In this case, the most logical condition is to test the user ID of the account executing the report, which I can do by interrogating the SSRS built-in field value User!UserID. For demonstration purposes, I’ll keep it simple and just check this value to see if my own account is the one executing the report.  If so, the variable pUseDefaultValues will be set to True.

As shown, you can use the value expression to test whether the user ID executing the report matches a specific string.  The resulting True or False value will subsequently be consumed by downstream parameters as a flag to either use or discard the default values we’ll provide in the next step.

Remember that you can use other criteria for setting the control parameter value – you could access other built-in fields (User!ReportName, User!ReportFolder, and User!ExecutionTime, among others), use the same logic to create a larger list of users who would see the default values, or go wild and create your own custom control variable logic using VB code.

One last point about the control parameter: Make sure to push this new variable to the top of the variables list (I wrote about the importance of parameter order here) since other parameters will be dependent upon the value of this one.

Configure the dependent variables

With the control parameter logic in place, let’s look look to the downstream parameter values, where we will conditionally set default values based on whether the former is set to True.

For each parameter we wish to conditionally set, assign a value in the Default Values tab.  We’ll use a value expression to test the control parameter value and, if it is True, we’ll assign a specified default value:

As shown, we’ll set a default value of “US” to the Country parameter if the control param is True; otherwise, a null (or Nothing in VB) is assigned.  You’ll apply the same pattern to each dependent variable, specifying the conditional value to set for each variable.  Note that this still works if you need to assign multiple values (assuming the downstream parameter in question is configured to allow multiple values) by creating an entry for each one and applying the same type of IIF() statement to each one.

Conclusion

This quick tip won’t change your life, but it may save you some time if you routinely deal with report specifications that disallow default parameters, this quick setting can be a time saver during the development and testing cycle.

In a future tip, I’ll expand on the logic I’ve defined here to describe how to build a data-driven parameterization scheme which can provide different default values for different users.

Have you been thinking about speaking at the upcoming SQL Rally in Dallas? If so, remember that the deadline for submissions is coming up fast – you only have until this Sunday (1/22) to add your abstract to the list of potential sessions.

Why Speak at Rally?

Great question! After all, speaking is hard work and requires a lot of time to prepare. I got the opportunity to speak at last year’s inaugural SQL Rally event, and I am happy to report that it was a great experience.

I want to set you expectations appropriately, so I should dispel any rumors about speaking at this event. To be clear, the following are NOT reasons to present at SQL Rally:

• You’ll get a huge speaking fee just for showing up.
• A private limousine will chauffeur you everywhere you need to go.
• Brent Ozar will see your presentation, and will be so impressed that he’ll invite you to join Brent Ozar PLF<your initial here>.
• You'll leave with a huge following of #sqlkaraoke groupies.
• You will be appointed to a seat on the PASS board.

Sadly I can’t promise that any of these things will happen simply because you come to Dallas to present at Rally, though your mileage may vary.

I can, however, enumerate some very real expectations that SQL Rally speakers can bank on:

• You’ll take your presentations to the next level. Perhaps you’ve been speaking at local events for a while, and maybe even presented at a SQL Saturday event. Do you have your eyes on speaking at national/international events such as the SQL PASS summit, SQL Connections, or SQL Bits? Participating in a regional event such as Rally can help to position you for a run at a larger event.
• You’ll get a free pass. Those selected to speak at the event will receive complimentary admission to the event.
• You’ll get to know some awesome people. Of course, as a seasoned speaker you already know a lot of people in your area. However, presenting at a larger event such as this exposes you to a wider variety of people, including PASS board members and HQ staff as well as other experienced presenters and chapter leaders. Expanding your network will have an impact on your career (write that down).
• You have a good chance of being heckled by local MVP and world-class heckler Sean McCown. Also, SQL for Smarties author Joe Celko is just down the road as well.
• You’ll get to hang out in a championship city – with me! Dallas is home to the world champion Mavericks, the American League baseball champion Texas Rangers, and former world champion teams including the Dallas Cowboys and the Dallas Stars. Plus, I’ll be there!

Regardless of which of these motivates you, you won’t be disappointed by coming to Dallas for this year’s SQL Rally. Submit your abstracts today, and I’ll hope to see you there in May!

Like many others, I’m planning a few changes for my life for next year.  I’m a believer in finding one’s own way, while still abiding the advice of the wise.  Often called upon for inspirations are the timeless words of Confucius, the inspiring speeches of Martin Luther King, the controversial yet insightful analyses of Sigmund Freud, and the various authors of the Christian Old Testament book of Psalms.  I find value in all of these, but for next year, I’m trying something a little different.  I’m going to look a little further off the beaten path to the words of one of the most unforgettable personalities of the twentieth century.

That’s right. I’m invoking the wisdom of Vanilla Ice.

His hair was whack. His clothes were loud. His ink was plentiful. His bass line was stolen catchy.  But in the words of his best known contribution to pop culture, I’ve found inspiration for the next twelve months of my life.

In 2012, I’m living out Ice Ice Baby.

All right, stop. Collaborate and listen.

I spent almost two thousand hours working last year – and that’s just my “official” work which doesn’t include after-hours writing projects, travel time, speaking, and professional reading.  But as I look back on the year, I can enumerate only a few new things that I learned.  Sure, I got incrementally better at the things I already knew, but 2011 is the first year in several that I can’t say that there’s one particular new thing that I’m proud to have truly conquered.

I’ve given this advice to others, but it seems that I’ve failed to follow it this year: Stay on top of your professional development. Don’t just stick with what you now know – branch out and learn.  I’ve had a “do” mindset this year, which is noble, but one also has to take pause to learn new things to continue growth.

Looking forward to 2012, there are several things I plan to either a) learn for the first time, or b) take my existing knowledge of that thing to the next level.  Among them:

• Data Quality Services – there’s not a huge amount of buzz around this yet, but as a business intelligence consultant I see grand potential in this tool.  Every organization, without exception, has bad data.  I’ve spent very little time getting to know DQS, but I’m planning to dive in to it in 2012, and I expect to be knowledgeable enough to be blogging about it by midyear.
• Analysis Services – Although I’ve been working with SSAS for a couple of years now, there are still a few facets of the product that I haven’t yet mastered.  I expect to remedy that next year as I explore the product more thoroughly.
• Kimball – I use the Kimball data warehouse methodology every day, but there’s so much more left to learn.  The Kimball Group has published dozens of books, and I’ve read only parts of a few of them.  To a BI professional, this collection is The Bible.  I commit to not just own and reference these works, but to consume them with vigor.
• Photoshop – Yes, this is completely unrelated to my BI career, but as many of you know, my wife and I started a photography business this year.  Although she’s running the business, I want to be in a position to help her out as much as possible.  I’ve been using Photoshop (the consumer version) for about a year now, and I have learned enough to perform most basic and a few advanced operations.  Toward that objective, I’m acquiring some training materials and am planning a self-guided education to take my postproduction skills to the next level.

Quick to the point, to the point no faking

There’s a dirty word that I keep overusing.  I’ve tried not to say it, but it just keeps slipping out.  Sometimes I say it subconsciously, and other times I blurt it out in the heat of the moment.  As yet, nobody has said it to my face, but I suspect that others may be talking behind my back about my overuse of this word.

The word: Yes.

“Tim, do you want to serve on this committee?” “Yes, I will!”
“Tim, can you proof this white paper?”  “Yes, I’ll get right on that.”
“Hey Tim, can you round up and manage some volunteers to help us do this thing?” “Yes, I’m your man.”

Individually, a Yes is a good thing.  Helping a friend, leading a team, and participating in a charity event are all great things to agree to.  Saying Yes isn’t the enemy.  Saying Yes too much, however, can get you in trouble.  I’ve been too quick to say Yes to almost every request, and I’m embarrassed to admit that it’s put me in a bind a few times this year.

Saying Yes isn’t just a way to end a conversation – it’s a commitment.  When you say Yes to something, you’re telling someone, “Don’t worry about the thing you need done.  I’m going to take care of it for you.”  It’s a transfer of responsibility.  Even if it’s not in writing, it’s a contract.  To say Yes with no reasonable possibility of delivering is self-delusion. Faking.

So for 2012, I’m going to continue to say Yes as much as I can, but I’ll do so more cautiously.  The three big F’s – family, friends, finances – take priority for my Yes.  When opportunities arise that I simply can’t fit in, I’ll have to politely decline.  It takes too much effort to pretend that I have more time and energy than I actually do.  Life is simply too short to worry about being overloaded, which leads to my next point…

If there was a problem, yo, I’ll solve it.

I have to admit that I wasted a lot of time and mental energy worrying about stuff in 2011. I made some big commitments in this past year and for the most part I’ve delivered (albeit late, in a few cases) on those commitments.  However, my tendency to procrastinate was in high gear this year, and I put off until the last minute some tasks that I could have easily checked off my list.  This behavior turns into a vicious cycle – procrastination leads to worry when the deadlines start to close in, the worry becomes a distraction that makes it difficult to concentrate on the thing that needs to be done, and the distraction makes it easier to keep procrastinating.

I’m setting a different standard for myself in 2012.  If something needs to be done, I’m just going to do it.  Is it really that simple?  It can be, yes.  As an incentive, I’m reclassifying some things that I have previously considered to be entitlements.  Watching TV (even my favorite shows), recreational reading, Facebooking, playing Wii, and other similar downtime activities fall into that group; rather than doing these things whenever I choose (or to avoid doing something else), I’m developing a realistic reward system to help balance work with play.  By prioritizing the things that need to be done over the things that are enjoyable but not necessarily productive, I expect to reclaim the time I’ve been wasting in worry.

Take heed, 'cause I'm a lyrical poet

I really enjoy writing, and I’m a pretty decent writer.  So why don’t I do more of it?  Oh, that’s right – I’ve been procrastinating (see previous bullet).

In years past, I’ve been quite an active blogger – I’ve consistently blogged as frequently as once or twice a week.  These days, that number is more accurately measured in blogs per month (or even per quarter).  That’s simply unacceptable.  It’s not that I’m not capable of writing more, nor do I have a lack of material.  I’m a consultant – the world is my muse.  The only explanation is that I’ve not chosen to make blogging a priority this year. 

Now to be fair, I have been busy writing this year – I contributed to the MVP Deep Dives V2 earlier this year, and I’m currently wrapping up another book (SSIS Design Patterns) to be released early next year.  However, this is still no excuse to skip out on blogging, which has a faster turnaround, generally requires less effort and research, is more flexible in terms of content, and often reaches a larger readership than book projects.  For someone with so much to say, blogging should be a part of my regular routine, not just something that I do when I have spare time.

I’m going to light the fire in 2012 again.  I'm setting a goal of one professional blog post per week, with the expectation that I’ll do more than that.  I’ve proven that I’m capable of more than that, and by removing the distraction of worry, I haven’t the excuse of not enough time.  Further, I’m setting aside weekly time – two hours per, as a start – for blogging, and I’m considering that block of time to be wholly reserved for blogging and not a flexible time slot to catch up on whatever task happens to be overdue at the time.

Word to your mother

In the same vein, I’ve recently revived my personal blog and will continue to share on that forum.  In the past, I had posted to my professional blog just about anything, including items that would be of little interest to those who don’t know me personally.  Having the separate personal blog allows me to blog more frequently about topics that interest me without annoying the professional audience on my technical blog.  Plus, I want to be able to share a piece of myself with my kids when they’re old enough to understand, and I don’t want them to have to try to distill the personal stuff that interests them from the professional stuff that doesn’t.

I haven’t made a hard commitment on a number of personal posts, simply because of the variety of frequency in which a bloggable event occurs.   Some might be deep and insightful monologues (I blogged last week about my mom’s house fire back in December of 1980), but others may be quick thoughts about my day.  I’ll blog on my personal site as often as appropriate, and I’ll let that play out however it will.

I'm killin' your brain like a poisonous mushroom

My job is literally killing me.

(pause for effect)

I really, really like what I do. I work with great people, I get to solve difficult and interesting problems, and I don’t have to brave the elements while I work.  Plus, my job isn’t physically strenuous.  And while that last part is a good thing, it’s also a bad thing – it means that I spend almost all of my working hours (sometimes 70-80 of them per week, counting my writing, photo editing, etc.) sitting on my posterior.

Although my numbers (weight/BMI/cholesterol/blood pressure) are reasonable for my age, I’m in the worst shape of my life.  I don’t walk enough and hardly ever run.  I’ve worked up a sweat maybe four times in the last two months.  I deal with a higher-than-average amount of stress.  Any M.D. on the planet who read the last two paragraphs would scream at me that I’m headed for really bad stuff in a couple of decades (if not sooner).

There’s only one way to change this.  I’ve simply got to get off my can and get active again.  I’ve resolved to do this at least a dozen times over the last several years, but the initiative always dies off quickly.  In 2012, that all changes.  My kids are old enough that we can get out and enjoy physical activities together, even if it’s just playing chase-and-tag in the back yard.  There’s a fitness center less than a half mile from my house, where I already have a membership. There are dozens of people in my local and national circle of friends who are involved in fitness initiatives, and many of them are distance runners.  I’ve got the motive, means, and opportunity to get in shape – and as of today, I pledge to do so.  (Note: I’ll be publishing a separate post on my personal blog with the specifics of my goals.)

Yo man, let's get out of here

The year 2011 is almost done.  As you can infer from the above, I’m not altogether happy with my progress this year, and as of now I wash my hands of that old way of thinking.  Now that it’s in writing, I’m committed to follow through, with the expectation that both of my loyal blog readers will call me out if I stumble.

And perhaps by this time next year, you’ll all be jealous 'cause I'm out getting mine.

There’s a large group of people that mean a lot to me.  Some of them I know well, and others I only know peripherally through their relationships with those with whom I am more familiar.  Some of them I like a lot, but with others I rarely see eye-to-eye.  Many of them I consider to be friends, and a few I would say are close friends, reliable confidantes that I’d trust with my car, my ATM card, my secrets.  Most of these people are not geographically close to me, but we make it a point to see each other at least a couple of times per year.

Were it not for the title of this post, you might assume that I’m talking about extended family.  But in this case, I’m describing a group of people who are, at times, as close as my own genetics-and-marriage family.  This it the SQL community, affectionately known as SQL Family (or #sqlfamily, for you Twitter-er-er-ers).

But what is SQL Family, really?  Just a group of people who do a similar job and tolerate each other at conferences?  A band of partygoers whose only commonality is an affection for Jaeger bombs and bacon?  A strange clique that prevents outsiders from coming in?  I’d submit to you that it’s none of these things.  Tom LaRock has challenged everyone in the most recent Internet meme to define the following:

What SQL Family means to me:

SQL Family is needing 30 minutes to walk the 150 yards from the hotel entrance to the elevators because I keep bumping into people that I know.  It’s knowing that I’ll never ride the escalator without passing a few friends going the other way.  It’s the assurance that I’ll never again be that guy in the hotel room at 9pm wishing I was still engaged in the event.

SQL Family is conversing with Bill Graziano and watching him write down what you say in his little notebook.  It’s chatting with Joe Webb and knowing that you have his absolute undivided attention.  It’s sharing an idea with Andy Leonard and hearing him tell you why it’s a great idea (or why it might not work, but very gently).  It’s meeting up with Lori Edwards and feeding off of her passion for community.  It’s asking Kevin Kline for his opinion on, well, anything, and getting a very intelligent and insightful reply.

SQL Family is putting a request for help on the wire to #sqlhelp, and immediately getting dozens of replies, direct messages, and code samples from other Family members.  It’s an open sharing of ideas without fear of IP theft or plagiarism.  It’s an openness to ask any question without getting smacked down.  It’s being able to ask questions about DBCC from Paul Randal, the guy who friggin’ wrote DBCC.

SQL Family is being cursed at by Sean McCown and Denny Cherry (and his wife), still knowing that they have your back.

SQL Family is meeting people you’ve “known” for years.  It’s learning the real names of people you know only by Twitter handle.  It’s sending out a Tweet that you’re arriving in town (almost any town) and getting replies like “How long are you here? Want to meet up?” 

SQL Family is the encouragement you get when you present at the PASS Summit for the very first time.  It’s 100 people telling you that you’re session is going to be great, and the same 100 people asking afterward how it went.  It’s looking out into the audience and seeing not a room full of strangers, but more than just a few friendly faces.  It’s a willing presenter on standby ready to fill in at the last minute to avoid an empty slot in the schedule.  It’s knowing that someone will loan you their laptop in case yours dies right before your presentation.

SQL Family is getting your picture taken by Pat Wright.  It’s a friendly hug from Karla Landrum, and huge bear hugs from both Jes Borland and Erin Stellato.  It’s getting to know the (sometimes) non-techie spouses of our SQL Family members.  It’s an open invitation to split hotel rooms and travel costs to reduce the cost of attending Family functions.  It’s being welcomed into the homes of other Family members.

SQL Family is a line of people cheering on fellow runners as one of their own completes a lengthy run.  It’s encouragement and kind words as one of our own battles a serious health issue.  It’s sincere condolences when one of us loses a close family member, and hearty congratulations when one of us brings into the world a member of the next generation of the SQL Family.

SQL Family is circling the wagons when one of our own is threatened or treated inappropriately.  It’s opening our arms to welcome in new members.

SQL Family is having the ability to disagree or even argue, knowing that, at the end of the day, we’re still a SQL Family.