Oracle OLAP and Essbase architecture and components

Oracle OLAP and Essbase architecture and components
Андрей Волков

Андрей Волков

Системное, сетевое администрирование +DBA. И немного программист!))  Профиль автора.

Your choice of an OLAP product from Oracle depends in part on your current IT infrastructure. If you are running an Oracle database as a data warehouse, the obvious solution is to add the Oracle OLAP option to your data warehouse. If you have an Oracle database as one source of data or if your organization has only non­Oracle data sources, your choice will depend on the criteria: purpose, buyer, typical end user, data management strategy, and architecture.

Knowing how Oracle OLAP and Oracle Essbase are architected and understanding their components will help you choose the correct product for your needs.



Oracle OLAP Architecture and Components

Oracle OLAP is a feature of the Oracle Database. This section describes how Oracle OLAP fits in with the Oracle Database, and then describes the Oracle OLAP components.


Oracle OLAP Architecture

Oracle OLAP has a simple architecture, as depicted in Figure 1. Oracle OLAP is a licensed option to the Oracle Database Enterprise Edition. As such, Oracle OLAP applications benefit from other features of Oracle Database, including scalability, reliability, security, backup and recovery, and manageability.

Client Tier Oracle delivers multiple tools to work with Oracle OLAP, but fundamentally, Oracle OLAP is part of the Oracle Database. While AWM is the main client tool that manages objects in Oracle OLAP, you can also use Oracle Warehouse Builder (OWB) to build cubes. From an architectural standpoint, client tools communicate directly with the Oracle Database, typically using Oracle Call Interface (OCI), Java Database Connectivity (JDBQ, or Open Database Connectivity (ODBC), in the same way as other Oracle Database tools. You can query OLAP cubes using SQL, PL/SQL, or the Multidimensional Expressions (MDX) language (via a driver available from Simba Technologies). Any tool that can query an Oracle database can query Oracle OLAP Database Tier Oracle OLAP is in the kernel of the Oracle Database, part of oracle.exe, that is running when you are running the database. Because of this, there is no such thing as running Oracle OLAP without running the Oracle Database or starting it separately. OLAP is always installed when you install the Enterprise Edition of the Oracle Database. You can choose to disable the OLAP option, but it is on by default.

Oracle OLAP architecture

Figure 1. Oracle OLAP architecture


At the heart of Oracle OLAP are analytic workspaces. Think of analytic workspaces as containers for multidimensional structures (cubes) in a relational database. Analytic workspaces are stored as binary large objects (BLOBs) in one or more rows in relational tables. An Oracle BLOB provides a convenient way to store binary data in a table. The BLOB provides this mechanism and is the magic that allows Oracle to store true multidimensional data in a system built around relational data. The analytic workspaces are stored in tablespaces and data files just like any other data in an Oracle database.

OLAP DML The OLAP DML is a rich, dimensionally aware 4GL procedural programming language that can be used to enhance the analytic content of the Oracle OLAP cube. This language is the same as that was used in Oracle OLAP's predecessor, Express, but it has been enhanced with many new commands. Using the OLAP DML, you can design advanced custom measures and user-defined functions, and write programs that perform calculations, assign data to stored measures, or flow data from one cube to another.

As a dimensionally aware language, the OLAP DML makes it easy to navigate and refer to data in a dimensional model. For example, the OLAP DML has hundreds of analytic functions and understands how to loop over dimensions and reference dimensional and hierarchical data. As a procedural programming language, it includes the ability to write programs and functions, looping, if/then/else, local variables, and other standard programming constructs.

You do not need to use the OLAP DML to design or query a cube, but it is a powerful tool that is available to add analytic content and to manipulate cubes. The OLAP DML is especially helpful if you are upgrading from Express.

SQL Interface to Multidimensional Data Types The primary method for accessing Oracle OLAP data is through SQL. With Oracle OLAP 11g and beyond, any time you create dimensions or cubes using API calls or using AWM, views are automatically created that present a star schema for your OLAP cubes. You access your data using standard SQL against these views. If you want to bypass these views, you can use the CUBE_TABLE function that was introduced in Oracle OLAP 11 g, which takes advantage of the metadata stored in the Oracle database, as in the following command:


select * from table(cube_table('global.units_cube'));




The syntax for OLAP_TABLE has a great deal of flexibility, and it is documented in the Oracle OLAP DML Reference. OLAP_TABLE is still available in Oracle OLAP, but it is generally simpler to use CUBE_TABLE or the built-in views with Oracle OLAP 11g.

OLAP API The OLAP API is a Java-based programming interface for OLAP applications, used to query OLAP objects. This API is quite extensive. Oracle Business Intelligence Beans (BI Beans) uses the API and provides an easier to use way of accessing the power in the OLAP API. BI Beans is available with Oracle JDeveloper.

A portion of the OLAP API (called the Analytic Workspace Java API in Oracle OLAP 10g) supports the creation and maintenance of analytic workspaces in Java. It provides a programmatic method for defining a logical dimensional data model and instantiating that model in an analytic workspace. AWM uses this API to create and modify analytic workspaces.

System Views with OLAP Metadata In Oracle OLAP 11 g, tables and views with information about analytic workspaces were added to the system catalog, in the SYS schema. These views can be queried to obtain information about the Oracle OLAP data available on your instance. This allows applications to find cube metadata, such as the list of cubes, measures, dimensions, hierarchies, attributes, and their definitions and interrelationships. For example, in the same way that ALL_TABLES provides information about all of the tables, the view ALL_CUBE_DIMENSIONS provides information about all of the cube dimensions.


Client Applications for Managing Oracle OLAP

AWM is the administrative tool used to design and manage Oracle cubes. This application is installed when the Oracle Database 11 g client tools are installed, and it is generally available under the Integrated Management Tools menu choice, but it can be installed separately as well.

Analytic Workspace Manager AWM is used to create and manage analytic workspaces. It enables you to develop a logical dimensional model, map logical objects to data sources, and load and aggregate the data.

Generally, there is a new release of AWM for each new release of Oracle OLAP; AWM is enhanced to present new features of Oracle OLAP with each release. You should always use the version of AWM that matches the version of Oracle OLAP you are using.


Using Analytic Workspace Manager

Figure 2. Using Analytic Workspace Manager

AWM is a Java application and runs anywhere Java runs. Figure 2 shows a typical use for AWM to map the CHANNEL_DIM table to the CHANNEL dimension.

Oracle OLAP Worksheet Oracle OLAP Worksheet, launched from AWM, is the command-line interface to Oracle cubes and dimensions. Using the OLAP DML, OLAP Worksheet can be used to view data; create new functions and programs; define and maintain forecasts; and add dimension calculation models, allocations, and other analytic enhancements to the cube. It serves as the primary mechanism for entering and viewing output for OLAP DML commands. The complete OLAP DML reference is available from OLAP Worksheet's help system.

Figure 3 shows the result of entering some typical OLAP DML commands in the lower portion of the OLAP Worksheet window.

Oracle Warehouse Builder OWB can also be used for generating Oracle OLAP cubes. With OWB, the building of dimensions and cubes is integrated into the entire ETL process, including transforming tables and views for use with Oracle OLAP. It provides a central application for building and managing data warehouses based on relational and multidimensional data.

Currently, OWB 11 g supports building 10g-mode analytic workspaces on Oracle Database 10g and 11 g. The next release of OWB 11 g (Release 2) is expected to support building 11g dimensions and cubes. For the purposes of this book, we will use AWM to build analytic workspaces, dimensions, and cubes in an Oracle Database 11 g database.


OLAP DML commands and result in the Oracle OLAP Worksheet window

Figure 3. OLAP DML commands and result in the Oracle OLAP Worksheet window


OX OX is a freeware tool developed by several Oracle consultants. It is made freely available with no support. It serves as an alternative way to present an OLAP DML command line with some useful features not available in OLAP Worksheet. You can access it from the Oracle OLAP Downloads web page.

Support for MDX Using an MDX provider developed by Simba Technologies, applications can also query Oracle OLAP cubes using MDX. For example, Microsoft Excel can query data directly from cubes using the MDX provider in the same way that Excel connects to Microsoft Analysis Services.

Support for SQL-Based Tools Because Oracle OLAP data is exposed via SQL views, any front end that uses SQL to access data can be used to present data from Oracle OLAP. An example of such a tool is Oracle's own Application Express, as well as many third-party tools.


Oracle Essbase Architecture and Components

As a storage-independent OLAP solution, Essbase can be implemented in an existing IT environment with no modification or disruption to the systems containing your source data. The data for an Essbase database can come from one or more data sources simultaneously. The following sections describe the architecture for a typical Essbase implementation, as well as the core and optional components.


Essbase Architecture

Figure 4 shows a high-level overview of a typical Essbase installation. As you can see, the implementation is broken into three tiers: client, middle, and database. The client tier contains all of the end user and design components. The middle tier is a web services tier that sits between users (end users and administrators) and the Essbase a relational database may contain a metadata catalog and may provide source data for the Essbase databases. Let's start with a high-level look at each of the tiers.

A typical Essbase architecture

Figure 4. A typical Essbase architecture

Client Tier The client tier includes the front-end options for Essbase. Analysts may use either Smart View or the Spreadsheet Add-in to retrieve and analyze data and to create ad hoc reports in Microsoft Excel. Smart View is the next generation of the Spreadsheet Add-in, offering integration with Microsoft Word and PowerPoint, as well as Excel.

Administrators use the consoles for Oracle Essbase Administration Services, Essbase Integration Services, and Essbase Studio to model and manage Essbase databases.

MaxL is a language used by developers to provide command scripts used for scheduled processes. XML for Analysis (XMLA) is a query language. There are several command-line and API options available as well, such as C and Visual Basic. Oracle offers a comprehensive set of query and reporting tools called Oracle Business Intelligence Suite Enterprise Edition Plus (OBIEE Plus). In addition, several Oracle partners have created custom applications integrated with Essbase.

Middle Tier In the middle tier, three services form a layer between the data sources and the Essbase Server. Whether these three services are installed on individual servers is a matter of design. They are able to exist on a single server and have been configured as such for smaller implementations.

Smart View requires a middle-tier component called Oracle Hyperion Provider Services. When a user issues a request using Smart View, it flows through Provider Services.

The Administration Services Server is used to manage users and user security, server options, applications, databases, and database objects. When building a cube from a data source, the Administration Services Server supports reading from a single relational table or a flat file.

The third possibility in a typical installation can be either Essbase Studio or Integration Services. Neither of these services is required. Rather, both tools support cube building from a data warehouse with a star or snowflake schema. Essbase Studio, however, is the next generation of Integration Services and is the tool of choice for a new Essbase implementation. Essbase Studio can also be used instead of Administration Services to build cubes from a single relational table or a flat file, with the added benefit that it generates all the data-load rules automatically.

Database Tier The database tier is where you find the Essbase Servers. These servers are responsible for storing, calculating, and serving data to the various components as needed. In many respects, the Essbase Server is the heart of the implementation. Historically, it brokered all of the transactions and was responsible for the tasks now available through the middle tier.


Types of communication protocols and query methods used with Essbase

Figure 5. Types of communication protocols and query methods used with Essbase


Relational Database Sources Below the middle tier are two relational sources: the Metadata Catalog and the RDBMS source. The Metadata Catalog is required by Administration Services, Integration Services, and Essbase Studio. The RDBMS source represents the original data sources that provide data to the Essbase databases in the Essbase Server. Essbase does not require data be placed in a relational structure before loading. In fact, data can also come from legacy systems, enterprise resource planning (ERP) systems, data warehouses, and flat files.

We have reviewed a typical architecture and some additional front-end components. Note that few components are required to simply build an Essbase database, load data, and create reports. A little later in this blog, we will discuss these core components. For now, we turn our attention to how the components in a typical architecture talk to one another.

Communication Protocols and Query Methods We omitted the communication protocols and query methods from the preceding discussion to be able to focus on the tiers. Figure 5 identifies the protocols and query methods.

Generally speaking, if a client-tier component needs the ability to communicate through a firewall it uses HTTP(s); otherwise, it uses TCP/IP. The query method varies by component. Table 1 summarizes the client-tier components


Client-Tier Communication Protocols and Query Methods

table 1. Client-Tier Communication Protocols and Query Methods


All the middle-tier components use TCP/IP to connect to the Essbase Server. The query method varies by component. Table 2 summarizes the middle-tier components.

In the database tier, the Essbase server uses TCP/IP to communicate with other components. The query method varies by component. The Essbase Server uses the protocols and components listed previously. The ETL components, such as the Oracle Data Integrator Enterprise Edition, use TCP/IP to the Essbase Server and query through the API to Essbase via prebuilt adapters.

 Middle-Tier Communication Protocols and Query Methods

table 2. Middle-Tier Communication Protocols and Query Methods


As you can see from Figure 5, a typical implementation connects an Essbase Server to your data sources and to front-end applications. Your implementation will depend on what you currently have in place and the Essbase capabilities you want to use. To help you in your assessment, we break out the core components of an Essbase installation and explain what they do. Then we discuss the other components and products that are available for Essbase under four categories: administrator and developer tools, data-integration tools, end-user applications, and additional Oracle components.


Core Components

Essbase has five core components: the Essbase Server, Administration Services, Spreadsheet Add-in, Smart View (Fusion Edition), and Provider Services. With only these components, you can create an Essbase database, connect to data sources, populate the database with data, retrieve and analyze data, and create ad hoc reports. In this case, the only reporting options are Smart View or Spreadsheet Add­in. All data loads are done through the Data Prep Editor inside Administration Services, and security is handled internally on the Essbase Server. Figure 6 shows how the core components relate to one another.

In this blog section, we touch briefly on each of the core components.


Oracle Essbase core components

Figure 6. Oracle Essbase core components


The Essbase Server The Essbase Server is at the heart of an Essbase implementation. It is a powerful multidimensional database that offers two kinds of storage options (block and aggregate), an extendable library of functions to define business logic, and a powerful calculation engine. As suggested by Figure 6, an Essbase Server can host multiple Essbase databases. Moreover, while only one Essbase Server is pictured in the figure, it is possible to run multiple instances of Essbase Servers at the same time. Essbase Servers are managed using Administration Services.

Oracle Essbase Administration Services (Server and Console) Administration Services is the gateway to your Essbase server. Database and system administrators use Administration Services to manage users and user security, server options, applications, databases, and database objects. It supports cube-building tasks, such as data modeling and loading data, for single relational tables and flat files.



The Administration Services console provides a graphical user interface (GUI) to the Administration Services Server. For example, from the Administration Services Console, a database administrator can create an Essbase database, design a database outline containing dimensions and members, create rules that transform source data to matching fields in the new database, load data from a single relational table or a flat file, and create and run calculations scripts. The Administration Service Server does the actual work.

Oracle Essbase Spreadsheet Add-in Spreadsheet Add-in is a software program that merges seamlessly with Microsoft Excel. With the Spreadsheet Add-in installed, your analysts have menus, toolbars, and keyboard shortcuts for Essbase within Excel. Analysts can connect to an instance of an Essbase server, retrieve data, analyze data, and create ad hoc reports. For analyzing the data, they can pivot the point of view, drill down to show more detail, roll up to show less detail, remove or retain subsets of data, format data, and calculate data. With the appropriate security permissions, they can edit the data and write back to the database.

The next generation of the Spreadsheet Add-in is Smart View, Fusion Edition.

OLAP architecture and components

Oracle Hyperion Smart View for Office, Fusion Edition Smart View is a web- deployed, thin-client program that is embedded in Microsoft Office applications— Excel, Word, PowerPoint, and (if Word is the e-mail editor) Outlook. Within Excel, Smart View provides similar functionality to the Spreadsheet Add-in, but improves upon the Spreadsheet Add-in with an intuitive, customizable user interface.

With Word and PowerPoint, analysts can connect to a database, copy a data point from an Excel spreadsheet to a document or slide, refresh the data, and display the data in charts or graphs. Imagine having documents and slides with data that you can refresh on demand! Analysts can even add content to Outlook e-mail messages if Word is set as the e-mail editor.

Smart View supports not only data coming from Essbase, but also data from OBIEE (and hence, Oracle OLAP) and Oracle Hyperion Planning, and content from Oracle Hyperion Financial Management, Oracle Enterprise Performance Management System, and Hyperion Financial Reporting and Hyperion Web Analysis. This means that you can collate information from multiple sources in one report.

Smart View is part of Oracle Hyperion Foundation Services, which ships with Oracle Essbase. Smart View requires Provider Services.

Oracle Hyperion Provider Services Provider Services is a set of services that connects end-user applications to data sources. There are three services: the Smart View provider, a Java API provider, and an XMLA API provider. Provider Services also enables high availability, clustering, load balancing, and failover services for Essbase Servers, bringing enterprise-level performance and reliability to Essbase and performance management applications. Figure 7 shows which services are used by the various applications and data sources.


Provider Services connects end-user applications to data sources

Figure 7. Provider Services connects end-user applications to data sources.






Additional Administrator and Developer Components and Tools

The components and tools described in this section ship with Oracle Essbase. They include Essbase Studio, Integration Services, and command and query languages.

Oracle Essbase Studio (Server and Console) Oracle Essbase Studio combines the cube-building functionality previously provided in Integration Services and Administration Services into a single environment. Essbase Studio supports modeling for all the different types of data sources from which Essbase applications are typically built. The Essbase Studio Console provides an easy way to perform tasks related to data modeling, cube design, and construction of analytic applications. A command­line language is also available.

Essbase Studio supports MOLAP, HOLAP, and XOLAP architectures. For the hybrid architectures, Essbase Studio supports several drill-through options: relational databases, OBIEE Plus, URLs, custom SQL, Oracle Hyperion Financial Data Quality Management, and Java methods. Drill-through functionality is supported from data cells and member cells and is dynamically linked to cubes with matching metadata context. Figure 8 shows the drill-through functionality.


Essbase Studio enables drill-through from data cells through Essbase to data sources

Figure 8. Essbase Studio enables drill-through from data cells through Essbase to data sources.


A common metadata repository, or catalog, captures all metadata related to all Essbase applications built in the enterprise and allows the reuse of metadata at the lowest level of granularity. The catalog gives Essbase Studio knowledge of the common metadata that is shared across the various applications enterprise-wide. For example, you can model a customer dimension once, and then leverage it across a series of Essbase databases. Essbase Studio also supports lineage tracking through a rich graphical view of the metadata relationships, allowing users to follow application lineages to their metadata components and through to the data sources from which they were sourced.

How does Oracle Essbase Studio compare with Integration Services? Essbase Studio is the next generation of Integration Services, incorporating all the functionality of Integration Services plus the cube-building functionality from Administration Services, and adding a wizard-driven interface. It provides the ability to connect to data sources via JDBC or ODBC, rather than just ODBC. Essbase Studio automates some work that used to be done manually, including automatically creating joins between tables and autogenerating data-load rules. In Essbase Studio, database administrators create minischemas instead of OLAP models. Essbase Studio provides greater flexibility in designing and reusing hierarchies across metaoutlines, because the hierarchies have been separated from the metaoutline. Finally, Essbase Studio separates the grouping of hierarchies and their relationships from the metaoutline by organizing metadata elements, such as measures and hierarchies into a cube schema. For more information, see the New Features guide for Oracle Essbase Studio 11.1.1.

Essbase Studio is a component of Oracle Essbase. Essbase Studio requires Hyperion Shared Services for user management and security.

Oracle Essbase Integration Services (Server and Console) Oracle Essbase Integration Services is the predecessor to Essbase Studio. For new Essbase implementations, you should choose Essbase Studio. If you have an existing Essbase implementation, your organization may be already using Integration Services. This section is for you.

Integration Services offers a set of scalable data-integration services and graphical tools to create multidimensional Essbase databases from SQL-based relational sources and data warehouses. Its services enable drill-through access from summary-level data in an Essbase database to detailed data in relational databases and/or data warehouses (HOLAP architecture) via ODBC connections. Built on a flexible, multitier architecture, Integration Services easily scales to accommodate changing requirements, and delivers scalability, performance, and reliability.

The Integration Services Console is a GUI in which database administrators can create a logical OLAP analysis model from the tables, views, and columns of relational databases and/or data warehouses. They can use the OLAP model to create a metaoutline containing the structure and rules required to generate an Essbase database outline, and then use the metaoutline to create and populate an Essbase multidimensional database.

Integration Services Server does the actual work of extracting data from the data sources, performing the operations specified in the associated metaoutline, and loading the data into an Essbase database. It uses the information in an OLAP Metadata Catalog to extract dimensions and members. This catalog is a relational schema containing: metadata describing the nature, source, location, and type of data to retrieve; metadata describing information required to generate Essbase outlines; and OLAP models and metaoutlines. You can create multiple OLAP Metadata Catalogs to store models and metaoutlines.

Integration Services is a component of Oracle Essbase.

Essbase Command and Query Languages MDX is a query language specification for OLAP data sources. MDX is the most commonly used multidimensional expression language today. The Essbase version of MDX includes an ever-growing list of functions developed specifically for Essbase. Because of this, MDX for Essbase is also called MaxL MDX and is technically a subset of MaxL. However, MaxL MDX and MaxL Data Definition Language (DDL) have little in common syntactically. MaxL MDX is used in Essbase to query both block and aggregate storage databases, and for outline member formulas in aggregate storage outlines.

Data definition means structural control of a database system, including operations like creation, deletion, and updating of users, applications, databases, and database objects. Statements in MaxL DDL include verbs like CREATE, ALTER, DROP, GRANT, and DISPLAY. Comments begin with /* and end with */.

MaxL is used to create repeatable processes. For instance, suppose that each month, you need to load the most current data. The following is an example MaxL script from the Essbase Bootcamp course:

/* CREATE PROCESS LOG AND LOGIN TO SERVER */ spool on to "$arborpath\\salesrisk_log.txt"; login $1 $2 on 'localhost';
/* Activate Bigcorp Sales database */
alter system load application 'Bigcorp';
alter application 'Bigcorp' load database 'Sales';
/* Load data files	*/
import database 'Bigcorp'.'Sales' data from server text data_file 'SalesRisk.txt'
using server rules_file 'SlsRisk' on error write to acterr;
spool off;

For queries, Essbase supports MaxL MDX through the C API, Java API, MaxL interface, and XMLA API.

In many ways, MaxL MDX is comparable to Essbase report scripts. MaxL MDX and report scripts are both capable of performing similar member selection and calculation functions, but report scripts also include a set of report-formatting options to control how results are represented. On the other hand, the focus of a MaxL MDX query is analytical data retrieval, with the underlying API handling the resulting data structures.

SELECT {Jan, Feb, Mar} ON ROWS,
{[Net Sales], [Cost of Sales]} ON COLUMNS 
FROM [HyptekAS].[HyptekAS]
WHERE ([North America], FY06)

Additionally, MDX is the core calculation functionality for Essbase aggregate storage applications.

Closely associated with MDX is XMLA, the most recent attempt at a standardized API in the OLAP and BI space. XMLA is a standard that allows client applications to talk to multidimensional or OLAP data sources. The communication of messages back and forth is done using the HTTP, SOAP, and XML web standards. The query language used is MDX. XMLA has already gained broad support, including from companies like Oracle, Microsoft, SAP, and SAS.

ESSCMD is a legacy command-line interface that performs operations interactively or through a batch or script file. Developers should avoid ESSCMD, as MaxL was introduced as a replacement with the release of Essbase 6.5.


Data-Integration Tools

If your data source is a data warehouse or transactional system, you may want to include a data-integration tool in your Essbase implementation. Data-integration tools fall into three categories: ETL; extract, load, and transform (ELT); and application-based solutions.

Oracle provides many options in the realm of data-integration tools, depending on the source and usage requirements. In this section, we introduce tools for data integration. These tools are available separately; they do not ship with Oracle Essbase. For more information, see the Oracle web site.

Oracle Data Integrator Enterprise Edition Oracle Data Integrator Enterprise Edition (ODI) delivers unique next-generation ELT technology that improves performance and reduces data-integration costs, even across heterogeneous systems. Unlike conventional ETL tools, ODI offers the productivity of a declarative design approach, as well as the benefits of an active integration platform for seamless batch and real­time integration.

In addition, hot-pluggable Knowledge Modules provide modularity, flexibility, and extensibility. Oracle provides Essbase-aware Knowledge Modules, allowing developers to create an end-to-end integration from transaction systems to an enterprise data warehouse to Essbase. ODI also supports the rest of the Oracle Enterprise Performance Management System.

For many organizations, a standards-based approach is important, if not critical. To that end, ODI may be used as an approach to Essbase integration. With ODI, you can import both data values and dimensional information.

Hyperion Data Integration Management Adapters If your organization has adopted Informatica PowerCenter, a third-party ETL tool, as a standard, rather than ODI, Hyperion Data Integration Management (DIM) may be appropriate. Simply stated, DIM 

is a collection of adapters that Oracle created to support the use of Informatica PowerCenter with Essbase and the rest of the Oracle Enterprise Performance Management System.

Like ODI, DIM allows you to import both data values and dimensional information.

Oracle Hyperion Financial Data Quality Management, Fusion Edition Another form of data integration is provided via Oracle Hyperion Financial Data Quality Management (FDM). Its data-preparation server can ease integrating and validating financial data from any of your source systems. Like ODI and DIM, FDM includes prepackaged adapters for Essbase and the rest of the Oracle Enterprise Performance Management System.

Unlike ODI and DIM, which are more IT-centric, FDM is a packaged solution for business users that helps develop standardized financial data management processes using a web-based guided workflow user interface. FDM does not provide dimension integration. Rather, with FDM, users validate, map and move data from source systems to Essbase or the target application.


Other Oracle Components for use with Essbase

In this section, we introduce you to other components that can be used with Essbase: Oracle Hyperion Data Relationship Management, Shared Services, Smart Search, and other Oracle Enterprise Performance Management System applications.

Oracle Hyperion Data Relationship Management, Fusion Edition Generally speaking, the dimensional information used by products such as Essbase might be referred to as master data. Oracle Hyperion Data Relationship Management, previously known as Hyperion Master Data Management, provides organizations with a solution to build consistency within master data assets despite endless changes within the underlying transactional and analytical systems. Specifically, Data Relationship Management provides a master data management solution built to enable dimension management.

In essence, Data Relationship Management is an application managed by IT and used by business users to do the following:

  • Create an enterprise view of analytical dimensions, reporting structures, performance measures, and their related attributes and hierarchies.
  • Construct departmental perspectives that bear referential integrity and consistency.
  • Provide validations and business rules that enforce enterprise governance policies.
  • Synchronize master data with downstream processes.

Often, Data Relationship Management is implemented alongside FDM, as they complement each other; the latter specializes in data values, and the former provides dimension management.

Hyperion Shared Services Shared Services provides a single sign-on (SSO) service, not only for Essbase and its components, but for all Oracle Enterprise Performance Management System products. SSO promotes an uninterrupted workflow when moving between Essbase components and/or Oracle Enterprise Performance Management System products. Shared Services can be linked with your existing corporate repository—for example, a Lightweight Directory Access Protocol (LDAP) server to authenticate users. Administrators can create user roles and groups to define which products end users can use and the level of access they have to the product. Together, user authentication and role-based authorization provide a secure environment.

Shared Services is part of Oracle Hyperion Foundation Services, which ships with Oracle Essbase. It is required for managing users in Essbase Studio.

Oracle Hyperion Smart Search Smart Search was originally designed for use with Google OneBox for Enterprise Google Search Appliance. The thought was to allow a user to search the company's internal data using business terms. As such, the OneBox appliance first creates an index of all dimension information for all deployed Essbase databases. Then people can use their browser to query the dimensions via Google. For example, you could type in "fourth-quarter cost of goods sold" and Smart Search would find related items. The found items are presented much like any other Google search results—as a list of content sorted by relevance. If you click a link, the associated data is presented in Excel using Smart View.

Smart Search now supports Oracle Secure Enterprise Search as well as Google OneBox. Smart Search is a component of Oracle Essbase.

Oracle Enterprise Performance Management System Applications Two of the applications in Oracle Enterprise Performance Management System use Essbase as a database and calculation engine: Planning and Oracle Hyperion Profitability and Cost Management. If you have either of these products installed, then you have the core components of Essbase already installed.


End-user Tools

End users are the consumers of OLAP data. End users include people at all levels in an organization and in many departments. The needs of end users will differ depending on roles and responsibilities. A financial analyst needs more investigative capabilities than say a CEO, who is likely more interested in seeing results presented in a dashboard report. OBIEE and OBIEE Plus provide the tools you need to satisfy the diverse reporting needs of your organization. In addition, many third-party vendors offer tools that are compatible with Essbase or Oracle OLAP. Alternatively, you can use Java to create custom end-user applications.


Oracle Business Intelligence Suite Enterprise Edition Plus

Oracle OLAP and Oracle Essbase both support two key components of OBIEE: BI

Interactive Dashboards and BI Answers, which provide a key pairing for delivering a world-class dashboard solution. BI Interactive Dashboards provides a personalized, role-based, 100 percent thin client web browser interface for important trends and key performance indicators, including gauges, charts, summary reports, and even condition-based guided analytics. BI Answers is a 100 percent thin client ad hoc reporting and analysis solution that is fully integrated with BI Interactive Dashboards and BI Publisher. End users can quickly create their own reports and then drill through, analyze, visualize, and embed the results in their own personalized dashboard.

Because BI Interactive Dashboards and BI Answers sit directly on Oracle BI Server, they provide two unique capabilities:

  • You can create federated queries that cross the bounds of relational and multidimensional data.
  • You can provide a single definition for a metric (such as profit) and reuse that consistent definition across all your reports.

OBIEE Plus extends OBIEE by bundling Hyperion analysis and reporting applications for use with Essbase: Web Analysis, Financial Reporting, Interactive Reporting, and SQR Production Reporting. Two of these products—Web Analysis and Financial Reporting—are particularly useful for sharing Essbase data throughout


Oracle Business Intelligence Suite Enterprise Edition Plus applications

Figure 9. Oracle Business Intelligence Suite Enterprise Edition Plus applications


the organization. Figure 9 shows how these front-end applications fit into an Essbase implementation. Note that the BI Server pictured here could just as easily be hooked up to an Oracle database to retrieve Oracle OLAP data.

Web Analysis is a powerful, intuitive, web-based interface that delivers interactive analytics to everyone in the enterprise. It is a purpose-built interface for ad hoc analysis and executive reporting systems. Web Analysis provides a broad range of flexible, easy-to-use display types, including grids, charts, pin boards, traffic lighting, and personalization. Users have complete control over layouts, formatting, fonts, and colors, as well as a flexible array of output options enabling wide distribution via print, HTML web pages, PDF, and online viewing. Web Analysis facilitates access to multidimensional data sources as well as Oracle Enterprise Performance Management System applications. It supports drill-through to relational sources, including drill-through to URL.

Financial Reporting, Fusion Edition, is the application-intelligent structured reporting solution for Essbase applications. It is a powerful tool that lets you graphically design and present your analytic data. You can use the Financial Reporting Windows client interface to design traditional financial reports (such as cash management reports and balance sheets) and nontraditional reports. Finished reports can be routed to a printer and viewed on the Web through a browser.

You can also use Hyperion Interactive Reporting and Hyperion SQR Production Reporting.


Third-Party Applications

Oracle partners and other vendors offer their own front ends for Essbase and Oracle OLAP. For example, for Essbase, Applied OLAP offers an enhanced spreadsheet- style interface called Dodeca, which integrates Oracle applications and non-Oracle applications on the desktop. For Oracle OLAP, Collaborative Consulting, Inc. has developed the ClearView add-in for Microsoft Excel that enables users to view and write back to Oracle OLAP cubes. It has sophisticated "spreading tools" for writing back to Oracle OLAP cubes and also tracks changes.



OLAP design requires a foundation in basic OLAP concepts and general OLAP design principles, as well as an understanding of the architecture, features, functionality, and restrictions of the selected OLAP product. Designers need to give more importance to end-user requirements than to the structure of the data source. OLAP products from Oracle offer effective ways to restructure and enhance data to meet user requirements, such as adding aggregate and calculated data or combining data from multiple sources.

Both Oracle OLAP and Oracle Essbase have architectures that fit easily into existing infrastructures. Oracle OLAP is an option to the Oracle Database, while Essbase is an independent OLAP database that can augment existing systems. The products are full featured and offer next-generation administration tools to making building and maintaining cubes as easy as possible.

The intention is to provide an overview of the build processes, rather than building a working Oracle OLAP analytical workspace or Essbase database. The procedures and sample data also allow us to introduce the user interfaces of some of the build/ administration tools within a logical flow.


  1. Alexander, Lisa. Hyperion System 9 BI+ Essbase Analytics Bootcamp Student Guide.
  2. Hyperion Solutions Corporation, 2007.
  3. Collins, Dave. "Hyperion 7.x New Features." Presentation. Hyperion Solutions, 2004.
  4. EPM Information Development Team. Oracle Essbase Database Administrator's Guide,
  5. Release 11.1.1. Oracle Corporation, 2008.
  6. EPM Information Development Team. Oracle Essbase Studio New Features, Release
  7. 11.1.1. Oracle Corporation, 2008.
  8. EPM Information Development Team. Oracle Hyperion Provider Services Administration
  9. Guide, Release 11.1.1. Oracle Corporation, 2008.
  10. Nader, Michael and Dave Collins. Dare to OLAP. Oracle Corporation, 2008.
  11. Oracle Corporation. Oracle OLAP User's Guide 11g Release 1 (11.1). Oracle Corporation, 2008.

Вас заинтересует / Intresting for you:

User Discovery in the process ...
User Discovery in the process ... 1474 views Akmaral Sat, 14 Jul 2018, 08:36:53
Desirable Functionality in Web...
Desirable Functionality in Web... 1817 views Akmaral Sat, 14 Jul 2018, 07:24:26
Types of OLAP Reports
Types of OLAP Reports 2198 views Akmaral Sat, 14 Jul 2018, 06:18:47
Oracle Engineered Systems for ...
Oracle Engineered Systems for ... 2075 views Александров Попков Fri, 15 Jun 2018, 14:05:15
Comments (0)
There are no comments posted here yet
Leave your comments
Posting as Guest
Suggested Locations