Automate Your Dynamic Mappings Rule Management System

Manish SengarNovember 17, 2015

Information is a critical business asset, but only when it’s extracted and utilized effectively. With the advent of the digital revolution and adoption of the Internet of Things (IoT), businesses face increasing challenges with information. By far, the biggest hurdles are:

  • Volume –The amount of data that can be effectively collected and analysed.
  • Velocity – The rate at which data should be ingested and processed.
  • Variety – Types of data that should be collected.
  • Veracity – Trustworthiness of the data.

In one way or another, all of these challenges relate to data quality, a significant concern right now that can potentially affect the success (or failure) of your company. As Ted Friedman and Michael Smith wrote in a Gartner report, “Poor data quality is a primary reason for 40 percent of all business initiatives failing to achieve their targeted benefits.” If your data quality suffers, you can expect even bigger challenges that affect the operational side of your business (meaning a significant correction effort is needed), as well as your company’s overall viability (if not detected and corrected in time, incorrect knowledge and potentially bad business decisions can ensue).

Where do data quality problems come from?

Data quality issues can arise at any stage or part of your business intelligence system. These include:

  • Data modelling — Incorrect representation of the business model.
  • Data sources — Issues with data provided by upstream source systems.
  • Data integration and profiling — Problems with data scenarios analyzed and the design of processes.

In addition, one of the most frequently encountered data quality issues arises at the ETL stage — specifically, when ETL implementation is not in sync with the current business definition of data mappings. This issue typically occurs when a large number of data mappings are changed often by a business, as indicated by their requirements (e.g., an inventory group mapping for digital advertisers or item group mapping for retailers). It  can occur in isolation with other data quality issues or even when no problems with source data and ETL processes exist.

Most often, data mappings are defined and maintained manually by businesses, and then passed on to IT to be implemented in the business intelligence system, using ad-hoc ETL processes. In many cases, organizations do not prioritize this process as much as needed, relying instead on manual maintenance. This, in turn, leads to these challenges:

  • A higher risk of human errors due to manual efforts.
  • A longer turnaround time.
  • The potential for inter-team conflict (e.g., between business and IT).
  • Lower overall confidence on the final BI content.

What can help?
At InfoCepts, our team came up with a solution known as Dynamic Mappings Rule Management (DMRM). Essentially, DMRM is a modular, customizable system that can be easily used by business users to create and maintain data mappings for data transformations, calculations, and clean up — with minimal IT intervention. Components include:

  • A user interface that allows users to:
    •  Create new mapping rules.
    •  Modify existing mapping rules.
    •  Delete existing mapping rules.
    •  Restore previous versions of mapping rules.

This can be implemented in a technology of choice, whether with Java-based web UI or MicroStrategy’s transaction report.

  • A DMRM schema that involves a set of backend tables to store user changes and help generate dynamic queries.
  • A DMRM engine, which serves as a core component of the solution. Currently, the engine is implemented in Informatica, basically as an automated workflow to analyze DMRM schema, generate dynamic SQL queries, and apply changes to target tables by executing the SQL queries against the data warehouse. It can also be implemented in an alternate ETL tool of choice.

Key highlights of DMRM
Our team integrated a number of features of make DMRM highly effective, such as:

  • A customizable design. Since the solution uses implementation agnostic schema design and dynamic SQL queries, it can be customized to meet the needs of a variety of scenarios. To date, we have implemented it successfully for media and retail clients, with minimal changes from one implementation to another.
  • A technology-independent modular design. Due to the solution’s modular design, minimal changes are needed for new implementations. For instance, the existing UI (or existing MicroStrategy set up) can be used, while tables in the schema design can be implemented in any database, using any modelling. In addition, the DMRM engine can be implemented in shell scripting or any other ETL tool.

How it’s used, what benefits it brings
DMRM is a particularly effective solution for organizations with a business model that requires a large number of frequently changing data mappings. Likewise, it can help companies that require faster access to data with modified mappings, and that currently use ad-hoc manual processes to handle data mapping changes.

With DMRM in place, companies can gain:

  • Significantly improved data quality due to zero manual intervention.
  • Faster turnaround time due to automated processing of requested mapping changes.
  • Better coordination between business and IT team, given that the ownership of data quality (accuracy and timeliness) is moved to business.

DMRM enables users to define their own rules and experiment with resulting mapping changes without going back to IT. It also eliminates the requirements of writing complicated Excel formulas and transformations to derive and merge data from multiple sources. Ultimately, this leads to increased business confidence in the information and analysis delivered in BI reports, along with increased end user satisfaction — a primary goal of any BI team.

Limitations
Due to the customizable and modular design, DMRM can be modified and implemented with minimal efforts. There are, however, a few considerations to keep in mind. These include:

  • A UI design that is specific to an organization’s requirements, which means the actual effort to implement can’t be estimated before requirements are available.
  • A shift in the ownership of data quality (accuracy and timeliness) to the business side of your company, which may require process changes to ensure a smooth adoption.
  • Additional awareness or training, given that your data quality is only as good as the changes made by business users.

Get in touch to learn more about DMRM — and to find out if it’s the right solution for you.