Mapping Value through Your Most Important Asset

Today I chatted with someone getting ready to go live with a new system and were in the process of making their legacy data fit with the new platform. It was a familiar scenario with the existing data from a 10 year old system, that grew out of nothing, had little validation and a data structure creaking at the seams. 

Everyone benefits from a combined approach 
towards better data quality.

With most ageing systems there are variations of the same products, companies and contacts. The resulting duplicates are generally held together by long serving staff who know the data intimately and can readily reason why it sits in the way it does.

Migration to a new platform with added  validation and business rules can pose interesting challenges for any BI or BA consultant but therein lies an opportunity. 

A rationalised mapping approach that involves key business users in a sit down exercise will give value back to their most important asset. By creating two-column lists with distinct values of legacy data on the left and their equivalent on the new platform on the right is a slow and hard slog but worth the effort. It can be done where variations exist in company names, product names or any other referential information. 

Those long serving serving staff that hold dupes together hold the value in this exercise in delivering meaningful mappings. It's an exercise in exchanging ideas and will result in rethinks to the functionality of the new platform as the nature of the data is explored. It will also result in a cleaner data set.

From a techie point of view the mappings form part of the data transformation between legacy and destination platforms which the data is driven through en route to success.

This sit down exercise has huge benefits as Insight relies on aspects of search, results, related results, relevance and ranking. These are the ingredients of the moment as most businesses reach out across a complicated digital landscape.

A Little Space Saving When Migrating Dates

Many years ago I worked on a Human Resources and Payroll system that sat on a Northgate system. 

Those familiar with Northgate systems will tell you the platform is based on the Pick operating system. All data is held in a character format and dates are held as numbers. 

Each date is a number relative to the 1st January 1970 so 365 would be 1st January 1971 and -365 would be 1st January 1969 and so forth. 

This notion stuck with me and have since used the same approach a couple of times when migrating large volumes data between platforms using flat files. 

The method of using views to migrate dates as a number relative to a chosen date using DATEDIFF function (SQL Server/Oracle) will save you space in any extract file. Times can also be tackled the same way output as seconds, thousandths of seconds, or even nano seconds relative to mid-night. 

Moving these dates onto the target platform is then achieved using the DATEADD function (SQL Server/Oracle) by adding the number migrated to the relative date chosen against  the previous extract.

This might seem like a little issue and little saving but with high data volumes prevalent these days it might just make a little bit of sense.

' hope this helps.....a little.

Enjoy :)

Generating Unique Values Against a New Dataset

non-SQL developers look away now....

Here's another Data Analysis tip...

When dealing with brand new datasets a DBA Developer sometimes needs to add a unique value which will form a way of identifying each row. The ROW_NUMBER() function is an obvious choice but if the data contains duplicates or has data quality issues it can be tricky. This method doesn't rely on the data itself, it just adds the unique values you need.

First we add a column that will contain the incremental number......


-- add a new column called rownum

alter table test_data add rownum int

Now we run the following three line script......

-- declare a variable that be used to assign incremental numbers
declare @i int

--assign a starting point for our id's

select @i = 10000000

-- The update statement will populate the column 
-- with consecutive numbers starting with 10000001
update test_data set @i = rownum = @i +1

I used 10000000 as a starting point so that each number will be the same length and will appear consistent when used in a reporting scenario and when exported back out to tools such as Microsoft Excel. 

One to have in your SQL tool kit for sure.....Enjoy!