Data Migration: Low Hanging Fruit Can Be Bad For Your Health!

 If you’ve been through a few data migration exercises then you’ll probably recognise the scene.  You’re in the midst of a busy data migration, the pressure is on, there’s too much to do and too little time to do it in. You’ve been back and forward with the business users trying to resolve data quality issues that are preventing data from successfully loading in the new system. One, two, three, or more trial data loads have come and gone and still there are pieces of data which stubbornly refuse to play ball and load in to the new system.

With deadlines looming you start looking for ways to solve the problem, to migrate the data, to just get it in there. Or perhaps, even worse, those above you begin to apply the pressure for you to do the same. The questions / demands start to come. “Look for the low hanging fruit, what can you do to just make it work? Just get the data loaded!”

One common idea is that a whole parcel of load errors associated with free text fields can be easily and quickly solved by simply truncating the data to match the maximum allowable length of the fields in the target system.  Load the data in; produce an exception report for the text fields that were too long and were truncated and, hey presto, problem solved! You’ve got a bundle of errors out of the way and proved that the data can load. There’s now confidence that, come the next cutover trial or the real cutover and go live, that the migration in this area will be fine. Now it’s been done successfully once it can be repeated successfully and can only get better. After all the business users will get your exception report and immediately and diligently get to work on cleansing those fields which needed attention.

They will, won’t they? It’s not like they got anything else competing for their time. Their line manager doesn’t need them to do anything else. They’re ready and waiting to cleanse data for you as their top priority.

OK, wake up and head back over to the real world! Once (or if) they finish doing the day to day tasks of their own job, not to mention the tasks of those of their colleagues who have been seconded to that big project that is underway they may have time to add the data cleansing tasks to their to do lists, but I bet those tasks don’t get added to the top of the list.  If that data is going to get cleansed then you need to present a reason to prioritise its cleansing.  A hard failure is that reason, and a much more compelling and immediate reason than an exception report for data which (after all) has already loaded.  If the data doesn’t load then it needs to be cleansed if it is to be available I the new system.

Give the business all the help you can. Point out exactly and ambiguously what the problem is, show them exactly which field(s) in which record(s) need to be cleansed and what good looks like (i.e. what rules and conditions must be met before the data can migrate) and use data profiling tools to give them some indications ahead of the next trial load that the cleansing they are doing is effective and having the desired effect. But, don’t own their problem nor try to solve it for them in isolation. Most times, the business folds will know the data much better than the data migration folks. As soon as the data folks start making assumptions about what is safe to do with the data in the name of getting it to load, be it truncating text fields or some other technique, then the organisation takes on a long tail risk and liability in that the meaning of the data may have been lost or changed which may manifest in negative impacts to process or operations at some point post go-live. Bad for the organisation and bad for you as often times it is the data migration folks that will be perceived as having been the cause of the problem.

But, in the end, pragmatism must rule the day. Your data migration must run. There will be those (hopefully few) areas where you’ll just have to make some calls for the sake of allowing the data to get into the system at final cutover. If you don’t you’ll find your name quickly in the same sentence as the words “…we didn’t go live because of…” So go ahead, pick that low hanging fruit, but wait until you absolutely must, it still may be poisonous, but less immediately deadly than the alternative! 

What Do You Mean You Don't Need a Data Model?

On more than one occasion across my career I've found myself having to justify why we should bother undertaking data modeling exercises. Thankfully I'm not facing that problem currently, but I am in the midst of refreshing a set of strategy, standards and guidelines documents and recently found myself writing a "Why Model Data?" section as a preface to one document. It got me thinking that, despite all of the prior times I'd had the discussions, I'd never really succinctly put down in one place the why it is that I think we should undertake a data modeling process - how it adds value, if you like. So, this blog post is the result.

Modeling of information systems without modeling of the data they operate with can, and does, lead to bad outcomes. Modeling at only a systems or application level often fails to consider important characteristics of the underlying data, instead painting a picture which is only representative of a particular method of use of that data. Such modeling frequently impedes reuse of data across organisations, lowers the quality and speed of decision making and can lead to the unnecessary IT spend and the development of siloed IT applications with unwanted and sometimes unrecognised redundancy. Modeling of data assists with building understanding not only of data content but also of data relationships, with an effective data model being a key enabler of successful integration and well aligned and efficient application architectures and portfolios.

The explicit act of data modeling also encourages discussion about the meaning of data and the appropriate (or otherwise) use of various data elements in different functions of the organisation. It may well expose differing definitions and understanding of the same elements of data that, prior to the modeling exercise, those in the organisation had been unaware of.

Higher level data models also serve as a useful communication tool, helping to bridge the IT – Business divide and provide a mechanism to forge a common understanding of particular areas of the organisation’s data. Data which is easily and well understood is more likely to be reused as not only is the barrier to reuse lowered, but the urge to collect and store the same data elsewhere for another purpose less likely to come to the fore. This important aspect can be a mechanism for cost avoidance in that it lessens the risk of the design and implementation of systems which make ineffective or inappropriate use of the data assets.

Finally, when coupled with other [types of] models, data models allow an understanding of an organisation’s current technology landscape and the way it interacts with business processes. This understanding forms the foundation for efficient and low cost impact analysis around process and system change, in turn enabling business agility.

So there you have it. Part elevator pitch, part business case executive summary. I hope it helps someone the next time they're arguing for funding to develop or maintain the enterprise data model or trying to convince a bunch of rogue developers that simply using Visio to draw an ERD after the application is delivered isn't really the best approach!

Querying Data for Report Parameters

Often there's a need to dynamically get data to populate the list of options for a report parameter. There are a couple of ways to go about this. One I often see is to query a transaction table to get a distinct list of items to appear in a drop down parameter. Something along the lines of

SELECT DISTINCT city FROM orders
ORDER BY city

In my opinion this is a bad idea for two reasons:

1. It places unneccessary load on the database. Querying thousands, maybe even millions, of rows to return a handful of rows might not be the best idea if there is a reference table (perhaps just listing cities or even listing all customers - with city information as part of the customer's address). The situation is even worse if the column being queried from the transaction table isn't included in a suitable index.
2. Looking at a transaction table for this information will only return a resultset for those items which have been involved in transactions. If it's important for the report user to be able to query against any city then there's a problem!

So, whereever possible I advocate querying small reference tables to populate report parameters. Look at the data model as a whole to try and find suitable tables and ask questions of key super-users, DBAs, application developers if need be - you might be suprised to find there's a table you can use - it just may be that it was named in a way that didn't make its purpose obvious.