The Lone Ranger And Data Integrity

September 29, 2010 8 Comments

Many IT Pros can relate to the the Lone Ranger. With the aid of but one trusty side kick, the Lone Ranger stands to protect the unsuspecting townsfolk from the wayward derelicts that plagued the American frontier. He also ate bacon three meals a day.

Like the Lone Ranger, IT Pros ride the fast-paced trail of the business environment, protecting key business systems from improper access and malcontent users. We’re often alone in our quest for data integrity, the lone voice standing out against complete and utter data anarchy. And we love bacon!

But I Neeeeeeeeeeeed It

Ok, I maybe overstating the issue slightly. Most users don’t awaken each morning with the hopes of bringing complete and utter data anarchy to our systems. They simply need something that’s beyond the current capability of the system. So they make do as best they can.

Secondary Systems

Some store data in Excel spreadsheets or Access databases (gasp!). Most of these secondary systems start off as a simple way to help one user track certain data. Over time, however, they can grow and spread like wet Gremlins. A second user discovers the existence of the neat new work-around. Then another, and another.

The next thing you know, the single-user, temporary, and unplanned work-around has become a team-level or department-level mission critical system. It’s frequently inaccessible to others who would benefit from the data, it’s difficult to back up, and it’s now yours to support.

All of this leads to disparate systems and silos of data. Maintenance becomes difficult; accurate reporting, impossible.

Getting Creative

Other users get creative with how they store data in the system. They find and exploit areas of the supported system to accomplish what they need to do. Sometimes they shove multiple pieces of data into one column; for example, they store two are three email addresses in the one allotted field. Or worse, they save two completely different types of data in one field; for instance storing a telephone number and an email address in the email address field.

Users can also repurpose an existing column for their own use under certain circumstances. They may put the customer’s email address in the P.O. Box field if they customer doesn’t have a P.O. Box.

Both techniques make producing reliable reports from the nearly impossible. Some of this can be controlled with data validation, but not perfectly. Users can still find ways around most protective measures.

Mirror, Mirror On The Wall

Why, oh why, do they do this to us? Don’t they realize that taking such liberties with data makes it impossible to have consistent and dependable information? Surely they’ve heard “Garbage In, Garbage Out” Oh, but they do it anyway.

But before we lash out and condemn them for making our lives miserable, we should first look in the mirror to see if any of the blame lies with us.

Have their requests for changes to the supported system continually fallen on deaf ears? Are their changes perpetually on priority level D? Have they asked for a newer version of the supported software, but been denied because we’re too busy to test and install it? Do we default to no rather than to yes?

It’s Not Us Against Them

Unfortunately in many organizations, the IT department has an almost antagonistic relationship with the departments and people they support. This is counterproductive, both to the organization and to our own goals. It makes life difficult for everyone.

So, don’t be the Lone Ranger. Reach out and work with users to support their goals. Strive to understand their needs and then look at the technology and processes that may help to fulfill those needs. You won’t be able to solve all of their problems, but building that relationship will help to solve some of your own problems.

Filed under Business, Consulting, Management, SQLServerPedia Syndication

Stop, Drop, And Roll Before Answering

September 17, 2010 12 Comments

“I can’t believe what they’re telling me!”, that’s what the little voice in my head screams in some of my initial client meetings. Fortunately it usually passes through the marketing and PR filter en route to my mouth and it comes out “That’s interesting. How’s it working for you?”

You wouldn’t believe some of the systems and processes I’ve seen over the course of my 16 years as a consultant.

Some people know that they have a horrible patchwork of broken systems that are loosely held together by duct tape and bubble gum. Others think their system is “flexible except for this one issue we’d like for you to fix.” I’ve learned that one man’s flexibility is another’s bad design.

Fortunately experience begets familiarity and some of the issues I see are remarkably similar to ones that I’ve solved in the past.

It’s tempting to show them just how smart I am and immediately jump to the solution. In fact, cutting to the chase would save them time and money so everybody wins. Except there’s only one problem: I didn’t listen.

You’ve Got To Listen

Listening is incredibly important, not only for consultants, but for everyone. Regardless of your walk in life, being an good listener will help you as you work with other people. Listening helps people to feel understood, to feel that you care. And that goes a long way in building a relationship.

Conversely, if you don’t take the time to listen, other people don’t feel understood. You’re not listening, so you simply cannot understand what’s going on. They assume you don’t care or that you’re just too self-centered. Without a good understanding of the problem, they reason, how can you possibly offer a good solution?

So before you offer a solution, take some time to stop, drop, and roll.

Stop

Don’t interrupt their description of the problem. Let them tell it to you in all its glory, even if you’ve heard it all before. It’s important to them to tell it.

Most people feel their situations are unique and quite different than the problems you may have faced in the past. Let them tell you why. In fact, you may actually learn that their problem is subtly yet significantly different that what you were expecting.

Two rules to keep in mind. First don’t interrupt. If the other person is talking, be quiet. You can nod your head in agreement or smile when appropriate, but don’t say anything. Second, when the other person has stopped talking, count to 5 to make sure that he is really finished and not just regrouping his thoughts. Only then do you know it’s your turn to talk.

Drop

Drop a few clarifying questions back to the other person. Ask them to explain in more detail or from another perspective. Many people will focus solely on the immediate affects of the issue or what they deem as pertinent. If you delve a little deeper you can uncover a lot more information. Asking relevant questions also reassures them that you are listening.

Don’t limit yourself to abstract or technical questions. Ask them about the impact of what they are describing. How is this situation affecting them personally? How has the problem affected their job or other’s perception of them?

Getting personal helps you to understand their motives. This is especially powerful when talking with a prospective client.

Roll

Roll back what you hear them saying. Restate their problem and its affects. Summarizing or restating what they’ve told you helps makes sure that you, do indeed, understand their issue. It also communicates to them that you understand.

This also helps to transition the conversation. It’s moving from their turn to talk to your turn. They’ve described the problem, now it’s your turn to offer suggestions or recommendations.

Solve

After they have completely described their scenario to their satisfaction, then you are free to offer your guidance. Your initial assessment may have been right on, but they wouldn’t have listened to you since you didn’t listen to them. Listening cost you only a few more minutes of time and the rewards were definitely worth it.

So, the next time you’re visiting a prospective new client or someone approaches you with a problem, remember: Stop, Drop, and Roll.

Filed under Consulting, Professional Development

The Best Four Sentence Blog Post Ever

September 16, 2010 Leave a Comment

“Eliminate all unnecessary words from your writing.” That’s the advice given me by Jeremiah Peschka (blog, twitter) at SQLSaturday 51 when I was picking his brain on writing. He recommended that I read “On Writing Well” by William Zinsser. I’ve added it on my list.

In a recent blog post, Seth Godin took this technique to an extreme. The result was nothing short of profound. In four short sentences, Godin managed to admonish and edify businesspeople the world over. Wow.

The power of making every word count.

Filed under Blogging, Business, Professional Development, Resources

Using Excel As A Reporting Services Datasource

September 15, 2010 7 Comments

It’s been said that Excel is the poor man’s database. I don’t know about that, but I can tell you that an awful lot of data lives in Excel spreadsheets in corporate America and around the world. Whether it’s project timelines or employee lists, many business depend on Excel.

This is evident in my consulting practice. It’s also commonly mentioned when I’m teaching Microsoft SQL Server Reporting Services courses for clients. Students want to see an example of a report that uses a Microsoft Excel Spreadsheet as its datasource.

Dude, Where’s My Data?

The first step in using an Excel spreadsheet as a datasource is to create a System Data Source Name (DSN) for the spreadsheet. Open the Data Sources (ODBC) applet from Control Panel | Administrative Tools. Of course the name and location of the applet can change depending on the version of Windows you’re using.

Add a new System DSN, providing a name in the Data Source Name box, and selecting the workbook that you wish to use for your report.

Next, from within Business Intelligence Developers Studio (BIDS), create a new data source. Change the Type to ODBC. Click the Edit… button and select the DSN that you just created in the prior steps.

Creating the Report

To use the data from the spreadsheed in a report, create a new Dataset. For the query, type

SELECT * FROM [Sheet1$] WHERE Is_Enabled = 1

Note that you can replace Sheet1 with the actual name of the worksheet tab in the Excel Workbook. The syntax requires that the name of the worksheet be followed by a dollar sign ($). As such, you must enclose the worksheet name in square brackets [] as shown below since a dollar sign is not considered a valid character in a select statement.

Once we’ve created the Dataset, we can display it in a report like any other Dataset.

And there you have it, a Reporting Services report that uses data stored in a Microsoft Excel spreadsheet as the source of its data.

Filed under Reporting Services, SQLServerPedia Syndication

The InfoSec 10 Conference in Nashville

September 13, 2010 Leave a Comment

For those in the Middle Tennessee Area: the Nashville Technology Council and Middle Tennessee chapter of Information Systems Security Association (ISSA) are hosting “InfoSec 10: The Changing Security Landscape” tomorrow in the Nashville Convention Center.

If you’re interested in security issues, whether from the technical or management perspectives, for healthcare and other industries, check it out.

Filed under Events, Security

I’m Choosing To Cheat

September 10, 2010 7 Comments

I’m cheating. And I bet that you are, too. In fact, most us of cheat at one time or another.

That’s the premise of Choosing to Cheat by Andy Stanley. In it, Stanley contends that we are all far too busy. We have too many commitments at work and at home; we’re stretched too thin, being pulled in every direction. There’s no way we’ll accomplish everything. Sound familiar?

So Many Things, So Little Time

So what do we do? We try as hard as we can. We spend a few extra hours at work to try to get caught up, cheating our family of that time. We take a long lunch or call in sick to get some personal things done, cheating work.

But we still come up short. No matter how hard we try, some things will be left undone. There’s just too much to do. We can’t do it all. We will miss some commitments that we’ve made. The only question is which ones.

Who Are Cheating?

For many of us, the two largest sources of commitments come from work and family. Think about those for a moment.

At work, you are replaceable. As good as you may be at your job, you’re still replaceable. If you quit, they will find another person to do your job. If you do your job poorly, they’ll fire you. If business becomes slow, they’ll lay you off. The company has very little, if any, loyalty to you as an individual. Yet many of us have great loyalty to our work. We work long hours, often burning the midnight oil, in the hopes of getting caught up or being recognized for a promotion.

Conversely at home, no one else can fill your shoes. You are the only husband or wife that your spouse has. Only you can be the mother or father to your kids. No one else can fill your role. And your family has nearly unlimited loyalty to you.

Yet when push comes to shove, many of us choose to cheat our family rather than work. We choose to devote extra time to the entity that has zero loyalty to us while robbing those that have nearly unlimited loyalty to us. We focus on areas where we are replaceable at the expense of areas where we’re irreplaceable. We choose to spend our time doing things that will be obsolete in five short years while cheating in areas where our impact may be felt for a lifetime or even longer.

Why? And what could we, should we do about it?

Making A Conscious Choice

In Choosing To Cheat, Stanley tackles this problem. He doesn’t pretend to have easy answers. But he does call your attention to the problem and offer some creative ways to approach your work and home life. I definitely learned a lot from the book.

It’s a short, easy read. You can easily finish it in one sitting or on a short flight. But it can be life changing if read with an openness and taken to heart. I have a good friend who would say that it literally helped to saved his marriage.

So, if you’re cheating, are you choosing wisely?

Filed under Book Reviews, Business, Productivity, Professional Development

A Community Divided

September 3, 2010 14 Comments

People have been screaming from rooftops for a month now about the PASS election process, ever since a beloved member of our SQL Server community was shunned in his bid to run for the PASS Board of Directors. The community has reacted vocally. PASS has reacted defensively. And unfortunately much of the back and forth has been speaking “at” each other rather than “with” each other.

Watching this has been almost as bad as watching someone burn bacon! Almost.

Kevin Kline (blog, twitter) has asked a number of leaders in the community to participate in the discussion through a series of guest posts on his blog. I believe that he’s trying is to bring the various sides together in a constructive way. With all of this passion in the community, we should be able to improve things. This is my contribution to the series.

Before going further, however, I’d like to first frame the discussion by describing the essentials of the debate as I see them. There are certainly a lot of nuances, however insomuch as the different facets can have one voice, I’ve tried to capture that. There will be, of course, deeper issues and varying opinions. And it’s entirely possible that I’ve missed the point altogether.

The Community Side

Steve Jones (blog, twitter) has proven himself time and again to be a staunch advocate for the community. His qualifications for community leadership are unmatched.

  • He’s a renowned SQL Server expert and Microsoft MVP
  • He has the media experience that PASS once coveted
  • He’s a good businessman with a proven track record of success
  • He’s a polished speaker and regular blogger
  • He frequently donates his time at conferences, SQLSaturdays, and User Group meetings
  • He’s wildly popular in the community

Steve would easily navigate the Nominating Committee (NomCom) and most certainly be elected. So I thought when I wrote a recommendation letter that accompanied his application.

Like most in the community, I was utterly shocked when I learned that he didn’t make the final cut. Many, if not most, in the community were outraged. And rightfully so. The NomCom rejected someone that the community obviously wanted, someone who would most certainly have been elected if allowed to be on the ballot.

But, this isn’t about Steve. This isn’t even about the election. This is about PASS putting itself before the community that it supposedly represents.

The PASS Side

For the past few years, PASS has taken quite a bit of heat from the community to be more transparent in its processes and decision making. The community doesn’t want a benevolent dictator; it wants peers who are willing to lead on our behalf because we don’t have the time or inclination . That message was made clear years ago.

Since then, PASS has worked really hard in this area and has made clear progress in many, many ways. Its finances are posted for all to see. Unabridged meeting minutes are published on the web just a few days after each board meeting. More of the board members and key volunteers blog about their PASS activities than ever before. We asked for transparency and we’re getting it. All we have to do is log on and look at the information as it’s made available.

The NomCom, which has bore the brunt of the community’s ire for the election controversy, is a prime example of the newfound transparency. The NomCom is charged with vetting each candidate and presenting a slate before the board. Before the NomCom was formed, the procedures and guidelines under which it was to operate were created and distributed for review. After a series of reforms and tweaks, the NomCom was given its marching orders and asked to do their job.

After weeks of work, the NomCom, a group of our peers that we’ve entrusted to do the grueling grunt work for us, reached a conclusion using the formulas and procedures given it. The NomCom members realized that their conclusion would be questioned and controversial. And they paused to reflect on that.

The question: Should the NomCom discard the quantitative results and replace them with on its own subjective feelings? Should it do what it felt the community really wanted by disposing of the pesky procedures that had been so carefully refined?

To do that would have really opened the doors to allegations of PASS being an old-boy-network . To discard the rules at its own discretion and pick the candidate it thought best would be the height arrogance.

The NomCom was in a pickle. Stick with the approved procedure and eliminate a popular candidate? Or dispense with the approved procedures and make a subjective call? You can’t win with those as your two options.

So they made the call to accept the wrath they they knew was sure to come. They stuck to the approved procedures set forth in the NomCom’s mandate. The integrity of the process is too important to toss it aside. It would be a huge step backward in the quest for transparency to do anything else.

I thank the members of NomCom for serving. I also commend them for doing what they believed to be the right thing even though they knew it may not be popular. That takes guts. It takes character. It takes integrity.

And it’s people who possess those characteristics that we want leading the community.

So Who’s Right?

To me the answer is obvious: both sides are right. On the one hand, I’m very frustrated that the candidate for whom I wanted to vote wasn’t on the ballot. I cast my ballot yesterday and it felt a bit hollow.

Yet I would have been equally, if not more, angered if the established process had been subverted to put Steve on the ballot at the expense of one of the other candidates.

The NomCom is not at fault here. The PASS Board is not at fault here.

At fault here are the procedures that governed the election process. I have every confidence that those procedures were far better than in prior years. Yet in this case, as extreme as it may be, they fell woefully short.

Where Do We Go From Here?

I think that the one thing on which we can agree is that we don’t want to find ourselves in this predicament twelve or twenty-four months from now. We want to learn from this, take corrective action, and move forward.

So what do we need to do to ensure that this doesn’t happen again?

  • Acknowledge that it was the process that failed and stop pointing fingers at our peers
  • Take heart that we have a slate full of good candidates even if our preferred candidate was left out
  • Accept partial responsibility for the community and get more involved

Should we throw the baby out with the bathwater and dispense with the NomCom? I don’t think so, but it’s a conversation that should take place.

Should we refine the existing procedures to handle situations like this? We must be careful in codifying too many things since situations will arise in the future where flexibility is required.

We need to have these conversations.

A Note About Transparency

Many years ago when Kevin Kline was the VP of Finance for PASS and I was Director of Logistics, we shared a cab to the airport after a Community Summit event. During the ride, we talked. I remember telling him that as a member of Ducks Unlimited, I receive an annual financial statement from the conservancy. It contains detailed information about the group’s finances. Knowing that makes me feel good about giving to the group.

I commented that as Directors of PASS, we should provide that kind of information to our members. And if we are not comfortable delivering that level of detail, we should do something about our finances so that we are comfortable with it. It took a while, but we did. We changed management companies and implemented a lot of other changes for the better. We took the first of many steps toward better fiscal and procedural transparency.

PASS is no longer a monolith that speaks with one voice. It has truly made great strides toward being a group of our friends and colleagues who are willing to freely give of themselves for our benefit. That’s not to say that PASS has completed its journey toward transparency. It hasn’t, but it is much further down the road than it was just a few short years ago when Kevin and I had that conversation.

And this is another opportunity to further refine the way PASS works, for the better.

Filed under Community, SQLServerPedia Syndication

Follow

Get every new post delivered to your Inbox.