BI-to-LOB Integration: Closing the Cycle

Razvan Grigoroiu
Epicor

December 2009

Summary: The business value of business intelligence (BI) will be attained only when it leads to actions that result in increased revenue or reduced cost. This article discusses the necessity of architecting BI solutions that focus on actionable data and information flow back to line-of- business (LOB) applications.

Introduction

While the final output of business intelligence (BI) is traditionally considered to be scorecards and reports that are used as strategic decision support, we will see that this is no longer enough. BI can take a greater role in ensuring that the value of information is harnessed to its potential.

The value of information is quantified by the action that is taken following analysis and its result. In order to maximize the value, BI applications must streamline the decision and action processes.

The business value of BI information is attained only when it results in a business operation that has the effect of increasing revenue or reducing cost.

A BI report might indicate increased demand for a particular product; but this information has value only if it leads to placed purchase orders and distributions to stores, so that sales figures go up.

Such transactional operations are executed and managed with the help of line-of-business (LOB) applications, such as Enterprise Resource Planning (ERP) systems. BI solutions that implement a better integration to LOB systems will ultimately provide the enterprise with a better return on investment (ROI).

Recent advances in hardware and software technologies, such as Microsoft SQL Server Integration Services (SSIS), allow up-to-date and quality data to be summarized and made available to the information user in near-real time.

The times when it took many person-days of IT staff effort to gather such data for presentation in quarterly meetings are now things of the past.

It is not uncommon for extract, transform, and load (ETL) processes to collect data from LOB applications and underlying transactional databases on an hourly basis or even more frequently.

There is available a wealth of near-real-time information that can be utilized by LOB users to streamline operational processes.

Because of this, today’s BI solutions can take a more active role in the operational space by offering a closer integration back to LOB applications. Information should be presented in an interactive, actionable way, so as to allow users to act on it.

This article argues that in order to attain the business value of BI information, the final output of BI must be an action that triggers a business operation on LOB applications. To achieve this goal,

BI solutions must be designed with actionable information in mind and must offer a better integration with LOB applications that will execute the action.

Agility Through Actionable Information

A classical BI solution would consist of four main logical layers: ETL, a data warehouse, OLAP cubes, and presentation (that is, analysis tools).

Data flows through these four layers in a way that is similar to the following: A set of ETL jobs run periodically to gather information from LOB data sources such as ERP systems and other applications that service a business need or their underlying transactional databases.

Data is transformed according to the need of the particular BI implementation and loaded into a data warehouse (that is, a database that is modeled in a denormalized star schema that is optimized for a decision-support perspective).

From there, it is then stored in an analysis-friendly multidimensional structure such as OLAP cubes.

On the presentation layer, Microsoft Office Excel is one of the more popular analysis tools that is used today. It offers a well-known user interface that can be utilized by users who occupy different roles in the enterprise—from executives and business analysts to buyers and other operational staff.

Using Office Excel 2007 and later releases, users can browse their data that is available in OLAP cubes by using pivot tables to get an overview on how the business is performing.

Potential problems or opportunities can be highlighted by using color schemes, and the user can drilldown analyze the particular information and conclude that an action must be taken.

Such an action is usually an operation on the LOB application from which the transactional data was loaded. Users might switch then to the LOB application and perform the required business operation.

In most cases, however, this is tedious work and disconnects from the analysis context. In many cases, when it comes to this part, users have expressed a desire for an automated solution.

In order to streamline decision-making processes, decrease response time, and track the causality of such operations, this action can be started from within the decision-support context and triggered from the analysis tool (Office Excel, in this example).

This step closes the data cycle from BI back to LOB applications (see Figure 1) and encapsulates decision-making logic.

Figure 1. LOB-BI-LOB data flow (Click on the picture for a larger image)

The next generations of BI applications can be designed in such a way that users can act on the information easily. This will bridge the gap between BI analysis and operational processes, and will lead to more cohesive enterprise solutions.

Information utilization is maximized, and BI can become in this way a driver for business-process optimization. Actions that are taken based on BI information can be better tracked; effects can be better analyzed, which will ultimately lead to better business performance.

BI solutions that provide actionable information allow companies to stay agile with business changes by reducing the time between the moment that a business event occurs and when an action is taken.

Actionable information in this sense must contain two parts:

• The condition in which an action is required (for example, on-hand is below a minimum level).
• An action that defines the response when the condition occurs (for example, perform an operation such as order items to restock).

It must specify how the business action can be invocated and where it can be reached (the endpoint).

Based on the principle of separation of concerns—and to allow a layered approach between a data tier (such as an OLAP back end) and presentation tier (that is, an analysis tool)—the actionable information belongs on the data tier.

Actions in this case would need to be consumer-agnostic and triggerable from any analysis tool.

Furthermore, the action implementation represents a business operation and, as such, is governed by LOB-specific concerns and business rules. Therefore, it must be executed by the corresponding LOB application.

Whenever we are faced with data integration between different technology layers, and loose coupling becomes a prime concern, a service-oriented approach comes naturally to mind as an architectural solution.

In this case, a service-oriented architecture (SOA) can represent the glue between the different components that must interact, and can provide the governing concepts of such interaction.

But, when it comes to service orientation and BI, where are we today?

Actionable Information and SOA

An SOA calls for different functional elements to be implemented as interoperable services that are bound only by interface contracts. This allows for a flexible, loosely coupled integration. Different components in such architectures can evolve naturally over time, or they can be exchanged without affecting the solution as a whole.

While LOB systems have been at the forefront of SOA adoption, BI products have moved more slowly to support such architectures.

Traditionally BI-technology vendors have offered monolithic OLAP solutions that use analysis capabilities that cannot be extended with actionable information to simplify end-to-end BI-to-LOB integration.

In recent years, however, we have seen some solutions open up and offer more interoperability features that bring us closer to service orientation.

With SQL Server Analysis Services (SSAS), Microsoft provides a feature that is called “URL actions” and represents a mechanism to store actionable information inside the OLAP cube metadata. Conditions in which the action becomes available can be expressed by using the MDX query language, and the URL provides an endpoint.

Such capabilities in OLAP technologies can be utilized to implement an end-to-end integration back to LOB applications in an SOA.

In an enterprise SOA, operations on LOB applications can be exposed as Web services that hide the complexity of underlying programs and, together with any external Cloud or B2B services, can represent basic components in an infrastructure that models business needs.

Office Excel has the native ability to consume the actionable information by displaying to the user any available actions on cells in which the predefined conditions are met. The caption that will be displayed can also be defined dynamically by using MDX expressions.

Each cell of the analyzed cube is defined by one member (called the current member) from every dimension.

The URL can be defined in the cube to include the current members as a query string.

When the user performs the suggested action on a cell, Office Excel will call the Web application that the URL has located. If necessary, the Web application can collect more information from the user, depending on the action.

Such a solution can leverage advances in rich Internet application (RIA) technologies such as Ajax or Microsoft Silverlight.

A Silverlight front end has the advantage over classical Web applications: Code that is written in a CLI language such as C# will run on the client in the browser application, which minimizes cross- computer calls to the server.

Silverlight is built on the .NET framework and can utilize Windows Communication Foundation (WCF) as an out-of-the-box framework for SOA integration. WCF provides a flexible API to implement SOAP Web service calls from the client process.

Consequently, Silverlight can be utilized as a bridge between SOA and BI.

The cell information (current members), together with any additional information that is collected from the user, can be sent to the Web service that implements the business action; and, at the end, any service feedback can be displayed to the user.

Data flow between any such services can be orchestrated and customized to fulfill the need of business operations.

Using Web services to expose operations on LOB applications in an SOA is a way to utilize existing software assets better and increase their participation in enterprise information flow. This also makes collaboration of the components more flexible, and it becomes easier to adapt to changes in business processes, which ultimately leads to a better IT-to-business alignment.

An architectural solution (see Figure 2) that leverages a technology stack that is based on SQL Server Analysis Services 2008—with URL actions defined on the cube (BI platform), Office Excel 2007 (BI analysis tool), and Silverlight RIA (BI-to-SOA bridge)—can exemplify utilization of enterprise SOA as an enabler of BI-to-LOB integration.

Figure 2. Technology stack

URL actions represent a simple and effective way to implement actionable information for BI solutions that are built on SSAS. The URL can point to a Silverlight RIA application that will act as a BI-to-SOA bridge and make calls to Web services by using WCF.

Implementation Considerations

Integrating data flow from BI back to LOB applications will be beneficial to operational processes. However, as soon as BI becomes an essential part of that world, its design will also be burdened with the mission-critical nature of operations.

Special implementation considerations and requirements must be taken into account, compared to classical BI applications that are intended exclusively for strategic decision support.

The BI solution that is integrated with LOB applications will target a wider audience and a larger number of operational users. BI will need to scale from a limited number of report consumers to a larger number of operational users at different levels in the enterprise hierarchy. In this case, scalability will play a much more important role, and it must be a leading factor when hardware and software design choices are made.

Taking a more operational role will also result in higher availability requirements for BI. Information must be available, regardless of potential hardware or software outages that could affect a server.

Data refreshes during ETL processing will have a more pronounced impact in this case, because it can affect user queries and delay operational processes. Therefore, failover strategies will need to be higher on the priority list than they are during the design of classical BI solutions.

One solution to increase scalability and availability is to use Windows Network Load Balance (NLB) to distribute user requests implemented on different cluster hosts.

NLB will detect a host failure and accordingly redirect traffic to other hosts. Scheduled outages such as ETL processes can be mitigated by using large-scale staging systems, to keep OLAP data available all the time.

Data volume will increase, because business operations are performed at a lower level of information aggregation than strategic analysis. In this case, data will also need to be stored and delivered more coarse-grained to information consumers.

Conclusion

Enterprise BI architectures can maximize information value by offering a closer integration back to LOB applications. Taking a greater role in operational decision making will empower business users to interact with actionable information in BI.

This will enable BI solutions to close the data cycle and drive performance improvement of operational processes.

A technology stack that is based on Office Excel 2007, WCF services, and SSAS—with URL actions that are defined on the cube— can be leveraged to implement data integration easily from BI back to LOB applications in an SOA scenario.

About the Author

Razvan Grigoroiu (rgrigoroiu@epicor.com) is a Software Architect who has been involved for the past 10 years with LOB and BI solutions for the specialty-retail sector.

Follow up on this topic

• SQL Server Integration Services (SSIS): http://msdn.microsoft.com/en-us/library/ms141026.aspx
• SQL Server Analysis Services (SSAS): http://www.microsoft.com/sqlserver/2008/en/us/analysis-services.aspx
• Rich Internet Applications: http://www.microsoft.com/silverlight/
• Service Orientation: http://msdn.microsoft.com/wcf