T-SQL Tuesday #14—Julie’s 2011 Goals in “the Cloud”

 It’s T-SQL Tuesday and the topic is Technical Resolutions for 2011.  Thanks to MidnightDBA  (Blog|Twitter) for hosting this month’s event. 

 My 2011 Technical Resolutions can best be summarized with a song….

 I’m just kidding—I can’t summarize my goals with a song.  I don’t write songs. But I am great at surfing the internet and lookie what I found at www.wordle.net. It was a handy dandy, feel free to distribute your  creations, category cloud maker!  So here is my 2011 Technical Resolution Cloud:

T-SQL Tuesday #14 – Audrey’s (Career) Aspirations for 2011

Here we are again for another T-SQL Tuesday. This month’s event is being hosted by MidnightDBA (Blog|Twitter). The whole crazy concept is the brainchild of Adam Machanic (Blog|Twitter). If you’re interested in what this thing is about, check out this month’s invite.  A quick bit of gushing praise for the whole T-SQL Tuesday thing: For me, deciding what to write about is the hardest part of blogging. When someone tells me, “Hey Audrey. Write about this. And we promise a ton of people will come read about it”, well, you don’t have to tell me twice. I’m on it like the paparazzi at a celebutante convention.

The topic of this month’s event is “Resolutions”, which is, you know, totally apropos since it’s the first month of the year and all. Personally, I don’t make many resolutions, and they’re usually boring. For example, last year’s personal resolutions included: 1) Lose 10 pounds (I didn’t) 2) Drink more water (I did), and 3) Clear out clutter (Sort of, a little). I do establish career goals, and as my mama always said, “They aren’t real until you write them down”. She was a list-maker. She would make a list, and if she did something that wasn’t on the list, she’d add it just so she could cross it off. Watching her made me a list-maker too. It’s part of my morning routine to sit down and write down what I’m trying to accomplish that day. Yes, I said write. Like with pen and paper. I need the ritual. So it makes sense to get my to-do list together for 2011. Thanks to MidnightDBA for giving me a good excuse to really think about it.

Audrey’s (Career) Aspirations for 2011

1) Learn more about SQL Server internals. I’ll be reading Microsoft SQL Server 2008 Internals by Kalen Delaney (Blog|Twitter), Paul S. Randal (Blog|Twitter), Kimberly L. Tripp (Blog|Twitter), Conor Cunningham (Blog), and Adam Machanic (info in 1st paragraph). This book has been sitting on my shelf, glaring at me for not reading the entire thing for far too long. It’s time to go cover-to-cover, baby.

2) Learn more about Analytics, using SQL Server as well as other products. I want to get better at the UDM/cube/presentation portion of the BI Stack. I’m still figuring out the right approach for this aspiration. But, I can assure you that Project Crescent and BISM are somewhere on the plan.

TANGENT: When I hear Project Crescent, I immediately think “crescent roll”, and then I think “Crescent City” which is New Orleans, and then I think of beignets, because they are tiny bits of powdered sugar-dusted heaven. Then, I get a little homesick, because there aren’t many places around Atlanta where you can get a good beignet. I grew up near Houston, which is close enough to NOLA that the good food tended to bleed over into our part of Texas. They should have just named it Project Beignet to save me the time it takes to get through my stream of consciousness. :END TANGENT

3) Re-read The Data Warehouse Toolkit by Ralph Kimball and Margy Ross. Why? Well, I haven’t read it in a couple of years, and I have a build-out of a dimensional model on my plate. Reading this book again is like stretching before the big game. I’ll feel warmed-up and ready to go when the project really gets rolling.

4) Learn PowerShell (for SQL Server). You know, there are a lot of reasons for learning PowerShell, but the one that motivates me the most is this: My first programming language was Turbo Pascal 7.0 during my senior year of high school in 1994. (I’ll save you the math… I just turned 35. [sigh…]) My first RDBMS was Oracle 7.3 in 1996. Neither had snazzy GUI’s to help me limp along as I was learning. I love how my world has come full-circle. Here we are in 2011, and people are singing the praises of a non-GUI-based way to interact with SQL Server. I want in on the fun. AND… I’m tired of hearing from Aaron Nelson (Blog|Twitter) about how great it is and not knowing for myself.

5) Finish my certifications. I got my MCTS certs for SQL Server 2008 Business Intelligence and SQL Server 2008 Database Development late last year. I promised myself that I’d go on to the MCITP exams this year. This one has a timeline too… I’ll get both before the end of the summer. I’m toying with the idea of going after SQL Server 2008 Database Administration too. You know, because they’re there. And I’m so ridiculously competitive that the idea of leaving the tests untaken is bothering me.

6) Blog, and blog consistently. My fellow Datachix, Julie Smith (Blog|Twitter) and I promised each other that we’d each blog every other week. That’s 26 blog posts for me this year. I’ll go a step further and say that 20 will be technical posts. You’re not getting much fluff from me this year, my friends.

7) Speak. I will present more. I will learn to present virtually. I will rock the house with my awesome, well-prepared, techically relevant, and entertaining presentations.

8 ) Finally, I will be a great consultant. I already know how I’m spending 2011 from a client standpoint, and I’ll be working to make sure that they look back on 2011 and remember it as the year that they finally got their data straight, their processes together, and their analytics moving in the right direction.

So, in conclusion, my 2011 aspirations are: Read, Study, Read, Learn, Test, Blog, Speak, Rock. Easy-peasy, right? Whoo-boy! I’ve got to go. I need to get started.

Wait, before I go… My 2011 aspiration for all of you is that you have a wonderful, satisfying, and all-around kick-ass year. Make time for the things you love. Learn something new. Try something you’re scared of. Make some new friends. Go for a walk in the rain without an umbrella. Watch a sunset or two… and maybe a sunrise. Tell the people you care about how awesome they are. When presented with a great opportunity, go for it. When 2011-12-31 23:59:59.999 rolls around, you’ll be glad you did. I’m totally rooting for you!

Thanks for reading all the way through my list, and if you see me, ask me how I’m doing on it. There’s nothing quite like public accountability to keep me honest.

Rock on, my friends…

—Audrey

T-SQL Tuesday #13 – Make Nice with the Business

This month’s T-SQL Tuesday asks the following question:  What issues have you had in interacting with the business to get your job done?   First, much thanks to Steve Jones (Blog | Twitter) for hosting this month’s event. 

As the old joke goes, this job would be great if it weren’t for the users.  (insert rimshot here) Seriously folks, my job title has “Business” in it, so I’d better be able to figure out what the user is asking for.  Being a BI Consultant is one part developer, one part psychologist, one part archaeologist, and two parts translator.  If the user asks for a report, that’s great.  Actually, that’s more than I often get.  Sometimes I get vague, conflicting requests.  Sometimes I get requests that just confuse me. 

Take my Top Five Favorite requests from end users: 

5) “There’s this report that John used to produce in 1997.  It was great.  It took him 12 days to put it together, but it had everything I needed.  I want that, only sooner.”

It’s a shame that John checked himself into a mental facility in 1999 and is now spending his days creating lovely landscape paintings with non-toxic watercolor paint and taking meds on a rigorous schedule.  It’s also a shame that no one can remember what the report looked like, only that it was really good. 

4) “I want to slice and dice the data however I want.”

Not bad.  At least they’re referring to being able to filter the data in some way.  The scary part of this request:  “However I want”.  Do you want to slice the data by what color shirt you were wearing on that day?  Let’s narrow this down a bit. 

3) “I’m not sure what I want, but I’ll know it when I see it.” 

–Sobbing–  You’ll know it when you see it?  Okay.  Okay… let’s extend that deadline. 

2) “The data should be sexy.” 

Sexy?  Let’s define sexy.  I think beautifully-structured, properly normalized, and well-performing data is sexy.  Is this what you meant?  No?  Wow, I thought we were on the same page here. 

And my all time favorite user request….

1) “I want it to be like an iPhone.  You know, an Apple feel to it.”

What, you want the data to wear a black turtleneck?  You want to be able to swipe and pinch the data? 

Users, I love you.  You keep my mortgage paid and my kids in shoes.  Truly, if it weren’t for you, I’d be out of a job.  But, sometimes, you make for good happy hour stories. 

Just this past week, I ran into a situation that BI Consultant nightmares are made of.  Let me set the stage: 

I’ve been at a new client for about 3 weeks.  Let’s just say I’m not exactly the resident expert yet.  It’s a very large client, with a very challenging data environment.  It’s the beginning of the month, which means that end-of-month and some quarterly reports are due.  Most of these processes have not been automated yet.  Read:  We’re copying query results into Excel and e-mailing them.  The one guy who is the resident expert is on vacation.  I get an e-mail asking for a report that I’ve never seen before. 

I take a deep breath, gather myself, and respond, “Yes, I’ll get that to you.” 

I make a quick phone call to the guy who’s on vacation, get a bit of info, and create the queries to run the report.  I slap that data into Excel, e-mail it out, pay myself on the back, and go home.  Everything looks great from my end. 

Next morning, 7:24 AM, an e-mail is delivered to my inbox.  To paraphrase, it said, “I don’t trust these numbers.  There’s a huge variance in the 3Q numbers that we can’t explain.  I have a meeting at 9:30 about these results, and I’d like to definitively say whether they’re correct.” 

Crap.  I’m not even through my first cup of coffee yet.  I top off my coffee, and begin my investigation.  This is the archaeologist part of my job.  On the surface is a report that isn’t making sense to the business.  My job is to dig backwards until I either come up with an explanation or prove that the data is correct.  No easy task, considering that I honestly don’t know where much of this data is sourced from. 

Step 1:  Verify Your Own Work – First, I opened up the query I ran to produce the report.  Key point here.  I saved it.  I save everything.  My first move was to verify syntax.  Did I do something stupid like join a table to itself or create a funky WHERE condition?  Did I accidentally paste something into Excel improperly?  (Tangent:  This is why automation is a Good Thing.  Eliminates human error.)  Nope, All quiet on the Western Front. 

Step 2:  Verify the Data Load – This data was sourced from a report database that is populated via an SSIS package.  Luckily, the guy who wrote the package sits a few rows over from me.  I check in with him, and he confirms that nothing has changed since the last load.  I ask for the source files anyway so that I have some outlets for additional research. 

Meanwhile, my Key2 Consulting compadre, Josh Robinson (Blog), is doing something really cool to help me out… He pulls the data into Excel and fires up PowerPivot.  Using the graphing functionality he’s got with the tool, he can point out anomalies in the data by different dimensions to try to narrow down exactly where we’re seeing the suspect data.  I was writing manual PIVOT statements in T-SQL, which was much less efficient than what he was doing.  Lesson Learned:  PowerPivot ain’t just for end-users.  It’s a great diagnostic tool. 

Step 3:  Verify the Source Files – I take a look at the source files.  Ha!  There!  The source data has the same disparity that the business users are complaining about.  This is good news.  This is a lead.  Now, I just have to find out who created these files. 

Step 4:  Find the Source File Owner – I make some calls, do some checking, and voila!  I have a name and a phone number.  It’s a very large company, so he’s halfway across the country, but I’ll still be able to get in touch with him. 

Step 5:  Contact Source File Owner – I call the guy who creates the source file.  He doesn’t know me from Eve.  After the requisite introductions, I ask about the change in the data.  He responds, “Oh yeah, we changed the way we’re pulling this piece of data.  You should see a huge increase in the number of gizmos from this month to that month.”  I thank him profusely, and move on. 

Step 6:  Wrap it all up – I make a courtesy call to the woman who is probably drumming her fingers on the table waiting for the data.  Then, I write up an e-mail with our findings, and I send it out to anyone who might care.

Wait, you thought we were done?  No, we’re not done.  Let’s back up a bit.  Yes, we explained the questionable data.  But a good archaeologist knows to dig just a little bit more.  I ask the business users, “Hey, so that source data changed, and the change was applied to this month but not that month.  Are we okay with that?”  This lead to another round of conversations.  Ultimately, we decided to keep the data as-is and note the reason for the change.  The point is, if you want to try to make friends with your business users, you answer the questions they have, and then try to think of the ones they haven’t asked yet.  They’ll love you for it.