Out Looking for Trouble
I talked yesterday about going back and revisiting systems that are up, running and don’t have known issues. I asked about your experiences – and one of the responses was from Clayton – thought I’d pass it along.
"As part of creating Data Warehouse solutions for clients, I typically perform a data quality check. This includes:
1. Determining if the data is consistent. I find systems that do not have foreign keys, that are rife with orphaned records, summary tables that do not balance to the detail records they were derived from, and dates that are way out of any reasonable range.
2. Determine how clean the data is. Unmanaged input, or data from legacy systems that has not been conformed can lead to difficulty in producing meaningful summary views. A typical example would be WalMart spelled 10 different ways. There are a myriad of tools and services available for data standardization and cleansing, let alone changing apps to use managed lists instead of free-form entry for critical fields. If the data is really ugly, this can be a daunting task, but well worth the effort, as it will make previously useless fields meaningful.
3. Is there a database diagram? There is most likely no diagram or documentation on what the database structure is currently or what it looked like at the system’s inception. I always generate one and work to get consensus on its accuracy before proceeding with any large development effort.
4. Where is the business logic? There are often reports that go through all kinds of convoluted logic to arrive at results that are difficult to verify when there is a discrepancy. More often than not, refactoring these monsters leads to additional database attributes that make the report simpler to write, and makes the same logic available to analytic applications.
With the raw improvement in computing power and I/O throughput, there is no excuse for not having applications that respect and enforce primary key relationships. Considering the fact that the need for good (and accurate) information is only going to increase, the effort to ensure your systems are “clean” and well supported, from a documentation perspective, will only make them more valuable."
Featured White Paper(s)
Making Databases Explorable
This paper looks at common difficulties encountered when maintaining a large number of databases and how the products in the … (read more)
Windows PowerShell: Why Developers Should Care
Windows PowerShell is Microsoft’s next generation scripting and shell tool. Find out more about this tool and why its importa… (read more)