Accelerating MDM Deployments While Reducing Project Risk

REVIEWER: Terry Scully, director of technology, data warehousing and business intelligence, Worldwide Financial Services Company.

BACKGROUND:

PLATFORMS: Microsoft Windows Server 2003 and Microsoft Windows XP.

PROBLEM SOLVED: For every data-related initiative we take on, like master data management (MDM), the critical path is often data analysis and mapping. While we had tools such as profiling that could analyze a single data source at a time, we didn’t have anything that could analyze across data sources to find the business rules and transformations that relate to our existing data landscape. So every time we had to map multiple data sources to each other, this ended up being a very manual process that was expensive and extremely hard to predict. The result was that we would always end up waiting on the data mapping and analysis team. For an MDM initiative, a tremendous amount of data mapping has to happen. You have to analyze your existing data sources to determine the “as-is” state in order to determine the data survivorship rules that will be used to populate the master. Then you have to map the master to the downstream consuming applications. The Exeros software accelerates the mapping process and reduces project risk by automating the discovery of business rules, complex transformations and data exceptions both within and between data sources. For example, one project that we expected to take 540 data analyst hours over six months took only 54 analyst hours over two and a half weeks. So the use of the software takes data mapping off the critical path and makes projects that were once very risky much easier to predict while accelerating our time to deployment by a factor of 10 times.

PRODUCT FUNCTIONALITY: Exeros Discovery analyzes the data values of two data sources simultaneously and automates the discovery of the vast majority of relationships within and between data sources. It automatically discovers complex case statement logic, substrings, reverse pivots, lookup/cross reference tables, etc. while also identifying the data inconsistencies between the two data sources. For the business rules and transformations the discovery engine can’t automatically find, it provides our data analysts with a workbench where they can hypothesize and test the business rules and transformations on the spot.

STRENGTHS: The product accelerates our time to deployment by five to 10 times so project schedules are more predictable. It finds very complex relationships that are hard for us to find by hand. We get a high level of consistency in the output regardless of the data analyst doing the work. The software gives us an auditable process that is quickly revalidated. Hand mapping is not nearly as reliable or auditable. The product finds data inconsistencies as part of the mapping process so we find the exceptions while we are mapping, not after the fact.

WEAKNESSES: It doesn’t discover everything. It still requires someone to run it, and it doesn’t automatically revalidate the rules it finds against new data that comes in every day. We would like an application programming interface so we can have the validation part of the product run on a daily or weekly basis. We would like more fine grained reporting.

SELECTION CRITERIA: This is the only product we’re aware of that automates the discovery of complex business rules and transformations within and between data sources down to the column level so the selection criteria was whether or not it could accelerate our data mapping process according to their claims (and it did).

DELIVERABLES: The product generates reports in Excel and HTML that include data models discovered, transformations and business rules discovered accuracy percentage of everything discovered and data values that are exceptions to the discovered rules. It generates the same output in XML: common warehouse model (CWM), Exeros XML (the company’s own extension of what is in the CWM output) and Informatica XML that can be used as basic skeleton input to a PowerCenter extract, transform and load job.

VENDOR SUPPORT: They have been extremely responsive and helpful. Any time we have an issue, they are right on top of things helping us to either work around issues or fix any bugs that we might find.

DOCUMENTATION: Documentation is very clear.