A Data Quality Riot Act
As the son of a former Marine Chief Warrant Officer (CW2) I have not only seen the occasional riot act being doled out to my brother; I have been on the receiving end of a few myself. I have discovered that in order for your data quality initiatives to be successful you need someone who is willing to play the role of drill sergeant on occasion. There are going to be occasions where someone needs to take control when a business process or system provides the latitude to introduce bad data into the environment. This person is going to need to motivated and passionate about rooting out bad data and the processes that allow it to be introduced.
Recently I received an email from a business analyst inquiring about a discrepancy in the between the operational system and the analytical application that his team had recently distributed across the enterprise. The discrepancy was that a name change in the operational system was not being reflected in the analytical application. At the surface, this seemed fairly benign. We took a look at the table responsible for this particular entity the table below depicts what was discovered.
| Id | First Name | Last Name | Employee Id | User Id |
| 10087632 | Olivia | Johnson | 257303 | johnsono |
| 10108465 | Olivia | Matthews | 0257303 | matthews |
Since this table behaves as a Type 4 slowly changing dimension, an update of the last name and user id would have simply move the original record to an audit table and introduced a new record with the correct last name. Instead, a new record was created not only introducing a new surrogate key for the same person but the unique constraint on the employee id was bypassed by inserting a leading zero into the field. This had to have been done by a user who was not properly trained on handling employee name changes in the operational system. A well written email can prevent this from happening again. The user could even be instructed to go back in to the system, delete the new record, and update the existing record properly. But that was not the case.
The users responsible for managing user accounts in the operational system have been instructed to create a new user account when someone submits a name change. These instructions also explicitly state to insert a leading zero for the employee id to bypass the uniqueness constraint placed on the field. What happens when an employee gets married then divorced and no longer wishes to retain her now ex-husband's last name or marries again? Will they add a second leading zero to the employee id?
It was upon hearing the details of the business process that I could feel the increase in my blood pressure. This operational system has been live enterprise wide for less than six months and the business processes that support it are already showing signs of failure. History did not provide the learning experience that you would have hoped; it will take someone to put on their drill instructor uniform and break the privates in their platoon of their bad habits. Only then can they be built back up with understanding that they are to protect the enterprises data like it was their homeland.
Themeword 2010: FOCUS
Focus
I have never been one for putting together a list of resolutions every year only to forget about them before the new year is a month old. However, there was a discussion and game of tag that emerged on Twitter among the SQL Server tribe recently about choosing a themeword instead of listing resolutions. After reading about the themewords that were chosen by Thomas Larock (@SQLRockstar), Colin Stasiuk (@BenchmarkIT), and doing a little reading about the concept over at HPC, I decided that I would jump into the ring and try it out this year.
My themeword for 2010: FOCUS
The one thing that I realized over the past year that has been missing in both my professional and personal life is focus. I need to focus more on my career development this year. The role of a Teradata DBA has been a great source of achievement and satisfaction for me over the past several years. I still enjoy the thrill I get when I hyperfocus on a performance tuning a query, writing a statistics maintenance routine, building an object catalog or developing a multivalue compression routine. But I also have the desire to learn more about data management, data quality, and data governance. While I blogged about data governance, data management and business intelligence this past year, many of the issues that I have dealt day in and day out with have centered on being a DBA and less on theses other areas. This year I want to spend more time focusing on these areas because they are going to continue become increasingly more important in both the public and private sectors.
I have also made several great connections on Twitter. I would like to focus on strengthening theses connections over the course of the upcoming year. In doing so, I need to also make sure that I focus on the value that LinkedIn can provide through its discussions and even local meet-ups that occur on a semi-regular basis. This also includes branching out into new realms such as attending the local SQL Server PASS meetings on a regular basis.
However, just as important as focusing on career development there needs to be equal amounts of time spend focusing on developing as a father and husband. I have been very fortunate that I have been able to be an active participant in the activities that both my sons have been involved in over the past several years, from coaching Little League to bowling tournaments to roller skating and school parties. But I need to work harder as they grow older to serve as a role model and provide guidance for them as they face the transition from childhood to adolescence and adulthood. As a husband I need to focus on setting time aside for my wife and I to do things together. All too often we are so caught up in our children's activities, day-to-day life, or my Playstation 3 gaming habits. It doesn't need to be anything elaborate like cruises or vacations abroad (although I wouldn't mind either!). Just something simple like catching dinner and a movie or having take-out dinner and watching a movie at home.
How to Pick Your #ThemeWord for 2010
- Think of a word that reflects your hopes and dreams for 2010.
- Share your ThemeWord with friends on Twitter, Facebook, or Your Blog.
- Be sure and use the hashtag #ThemeWord.
Tag Your IT!
I am going to tag a couple of the people that I have met on Twitter this year:
- Jim Harris (@ocdqblog)
- Merv Adrian (@merv)
- Tamara Dull (@tamaradull)
- David Accampo (@daccampo)
- Jill Dyche (@jilldyche)
All Your URL are Belong to Us
Today's Lesson
Today, Google announced the launch of goo.gl, its own URL shortening service. It is currently in a "limited" release for users of its Google products such as Feedburner and the Google Toolbar. This was done in concert with Feedburner's new enhancement called Socialize, which will currently send your feed to the Twitter account you specify. In order for your feed to be socialized in near real-timte they suggest you leverage Feedburners "ping" feature whenever you publish a post. Overall, the Socialize feature of Feedburner is a nice enhancement and I hope that we will see additional services like Facebook, MySpace, and LinkedIn included in the future.
However, the most important feature that was launched today was the URL shortening service. Why is this so important? Because several URL shortening services have come and gone over the past couple years. With the demise of these services comes the inability to redirect the user from the shortened URL to the target URL, also known as linkrot. Brent Ozar wrote about his suspicions of using free web services to shorten URLs for this very reason. With Google the chance that your shortened URL will rot is virtually nil given Google's appetite for data.
While Google has said it will protect users of its URL shortening from malware and phishing it would be nice to see a link preview feature enabled. The current version of TweetDeck (v0.32.1) simply opens your web browser with the resolved goo.gl link while for other services such as bit.ly a preview of the full URL is given in a pop-up window . This is either due to TweetDeck not understanding how to handle goo.gl's URL preview or because goo.gl has not exposed a URL previewing feature. Google would be doing its users a disservice if a URL previewing feature is not exposed sooner than later.
Finally, how long before the current crop of URL shortening services fade away or are bought by Google. With the adoption of bit.ly as the default URL shortening service by Twitter in 2009 combined with Twitter's explosive growth in 2009 the loss of bit.ly's URL resolution over time could be troubling for many.
Will goo.gl become the dominating URL shortening service or do you think services such as bit.ly and ow.ly can survive?
Teradata Database Object Summary
In my previous blog post I showed how to build an Object Catalog of your Teradata data warehouse. Trouble is even on moderately sized systems your Object catalog could result in tens of thousands of rows. One of my object summaries is over thirty thousand rows. This can be overwhelming if you hand it off to management since they prefer a thirty thousand foot view.
So in order to provide a higher level overview of your Teradata system you can create a view on top of your object catalog that summarizes information at the database or user level providing information such perm space allocation/consumption, average table perm space consumption, average number of rows per table, number of tables with/without stats, and a count of each object type in the database.
The following view could be extended fairly easily to include additional metrics on such as the largest and smallest tables in both row count or perm space consumption. For my needs, I didn't need to include those items. The SQL can be found below:
My Tweets
Sweet! My books written by @rands and @williammcknight arrived! http://yfrog.com/0rlmwsxj - posted on 07/29/2010 18:08:49
@JAdP I prefer my tomatoes with salt on them. Will have to try them with sugar. - posted on 07/29/2010 16:32:01
Would be nice if Teradata BTEQ would resolve environment variables: .EXPORT REPORT FILE='$reporttmp/my_report.txt' Not just in .OS command - posted on 07/29/2010 15:28:51
"How should somebody teach themselves database and programming skills?" http://bit.ly/dot978 - Great suggestions! (via @CurtMonash) - posted on 07/29/2010 13:03:19
@peschkaj Would this do? http://bit.ly/btRuCy - posted on 07/29/2010 11:18:27
Recommended Reading
- You Know You Have Big Data When…(Humor)
- Data Quality and the Cupertino EffectBy: OCDQ Blog Feed
- Finding Data QualityBy: OCDQ Blog Feed
- The Root! The Root! The Root Cause is on Fire!
- Potato Chips and The Myth of the Data Warehouse
- Plugin by C. Murray Consulting
Friends
- Brent Ozar
- David Accampo
- Jill Dyche
- Jill Wanless
- Jim Harris
- Paul Boal
- Phil Simon
- Shawn Rogers
- Tim Ford
Tag cloud
