| Architect: Business Intelligence
Integrating Oracle's Hyperion Essbase - System 9 with Oracle Business Intelligence
By Mark Rittman
A step-by-step guide to using Oracle's Hyperion Essbase – System 9 as a back end for Oracle Business Intelligence Suite Enterprise Edition
Published May 2008
When you put together a business intelligence architecture, one of the main requirements you will want to address is being able to provide fast and flexible data analysis. Your relational data warehouse will hold your detail-level transactions, and your users will typically want to summarize this data, add their own calculations, and analyze it with an ad hoc query tool.
You probably know that Oracle Business Intelligence Suite Enterprise Edition provides connectivity to many different relational database platforms and leverages the scalability of relational databases such as Oracle Database 11g to provide fast, integrated access to your detail-level data warehouse. For users with more-complex analytical requirements, though, Oracle Business Intelligence Suite Enterprise Edition can also connect to multi-dimensional data sources such as Oracle’s Hyperion Essbase - System 9, in order to provide access to advanced calculations, forecasts, allocations, cross-dimensional queries, and aggregated data. Like Oracle Business Intelligence Suite Enterprise Edition, Oracle Hyperion Essbase is standards-based, hot-pluggable, and part of the Oracle Fusion Middleware family of products.
This article provides an introduction to Hyperion Essbase, describing its architecture and explaining how it fits in with Oracle’s middleware and business intelligence technology stacks. Later on, you will see how easy it is to build a Hyperion Essbase multidimensional database and then connect it to Oracle Business Intelligence Suite Enterprise Edition, taking data from the Sales History sample schema that comes with most recent versions of Oracle Database.
A Brief Introduction to Hyperion Essbase
Hyperion Essbase is a multidimensional database server. and can be thought of as an “analytic server” that complements Oracle Business Intelligence Suite Enterprise Edition and powers several Hyperion planning and performance management tools.
Named by Information Age magazine—alongside the BlackBerry, Google, Netscape, and Linux—as one of the 10 most influential technology innovations of the past 10 years, Hyperion Essbase is an Online Analytical Processing (OLAP) server that takes data extracts from a range of datasources; calculates and then aggregates this data; and then provides fast access to the results, using a dimensional model.
Hyperion Essbase supports extraction from a wide range of datasources, including Oracle Database 11g and other relational databases, files, and spreadsheets, and provides query access through several APIs and languages, including XML/A and MDX. Like Oracle OLAP, Hyperion Essbase stores its data in a specially optimized multidimensional data store, but unlike Oracle OLAP, stores it outside of a relational database engine, usually on its own dedicated server. With the latest release of Oracle OLAP, organizations typically use it for adding analytics and advanced calculation capabilities to their Oracle data warehouse, whereas Hyperion Essbase is typically used in heterogeneous environments that need to support a range of database, business intelligence, and planning and forecasting tools.
Hyperion Essbase Architecture
Hyperion Essbase is a three-tier application that complements Oracle Business Intelligence Suite Enterprise Edition.
Hyperion Essbase stores its data in special multidimensional databases that are optimized for fast access to aggregated data. Depending on the type of data being stored, Hyperion Essbase can create either “block storage” or “aggregate storage” databases, the former designed for smaller, denser data sets requiring read/write access and the latter designed for sparse, sales-analysis-style applications with lots of dimensions and read-only access. Data can be loaded into Hyperion Essbase databases by use of file imports or direct SQL extracts or through utilities that are provided alongside the Hyperion Essbase database server.
The most important single bit of information in an Hyperion Essbase database is the “outline,” which describes the dimensional and hierarchical structure of the database and details the dimensions, dimension members, hierarchies, measures, derived measures, and key performance indicators it contains.
You can either create a new database and its outline using the command line, or you can use tools such as Hyperion Essbase Administration Services and Hyperion Essbase Integration Services to create your database graphically and define its structure. Using these tools, you can either set up the database outline manually or derive this metadata from the data in a relational star schema.
Once your Hyperion Essbase database is defined and loaded, users of Oracle Business Intelligence Suite Enterprise Edition can import the metadata for their Hyperion Essbase database into their semantic model and report on it, along with their relational data, using Oracle Business Intelligence Answers, Oracle Business Intelligence Interactive Dashboards, and Oracle Business Intelligence Publisher. Oracle Business Intelligence Suite Enterprise Edition uses another Hyperion Essbase utility, Hyperion Essbase Provider Services, to provide native connectivity to Hyperion Essbase through the XML/A API and automatically converts the multidimensional data structures in your Hyperion Essbase database into the relational, dimensional data model that Oracle Business Intelligence Suite Enterprise Edition expects.
So now that you understand the capabilities and architecture of Hyperion Essbase, how do you go about creating an Hyperion Essbase database and plug it in to Oracle Business Intelligence Suite Enterprise Edition? This article shows you how, using data taken from the Sales Analysis sample schema included in Oracle 9 i, Oracle Database 10 g, and Oracle Database 11 g.
Example 1 : Creating and Populating an Hyperion Essbase Database
In this first example, you will create and populate an Hyperion Essbase database, using data taken from the SH sample schema. To create the Hyperion Essbase database, you will need to have installed and configured the following products:
In addition, if you want to follow the second example and analyze your Hyperion Essbase database by using Oracle Business Intelligence Suite Enterprise Edition, you will need to install and configure the following additional products.
You can download all of these products from Oracle’s E-Delivery Web site (http://edelivery.oracle.com).
The examples in this article were written with Microsoft Windows XP as both the server and client platform. Although you will need to use Microsoft Windows to run the client tools used in the examples, the server elements will run on any supported Hyperion Essbase platform.
Finally, you will need to create an ODBC datasource that connects to the database containing the SH sample schema. Once you have completed these steps, you are ready to proceed.
Introducing Hyperion Essbase Integration Services
In this example, you will create your first Hyperion Essbase database by using Hyperion Essbase Administration Services.
With Hyperion Essbase Integration Services, you can quickly and easily load data into an Hyperion Essbase database and also create the database and derive the database outline at the same time, based on the hierarchies found in your relational source data. For more-complex data and scenarios, you can use a tool such as Oracle Data Integrator to transform your source data into a star schema, or you can manually load data into your Hyperion Essbase database dimensions and measures, using Hyperion Essbase Administration Services and direct SQL data requests.
Hyperion Essbase Integration Services has client and server elements and stores metadata about the relational datasources (referred to as the OLAP model) and the database outline you derive from it (known as the OLAP metaoutline) in a repository, which you can install into an Oracle Database instance. To connect to the Essbase Integration Services Console, provide the username and password for the Hyperion Essbase Integration Services repository and the username and password for an Hyperion Essbase server.
After you log in, the Essbase Integration Services Console presents you with a wizard that enables you to create new OLAP models and metaoutlines, open existing ones, or display ones you have recently edited or created.
As previously mentioned, creating a new Hyperion Essbase database by using Hyperion Essbase Integration Services involves two distinct phases:
To start creating your OLAP model, click the OLAP model icon and the Open button. You will then be presented with a dialog box in which you can select the datasource that connects to your source data. Using the Data Source drop-down list, select the datasource that points to the SH sample schema (if you haven’t already created this, go back and create it now) and enter the username and password to connect to it.
Note that if this is the first time you have accessed the SH schema, you may need to unlock the account or change the password; to do this, log in to your Oracle Database instance as a user with the DBA role and issue the following commands to unlock the account and set the password to “password”:
Once you have successfully connected, the Essbase Integration Services Console will appear, ready for you to define your OLAP model. If you have not already done so, you can click Intelligent Help on the application toolbar to display contextual guidance on preparing your data.
Creating your OLAP model involves three steps:
Hyperion Essbase databases are “hypercubes,” which means that every measure in the database is dimensioned by all of the dimensions. An Hyperion Essbase database translates easily into a relational star schema, where a single fact table joins to one or more dimension tables and the OLAP model you are now creating is a representation of this star schema, together with the hierarchies in the star schema dimension tables.
The first step in defining the OLAP model is to pick the fact table, which you can do by either selecting Tools -> Create Fact Table from the application menu or locating the SH.SALES table in the list of database objects on the left-hand side of the Essbase Integration Services Console and dragging it onto the panel on the right-hand side. When selecting the fact table, select No when you are asked whether you would like to create a Time dimension (you will do this manually in a moment) and select Yes when you are asked whether you would like to create an Accounts dimension.
When you have selected your fact table and answered the above questions, the Essbase Integration Services Console should look like it does in the following screen shot:
The Accounts dimension is a type of dimension that details the measures within your database, and you will return to it later. For now, though, your next step is to define the dimensions in your OLAP model. To do this, either drag and drop the SH.CHANNELS, SH.CUSTOMERS, SH.TIMES, SH.PROMOTIONS, and SH.PRODUCTS tables onto the OLAP model diagram or have the Essbase Integration Services Console select these tables for you automatically (based on their foreign key joins to the fact table), by selecting Tools -> Create Dimension from the application menu.
If you choose the latter option and have the Essbase Integration Services Console add the dimension tables for you, you will be prompted after a few seconds to accept the autodetected dimensions, as shown in the screen shot below:
Click OK to accept these dimension tables, and review the updated OLAP model, which should show your dimension tables surrounding, and joined to, your fact table, as in the screen shot below:
Note that the CUSTOMERS table appears to have been added twice to the OLAP model; one of these tables is, in fact, the COUNTRIES table, which joins to the CUSTOMERS table and contains details on the countries and regions in which customers are located.
Now that you have added the dimension tables to your OLAP model, your next step is to define hierarchies for each of the dimensions. To do this, double-click the PRODUCTS dimension table, select the Hierarchies tab, and then click the Add button to start creating the product dimension hierarchy.
Using the list of columns on the left-hand side of the dialog box, select the following columns in the order specified:
The Hierarchy dialog box should then look like the following screen shot:
Creating your hierarchy in this way tells Hyperion Essbase Integration Services that in your OLAP model, the PROD_NAME column is aggregated into the PROD_CATEGORY_DESC column, which is, in turn, aggregated into the PROD_TOTAL column.
In our sample data, the PROD_NAME column contains some characters (double quotation marks) that are not considered valid Hyperion Essbase dimension member names. To fix this problem, select the PROD_NAME column and click the Transform button.
You now use the Transform function to replace any occurrences of double quotation marks in the product name with the value Inch. To do this, in the Transform dialog box, select the Replace tab and use the Search for and Replace All Occurrences fields in the dialog box to replace all occurrences of double quotation marks.
Click the Add to List button to add this step to the build instructions.
An alternative to using product names and customer names as your dimension member names is to use their IDs instead and place the names into dimension member aliases. Aliases do not have the same length and character restrictions as dimension member names and can be defined when you create the OLAP metaoutline. See the Hyperion Essbase documentation for more details.
Now create hierarchies for the TIMES, CHANNELS, PROMOTIONS, and CUSTOMER dimension tables, creating your hierarchies as follows:
Finally, double-click the TIMES dimension table and change the dimension type to Time, as shown in the screen shot below. Creating a Time dimension within your database gives you the ability to perform time series calculations, and although you will not use this feature in this example, it is good practice to define a Time dimension in case you will need these features in the future.
Use the File -> Verify option in the application menu to check the validity of your model, and if it is valid, select File -> Save and save your OLAP model.
Deriving the Metaoutline
Now that your OLAP model is complete, you can use it to derive the OLAP metaoutline.
To do this, close the OLAP model if it is still open and select File -> New from the Hyperion Essbase Integration Services application menu. You will then be presented, as before, with the Hyperion Essbase Integration Services welcome menu; this time click the OLAP metaoutline icon. From the drop-down list, choose the OLAP model you have just created, and click the Open button, as shown in the following screen shot:
When prompted, select the ODBC datasource that connects to the SH schema, enter the connection details, and click OK to proceed.
When the Essbase Integration Services Console opens this time, it will be configured to enable you to create an OLAP metaoutline. If the Intelligent Help feature is not displayed, switch it on, using the application toolbar. The Essbase Integration Services Console should then look like the following screen shot:
You will now use the Intelligent Help feature to help you create the metaoutline.
Locate the Dimension and Members button in the Intelligent Help panel, and click it. In the list of Dimension and Members help steps that then appears, locate Create Standard Dimensions Automatically and click it. Then, when the contextual help text is displayed, locate the autodetect link and click it, which starts the process of automatically deriving metaoutline dimensions from your OLAP model.
The Essbase Integration Services Console then presents you with a list of all the hierarchies in your OLAP model. Click the Select All button, to select all of the hierarchies, and then the Create button, to create the metaoutline dimensions.
The new metaoutline, displayed in the bottom right-hand side of the Essbase Integration Services Console, should look like the following screen shot:
Now that you have derived the standard dimensions, your final step is to create the Accounts dimension in your metaoutline, which does not get created for you in the previous step.
To do this, drag and drop the Accounts dimension entry from the left-hand pane and drop it onto the metaoutline details, just below the last dimension in the list, and then drag and drop the AMOUNT_SOLD and QUANTITY_SOLD columns from the fact table onto this new dimension.
Use File -> Verify to check that the metaoutline is valid (you should receive the message “The metaoutline is valid for aggregate storage”) and then File -> Save to save the metaoutline to the Hyperion Essbase Integration Services repository.
Finally, you can set the storage mode for your Hyperion Essbase database. To do this, right-click the SH Metaoutline object on the right-hand panel of the Essbase Integration Services Console and select Properties. Using the Properties dialog box, select the Data Storage Model tab and select Block Storage as the storage type, as shown in the screen shot below.
Now that you have both an OLAP model and an OLAP metaoutline and have chosen your storage model, you can use them to define and then load your Hyperion Essbase database. To start this process, select Outline -> Member and Data Load from the application menu to bring up the “Member and Data Load” dialog box.
Hyperion Essbase Integration Services can create your Hyperion Essbase application and database for you if it is not already present: Type in Sales as the application name and Sales as the database name in the Essbase Name section of the dialog box. Next, in the Calc Scripts section, select Use Default Calc Script, which ensures that Hyperion Essbase Integration Services not only loads the detail-level data into your Hyperion Essbase database but also aggregates the data within it so that values are present at all levels of the cube.
Click Next and then Finish to start building the database immediately. Follow the progress of the database build in the “Member and Data Load” progress window, as shown in the following screen shot:
Check that no records were rejected during the load and that 57,906 dimension members are loaded, along with 916,782 fact table records. If you notice that records were rejected, it is probably because you didn’t use the Transform function in the Hierarchy editor to remove double quotation marks from the PROD_NAME column or because you selected different or additional table columns in your hierarchies, which can cause issues for unique member outlines, because column values are not unique across all tables. If problems occur, go back and fix them and run the “Member and Data Load” task again from the application menu.
When you have successfully built your Hyperion Essbase database, you can start Essbase Administration Services Console and take a look at the database that Hyperion Essbase Integration Services has just created for you.
To view the outline for your Hyperion Essbase database, locate the Sales database and double-click the Outline entry. You can now view the database outline as well as dimension members arranged into the hierarchies you defined by using Hyperion Essbase Integration Services.
You can also use the Essbase Administration Services Console to preview the data in your database. To do this, right-click your database and select Preview Data. Double-click the dimension headings to explore the data in your database.
If you find that some or all of the data in your database is missing, check that you did not run out of disk space when performing the calculation step in preparing your database—if Hyperion Essbase Integration Services runs out of disk space when performing the database calculation, it will stop at that point and leave some or all of the database uncalculated but otherwise not report an error. If this happens to you, free up the required space (the database, fully calculated, takes up around 1.8GB of disk space) or, alternatively, remove some of the rows from the SH.SALES table and reload, using a smaller data set.
Example 2 : Integrating Hyperion Essbase and Oracle Business Intelligence Suite Enterprise Edition
If you have installed Oracle Business Intelligence SuiteEnterprise Edition, you may want to include the data in your Hyperion Essbase database in your Common Enterprise Information Model. To do this, ensure that you are using Oracle Business Intelligence Suite Enterprise Edition version 10.1.3.3.2 or higher and that you have a network connection between it and the server that hosts your Hyperion Essbase database.
Before you can use Oracle Business Intelligence Suite Enterprise Edition to report on your Hyperion Essbase database, you will need to add some more descriptive information to your database outline that is required by the database metadata import process. To add this metadata, open the outline for your database to display the list of database dimensions. Right-click the Channel dimension, and select Generations… from the menu.
You will now define names for generations in this dimension, the first generation in the channel dimension being the top, total level and the second generation being channel class and so on. These generation names are then read by the Oracle Business Intelligence Administration tool and used to give names to the corresponding dimension levels created as part of the physical database import process.
Starting with generation level 2, name this generation “All Channels,” then name generation level 3 “Channel Class,” then finally name generation level 4 “Channel.”
Click OK to save these generation names, and then create corresponding generation names for the other dimensions in your model, using the following guidelines:
You are now ready to import your Hyperion Essbase database metadata into Oracle Business Intelligence Suite Enterprise Edition’s metadata layer.
Start Oracle Business Intelligence Administration, and connect to your repository. From the application menu, select File -> Import -> From Multidimensional… Using the “Import from Multi-dimensional” dialog box, enter the connection details for your Hyperion Essbase server.
Once you have successfully connected to your Hyperion Essbase server, you will be presented with a list of available databases. Locate the Sales database, and click Import to start importing the database metadata.
Once the import is finished, click Close to close the dialog box, and then check the Physical area to see your imported database. The dimensions and measures, together with the hierarchies and generation names you just created, have been successfully imported into your common enterprise information model.
Now that you have imported your cube metadata into the semantic model physical layer, you can use the physical model to create the logical model.
To do this, select the folder containing your physical model and then drag and drop it into the logical area of the semantic model. The Administration tool will then automatically create a corresponding logical model, together with logical table joins, dimensions, and hierarchies that represents a relational version of your Hyperion Essbase multidimensional database.
Finally, drag and drop the Sales logical model into your presentation layer to create a corresponding presentation model. You can delete the Gen1 logical columns in each of your presentation tables, because these are related to internal outline structures and should not be queried by end users. Note that you may need to rename your presentation model folder if it clashes with the name of the generated fact table.
Now, once you’ve checked in and saved the changes to your repository, you will be able to use Oracle Business Intelligence Suite Enterprise Edition to query your Hyperion Essbase cube, along with all the other relational and multidimensional datasources in your semantic model.
In this article, you have been introduced to Hyperion Essbase and seen the role it plays in Oracle’s business intelligence foundation and the wider world of Oracle Fusion Middleware. You have created a simple Hyperion Essbase database, using data taken from the Sales History sample schema, and then integrated it into the Oracle Business Intelligence Suite Enterprise Edition semantic model.
Oracle’s Hyperion Essbase - System 9 is a technology with a long history and an active developer network, many of whose members you can meet on the OTN Essbase Forum and at user group events around the world. Now that you can create a simple database, take a moment to look at the online documentation and investigate some of the other features of this world-class analytic server.
Mark Rittman is an Oracle ACE Director and cofounder of Rittman Mead Consulting, an Oracle Partner, based in the U.K., providing specialist business intelligence and data warehousing solutions. He chairs the U.K. Oracle User Group Business Intelligence & Reporting Tools SIG, cochairs the ODTUG BI&DW SIG, and runs a blog at www.rittmanmead.com/blog.