Seattle in October

By Airborne Geek on September 29, 2011

Next month, something awesome is going down.

PASS 2011

I haz a happy

For the first time since the Two Weeks in Junetrip in 2008, we’ll be going to conferences. For the first time in…well…ever, the conferences we’re going to are the same one at the same time.

Finally, FINALLY, we’re heading to Seattle for the Professional Association for SQL Server’sannual Summit. This conference was first put on my map when it basically replaced the MS BI conference in 2009 (which wound up getting scheduled for every two years). At the time, I was getting deep into my first “real” DBA job, taking the somewhat long and temporarily divergent road towards a career in BI. I came very close to footing the bill myself for the conference that year, and it would have been pretty interesting to see what would have happened since then had I made that move…

Anyway, we’re both going for real this year, and I’m looking forward to just about everything about it. I can’t wait to meet everyone from the SQL Server community that I haven’t yet (which is most everyone), to possibly sing badly (#SQLKaraoke), to try to split myself up into three pieces to go to all of the sessions that I want to go to (although I haven’t run through the schedule yet), and to try to find time to mumble onto the keyboard to get some posts up here about whatever is going on.

There are a couple things different about this one, though. I won’t be attending a precon session for the first time. That’s a little weird for me, as I think of it as part of the conference experience, and I always get a lot of good info out of them. When I’ve gone to conferences before, I haven’t had great reasons to go to BI-related sessions; I’d just check them out because I’d have the time and/or because I was interested in that sort of stuff. This time, pretty much the primary reason I’m attending is to go to every SSAS session I can. This is really awesome for me.

The other big thing that will be different is my attendance in after-hours events. For the TechEds & Connections conferences I went to, I went to very few after-hours events, either official or unofficial. We’re going to be hitting as much as we can this time, spending time with as many of our fellow Data Nerds™ as possible. Hopefully we won’t annoy too many people—we wouldn’t want to get blacklisted or anything 😉

So, in about a week and a half (!), we will get to spend some quality time in the back of a Southwest Boeing, KBNA—>KSEA. This trip will put me both the furthest West & North I’ve ever been (I don’t get out much), and that will be neat, too. We’ll be missing the photowalk on Monday, which is a bummer, but will be in town through the following Monday, so we’ll have plenty of time to spend with the Emerald City.

I know it’s getting close, but there’s technically still time! If you’re a SQL Server Pro, any of DBA, BI Monkey, or Dev, if at all possible, you should be here for this. I don’t explicitly know what we’re all getting into, but from what I’ve heard and read, there is no better place to be.

Did I mention that I’m excited about this? Yeah, I’m excited about this. The lack of sleep that’s going to happen that week, though? Yeah, I like my sleep. A lot. I’m not sure how that’s going to go.

Posted in , , Tagged , , , 1 Comment

T-SQL Tuesday #22: Data Presentation

By Airborne Geek on September 13, 2011

TSQL Tuesday Logo

Robert Pearl hosts No.22

It seems like it hasn’t been that long since last month’s T-SQL Tuesday post; I suppose time flies when you’re having fun and trying to finish up the same ETL project you’ve been working on since March.

This month’s SQL blog party is being hosted by Robert Pearl (blog| @PearlKnows), on the topic of “Data Presentation.” This is a good topic for me at this point, as I’ve all but finished my transition from DBA to BI Monkey (that’s something else I need to write about…). I think Robert is looking for specific examples of ways to present data, but since, as usual, I don’t have anything specific that I can actually publish, I’m left to speak generally about the topic.

Data Presentation: Just as Important as the Data Itself

In a previous life, I was responsible for almost everything data-related for the systems that we ran. As a result, I would get a lot of requests for data. One of my favorite requests would come in the form of, “can you give me some numbers for <X system>?” I would try to keep my response at least marginally non-snarky, but it would generally include two questions:

  1. What, exact, “numbers” do you want? (this is especially where I would have snark problems)
  2. What do you want the data to look like?

Of course, the first one is an important question—if the requestor cannot articulate what it is they actually want (or even what question they’re trying to answer), little else is going to matter. I’ll not dwell on this particular item too much, but suffice to say, sometimes getting a good answer to this seemingly easy question is anything but. I’ve basically come to the conclusion that this is normal.

Once over that hurdle, the conversation can move on to the presentation of whatever data/”numbers” it is the requestor wants. There are almost as many options for presenting data as there are for way to write the T-SQL to retrieve it. Just like writing the SQL in a way that is performance- and resource-conscious, care should be taken when working on the presentation design. It is imperative for the data to be presented in such a way that is understandable and digestible by its intended audience.

Notice I didn’t say “digestible by the party asking for it.” Don’t forget that the request originator may not be the party who is ultimately going to be parsing the provided data. If the audience is not clear in the original request, add a third question to the two that I have listed above: “Who is going to be acting on this data?”

Options for What Happens Next

When the “What do you want it to look like” question is asked, chances are decent that you’ve an idea about what the answer is going to be. If this is a one-off, ad-hoc request, Excel is a popular option. Alternatively, if a robust reporting system is in place, or this request will be a recurring one, developing a report to present the data might be a stronger choice. There are of course other options: the data could be destined for a statistical analysis application, where a CSV file would be more suitable. I would consider this an outlier, though—most of the time, data is prepared for direct human consumption.

Excel is such a popular option that you could almost call it Data’s Universal Distribution Engine (DUDE). Sending data over in Excel is less about the “make it pretty” side of good presentation as it is the “make it useful” side. I’ve found that Excel is a choice a lot of the time because the requestor wants to do more manipulation of the data once they get it. I’ll leave whether or not that is a good thing to the side; the truth is, such activity happens all the time. As a result, when preparing data for an Excel sheet, I like to have an idea of what the user is going to do with it. This sometimes helps to determine what data the user is looking for (if they don’t have a clear idea) but also can help with some formatting or “extras” to include. These “extras” could take the form of running subtotals, percent changes for Year over Year situations, or anything else that is easier to add via SQL instead of someone having to putz around in Excel.

Writing a report to present data has a different set of opportunities than pasting data into Excel. One of the things that I like to see in a solid reporting environment is a set of standards that apply to the reports themselves. Things like common header contents (report name, date/time stamp, name of the data source/DB the data is from, etc), standard text formatting, a common set of descriptors, etc, etc. In addition to making individual reports easier to read & feel more familiar, it can make it easier to compare data etween reports the hard way (one on each monitor), if one has to.

It's only worth 1,000 words if the first ones that come to mind are work safe

One thing each of these two tools gives you is the ability to present data in the form of pretty pictures. There’s a time and a place for everything, but the old cliché, “a picture is worth a thousand words” can/does apply. Sometimes it’s just flat-out hard to beat a good trendline. I have a much easier time seeing even the simplest of trends when data’s plotted out in a histogram. Conversely, one of my coworkers can look at a pile of numbers, not even sorted chronologically, and tell you what is going on in about three seconds.

Knowing where to put your effort goes back to knowing who your intended audience is. Likewise, knowing when to say “no” to visualization is a terribly useful skill. Every data element on the chart should be discernable, or else it doesn’t convey the information it is supposed to, and now the visualization is working against itself. The pie chart to the right? Don’t do that.

Summary

That’s about all I’ve got. In short: Presentation is important. Unfortunately, it can also be complicated. It’s important to ask questions early on in the process and to know your audience. Standardize if you can; help out a little with the complicated work if it can be done in SQL. Also, add visual representations without going overboard. I’ve always found turning “data” into “information” for people to be fun; if it can make someone else’s job easier/more fun, too, then all for the better.

Posted in , , Tagged , , 3 Comments

A Note About cliconfg.exe on x64 Machines

By Airborne Geek on August 30, 2011

If you don’t know (and that’s a good thing in this instance), cliconfg.exe is one of those older-than-dirt parts of Windows that is still around because it serves a specific purpose. In this case, it is used to configure SQL Server connection aliases.

Aliases are basically an abstraction between a SQL Server Instance’s actual connection string information (DNS name, Instance name, port, etc) and the name used by a client to connect to it. In this case, “client” can be either code/an application or a user. I know aliases are mostly evil, however some people do use them extensively. I don’t know that they’re all bad, but that’s a different post for a different time.

x86 cliconfg.exe vs x64 cliconfg.exe

Sit down at any remotely-modern Windows machine (it’s been there since 2000) and you can bring up a Run box, type “cliconfg”, press ENTER, and the utility will launch. If you do this on a 64-bit build of Windows, the 64-bit version of the utility will launch, as you might expect. This sounds good at first, but it might not be.

The first problem this presents is there is absolutely zero way to tell from within the utility itself which version of it is running. Therefore, if you’ve just moved from a 32 to 64-bit machine, there isn’t anything that might get your attention and suggest that anything unexpected is going on. This has probably happened a long time ago for some, but if you’re a corporate monkey like I am, you might have only recently been able to make the jump to a 64-bit OS.

The ultimate issue that crops up stems from why there are two versions of cliconfg.exe in the first place. In short, Windows keeps separate lists of aliases, one used by 32-bit applications and the other by 64-bit. These lists are maintained by their respective versions of cliconfg.exe. These lists are completely independent, and are only accessible by applications compiled with the same bit width.

In order to launch the 32-bit version of cliconfg, it has to be run from the folder it’s in. The path to this file is %SystemDrive%\Windows\sysWOW64\cliconfg.exe. As mentioned, it looks (and acts) just like the 64-bit version. I’d think that the folder should be named “sysWOW32” instead, but what do I know?

Which one to use?

Setting up aliases needs to be slightly planned out, although the same aliases can just be set up on both “sides”, and call it done. If one chooses not to do that, it’s a matter of thinking about what apps are going to
be utilizing any given alias, and then setting that alias up on the same bit width as the app. For example, to use an alias from within SQL Server Management Studio, the alias needs to be set up on the 32-bit side, as SSMS is still a 32-bit application.

Plan B (or Plan A?)

Here’s a different idea: Don’t use cliconfg.exe!

It’s old. I have a feeling it will go away some day. It isn’t very clear on what you are doing (32 vs 64-bit). All problems. Instead, consider using the SQL Server Configuration Manager. It’s new(er). It’s part of SQL Server, so it will probably be around for at least a bit. It is also very clear about the difference between 32 and 64-bit aliases.

For the current topic of conversation, that last point is the most important one. On a 64-bit machine, the Config Manager will have two Native Client items in the tree view in the left pane, one for 64-bit and the other for 32. Aliases configured in one do not show up in the other, making it perfectly clear what is going on.

I know that aliases are bad; using cliconfg.exe to manage them is arguably worse. However, if the situation exists that they need to be used, care needs to be taken in 64-bit client environments. The best way to deal with this situation is to use Configuration Manager.

Posted in , , Tagged , , 12 Comments

T-SQL Tuesday #21: “This Ugly Hack is Only Temporary”

By Airborne Geek on August 10, 2011

…Unless “nothing is temporary around here” also applies to your shop. Then…well…good luck with that (I feel your pain).

Wednesday is better than Tuesday anyway; everyone knows that

It’s been a while since I’ve participated in a T-SQL Tuesday; haven’t been writing at all, really, as you can see. Summer apparently does that to me. If you’re reading this, then it means I got it shoehorned in.

T-SQL Tuesday is the brainchild of Adam Machanic (blog | @AdamMachanic) that started, apparently, 21 months ago. Its purpose is for bloggers to get together and cover a particular topic every month. This is good for readers and also is an easy way to get a blog topic, if one has a hard time coming up with them (like me). This month’s topic is hosted by Adam, and has been billed as “revealing your crap to the world.” There will be lots of good stuff to read this month, calling it now.

RI Enforced by aTrigger? Check.

I’m going to preface this one by saying that it never actually saw the light of day. Sometime between Design & Development, the project that it was part of got canned, so the Development instance was the only place this was ever deployed to. But, it was still crap, and it was crap that I came up with, at that, so here we go.

There once was a big ugly “Location” table. This table was way wider than it should have been, mainly due to the fact that “normalization” was a term missing from the historical evolution of the system it was a part of. As you can imagine, this table contained a lot of character columns of varying widths (up to and including TEXT). To make matters worse, in lots of cases, the UI allows free text entry, which makes much of the contents suspect, at best.

A project came along that basically would require normalizing out a couple of columns related to a certain location attribute. For the sake of discussion, this could be just about anything—we’ll use “Preferred Shipping Carrier” as an example.

At the beginning of design, this “PreferredShipCarrier” column is full of shady entries. There are some “UPS”es, “FedEx”es, and “None”s, but there’s also a lot of NULLs (which shouldn’t really happen), “N/A”s, and “UPPS”es, all of which are incorrect for one reason or another. Obviously as part of normalizing this column out, there would need  to be some corresponding data cleanup, but that’s beside the point.

Where things go south is how this was to be implemented. Since the column in the table is VARCHAR, a real Foreign Key relationship can’t be set up to the INT Surrogate Key in the new “PreferredShipper” table. This is the first sign of trouble. Second sign is that we can’t change the UI—it has to stay in its free-text entry state. This leaves the database exposed to bad data, as there can’t be RI to enforce valid entries.

Enter a Trigger. This can be used to, upon an attempted row insert into the Location table, look up the value entered for Location.PreferredShipCarrier, and throw an error if it doesn’t find a matching row. I hated it, but it got the job done.

Auditing with Triggers, too? What is up with you and Triggers?

SQL 2000 was terrible for auditing—it didn’t have all of the new fancy stuff that we have now for this sort of thing. That means, when you’re dealing with 2000, and the business wants an audit trail of who changed what when, your options are/were pretty limited.

At one point, a requirement came along to implement audit trails on some tables. This means I needed to duplicate tables, adding some metadata columns to them (audit timestamp, who dunnit, what the action was, etc), and then put a trigger on each of the source tables. Some of these particular tables were busy, so the triggers & log tables got a lot of action and I hated having to do this to get what was needed.

If the trigger was fired for a DELETE, it would log the row that was deleted. If an UPDATE was happening, it would first check to see if any column contents were actually changing, and if so, log the previous state of the row to the log table. These triggers will grow relative to the width of the table (because they contain three explicit lists of the tables’ columns), so if the tables being audited are wide, the trigger will get pretty long. Additionally, since triggers in SQL Server are set-based operations, running on all rows that are being DELETEd or INSERTed, special care needs to be taken so they can operate when more than one row is operated on. This can make them a bit ticklish to write.

I know this isn’t necessarily crap, as when you’ve got to audit, you’ve got to audit. I don’t like it if for no other reason than the extra clutter it puts in to the DB and just the general idea of triggers all over the place. All manner of things can happen in triggers, and if you are operating in an unfamiliar DB or troubleshooting a goofy problem, things could be going on in there behind the curtain making troubleshooting harder.

In short: Friends don’t let friends mash F5 on CREATE TRIGGER.

Posted in , , Tagged , , Leave a Comment

Atlantis, Go at Throttle-Up

By Airborne Geek on July 6, 2011

Atlantis

Atlantis on the pad, the night before STS-132 in May 2010

This week, we all have the opportunity to hear this, one of my favorite phrases in the English Language, one last time.

“<Shuttle>, Houston, go at throttle-up” is the command from NASA Mission Control in Houston to the ascending Space Shuttle to open the throttles of the three Main Engines back up to 104.5% of rated thrust. This event occurs as the shuttle is coming off of Max Q, about a minute-10 into the flight. I grew up listening to Lisa Malone’s voice narrating this radio exchange. Because I’m a huge nerd, I use this phrase in day-to-day life whenever I can.

STS-135, a mission that wasn’t supposed to happen at all, is currently scheduled for Friday at 11:26a Eastern Daylight.

This is the final flight of Space Shuttle Atlantis, and the last one of the entire Space Shuttle program. NASA is taking a back seat to commercial endeavors in the human spaceflight department, instead focusing on heavy-lift capabilities. These heavy-lift rocket(s) will propel robotic missions to far-off places such as asteroids and will hopefully be the technological basis for long-term manned missions, as well.

I have my thoughts about this situation, but that’s not what this is about. This post is about Atlantis. This post is about a workhorse that is fading into history. This post is maybe a little bit about NASA Tweetup, as well, as Tammy and I were part of the STS-132 launch Tweetup. At the time, it was officially the last flight of Atlantis; the first of the last. That is an experience that we will never forget and will always be thankful for. It was an item off my bucket list and a lifelong dream. The launch, however, was almost just the icing on that Tweetup cake—the other activities, the people we met (both other Space Tweeps and NASA personnel), and just the experience of it all were truly what those two days were all about.

The best place to watch peoples’ lives changing (and average Joes being on NASA grounds and rubbing shoulders with others who feel the same way along with the employees that make this happen truly does change lives) in real-time this week is the #NASATweetup hashtag on Twitter. If you’re not a Twitter user (why the hell not?), you can get the feed here. It’s also a good idea to check NASA Buzzroom. It’s down at the moment, and I don’t know if it’s going to be back up for the Launch or not.

Not being all that good at writing is making it hard for me to say what I’m really wanting to say about this. Suffice to say that Friday’s launch is a big deal, both to me, and really, to all of us. I think the space program, the people who forge this trail every day, and even us ardent supporters who watch from the sidelines will have a greater impact on humanity as a whole than any of us can comprehend today.

Godspeed, Atlantis (and Roger Roll!)

Posted in , , Tagged , Leave a Comment