New trends in data warehouse

There are some new and exciting advances in the business intelligence domain that elevate the value that business intelligence brings to business. In the final article in our series, we look at some of the emerging capabilities in data warehouse and how those capabilities leverage flexibility and responsiveness for business intelligence solutions.

Back to top

Detecting anomalies

Detecting deviations is a very challenging and daunting business task. Deviations in business performance are often hidden in the terabytes of enterprise data and may be discovered if a business analyst happens to slice down to a certain view of the analytical report. Traditionally, business analysts form business hypotheses based on their expertise and intuition. By leveraging OLAP tools, analysts can first start with high level aggregated reports, inspect entries visually, detect the problematic areas, and drill down to lower atomic level to find the causes. This approach seems applicable and feasible as long as your data is manageable. But consider what happens if your enterprise has the following characteristics:

• Enormous data from various data sources
• Large number of stores, products, and customers
• Hidden anomalies in this data

This level of complexity is a typical problem for most enterprises. There are areas of the business that are under-performing, but management does not immediately recognize the problem. Early problem detection would minimize the impact of this under-performance. To accomplish this objective, you would need to be able to programmatically do multidimensional deviation detection to determine where certain areas are "out of whack" with the performance of their peer areas.

So what is the solution? Although OLAP tool can help you answer what if questions, you also need to be able to answer why questions. Mining technologies have been introduced to multidimensional databases, adding the "discovery-driven" functionality to the traditional "hypothesis-driven" capability. Miners for multidimensional databases, such as DB2® OLAP Miner, were designed to detect anomalies and deviations beyond normal human capabilities. DB2 OLAP Miner complements DB2 OLAP in several ways:

• Relief from the manual detection process: DB2 OLAP Miner relieves analysts from the task of manually drilling down to atomic levels of data to detect anomalies or find an explanation for certain trends. When the data set becomes extremely large, the manually-driven approach becomes tedious and error-prone for analysts. The analysts might overlook significant aspects due to the heavy reliance on intuition and visual inspection.
  
  
• Capability to detect patterns in multidimensions: Contrary to common misconceptions, patterns exist not only in atomic data level but also in dimensions. Moreover, the fact that different dimensions have different patterns also calls for mining in OLAP. An OLAP Miner engine helps detect patterns that exist in granular data as well as in summarized data. An OLAP Mining system mines along multiple dimensions, recognizes the patterns along these dimensions, compares or merges them, and interacts with the user in a very intelligent way. For example, OLAP may tell you "Blues products are profitable in NC in August" but OLAP Miner tells you "Blues products are profitable in NC in August, but much of the profits came from jackets in large stores during the tax-free weekend sale".

In a nutshell, OLAP explains what happens to your business; OLAP Miner further gives you why it happens. OLAP Miner complements OLAP in such a way that OLAP systems can provide services on discovery as much as on access.

Back to top

Discovering hidden patterns

While OLAP is hypothesis driven and human driven, data mining is data driven and discovery driven. OLAP allows analysts to ask a specific question, such as "What is the sales performance in New York for product A in year 2003 third quarter?" In contrast to OLAP, you don't have to ask a specific question for data mining. Instead, data mining, equipped with sophisticated statistic algorithms, discovers or unveils hidden patterns which are impossible to detect with human endeavor.

Data mining allows business to be proactive. Using data mining technology you can discover new problems or opportunities that you did not know existed. It is always interesting surveying the shopping basket of people in line at the express checkout in a grocery store. The combination of what they urgently had to run out to buy makes you wonder. The variety of human behaviors is just one of the factors that contribute to subtle differences in business operations across an organization.

The old, traditional approaches, using the mining workbench, heavily rely on mining experts to derive the mining models. In contrast, the latest approach has begun to shift the paradigm to integrate data mining technologies into databases themselves. This permits the mining models to interchange information through Predictive Model Markup Language (PMML).

Moreover, the visualization tool is not limited only to the workbench; various levels of decision makers can visualize the mining models online, around the clock, around the world, through the Web. The deployment of mining models has also advanced to be easily integrated with operational systems, and can be dynamically applied to each individual customer's data at various sales channels. The integration process can be as simple as invoking an SQL statement to apply new data against a mining model stored in a DB2 database. The new integrated architecture is depicted in Figure 1.

Let's look at the three core components in the new integrated mining paradigm.

Back to top

Mining with IBM Intelligent Miner Modeling

Although the traditional workbench mining tools still dominate the markets, there are still many roadblocks to integrating applications with mining tools. For example, it is difficult for an operational application to dynamically trigger actions to tune the mining parameters, substitute input data, and rebuild mining models. IBM Intelligent Miner (IM) Modeling solves this problem by providing DB2 mining UDF functions and letting users store mining results in relational tables. Such tables can be incorporated in standard reporting solutions and are easily understood by end users.

IM Modeling provides an SQL programming interface to the data mining functions. Built as DB2 data mining functions, IM Modeling directly integrates data mining technology into DB2 UDB. This leads to faster application performance as well as better integration.

Companies can benefit immensely from storing a model and scores as database objects. Some of these benefits are:

• Ease of automation and integration: Since mining function is tightly integrated with databases via SQL, repeatable mining processes and tasks can be built to avoid manual deployment or set up. This automation process can take the form of a trigger, a batch file or real-time invocation from applications. This automation and integration includes building mining models as well as deploying scoring results. There is no need to regularly update the mining models for scoring since the mining models are stored in relational tables which scoring functions can directly access. Therefore, it reduces a lot of synchronization steps.
  
  
• Efficiency and effectiveness: One of the benefits is marketing automation and simulation. For example, companies can mix and match different mining models in a specific campaign, create multiple campaigns, select a subset of customers to simulate the impact of different campaigns, and then store and compare the results. The whole process takes place in one environment because both models and scores are database objects. Another benefit is administrative efficiency. The DBA can easily backup and restore transaction data and mining models as well as scoring results in the regular database administrative steps.
  
  
• Performance boost: With built-in SQL interfaces for mining functions, performance can be significantly improved because you don't have the overhead of transferring or moving data since the mining functions are executed where the data is stored.
  
  
• Leveraging of existing IT skills: Mining with SQL programming languages empowers regular IT staff to perform the mining tasks which normally require expertise from data mining experts. Since the number of mining experts remains constant and the demand of mining keeps increasing, the mining experts' turnover rate remains high. SQL mining programming language allows companies to automate the mining processes significantly, and thus diminish the threat of losing skills. At the same time, the deployment of mining increases.

Visualizing with Intelligent Miner Visualizer. Visualization of the mining models provides the ability to interact and graphically present the results of mining. It allows the analyst to investigate the rules derived from data, data distribution and statistic details pertaining to each mining algorithm. The visualizer enables companies to analyze model results in order to gain new business insights and find new market niches.

Visualization of the mining models is no longer confined to workbench mining tools. The Intelligent Miner Visualizer allows you to view PMML-conforming mining models from many possible channels, including Web applications, portals and PDAs. Applications can call these visualizers to present model results, or they can deploy the visualizers as applets in a Web browser for ready dissemination. If you deploy your application as applets in a Web browser, you will have the benefits of having a central point of installation and reducing administration time, as well as being able to integrate with other existing Web applications or portal applications.

Proactive scoring with Intelligent Miner Scoring. While mining technologies discovered the hidden or counterintuitive patterns, the applicable values rely on the deployment model. The real-time deployment allows companies to score (segment, classify, or rank) records bases on a set of predetermined criteria expressed in the data mining model.

Take the customer segmentation in the Web store as an example. A retailer can analyze the customers' Web shopping behavior and from it derive patterns, models, and statistics which in turn can be used to determine what they can expect to be sold and to whom. The company might classify customers into several categories according to their historical spending patterns, such as platinum, diamond, gold, silver, and so on. When a customer (Joe Smith) logs on the Web again, his data will be gathered and applied against the model. The model might classify Joe Smith in the Platinum category, and he is presented with his customized discount, up-sale or cross-sale information. This allows companies to seize the business opportunity from various points of sale.

Current trends of real-time analytics favor timely deployment of analytics models over more perfect preparation of the data. In other words, the goal is to produce a real-time deployment within a matter of days instead of weeks in order to catch business opportunities, even if the mining model is less than perfect. Scoring technologies become very important in implementing a closed-loop, real-time CRM application.

Since easy integration is key to success, let's take a close look at IM Scoring. Within IM Scoring, the mining functions are grouped into the mining types Clustering, Classification, and Regression. Scoring functions are provided to work with each of these types. Each scoring function includes different algorithms to deal with the different mining functions included within a type. For example, the Clustering type includes Demographic and Neural Clustering; thus, scoring functions for Clustering include algorithms for demographic and neural clustering.

A typical usage of the IM Scoring mining functions would include the following four steps:

1. Import the mining model into a DB2 table, where it is stored as a large object.
2. Apply the model to data stored in DB2 tables.
3. Store scoring results in a DB2 table.
4. Extract information about the results, for example, the cluster ID and score.

In conclusion, IM Scoring provides a very easy-to-use API based on SQL UDFs and UDMs. It provides vendor interoperability by facilitating scoring on mining models specified in PMML.

Back to top

Sense and respond

New technologies are becoming available that enable businesses to take immediate actions in response to business events as they occur. Rather than finding out later that a problem arose that needed resolution, with sense and respond technology you can respond immediately because the business events themselves can be made actionable.

In a traditional business intelligence environment, there can be a significant delay in the time it takes from the generation of a business event to the materialization of an analytical report that communicates the business impact of that event to a person. However, speeding up this process would help the business react in a more timely manner. Some have given this new design for real-time analytics a trendy name - the Zero Latency Enterprise - thereby implying the speed to business intelligence that this would bring about.

Another dimension of real-time business intelligence is the actionable part. The system provides not only for the detection of a situation but for initiating the appropriate action in response to the situation. Terms like closed-loop business intelligence and business activity monitoring describe the move toward these capabilities. An organization that becomes more responsive gains more control. It can be react to problems that occur and quickly move to resolve these problems before the business is impacted. It can also become proactive, allowing the business events to lead the company to seize opportunities swiftly. With business intelligence, the organization can predict when problems or opportunities are about to arise and take the appropriate action.

This active intelligence brings about some new patterns of business processing. The first pattern is sense and alert, where situations are detected and the appropriate person is notified. At first blush this may not sound like something that is too revolutionary. However, when you look at the flood gate of information and events that consume a business person, the opportunity to have a system filter out the unimportant information and get right to the information that requires attention is a great thing.

Another pattern of active intelligence is sense and respond. This extends the sense and alert pattern by having the system initiate the response action automatically. Automation is nothing new to IT systems. Custom automatic systems have served business operations for years. However the marriage of automation with business intelligence and business process management is significant.

One example of active intelligence is a set of IBM research technologies called Active Technologies (see http://www.haifa.il.ibm.com/projects/software/amt.html). These technologies demonstrate how an intelligent framework for managing and reacting to business events can be created. Active technologies consist of three elements:

• Active Middleware Technology
• Active Dependency Integration, and
• Active Real-time Automated Decision Making.

Active Middleware Technology (AMIT) is a rules engine for monitoring events and determining when events become situations. The rules correlate different events over time to look for trends or patterns of conditions that need action. This is important because any one event by itself is meaningless but within a context of previous events that have occurred it then takes on significance. A simple example illustrates this idea: One bee sting for most people who are not allergic is nothing more than a painful distraction. However two or three bee stings can bring on much more serious health concerns. This escalating concern pattern is an important part of the sense and respond processing.

Active Dependency Integration (ADI) models the interactions between events, business entities, and their interdependencies. It provides a dependency context for AMITs event monitoring rules engine. Entities whether they are computer system entities or human entities have relationships and dependencies with other entities. These relationships are an important part of the understanding required to provide proper meaning to business events.

The third active technology, Active Real-time Automated Decision Making (ARAD), enables optimization of the actions initiated to respond to choose the best alternative amongst several alternative actions for the situation.

Now let's look at a business scenario that shows how sense and respond can benefit a business. We'll be using the fictional example company IFM whose business intelligence requirements we've discussed in the first three articles in this series.

Back to top

Scenario solution architecture overview (Sense and respond)

Christina and the management team have noticed there are conditions that are occurring in the stores and in the supply chain that are not being detected in a timely way. If they could be detected, then the pricing business process could be initiated to react to the problems. The first reactive process is to monitor various measures that would indicate if there is a problem with the sales of a particular product. An inventory problem pops up as the most challenging. Since IFM has over a thousand stores that sell an assortment of over 10,000 products, it is very difficult for IFM corporate personnel to monitor all possible deviations in sales and inventory levels of a particular product. They decide to implement a monitoring process which triggers a discount offer to promote a sale if a particular product is overstocked.

In this scenario, an excess inventory situation is detected via OLAP Miner. The mining result triggers a promotion business process. The sense and respond behavior is implemented by the combination of these products plus the OLAP reporting engine and rules engine components discussed in previous articles. The steps of sense and respond process is relatively straightforward:

1. Sense
   1. DB2 OLAP Miner looks for deviations.
2. Respond
   1. WebSphere® Business Integration Server (WBI) initiates a new business process.
   2. The process gets additional analytical data via the OLAP reporting engine connection.
   3. The process invokes the appropriate business rules.
   4. The process broadcasts the new discounted price.

The OLAP Miner mining result contains the dimension information, the current inventory values and the expected values as well as the magnitude of the deviation. These results are output to a flat file that WebSphere Business Integration polls for. Whenever the file is updated, a new business process instance is started. After cleansing and filtering the mining results of unwanted candidates, WebSphere Business Integration queries the Web store for pricing information. In addition, it retrieves the product profit percentage information through a URL to OLAP reporting engine (in our example it is Alphablox). It then makes a query to the rules engine to determine the discount, based on excess inventory quantity, price and profit percentage for the particular product. The result of discount information is then presented in the portal server. Figure 2 depicts the above process.

Back to top

OLAP Miner (Sense Component)

• Enable OLAP Deviation Detection

Once the IFM data warehouse team has created their multidimensional database, they use OLAP Miner to help them detect anomalies and deviant values within their cubes. The OLAP Miner Client automatically shows the applications that IFM currently have and the available cubes on which they can do deviation detection. The next paragraphs show how IFM defines their deviation detection and how to run it.

• Name the deviation detection definition.

IFM creates their deviation detection definition by first selecting the cube that their want to inspect. From the menu they select "Create Deviation Detection Definition".

• Select members from each dimension

Next they define an area of the cube they want OLAP Miner to examine and detect deviations. In their business scenario, they want to detect the atypical inventory status for the product in July 2003 across different market segments. They are not interested in a higher aggregated level since it has no direct impact on inventory planning. Therefore, their OLAP deviation definition consists of setting inventory as the measure, selecting July 2003 as the time dimension slicer, and choosing all product family and market segments to detect. OLAP Miner displays the selections that they made in the deviation detection definition. IFM also specifies the top 30 deviations that they want OLAP Miner to return so that they can do the promoting more efficiently. Figure 3 illustrates the OLAP Miner deviation detection wizard.

• Run and Interpret OLAP Miner Result

After the IFM data warehouse team runs the deviation and selects the results file, a list of deviations is displayed in the right pane of the main window. On the result page, different columns are presented: Scenario, Year, Caffeinated, Market, Product, Inventory, Expected, and Magnitude. The first five columns represent the dimensions that IFM selected in the deviation detection definition. The inventory indicates the current value in the cell of the cube detected. The Expected column describes whether the calculated expected value is higher or lower than the actual value of the cell. OLAP Miner calculates the difference between the expected value and the actual value and then derives the magnitude. The higher the magnitude number is, the more deviated it is and the greater the disparity that exists between the expected value and the actual value.

Take the fourth row from IFM previous result as an example. It shows that the current inventory for product 200-30 with the Caffeinated characteristic in the West region is 4,229, a value which is much greater than the expected value. This indicates that the West region has excess inventory on product 200-30 and some promotion can be performed to vacate the inventory.

Figure 4 displays the cells that deviate from the normal patterns according to IFM's deviation detection algorithm.

Back to top

WebSphere Business Integration (respond component)

WebSphere InterConnection Server (WICS) drives the business process that handles the "respond" part of the solution. It links to the OLAP reporting engine for analytical data and to the rules engine for the enforcement of the pricing policy. The sections below discuss the WBI features used to integrate these components and implement the business process.

Back to top

WBI Adapters

WBI adapters (also known as "connectors") connect the various systems into the business process logic. A custom OLAP Miner adapter retrieves the OLAP Miner deviation detection output, converts it to a form useable by other WBI components and passes it on. This adapter is configured to use a custom data-handler that was written specifically to process the OLAP Miner output into a WBI business object. This data-handler is assigned a mime-type of text/Deviation. In the process of converting the text OLAP Miner output to a WBI Server business object, the data-handler filters out extraneous data, such as the aggregated monthly and yearly information. Listing 1 below shows the format of the OLAP Miner output.

 1: "OLAP Miner Version 7.1"
 2: "Scenario" ,"Year" ,"Caffeinated" ,"Market" ,"Product" ,"Inventory" ,"@Expected" ,"@Magnitude" ,"@Bitmap"
 3: "Scenario" ,"Jul" ,"Caffeinated" ,"Massachusetts" ,"100" ,-1493 ,2.27693 ,7 ,00011
 4: "Scenario" ,"Jul" ,"False" ,"Massachusetts" ,"200" ,1304 ,7558.66 ,6 ,00111
 5: "Scenario" ,"Jul" ,"True"  

Since OLAP Miner output data is text-based, a JText connector (configured to use the custom data-handler) could theoretically have been used instead of the custom adapter. However, a JText connector always moves an input file as it is processed, which presents a problem, since the OLAP Miner output file is needed for other analysis. Therefore, a custom OLAP Miner connector was used instead of a JText connector. Figure 5 shows the configuration properties for the OLAP Miner connector, including the mime-type designation for the custom data-handler. Note that the connector polls the OLAP Miner output folder for new files.

A custom rules engine adapter passes information to a rules engine and obtains the rule-based discount result. The details of the custom rules engine adapters were discussed in article 2 of this series.

A JText connector, which is the WBI adapter specialized for handling text input files in various formats, uses a map to convert the business process results to XML for display in WebSphere Portal Server.

Back to top

WBI Server Business Objects

The OLAP Miner connector and Deviation data-handler convert each row in an OLAP Miner deviation detection output file into a line item (METAOUTLINEBO) business object within a DeviationDetectionBO business object. The map shown in Figure 6 is used to convert METAOUTLINEBO line items to the rules engine business object.

Finally the output JText connector uses maps to convert the DeviationDetectionBO to an XML-based business object from which the output XML is produced.

Back to top

WebSphere Business Integration collaboration

The WBI Server collaboration defines the flow of the business process. The primary purpose of the collaboration in this scenario is to use OLAP Miner deviation detection output, along with product profit and price information to compute a discount for overstocked products. The collaboration also delivers the deviation information, formatted as XML, to the WebSphere Portal Server. As the diagram in Figure 7 indicates, the business process collaboration is triggered from the OLAP Miner deviation detection process (OLAPMinerConnector), uses rules engine information in addition to the deviation information, (VersataConnector), and produces a text XML output (JTextOLAPConnector).

Figure 8 shows an annotated WBI process designer screen shot of the collaboration business process.

Since the input deviation information includes entries for overstock and for out-of-stock situations, the collaboration discount computation first eliminates the out-of-stock entries ("cleanse") before determining the discount for each overstock entry. To determine the discount, a rules business object containing profit and deviation information is passed out a port to the custom rules engine adapter, the adapter initiates rule evaluation in the rules engine, and returns the updated rules business object to the collaboration. Finally, the collaboration updates each overstock deviation entry with discount information and passes the updated deviation information to the output JText connector.

Back to top

Rules engines

The discount percentage value for overstocked products is computed in a rules engine. The rules for choosing a discount for an overstocked product consider the variance of the deviation of on-hand supply from the expected value, as detected by OLAP Miner, and the price and percentage profit for the product. If the variance is extremely large, the discount is selected based on whether the profit percentage is high, medium or low, with "high", "medium" and "low" values defined based on corporate policy. If the variance is low, the rules for computing the discount are a variation on the dynamic pricing rules used to compute the markup in article 2. As in article 2, WBI adapter technology makes switching from one rules engine to another a simple matter of selecting the appropriate custom rules engine adapter.

As we stated in Article 3, percentage profit is not something readily available in operational systems. It is a calculation on historical results. Profit will fluctuate over time and across the enterprise. For that reason this analytical information must be extracted from the data warehouse environment.

Back to top

A sample application of deviation detection

Figure 9 demonstrates how three different products have been detected to have deviation behavior, either large overstock or under stock. According to its corresponding profit, each product has been targeted with a different discount offering.

Back to top

Conclusion

In this article series, we've shown you how some of the exciting new technologies in the industry can enable a more dynamic and responsive business environment. Through the examples of the IFM company we have illustrated how business process management gives the organization more control over process re-engineering, how externalization of business rules allows for the separation of policy from the process, how business intelligence can enhance the business process, and finally in this last article how sense and respond techniques can make a business more reactive and proactive in managing its operations.

About the authors

Comments (Undergoing maintenance)

Back to top

Trademarks  |  My developerWorks terms and conditions

Table of contents

• New trends in data warehouse
• Detecting anomalies
• Discovering hidden patterns
• Mining with IBM Intelligent Miner Modeling
• Sense and respond
• Scenario solution architecture overview (Sense and respond)
• OLAP Miner (Sense Component)
• WebSphere Business Integration (respond component)
• WBI Adapters
• WBI Server Business Objects
• WebSphere Business Integration collaboration
• Rules engines
• A sample application of deviation detection
• Conclusion
• About the authors
• Comments

Next steps from IBM

• Try: DB2 Enterprise
• Demo: DB2 serving ATOM feed
• Webcast: A smarter way to successful SOA - Information as a trusted strategic asset
• Briefing: Information on Demand
• Buy: DB2 Enterprise

My developerWorks community

Dig deeper into Information Management on developerWorks

DB2 pureScale

See how true transparent scaling architecture can be achieved on distributed platforms

Special offers