Everything Oracle

Introduction

This brief discussion of the features and functionality of OBIEE is divided into the following sections:

History

Heterogeneous Data Source Support

Installation and Configuration

Report Development

Repository Development

Performance and Resilience

Guided Analytics

Time Series Comparisons

Authentication

Usage Monitoring

Fusion Intelligence and BI Applications

OBIEE Report Card

History

In September 2005, Oracle Corporation announced that it had agreed to purchase Siebel Systems for close to six billion USD. Apart from the obvious benefits of eliminating a competitor, with this acquisition Oracle obtained Siebel’s CRM product range and a business intelligence tool, called “Siebel Business Analytics Platform” (a product that Siebel had in turn procured with its acquisition of nQuire in 2001). By December 2006 Oracle had rebranded the business intelligence tool, calling it “Oracle Business Intelligence Enterprise Edition” (OBIEE), release 10.1.3. The “Oracle Business Intelligence Infrastructure Upgrade Guide” that accompanied this release and which is aimed at Siebel customers upgrading to OBIEE reveals very little by way of new functionality and shows a one-to-one mapping between the 18 Siebel software components and the corresponding components of OBIEE. A new release of OBIEE, rumoured to have extensive additional functionality, is anticipated in 2009.

Heterogeneous Data Source Support

Large organizations often find themselves, as a result of acquisitions, in possession of databases sourced from different software vendors, and these databases are often widely distributed geographically to serve specific customer markets. The standard mechanism for data consolidation in these circumstances has been by means of data warehousing, in which ETL processes extract data from the source OLTP databases on a daily or weekly basis. Within the data warehouse, data is aggregated into summary tables, and then business intelligence tools access data from the data warehouse. This scenario works well for medium-term and strategic decision making, but it fails to provide real-time access to data.

OBIEE works on the basis of what might be called “just-in-time ETL”. A user query is parsed into subqueries containing native, optimized SQL, and these subqueries are then dispatched to the corresponding databases to retrieve the data needed to satisfy the query. The data is then merged together and presented to the client application as though it had come from a set of columns belonging to a single database table. The data sources involved in these “heterogeneous” or “federated” queries can be transactional OLTP databases, data warehouses, applications such as SAP, or flat files.

The data sources currently supported by OBIEE, excluding version variants, together with their corresponding software vendors are as follows:

Access (Microsoft)

Adabas (Software AG)

DB2 (IBM)

Essbase (Oracle Corporation)

IDMS (ICL)

IMS (IBM)

Informix (IBM)

MySQL (Sun Microsystems)

Netezza SQL (Netezza)

NonStop SQL (HP)

ODBC (Generic)

Oracle (Oracle Corporation)

Redbrick (IBM)

SAP (SAP AG)

SQL Server (Microsoft)

SQL Anywhere (Sybase Inc.)

Sybase (Sybase Inc.)

Teradata (Teradata Corporation)

TimesTen (Oracle Corporation)

VSAM (IBM)

XML (Generic)

The very wide range of data sources supported by OBIEE makes it an attractive platform. A business can be reasonably sure that when it expands and makes further acquisitions, then it’s highly likely that the acquired data sources can be integrated seamlessly into OBIEE without any changes from an end-user perspective, and without needing to rework existing business intelligence reports and graphics.

Installation and Configuration

Architecture

OBIEE has a complex, SOA-based architecture. The following diagram, from Oracle’s documentation, shows the main components that constitute OBIEE:

In addition to the components shown in this diagram, OBIEE sports a varied collection of configuration files and supporting tools. And while the diagram shows a clustered configuration, it shows only one instance of each of the BI processes. A typical OBIEE configuration will have multiple instances of the BI Server, BI Presentation Services, BI Scheduler, and BI Java Host processes, each running on a different machine.

The BI Presentation Services and BI Server are the two key components of the OBIEE architecture. The BI Server connects to a wide variety of data sources, but integrates the data it retrieves using a mapping held in a local data store, the BI Repository, so that to its client applications it appears to contain a single table with a large number of columns. This semantic view of the enterprise’s data is presented to end users by the BI Presentation Services and is the basis for constructing ad hoc queries.

Stability

Once OBIEE is up and running the product has a good record with regards to its stability. However, at least on some platforms, during reconfiguration operations the product displays instabilities, with the same operation leading to different outcomes on different occasions.

These instabilities appear to be a consequence of the SOA-based architecture. By spreading functionality among a variety of networked components, Oracle has ceded greater control over its software to the host operating system and to the reliability of its RPC implementation than would be the case with a monolithic product. Stability in a SOA-based environment inevitably requires a robust product implementation, one that can recover from mismatched inter-process communications in a graceful manner. And much greater care needs to be taken when it comes to product testing, as a certification can be invalidated by a minor patch release of the operating system.

Maintenance

Taken together, the large number of processes, configuration files and configuration tools makes OBIEE maintenance challenging, to say the least. The problem is exacerbated by the need to duplicate configuration information, such as port numbers, so that two or more files must be updated simultaneously to make a valid change to the configuration. And there is no consistency between configuration file structures. OBIEE seems more like a collection of middleware products from different vendors that have been “shoehorned” together, rather than being the outcome of a consistent and considered design effort.

OBIEE is sorely in need of a central management console, one that can choreograph the stopping and starting of operating system processes and that can ensure that the contents of configuration files are mutually consistent. In addition, an independent process is required to gather statistics and provide informative diagnostics in cases of error.

Incremental Upgrades

The clustered configuration of OBIEE allows upgrades to be managed as a “slow burn” rather than as a “big bang”, by simultaneously running both old and new software versions on two different clusters or server pools. The HTTP servers that feed each cluster can be arranged to randomly assign user queries to different clusters in a fixed ratio, so that initially only a small fraction of the user base is assigned to the upgraded cluster. Then, as confidence in the stability of the upgrade increases, servers can be migrated from the old to the new cluster pool.

Report Development

Ease of Use

From an end user perspective, Answers and Dashboards offer a well engineered reporting environment. Ordinary users are presented with a number of dashboards, each of which is divided into pages. Each page is divided into collapsible sections that display data by means of a variety of views, such as tables, pivot tables, and charts.

For the power user, the basic ad hoc query functionality within OBIEE is easy to use. The user selects a subject area. Within that subject area items are arranged in a set of hierarchical folders. The user clicks on an item to add it to a request that defines a report. Requests that need to be run repeatedly can be placed on a dashboard page so that they are executed automatically on entry to that page.

Ease of Development

Report development in OBIEE is somewhat of a Dr Jekyll and Mr Hyde affair. Like all modern BI development tools, Answers and Dashboards makes use of property sheets to specify report content and formatting. Now, as long as a developer can work within this property sheet framework reports can be developed and modified very quickly, which can make Answers and Dashboards a very productive development environment.

Unfortunately, it is not possible to satisfy realistic BI reporting requirements using this property sheet approach to report development – the range of functionality available is far too limited. For example, basic requirements such as changing the default colour scheme to match that of the corporate livery, or dynamically colour coding the cells in a pivot table to highlight important items of data based on non-trivial conditions are not supported using the property sheet approach. Instead, the developer has to resort to more traditional web development techniques: HTML, CSS, XML, Javascript, and HTML DOM. Some of these web development techniques are supported by Oracle and some are not. But even where the relevant techniques are supported, the almost complete absence of any documentation forces developers to reverse engineer configuration files and web pages in order to determine the meanings of the CSS styles and XML messages that have to be modified.

So, it’s very important to determine how much of your reporting requirements can be met using the property sheet approach. Straying outside this regime can have a significant negative impact on development timescales and project cost. And developers who are familiar with reverse engineering the OBIEE internals will be in short supply.

Analytics

Answers and Dashboards sports a moderately useful set of analytic functions. However, while backward looking running aggregates are available, allowing lagged measures to be calculated, windowing and forward looking analytics are not.

In general, you’ll find a far better range of analytic functions in your back-end data source (for example, Oracle’s own database has a comprehensive set of analytics). If you’re working with a single data source then it’s possible to “function ship” analytic function calls to the data source, so you can leverage whatever functionality is present in the data source when it comes to report development (as a bonus, using native analytics can often boost query performance).

Graphics

Apart from standard tables and pivot tables, OBIEE supports a good range of basic business graphics: charts (2D and pseudo 3D), gauges, funnel charts, and tickers.

However, there is no inbuilt support for scientific, technical, or engineering graphics, such as 3D charts, or charts with error bars or confidence limits denoting a degree of risk or uncertainty in the data values.

There is increasing pressure among large corporates to milk their data to the fullest possible extent. The last decade has seen a growing interest in various ways of pursuing this objective, borrowing techniques that were once the preserve of the sciences, techniques such as data visualization, Bayesian estimation, Monte Carlo simulation, and Applied Information Economics. Now it’s not as though Oracle is not investing in these areas; it is, as we can see in the case of Crystal Ball, for example. The problem is that graphics functionality is dispersed and duplicated across a vast range of Oracle products. Just as Oracle needs to develop a single comprehensive reporting solution – which is what we hope OBIEE will become – it also needs a comprehensive graphics solution that can be plugged into any software product that needs graphics support. If only Oracle would spent less time acquiring new products, and more time consolidating and modularizing the vast number of products that it already possesses, its client base would be better served.

For the present, getting advanced graphics into a dashboard involves using a link or embedded page. The problem with this approach is in ensuring that the graphics engine will display the same data as that associated with the end user’s query. Where the query is static the graphics engine can retrieve the same data from the database in parallel with OBIEE – though this is hardly efficient. But where the OBIEE query depends on values entered or selected by the user at run time the relevant data has to be passed to the graphics engine, which involves complex post-processing of the web page.

Repository Development

Repository development is the process by which a developer maps a disparate set of tables from various data sources onto the presentation layer that is used for end user reporting.

An Example

Let’s introduce repository development by way of an example. Suppose your end users wanted to see sales for the current month versus sales for the previous month for the year 2000, and got the following report:

Sample Report using Oracle supplied OBIEE Repository

The “Amount Sold MAgo” column value should match that in the “Amount Sold” column for the preceding month. But, as you can see, sometimes it does and sometimes it doesn’t (for example, “Amount Sold MAgo” is 1,794,931.14 in April, but the “Amount Sold” in March equals 1,859,892.06).

Discrepancies like these would quickly cause end users to question the reliability of the data they were being presented with, and if the report had been generated by an external auditor doing a spot check then it might well lead to a regulatory investigation. Even worse, in practice, the second column would probably be replaced by a percentage change in sales on the month or some similar derivation, in which case the discrepancies would go unnoticed, possibly with adverse consequences for business decision making.

The repository that contains this mapping error is one of the demonstration repositories produced by Oracle itself, and used extensively in Oracle’s set of OBE tutorials. If Oracle can make such an error and not uncover it during QA and testing, might not your developers do the same?

Conventional versus Ad Hoc Reporting Tools

A developer using a conventional reporting tool is less prone to make an error because he is producing a specific report for a specific purpose, and he specifies the actual SQL statements that are used to produce the report. And because the report has a well defined purpose, developing test cases to confirm that the report produces the correct results is straightforward.

With an ad hoc reporting tool, such as OBIEE, an almost limitless number of report variants can be created by end users so that exhaustive testing is not possible. Because of this limitation it is essential that the tool used to build the mapping between the physical and presentation layers makes it easy for a developer to detect design errors.

The OBIEE Approach to BI Design

Overall, the OBIEE approach to repository design is a good one. It introduces a third layer, the logical or business layer, that sits between the physical layer and the presentation layer.

The presentation layer represents the end users’ view of the data with dimensional attributes and aggregated measures, such as “Product Category” and “Total Sales”, organized into hierarchical folders. The items in the presentation layer are mapped onto the logical layer.

The logical layer consists of one or more simple star schemas – a logical measure table with one or more dimension tables as masters. The advantage of a star schema is that irrespective of the way in which attributes and measures are selected the measures will aggregate to produce meaningful reports, avoiding design traps. The logical layer is mapped to the physical layer, allowing data in a single logical table to be sourced from disparate physical tables, so that a single logical query can generate multiple physical queries, whose rowsets are merged together to produce the final result.

The physical layer is not physical in the sense that it maps directly onto tables in the data sources. Instead, its purpose is (1) to explicitly define semantics that may only be implicitly defined in the data sources; and (2) to define a set of unique paths for navigating between tables present in the data sources, so that valid SQL statements can be constructed. The physical layer is organized around a physical layer diagram that resembles a conventional foreign-key master-detail diagram that shows the relationships between tables in the data source. The difference between the two is that the data source diagram will usually contain navigational ambiguities. These ambiguities are not a problem for a developer constructing a conventional hand-crafted report, since the developer brings an understanding of the semantics on which the diagram is based. But, since OBIEE lacks this understanding, it is necessary to produce a physical layer diagram that (1) disambiguates data source tables which have different meanings in different contexts, and (2) defines a unique foreign-key path between data source tables. With this structure in place, OBIEE can automatically navigate the data source tables and generate the SQL statements that match end user requests.

The physical layer diagram is created by removing multiple foreign-key paths between existing tables, by adding virtual tables (by creating aliases) where the same table is used in different semantic contexts, and by adding foreign-key links between tables in the extended set – the end result is more tables and simpler links.

As the above discussion is rather abstract, let’s take an example. Suppose in your database you have employees who work for departments that belong to sectors (table “employees” is a detail of table “departments” which is a detail of table “sectors”). Suppose that employees are managed by other employees (table “employees” is a detail of table “employees” – “pig’s ear”). Suppose that some employees work directly for sectors and not for departments (table “employees” is also a detail of table “sectors”, as well as “departments”, and the pair of relationships is mutually exclusive). Suppose that sectors are managed by sector managers (table “sectors” is a detail of table “employees”).

Data Source Foreign-Key Diagram

In this example, an “employee” plays four different roles: someone who works for a department, someone who works for a sector; someone who manages other employees; and someone who manages sectors.

If an end user picked “Sector Name” and “Total Employee Salary” as the basis for a report, then using this conventional FK diagram to generate the corresponding SQL would be ambiguous: did the user want “the total salary of those employees who work directly for a sector” or “the total salary of those employees who work for departments that belong to a sector”? If the physical layer diagram matched the conventional diagram then OBIEE would have to toss a coin! Indeed, the problem here extends beyond the physical layer as providing an end user with “Sector Name” as an item without qualification is in itself ambiguous – a user would lack the information needed to define which query was intended.

To resolve an issue like this we could create an alias for the “sector” table in the physical layer, say “employee sectors”. This would allow us to offer the end user the opportunity to construct the two different reports, with items organized in different folders within the presentation layer, such as “Departmental Employees” and “Sector Employees”. For similar reasons we need to resolve the potential circular join, “employees => departments => sectors => employees”, by creating an alias for the “employees” table as “sector managers”, and for the self-join between “employees” and itself by introducing an alias called “employee managers”.

Physical Layer Diagram

With this structure OBIEE can provide the presentation layer with items that are semantically unambiguous, and when the logical layer maps onto any set of columns within this extended set of tables, OBIEE can join the tables together using an appropriate SQL statement.

It’s important to ensure that the relationships defined in the physical layer still make sense when data is aggregated; for example, the invalid report with which we opened this section arises because Oracle attempted to map the days of one month onto those of its predecessor, forgetting that the number of days per month varies from month to month – it’s possible to map the 30 days of April onto the first 30 days in March, but the sum of sales for the first 30 days of March is not equal to the sales for March!

A SQL Free Zone

While the Administration Tool that OBIEE uses to map the physical layer onto the presentation layer provides a reasonably productive development environment, it does not leverage a developer’s knowledge of SQL – it’s effectively a “SQL free zone”.

It’s not possible to select a number of attributes in the presentation layer and view what SQL OBIEE would create if the corresponding request were run. Oracle’s documentation does not divulge the algorithms used for SQL generation. The SQL generated by OBIEE is not always optimal, and small changes to the repository design that have no functional significance can alter the SQL that is generated and materially affect performance. It’s not possible to override OBIEE’s generation choice and say, “If the user selects this particular combination of items then use this SQL statement instead” (hard coding the SQL in an opaque view is possible, but it can be too prescriptive).

Quality Assurance

Whereas a conventional program is linear in structure and can be QAed by reading it in its entirety, the QA process in OBIEE involves opening up a vast number of pop-up windows in turn. There’s no way to extract all the non-default property values into a linear stream that would facilitate the QA process.

Performance & Resilience

Clustering

Performance and resilience are well served by OBIEE’s SOA-based architecture. The BI Server, BI Presentation Services, BI Scheduler, and BI JavaHost processes can be independently clustered. BI Server clustering is controlled by primary and secondary cluster controllers, each of which runs on a separate server. The primary cluster controller monitors which servers in the BI Server pool are available and allocates requests to these servers to balance the load. The primary and secondary controllers engage in a constant dialogue to confirm that each is functioning correctly. If the primary cluster controller fails, then the secondary cluster controller takes over. Provided all processes are clustered, then any single point of failure will not prevent OBIEE from continuing to operate.

BI Server Aggregation versus Database Query Rewrite

Most recent databases provide support for query rewrite (for example, Oracle has supported materialized views and materialized view logs for about a decade). When a query is sent to a database with query rewrite enabled then the database first checks to see if the query could be rewritten and run against a summary table containing a relatively small number of rows. If this is not possible, then the original query is run against the underlying fact table, which will usually contain a very large number of rows. This elegant solution is completely opaque to the client application that initiates the query, and avoids business intelligence applications having to have a knowledge of the supporting summary tables and their data structures. The availability of query rewrite is particularly important in a heterogeneous environment where the number of data sources is multiplied.

Databases offering query rewrite usually also offer a fast refresh facility (using materialized view logs in the case of Oracle) so that a summary table can be refreshed following an ETL transfer without having to read the entire contents of the associated fact table – a log of changes to the fact table following ETL is kept and then the summary table is updated by reading the log table which will typically contain a relatively small number of rows.

The BI Server configuration tool has an “Aggregate Persistence Wizard” that offers a query rewrite facility. The Server can generate summary tables in a client database, and can read from these tables instead of the associated fact tables whenever possible. The Job Manager can be used to schedule a regular update of the summary tables to coincide with the completion of client ETL processes.

The important point to bear in mind is that this query rewrite facility is only from the point of view of the BI Server. The disadvantages are clear. Functionality that properly belongs within the client database has been migrated to the BI Server, leading to a poorly-defined architecture. Other client applications that need to access the client database cannot make use of this query rewrite, something they would be able to do with a native query rewrite implementation. Instead, to achieve performance gains these other client applications would have to select directly from the summary tables.

So where a client database has a query rewrite facility then this query rewrite facility should be used rather than relying on BI Server aggregation. Most modern databases will have a native query rewrite facility, but in the case of legacy databases, the BI Server aggregation facility could prove useful.

Query Caching

OBIEE offers cluster-wide query caching in which the results of a query are stored locally on disk and reused when the same or a similar query is issued in the future. Caching is an essential facility when it comes to improving query performance. The nature of ad hoc queries and the design of the BI Presentation Services encourage users to repeatedly issue the same query by constantly reformatting the query results using different pivot table configurations and charts. This behaviour generates a very high level of query reuse.

The problem with caching of any kind is how to deal with stale data. How you decide to deal with this depends on how time critical your business intelligence queries happen to be. OBIEE provides a wide variety of mechanisms for dealing with stale data. The cache can be purged automatically at intervals. Caching can be enabled selectively for individual physical tables, so that data in a frequently updated table is never cached. OBIEE provides a set of ODBC procedures that can be called when ETL processes complete to programmatically purge designated tables. But, by far the best way to selectively purge the cache is to use an event polling table. This table is populated by the client database whenever changes are made to any table (by an ETL process for a data warehouse or by a table-based trigger for an OLTP database). At specified intervals the BI Server polls this event polling table and deletes from its cache any query results that are dependent on the changed data.

Timely Decomposition of Queries

OBIEE is often used by large organizations with an international presence, and with source databases distributed on a global basis. While the BI Server has the capability to perform its own calculations when mapping the physical layer onto the presentation layer, pulling in large volumes of data from disparate data sources is not a practical proposition, and will quickly lead to an abysmal level of performance or to queries that time out.

So whether or not it’s practical to use OBIEE for “heterogeneous” or “federated” queries depends entirely on whether these queries have a “timely decomposition”: they can be decomposed into sub-queries, each of which only returns a relatively small volume of data (relative to the speed of the network connecting the BI Server to the data source). The hierarchical nature of large organizations is such that rows sets rather than individual rows tend to span remote databases (the European sales data might be held in London, the US sales data in New York, and the Far East sales data in Singapore).

The functions that are applied to data by business intelligence queries are principally aggregate functions, such as “sum”, “average”, “maximum”, “variance”, and “standard deviation”. A timely decomposition is possible if an aggregate function can be decomposed into other aggregate functions that are performed separately by each database. Clearly, in, say, the case of a sum this decomposition is possible:

Global Sales =

   Sigma [ sales(i) ] =

   Sigma-European-Sales [ sales(i) ] +
   Sigma-US-Sales [ sales(i) ] +
   Sigma-Far-East-Sales [ sales(i) ]

It’s not so obvious that a timely decomposition is possible in the case of more complicated aggregate functions, such as the variance. But, with a little mathematical manipulation, decomposition is also possible in these cases; for example:

Global Sales Variance =

   Sigma [ ( sales(i) – average_sales ) ** 2 ] / ( N - 1 ) =

   ( Sigma [ sales(i) ** 2 ] –
     ( Sigma [ sales(i) ] ** 2 ) / N
   ) / ( N - 1 ) =

   ( Sigma-European-Sales [ sales(i) ** 2 ] +
     Sigma-US-Sales [ sales(i) ** 2 ] +
     Sigma-Far-East-Sales [ sales(i) ** 2 ] –
     ( ( Sigma-European-Sales [ sales(i) ] +
         Sigma-US-Sales [ sales(i) ] +
         Sigma-Far-East-Sales [ sales(i) ]
        ) ** 2
     ) / N
   ) / ( N - 1 )

Unfortunately, in the case of most analytic functions, especially window functions, a timely decomposition is not possible. In many cases this will not matter as the number of data items that a user wishes to view on screen will always be modest (a user is not going to trawl through millions of rows of data). So data can be “time-bined” with the heavy processing performed on each time bin at source, leading to a modest network transfer to the BI Server.

Where an exact value must be computed, perhaps in financial calculations to meet a regulatory requirement, the BI Server offers no solution at present. An example of such a calculation is the inverse of a cumulative distribution function (suppose as a marketing scheme reward is offered to the 10 millionth purchaser of a product, and that the product is being marketed internationally, with millions of sales held in different global databases). The weakness of the BI Server in this regard is due to the relative simplicity of the mappings that it supports: logical columns are defined in terms of physical columns using a declarative algorithm. If a more sophisticated procedural algorithm was possible, then the logical layer could send repeated queries to the individual databases using an iterative algorithm. In the case of an analytic function, such as the inverse distribution function, then this algorithm would converge rapidly, so that an exact answer could be obtained without having to bring all the data together in a single database.

Guided Analytics

OBIEE is proactive when it comes to providing the information needed for business decision making. It is possible to schedule batch jobs, called iBots, that deliver specified reports to individual users at specified intervals using email, pager, palm, or phone. More importantly reports can be generated conditionally so that they are only sent if some other report returns some rows (or fails to return any rows), and dashboard components can be displayed conditionally using the same criteria, leading to “Guided Navigation”. The availability of this functionality reduces the routine checking that managers and executives have to do as they can be confident that OBIEE will notify them immediately that events requiring their attention are triggered (see OBIEE Guided Analytics).

Time Series Comparisons

Time series comparisons, such as the difference between this year’s and last year’s sales, are the “bread and butter” of business intelligence queries. The information needed for these comparisons may be generated and summarized within the data warehouse or database to which the BI Server connects. However, the BI Server can perform these calculations itself (the “Expression Builder” than maps the Physical layer onto the Logical Layer has “Ago” and “ToDate” functions specifically for this purpose). Alternatively, native analytic function calls can be “function shipped” to database, allowing time lagged data to be calculated at source (usually in a more efficient manner).

Authentication

Each connection request is authenticated by the BI Server. OBIEE offers four methods of authentication:

Repository Authentication

LDAP Authentication

External Table Authentication

Database Authentication

Whenever the BI Server process is started it reads the contents of a repository that defines the behaviour and functionality that is to be performed by the BI Server. User names and passwords can be stored in the repository, providing the BI Server with immediate access to the data needed for authentication.

With LDAP authentication the user name and password are forwarded by the BI Server to an LDAP server to be authenticated. In addition to its role in authentication, an LDAP server can pass user information, such as a display name or a group membership, back to the BI Server to be used in customizing the user’s session.

With external table authentication the user name and password are stored in a database table. Additional information relating to the user can also be stored in the same table and can be used to customize the user’s session.

In the case of database authentication, the BI Server attempts to logon to a specified database using the supplied user name and password, and, if successful, the user is authenticated (this is the authentication option used by Oracle’s BI Application products that are built around OBIEE).

Usage Monitoring

Usage monitoring records statistics associated with each query, such as user name, query execution timings, and the retrieved row count. The BI Server has an optional facility called “usage tracking” in which this information is stored in a database table or a log file.

Of course, in order to analyse this information you need a reporting facility – which is exactly the service that OBIEE provides. So, in an elegant solution that allows OBIEE to report on OBIEE performance, Oracle provides a usage tracking repository and a catalog that contains a set of reports that can be used for this purpose. The usage tracking repository is merged with the business repository (the BI Server Administration Tool provides a two-way merge facility that can be used for this purpose). The default set of reports are basic but they can be enhanced as required using standard OBIEE functionality.

Fusion Intelligence and BI Applications

In its early days Oracle just offered a database and some tools such as SQL*Forms and SQL*Reports to build bespoke applications from scratch. Then it decided to move into the market of tailorable applications, what is now called Oracle Applications / E-Business Suite (Oracle Financials and Oracle HR, for example). While these applications come with their own inbuilt reporting functionality, large organizations may well hold application data on disparate databases, and so a more generic reporting solution is required.

OBIEE, as I have described it so far, provides a means of building business intelligence applications from scratch. With its Fusion Intelligence offerings Oracle provides OBIEE plus pre-build repositories and catalogs that allow reports to be generated from its E-Business Suite (by connecting to DBI materialized views and base summaries) and from PeopleSoft. The default reports and graphics can be very easily altered using the functionality built into OBIEE, either by modifying the existing reports, or by merging the existing reports with similar data from non E-Business Suite / PeopleSoft data sources.

But Oracle also offers BI Applications. With this product you get OBIEE, pre-built repositories and catalogs, together with a data warehouse and a set of ETL processes to populate it from a number of Oracle owned and third-party data sources.

So Oracle offers you a step by step migration route from its long standing ad hoc reporting tool, Discoverer, to OBIEE, to Fusion Intelligence, and, finally, to BI Applications. However, you might find it sobering to compare the relative costs of the licence fees that accompany these potential solutions to your BI reporting requirements. Having done so, you might well conclude that Discoverer is not such a bad BI reporting tool after all!

OBIEE Report Card

So, what’s the overall verdict on OBIEE? In many areas OBIEE deserves a “very good”, but in others the verdict is “could do much better”. My rating (out of ten) for the various features and functionality provided by OBIEE is as follows:

Feature	Rating
Heterogeneous Data Support	9
Product Stability	7
Ease of Maintenance	4
Incremental Upgrades	8
End User Ease of Use	8
Front-End Productivity (property sheet support)	9
Front-End Productivity (no property sheet support)	2
In-Built Analytics	7
Access to Data Source Analytics	9
Basic Business Graphics	8
Technical Graphics	2
Repository Development	7
Scalability	8
Fail Over	8
Query Rewrite	7
Query Caching	9
Federated Query Support	7
Guided Analytics	7
Time Series Comparisons	8
Authentication	8
User-Based Security	8
Production Performance Monitoring	8
Version Control	4
Documentation (Conceptual)	4
Documentation (Procedural - General)	7
Documentation (Procedural - XML, CSS)	1

Everything Oracle

Home

Everything Oracle