Right-Time ETL and Integration

I consulted with a major financial services company that wanted my opinion on a dashboard they were building for their CFO. This dashboard would have a real-time tachometer showing each instance when a journal entry transaction was entered into one of their 11 general ledgers around the globe. Curious, I asked about the reasoning behind the functionality, and their answer was that the CFO wanted more up-to-date information. In reality, the CFO needed midmonth data to help understand why one system was always running behind in closing out their monthly books. The CFO was not asking for real-time data, he was asking for right-time data.

With advances in technology and the need to shorten the time to decision, organizations are moving beyond the traditional nightly or weekly load into more real-time and near real-time applications. These developments in advanced data loading and real-time integration are exciting and applicable to many business situations, but seven best practices must be applied to fully leverage the technology.

Any application - whether BI, performance management, planning, reporting or analytics - should start with the business problem at hand. Before any code is specified or written, the business requirements must be modeled, interrogated and approved by the business sponsor.

A solid understanding of the data, presentation, business rules and consumption is necessary. Whether this is done through use cases, prioritized lists, current report analysis or some other method, it needs to be done.

Getting signoff on the requirements is equally important. We all realize that requirements will need to evolve in the business, but getting a finite, point-in-time signoff will support selection of a technology platform that addresses the known requirements.

Every piece of business data has a shelf life. After a certain point in time, business data is not useful for decision-makers. For example, in today's economic environment, there is a need for much more up-to-date customer credit information to decide whether to extend lines of credit. Quarterly data that was previously acceptable may be required on a monthly or weekly latency instead. Another example is inventory levels. As organizations manage their inventory and cash flow more closely, a more up-to-date inventory level may be required for decision support systems.

As with business needs, the people who best understand the timing requirements are the business leaders and users. It is very tempting for IT to gather the needs and then apply the most advanced tool in its arsenal, such as real-time integration. Instead, IT must be patient and get direction from the business. And just like the data requirements, IT needs to get signoff on these requirements.

By skipping this step, the financial services company mentioned earlier would have incurred additional costs, increased the project's time frame and risked jeopardizing the relationship between IT and the CFO.

In a traditional data warehouse environment, the main decisions about history were typically a) which type of slowly changing dimension was to be used and b) what the budget was for storing this history. With real-time and near real-time integration technologies, the BI application often connects directly to a data source and does not physically move the data into a warehouse or mart. Because transaction systems are dynamic by nature, the capture of the various historical points of the data is at risk of being lost. This may be acceptable to the business because it may not care about the intermediate results and only want end-state data. For example, if the business need for end-user reporting on a 401(k) is to report on the various positions in equities at the end of the day, the multitude of trades and reconciliations that happen throughout the day may not be necessary in the decision support system.

Decision support systems typically will need internal data that resides within an organization's systems and external data from outside parties, such as business partners or data syndicators. Although the business may need this data in a low latency fashion, there may be business or technical boundaries that will not allow a movement from high latency to low latency. IT must understand the contractual as well as technical constraints with regard to external data.

I worked with a product manufacturer that was purchasing market share data to gauge their relative position in the market. This manufacturer wanted to test a few new products and correlate this with the market share data. They wanted to gauge market penetration on a weekly basis to decide whether to formally launch a product as part of their mainstream product line, but they were constrained by their market share data provider, which only provided monthly aggregate data that typically lagged by one month. All of the manufacturer's technology and middleware could not change the reality of the constraints of their data provider.

Just as business requirements will evolve, so will an organization's technology choices. It is critical that IT incorporate the ability to easily move from high to low latency and switch between traditional "lift and shift" and more real-time integration technologies when selecting and building an architecture. IT needs to avoid the temptation to hardwire into the existing systems. The use of middleware and SOAs will mitigate against the hard-wiring issue.

Many of the organizations I work with have been quite successful in implementing various data marts and data warehouses. They have acquired the technologies that are right for their particular application and business functions. They have also invested in their staff's ability to work with their various tools to support the loading of the warehouse and marts.

Although organizations should look at leveraging existing investments in their tools and architecture, the move toward the hybrid low/high latency environment will most likely require an acquisition and/or repurposing of existing technologies. Organizations will need to guard against trying to fit a square peg in a round hole because they are comfortable with a certain technology. Moving to right-time mode requires IT to sometimes get out of their comfort zone created by traditional high latency warehousing projects.