Business Intelligence Network - The Vision for BI and Beyond

ADVERTISEMENT

Techniques for Integrating Business Intelligence into the Enterprise, Part 2

Published: 18 July 2006

This article investigates the second of the four popular ways of integrating business intelligence into business processes.

 Editor's note: This article was originally published at http://www.businessintelligence.com/ex/asp/code.85/xe/article.htm.

In this second article concerning techniques for integrating business intelligence (BI) into the enterprise, I would like to continue the discussion by investigating the second of the four popular ways of integrating business intelligence into business processes. To review, these four ways are as follows:

Option 2 - Embedding Analytics in Operational Applications
Embedding analytics in operational applications is typically an approach that is undertaken by IT developers during application development. Developers have a choice in how they want to do this. They can either exploit business intelligence functionality at the application level, at the data level or at both levels. Let's explore these approaches in more detail to understand the advantages and disadvantages of each.

Figure 1

Embedding analytics in operational applications at the application level can be done by using specific proprietary or industry standard application programming interfaces (APIs). An example of an industry standard API is JOLAP. Operational applications can use this approach to call popular business intelligence tools and analytic applications or to call pre-built business intelligence components to leverage business intelligence functionality. Such calls may be to request a report, a cube, automatic analysis, visual charting, etc.

Developers can simply hand code these application level APIs into their operational applications; or, alternatively, they can use their existing application development tools to select the functionality they need from of a library of pre-built business intelligence components (Figure 2). The types of components include OLAP, mining, navigation and visualisation components. Vendors such as Oracle (BI Beans), IBM DB2 AlphaBlox and Microsoft ProClarity (Analytic Application Development Platform) all offer this kind of pre-built business intelligence component framework with the main emphasis on plugging into existing interactive development environment (IDE) tools used by IT application developers. Examples of IDE tools include BEA WebLogic Workshop, Borland JBuilder, IBM WebSphere Application Studio, Microsoft Visual Studio, Oracle JDeveloper, etc. Other business intelligence vendors such as SAS (AppDev Studio) and Business Objects (Application Foundation) offer their own application development tools; however, these are more aimed at analytic application development rather than embedding analytics into operational applications.

Figure 2

Several business intelligence tools and analytic applications also have their own APIs, although the trend here is toward Web services as the dynamic industry standard mechanism to interoperate with business intelligence tools.

At the data level, the DBMS vendors over the last few years have been particularly busy enriching their database products with more and more business intelligence functionality. This has been done in a number of ways:

• Extensions to the SQL language (e.g. new aggregate functions)
• Materialized view technology
• OLAP in the database
• Mining in the database

In the area of SQL, IBM, NCR and Oracle in particular have added extensive support for new aggregate functions. Microsoft has also added some OLAP functions in SQL Server 2005. The SQL language now supports statistical and analytical functions, regression functions and OLAP functions. Figure 3 shows an example set of these.

Statistical & Analysis	Regression	OLAP
AVG CORRELATION COUNT COUNT_BIG COVARIANCE MAX MIN RAND STDDEV SUM VARIANCE	REGR_AVGX, REGR_AVGY REGR-COUNT REGR_INTERCEPT REGR_R2 REGR_SLOPE REGR_SXX, REGR_SXY, REGR_SYY	CUBE ROLLUP GROUPING RANK DENSE_RANK ROW_NUMBER NTILE PIVOT UNPIVOT

Figure 3

Operational application developers can exploit analytical extensions available in the SQL language just like they code any other SQL. Also, all these BI SQL functions are subject to database optimization and parallel SQL execution if you have a parallel version of a DBMS product installed. DBMS vendors will continue to add to this list over time.

Materialized views are also very powerful in that they allow relational DBMS products to support summary data in the form of a view over relational tables. The classic example here is materialized views over the top of a star schema to represent a higher level layer in a cube. It's like a cache. This is not new, of course, in the sense that many companies have been creating static summary tables for many years. The difference with a materialized view is that the DBMS can automatically refresh summary data (i.e., there is no need to re-run batch jobs or DBMS snapshots to create refreshed summary data if the detail changes). In other words, this is not a pull to calculate higher levels of summary data, but a push for automatic refresh when the data changes in the detail. In addition, DBMSs may shield these materialized views from the application developer in the sense that an administrator may create materialized views; but it is the DBMS optimizer that chooses whether or not to use them to provide a query result set more rapidly. Figure 4 shows how IBM's DB2 Optimizer exploits materialized views (called materialized query tables/MQTs in DB2). IBM DB2 cube views are based on this MQT technology.

Figure 4

Oracle also support materialized views and Microsoft SQL Server 2005 Analysis Services has proactive cache cubes.

Support for OLAP in the database differs somewhat across the DBMS vendors. We have seen the SQL extensions that would give the impression that so-called relational OLAP (ROLAP) is increasingly becoming important. The last several releases of Oracle has brought their multidimensional engine inside the Oracle RDBMS such that table functions, which exploit the multidimensional engine to access a workspace (cube), can be included in SQL statements and hidden SQL views. Therefore, SQL in operational applications or Oracle PL/SQL stored procedures can be used to perform OLAP functions on Oracle workspaces. Microsoft, of course, has SQL Server 2005 Analysis Services, which has been extended to offer tighter integration with relational data using features such as proactive caching mentioned earlier and unified dimensional models (UDMs). However, MDX is still the OLAP language and has a Web services interface to Analysis Services via XML for Analysis (XMLA). In addition, DBMS vendors have now added OLAP metadata. Both Oracle and IBM DB2 for example are now OLAP aware in the sense that they now know what dimensions, dimension hierarchies and measures are, as opposed to just knowing about tables, columns and views. Oracle has an OLAP Catalog, and IBM DB2 Catalog also now holds metadata.

DBMS vendors have also been active with regards to data mining in the DBMS. The introduction of a data mining model standard called Predictive Modelling Markup Language (PMML) has really opened up the capability for business analysts to create mining models and then deploy these into the DBMS as user defined data functions (UDFs) by exporting them in PMML format from data mining tools (e.g., Angoss, IBM DB2 Intelligent Miner for Data, SAS Enterprise Miner and SPSS) and importing them into the DBMSs. Then, any operational application can execute the mining model by including the UDF in an SQL statement in much the same way as you would code an AVG, MIN or MAX function. This causes the DBMS itself to mine the data in place (i.e., in the DBMS) without the need to extract that data out into a separate file. Of course, just like any other aggregate function the mining would be done in parallel if you are running a parallel DBMS server. This is real-time model deployment whereby an operational application makes use of a DBMS mining model on demand. For example, a bank call centre operator using a customer relationship management (CRM) operational application might unknowingly make use of a risk scoring mining model on demand to score a customer to see if the customer should be given a loan. Here, the call centre operator has no idea they used in-place data mining in the database, all they got on their screen was a risk score or recommendation to lend or not.

Figure 5

These examples of business intelligence in the database show that application developers of operational applications have a lot of functionality they can exploit in the database via SQL higher level query process languages such as XQuery. These functions may be operating against data in a data warehouse while operational transactions update operational databases. However, operational applications can exploit transaction processing and business intelligence processing in the same application.

So what are the strengths and weaknesses of this approach to integrating business intelligence into the enterprise? The advantages of this approach include:

• Tight integration of business intelligence functionality in an operational application means that analytics are used in the same business context as operational data
• This approach is particularly suited to information consumers (e.g., customers, front line operations staff) who are perhaps tied to a specific application user interface
• The user is presented with a consistent user interface and operation; user does not have to exit operational application to access analytics
• Analytic functions embedded in the DBMS (e.g., IBM, Oracle, Microsoft SQL Server) support exploitation of business intelligence within enterprise applications

The disadvantages of this approach include:

• Still requires an IT developer to do this kind of integration
• Perhaps only new applications are likely to take advantage of this option
• It may be difficult to uncouple the analytical processing functions in the future if this became a requirement
• May only be support certain development environments (e.g., Java and J2EE)

In "Techniques for Integrating Business Intelligence into the Enterprise, Part 3," we will continue this discussion and look at BI Web services as another technique for integrating BI into the enterprise.

Read other installments of this series: Part 1, Part 3 and Part 4.