Posts Tagged ‘SQLMag’

Is There Such a Thing as Easy ETL?

Thursday, June 30th, 2011

E.T.L. That’s Extract – Transform – Load.  That doesn’t sound like a lot of work when all you need to get loaded is a simple Access database or an Excel spreadsheet.  In a situation like that, the process is so simple, all you really need to focus on is the L in ETL.  There’s not a whole lot of E.T. to process, despite how wonderful that movie is. [pun intended]  But as soon as your data loading process involves some difficult or sophisticated cleansing or transformations, it gets really, really hard.

The other cross-thread that had really caught my interest lately is the USA federal governments Open Data Initiative.  I think it’s remarkable that President Obama is the first president to appoint a federal CIO.  (Shouldn’t that have happened in the past?)  In addition, President Obama instructed the entire executive branch to open up their data (where security isn’t at risk) and make it readily available to the public.  And the US government collects mountains of interesting and valuable data for its own uses, but figuring out how or who to share it with was always an afterthought.  While I was a contractor for NASA, for example, I worked on some incredibly interesting projects which yielded amazing and commercially valuable information.  It was all public domain.  But unless you knew it was there, you couldn’t get to it. Making use of all of that data always intrigued me.

Now, with ODI, it’s all being put on the internet at an ever-increasing rate at Data.gov.  However, all of this data, while open and available, is not standardized.  Some data sets might be a CSV file, while others might be something like a spreadsheet.  That means you’ll need to extract, transform, and load that data if you want to synthesize more valuable data sets.

For those reasons, I’ve been researching tools to help make this process easier.  (I also wanted to research SSIS and ETL tools for my Tool Time column in SQL Server Magazine.)  Now, I’ve been following expressor software for quite some time and really like their unique approach.  (I actually ran into the expressor software team at a PASS Summit one or two years ago and asked for a demo of their software.  And I really liked what I saw.)  Rather than the workflow approach used by SSIS, expressor software uses a data mapping approach combined with reusable business rules.  Their mapping approach is fundamentally different from the traditional point-to-point, source-to-target mappings paradigm.  Basically, you can define a semantic type representative of your business data, create a business rule(s) to apply to the data, and then implement a “canonical” mapping which connects data sources and targets to that same semantic type.  And it’s free!

 

Abstraction is Awesome

What’s cool about that?  Don’t forget that “semantic” means “meaning”.  So a semantic type is an abstraction of the meaning of the data.  The net result is that expressor shields your data integration application, with its associated business and transformation rules, from changes that might occur to underlying target or source files with different field names and data type representations have to be processed. 

For example, let’s assume that you need to process invoices from different vendors in slightly different formats.  If you use a traditional ETL tool like SSIS, any changes in the source and/or target formats will require you to modify your data mappings and transformation rules, because the mappings are tied directly to the metadata structure of the invoice file format(s). expressor, on the other hand, lets you define a common “invoice” semantic type, build all your downstream data processing off that type and map one or multiple invoice file schemas to the type.

This approach greatly simplifies the mapping process and provides for more flexible data integration applications that can be more easily adapted to changes in the source and target data sources.

expressor Studio Desktop

 

 

Benefits Abound

Since the semantic types in expressor are captured as reusable artifacts, you can also reuse them again in new data flows within your project(s).  You can even share them across your entire organization.  As I tinkered with the expressor Studio tool, I hit on a few other benefits with this approach:

  • Handles data type conversions automatically without having to write data transformation rules for these conversions
  • Builds new semantic types from existing types and reuses types in existing and new applications
  • Creates multiple, reusable business rules against a single type and applies them repeatedly as needed
  • Easily implements data quality rules and constraints

In an Ideal World…

In an ideal world, I’d figure out some brilliant way to make money from bringing together all kinds of that government data that I used to work with.  Other folks are doing it at the Windows Azure Data Market.  But in the meanwhile, I’m also looking forward to tinkering with this data to build better demos.  Along the way, I’m going to use the expressor Studio desktop ETL tool (Did I mention that it’s free!) as well as tell you about my experiences as I try to build out some Data.gov data sets.

Those of you who know me, know that I look a good discussion and cooperative, constructive team work.  So I encourage your feedback and suggestions, as I work through these data integration challenges and share my experiences.  I’m looking forward to sharing with you my insights on what the expressor data integration software can do with this challenge and what some of its features and capabilities are.  In upcoming releases, I’ll let you know what I find intriguing and worth mentioning.

Check out their website, www.expressor-software.com, to learn more about their company and products.

Enjoy,

-Kev

Follow me on Twitter

From SQLMag Tool Time Column: SSMS Plug-in News

Thursday, May 12th, 2011

I first wrote about Mladen Prajdic’s excellent tool in my Tool Time column at SQL Server Magazine HERE.    The tool is a nice plug-in to SSMS and definitely worth having.  If you’ve never installed it or have only installed an older version, but sure to pick up the newest release.  Here’s Mladen’s press release complete with hyperlink for the tool:
SSMS Tools Pack 1.9.4 is out! Now with SQL Server 2011 (Denali) CTP1 support.

As Mladen says:

…this release adds support for SQL Server 2011 (Denali) CTP1 and fixes a few bugs. Because of the new SSMS shell in SQL 2011 CTP1 the SSMS Tools Pack 1.9.4 doesn’t have regions and debug sections functionality for now. The fixed bugs are: A bug that prevented to create insert statements for a database A bug that didn’t script commas as decimal points correctly for non US settings….

Enjoy!

-Kev

More content at http://KevinEKline.com

New on SQLMag.Com: Update to SP_WHOISACTIVE

Thursday, April 21st, 2011

I profiled Adam Machanic’s (blog | twitter) excellent stored procedure, SP_WHOISACTIVE, back in August of 2010 in my monthly SQLMag column, Tool Time.  Adam has been diligent about maintaining the tool and adding new features. Read the details on my SQLMag Tool Time column.

Enjoy!

-Kev

SQLServerPedia Has a New Editor-in-Chief. Oh no, it’s Me!

Monday, April 4th, 2011

Attrition has taken its toll once again as a few good friends  move from being colleagues to being former colleagues.

As a result of the folks moving on to bigger and better things, I’m now stepping up in to the roll of editor-in-chief of SQLServerPedia.  This roll is mostly about checking on the quality of content produced by our syndicating bloggers to make sure that they’re not trying to sell products or services and that their posts are of high quality.  That’s about where the official duties end, save for things like acting as a judge in big SSP contests.

One new aspect that I want to bring to the roll is that of a newsletter.  At first, it seemed like it be great to simply point out our most popular blog posts once per month or per fortnight.  Our most popular blog posts are quite easy to track through our off-the-shelf analytic tools, after all.  However, I want to bring more to it than just a recap.

Here are some ideas I was considering and for which I’d like your feedback:

  1. I’d like to highlight a blogger in a brief interview in each newsletter.  Would you be interested in reading it?
  2. I’d like to riff on the database industry, in general, and SQL Server, specifically, depending on my mood at the time.
  3. Get some help from you, dear reader, on a variety of crowd-sourced and community driven content much like we’ve done with the useful (and exhaustive) list of SQL Server Twitterers found HERE.
  4. Spend more time doing either podcasts (no video) or videocasts.  Would you use either?  Do you have a preference?

Of course, I’ll continue to do all of my usual blogging including my Tool Time tips for SQL Server Magazine, professional development advice in the Plays Well With Others column, and thoughts on the database industry, virtualization, cloud computing, and pretty much anything else I feel like blabbing about.

Thanks for the opportunity to serve.  I look forward to your feedback!

Best regards,

-Kevin

~~~
More content at http://KevinEKline.com

Plays Well With Others – Dealing with Micromanagement

Saturday, November 20th, 2010

Imagine you’re working on a new project.  It’s an important project and its success will be a big win for the organization.  You were chosen for the job because of your competency, skill, and effectiveness.  You get things like this done all the time and have a track record for pulling it off.  Now that the project is underway, you’re finding that trust and support you need from management is absent.  Instead, you’ve got a micromanaging boss, who’s put so many additional requirements on your for reporting, meetings, and whatever their favorite nit-picking happens to be that management is actually an impediment to successfully completing the project!

Bad bosses are the suck

The bad news is that this happens to all of us at some time or another.  In fact, it’s so common that exit interviews show that nearly one in three professionals have changed jobs to escape micromanaging or unreasonable bosses.  The good news is that you can survive and even prosper under this sort of “boss behaving badly” scenario.

The WHY of Micromanaging

The first step towards surviving a micromanager is to understand why they are micromanaging.  There are three main reasons that bosses micromanage:

It’s Not the Boss, It’s You:  A little people-watching should reveal to you whether the manager acts this way with everyone on the team or whether it’s just you.  Yes – it actually is possible that you’re the only one (or a part of small number of people) in a larger team that are getting the “breathing down your neck” treatment from the boss.  If this is the case, you’re probably perceived as not meeting the standards of professionalism where you work.  I had one colleague complain about being micromanaged by our mutual boss, while never seeming to realize that she left early both for lunch and at the end of the day and seemed to arrive late most mornings and when returning from lunch.  On top of that , the quality of her work was mediocre on her best days and was frequently late.  Sadly – some people need to be micromanaged or the manager might never get an honest day’s work out of them.

Bad bosses make the team less effective, not more effective

It’s the Boss, and They Know It: This second sort of boss is probably the worst kind that you’ll ever worked for in my professional career (stints at fast-food places as a teenager excluded).  This sort of boss revels in being the boss.  They don’t really care if you’re good at what you do or if the team is particularly successful (though they don’t want their team to become an abject failure since this puts them in jeopardy with their own boss). Instead, this boss is most interested in the exercise of power and might do things like require undue approval, frequent reports and status meetings, and frequent revisions to the work you’re doing.  This sort of micromanaging boss is also prone to publicly disciplining their subordinates.  There’s seldom much you can do to make this work environment better and, almost always, the team experiences high turnover – losing most members with 24-36 months.

It’s the Boss, and They DON’T Know It: This final type of boss is one that you can work with. And if they’re otherwise a rational and reasonable person, someone you can probably prosper with.  In this case, the micromanaging boss is unconsciously motivated by fear and anxiety.  At some point in their past, they failed miserably due to some situation that went out of their control.  Now that they’re in charge, they’ll do everything in their power (subconsciously or not) to make sure they never experience that again usually by micromanaging everyone on their team.

If the scenario is about your work behavior, then fix that first.  Don’t give a micromanaging boss any excuse to watch you like a hungry hawk by being surfing the Internet, hanging out at the water cooler, or not being timely.  If the scenario is about a boss who uses micromanagement as a means of exercising power, simply get out of that team as soon as possible.  But what about the third scenario?

Anxiety Manifested as Micromanaging

So what are the tips and tricks needed to get past the boss who micromanages due to a subconscious but exaggerated sense of anxiety?  Again, the good news here is that by properly understanding what keeps these bosses up at night, you can answer those needs out of your own initiative thereby giving them good cause to loosen their grip on your every move and show some trust in your talents.

  • Prioritize: Micromanagers tend to change priorities on the fly because they get caught up in specific, very granular details on a project.  They’re notorious for trying to add a multitude of tiny changes that, when taken as a whole, double the amount of effort.  However, the granular details are usually unimportant to the success of the project.  So it’s your job to keep the micromanager’s eyes on the prize and not focused on the minutia.
    • Communicate frequently about progress on the project in general (see below).
    • Send ad hoc emails summarizing any changes in scope and your understanding of the boss’ expectations regarding those changes.  Make sure that you also estimate a change in scope to the teams overall ability to meet its objectives.  One big change or lots of small changes are equally likely to derail a project or make a team ineffective.  Make sure that you explain that sort of impact in your recap email.
    • It’s even more important to provide recap emails if a scope change comes out of a verbal exchange.  Verbal exchanges, after all, don’t have a paper trail if there’s ever a dispute.
    • Develop a shorthand or code for prioritizing work that makes sense to you both.  Any time the micromanager tries to pile on too much work or make too many changes, make sure that they rate such work on a scale of importance.  It doesn’t matter what the scale is, 5-stars or 5-alarms for urgent, as long as you both agree.  If you have 30 low-priority action items on your list and 1 top-priority item, you both already know which one you should be working on.
    • Perhaps most importantly, use the micromanager’s nit-pickiness to renegotiate priorities to your advantage.  If they ask for such big changes that it impacts the project deadlines or ask for so many small changes that you can’t make any forward progress, don’t say “Yes, I can do that”.  Instead, any time they suggest more than a very minor change, tell them “I can do that, but only if one of these other changes drops of the list.  Which of these do you want me to put on the backburner?”  They’ll feel engaged and see your own level of engagement and dedication to the project as a positive.
    • As an add-on to reprioritizing, you must put double-emphasis on any action items that you need from them.  In a sense, you should micromanage them! By making clear where you need their help to make the project a success, you keep their eyes on the big goal.  Plus, by forcing them to refocus on their own responsibilities, you gain a little extra space to get your work done without them constantly looking over your shoulder.
    • Overcommunicate: Micromanagers, whether they’ll admit it or not, are afraid of not knowing what’s going on.  You can conquer the micromanager’s need to be constantly “up in your stuff” by providing them timely updates on your projects and activities.
      • Provide email updates more often than you might normally like, a couple times per week at least.  The emails should not only detail progress on the project, but also provide a steady stream of reassurances that you’re on track regarding timelines and that you’re aware of the importance of the project.  This alone will go a long way towards quelling their inner anxieties.
      • Make sure that they call meetings that accomplish a specific goal: deciding on an important strategy, setting the priority on a bunch of work orders, anything but mere status updates.  Status meetings are a huge waste of time.  Everyone at the table waits and wastes 50 minutes in a 60 minute meeting, since each person talks in turn and usually try to hard to make themselves look good in the process.
      • Don’t hide behind email.  We have many types of communication available, each with their own degree of intimacy and immediacy.  Email is distant.  If things aren’t going well or your own stress-levels are going through the roof, sit down with the micromanager for a talk.  If you’re not geographically close, ask for a phone call.  It’s easy to turn a blind eye to an IM and to procrastinate on an email.  But an in-person meeting sets a very different tone.
        • During this conversation, remind the manager that you’re there to make their projects (and by extension, them) a success and that “administrivia” has reached a level where it’s impeding your ability to engender their success.
        • You might want to appeal to their sense of propriety – every business relationship is an implicit contract (if not an explicit one).  That means both parties have certain responsibilities.  The agreement between superior and subordinate is “you do want I ask and I’ll try to make that as easy for you as possible”.  You can appeal to the micromanager’s ingrained desire to keep an agreement by gently showing where their side of the agreement has gone off course.  Demonstrate how much work you’ve accomplished using your email paper trail as evidence, then use the same evidence to show the degree of unneeded or additional work heaped on you.  Refocus the micromanager on the project goals and deadlines, and then point out that there’s simply too much micromanaging, er, work required to meet the deadline.  In a sense, you’re steering them to the decision that you want them to make – more trust to get things done, a little less ad hoc reports, fewer boring and unproductive meetings.
        • Use inclusive pronouns like “we” and “us” to set a collaborative and consensual tone, something like “We’ve got way too much on our plate to get all of this done by the deadline.  We don’t want this to be a flop.  Which of these can we drop to get us back on track?”
        • Tenacity: Keep at it.  Micromanagers act the way they do literally because of who they are.  You can very likely build enough trust with a micromanaging boss to earn a few feet of breathing space and less scrupulous attention detail.  But you’ll never fully escape it if you work for a micromanager.  You’ll have to build attention to detail and overcommunication into your daily routine.  As it says on the shampoo bottle,  “lather, rinse, repeat”  then get up and do it again tomorrow.

Gimme the Cliff Notes

Micromanagers make us feel untrusted and stymied by their constant need for tediously detailed and frequent updates, constant changes to minor details of our work, and overly developed attention to administrative details that really don’t matter in our daily job.  But there’s hope!  By proactively micromanaging the micromanager, you can build trust and earn their respect.  Overcommunicate on the details of your work.  Constantly seek their explicit prioritization for changes to your scope of work.  Make sure that any changes are rated for importance and evaluated against the overall goals of the team.  And when things get bad enough, schedule a meeting to realign project tasks and to get the project back towards accomplishing your mutual goals within a defined deadline.

- Kevin

Free Poster – SQL Server Dynamic Management Views

Sunday, August 29th, 2010

I enjoy working at Quest Software partly because we do a lot to give back to the community. Here’s one example, a free poster describing all of the SQL Server 2005 and 2008 dynamic management views.  Get it here.  It’s the perfect compliment to the free system map poster available from Microsoft here.


New on SQLMag – Automating the Startup and Shutdown of Windows Services

Tuesday, April 27th, 2010

I just posted a new blog entry on how I automate the shutdown and startup of Windows services.

Check it out at http://www.sqlmag.com/blogs/tool-time.aspx.  Be sure to let me know what you think and if you have your own unique methods for automating the control of Windows services!

Many thanks,

-Kevin
-Twitter @kekline

New Interview and SQLMag ToolTime Blog Article on “SQLClue”

Tuesday, January 19th, 2010

A neat new administration and configuration management tool for the SQL Server set.  I especially like the Run Book features, because Run Books are the way us “oldtimers” like to run our IT infrastructure.  My pal, Buck Woody, also likes Run Books (and really big explosions by the guys on MythBusters, but that’s another discussion).  Find all the details about the new tool, created by Bill Wunder, here.

The DotNetRocks! Team

"Richard, wasn't that an awesome interview?!?" "Carl, I could've danced, er, talked all night!"

In addition, I’m honored and amazed that the team at RunAsRadio invited me to participate in interview #143.  These are the same guys who also put together one of the best and longest running IT podcasting shows – .NETRocks.  Unlike a lot of interviews, it seems like we had too little time to cover all the topics.  I’d love to hear your feedback!

Thanks,

-Kev

Twitter @kekline

The Awesomeness of ROBOCOPY Now at SQLMag

Tuesday, January 12th, 2010

If you’re like me, you’ve used Ctrl+C/Ctrl+V more than a time or two, but you’ve always felt a little dirty afterwards.  Yeah, you can use the command-line COPY or XCOPY utilities.  But they’re decidedly 20th Century, much like some of the laundry still loitering under my office couch.

So I spent a little time over the holidays cleaning up my file system by scripting a backup routine using the very cool and very free ROBOCOPY utility.  Now, I’ve got a regularly scheduled job that, after the first run which captured all my files, only backs up new or changed files (and directories) while keeping all of their NTFS attributes.  Boo-Yah!

Read all about it on my Tool Time Blog at SQLMag. I’ll also show you how to use some other free and powerful alternatives in future entries.

Enjoy!

-Kev

Twitter @kekline

Like PowerShell for SQL Server?

Monday, December 14th, 2009

Check out my new Tool Time column in this month’s SQL Server Magazine. It features some great new PowerShell scripts and information from Chad Miller (blog) who I had the pleasure to meet in person back in October of this year while speaking at the SQL Server of central Florida and at SQL Saturday #21.

In addition, I’d like to point out a free utility called PowerGUI.  If you’re like me, the last thing you have time for is learning a new code like PowerShell.  So I copped out and got the next best alternative to real code, the fancy and pretty drag-n-drop interface of PowerGUI.  (Full disclosure: PowerGUI is made by Quest Software, my employer, but by an unrelated business unit.)  Notice that the hyperlink is to a .ORG website.  That’s because PowerGUI is not only free, it’s also community-supported.  There are tons of free scripts for all sorts of Windows-related technologies like Exchange, Active Directory, and of course SQL Server.  Give a look and let me know what you think of it.

Thanks,

-Kev

Twitter @KEKline