Vlamis Software Solutions Inc.'s Business Analyzer (VSSBA) bridges the gap between the developer and the business user by providing an intuitive, easy-to-use environment for building and deploying (OLAP) applications without the need to write code. VSSBA utilizes BI Beans to expose the functionality available in Oracle9i OLAP.

Vlamis Software Solutions is an Oracle Certified Solutions Partner specializing in business intelligence (BI) and data warehousing solutions. It first released VSSBA in April 2002. VSSBA runs exclusively on Oracle9i technology, so before reviewing VSSBA I'll outline two key components of Oracle9i that provide the foundation for building BI applications: Relational multidimensional integration and Oracle's Java OLAP API and BI Beans.

Relational Multidimensional Integration

In Oracle9i OLAP Release 2, Oracle's long established Express multidimensional server is hosted within the Oracle database environment. An Express database is now called an Analytic Workspace (AW). (See "Express Picks Up Speed" from the April 10, 2000 issue for more information on Express.) Prior to Release 2, Oracle9i OLAP data was stored in a relational star schema (consisting of fact and dimension tables); with Release 2, data can be stored relationally, multi-dimensionally in an AW, or in a combination of both.

This choice provides the designer of new applications with a large degree of flexibility, and existing Express clients with a migration path for data and business rules currently stored in Express. To migrate, you need to create the necessary AW metadata — to enable access from the Oracle9i OLAP Catalog (more on this later). The amount of effort required will depend on the complexity of the data model, the extent to which manual or automated migration is desired, the availability of conversion utilities, and how much those utilities automate the process.

The Express engine has been integrated into the Oracle Server kernel, eliminating the need for a separate Express Server process. Analytic Workspaces use the same multidimensional structures as Express databases (dimensions, hierarchies, attributes, measures, and so on). Each AW is stored as a binary large object (BLOB) in a single Oracle table — hence there are no external files to manage. The Express Stored Procedure Language (SPL) has been renamed to OLAP Data Manipulation Language (DML). Express SPL programs and commands that manipulate data will run as is. Some minor modifications might be necessary for other commands because a few are now obsolete — for example, commands that communicate with the operating system. Because an AW is entirely within the Oracle database, it shares administration, security, management tools, and all run-time processing with Oracle. This level of integration differentiates the Oracle OLAP offering from those of Microsoft (Analysis Services) and IBM (which resells Hyperion Essbase with DB2); both Microsoft and IBM bundle their respective OLAP and relational servers but they are installed and managed separately. However, to take advantage of Oracle9i OLAP, companies must use Oracle9i Enterprise Edition.

Oracle Java OLAP API and BI Beans

The Oracle Java OLAP API is a pure Java API for the Java 2 Enterprise Edition environment and it allows Java programs such as BI Beans to access the OLAP features of Oracle9i. BI Beans are a set of reusable application components and services that enable developers to build BI applications. The BI Beans fall into three categories: presentation (crosstab, table, and graph), analysis (query and calculation builders), and catalog services. The Java OLAP API accesses OLAP data via the OLAP Catalog. The OLAP Catalog provides a layer of abstraction between data query and physical data storage, which means applications don't need to be aware of the data source — relational or multidimensional — or how to access the data. The Java OLAP API supercedes the existing Express API so custom user interfaces and those built using Oracle Express Analyzer or Objects would need to be redeveloped if the underlying Express database were migrated to an AW. The Java OLAP API is a development environment aimed at professional Java software developers, not business users.

Oracle Reports uses the API to access data and provide a dimensionally aware query builder you can use to construct and save a report that can be run like other Oracle Reports. This method is suitable for canned reports, but for business users to develop and share ad-hoc analyses without writing code or using a prebuilt application, a third party product such as VSSBA or IAF Vista from IAF Software Inc. (www.iafsoft.com) is required.

Using Business Analyzer

The VSSBA thick client uses a Windows-type file menu and tool bar and presents data in grid (crosstab) and graph formats. Users can create and share calculations (called "custom measures" in VSSBA) and queries (selections). Use the BI Beans Explorer to access and organize these user-defined objects in folders within the BI Beans catalog. You can control folder access: For example, sales users can be prevented from accessing objects in the finance folder. Wizards guide the user through the steps necessary to create different objects.

Crosstabs provide all the standard features you would expect in a robust, general-purpose OLAP tool, including rotate, drill-down, extensive formatting — to individual cell level if necessary, and dynamic color-coding. Data can be exported to a text or comma-separated-value file for import into other applications such as Excel.