DEVELOPER: Business Intelligence
Building a CubeBy Tracy McMullen and Edward Roske
Learn the basics of building an Oracle Essbase aggregate storage database.
With the world economy keeping all of us awake at night, better company performance is critical. Fortunately, Oracle Essbase provides an environment for deploying prepackaged applications and developing custom analytic and enterprise performance management (EPM) applications. In this article, we’ll get you started on your rapid application development journey to building Oracle Essbase applications and databases to deliver high-speed reporting and analysis for your user community.
Oracle Essbase supports two database types: aggregate storage option (ASO) databases and block storage option (BSO) databases. This article focuses on the creation of ASO databases, which will be your default type of database in most cases. (ASO databases are designed to handle more dimensions and members, smaller batch windows for loads and aggregations of sparse data, and smaller database footprints.)
Answer Questions for the Juggling Wolverine Company
In this article, we will use the fictitious Juggling Wolverine Company (JWC) to illustrate the basics of Oracle Essbase. JWC specializes in juggling services around the globe, but the business is struggling (the economy is hurting everyone), and the company has questions: How are sales trending across customers? What is the profitability by customer? Which customer type drives the most revenue? (JWC’s marketing efforts need to be directed accordingly.)
JWC has asked you to build an Oracle Essbase database to answer these questions and more. Your mission, should you choose to accept it, is to create an Oracle Essbase application and database; build dimensions and members into an outline; define member properties and hierarchy types for the dimensions and members; and, finally, load data. With the mission completed, JWC analysts will be able to analyze data in the Oracle Essbase database via Smart View, receiving answers to their pressing questions so they can make good business decisions and improve overall performance.
This article assumes that you’ve installed and configured the Oracle Essbase server and its related components, including Shared Services (for security and other “plumbing” type components), Administration Services (for administration of Oracle Essbase databases), and Smart View (for reporting and analyzing Oracle Essbase data). When prompted during the Oracle Essbase installation, install the sample applications. (Although we won’t use them in this article, the sample applications provide helpful objects for review and learning.) In Shared Services, create and provision a user as an Oracle Essbase administrator. Once these prerequisite tasks are completed, you are ready to begin.
Create an Application and a Database
The first steps of creating a new application and a database with Administration Services are simpler than paying taxes. (In Oracle Essbase, an application is a container of databases, and a database is the actual Oracle Essbase “cube” that stores and organizes data for fast analysis and reporting.)
Let’s knock out the simple steps:
1. In Administration Services, select File -> New . Then select Aggregate Storage Application.
2. Select the Oracle Essbase server, and specify a new application name. For our example, call it Juggle . (Although Oracle Essbase supports Unicode applications, do not check Unicode mode. )
3. Click OK . You now have an application with no databases within it. (It is completely useless, though, so don’t stop now.)
4. Right-click the application you just created ( Juggle ), and select Create Database .
5. Your server and application should already be selected, so type in a new database name (for our example, use Juggle , because that’s how creative we are) and click OK .
Is that it? No, it is just the beginning.
Build Dimensions, Members, and Hierarchies in the Outline
Each Oracle Essbase database will have a single outline —an object that contains all the hierarchical information for your database. Unlike a relational database, in which the hierarchy is applied to data already stored in tables, the outline in Oracle Essbase (and, as such, the hierarchy) directly controls how data is stored and indexed.
Your Oracle Essbase outline will contain dimensions —common groupings of data elements we analyze and report on. Examples of dimensions are Time, Account, Product, and Market. Think of a dimension as something that goes down the side or across the top of a report.
Dimensions are made up of members . Members will have a member name and an alias—member properties that provide two ways to view the member. In the Product example in Figure 1, the product number is the member name and the product description is the alias. Note that products roll up to the product family, which rolls up to the total Product, given the hierarchical nature of Oracle Essbase.
After requirements-gathering sessions with stakeholders at JWC, you design a short five-dimensional outline to address JWC reporting and analysis needs (it’s not uncommon for ASO outlines to have 20-plus dimensions and millions of members). The dimensions and members identified for the JWC cube are listed in Table 1.
Table 1: JWC dimensions and members
1. In Administration Services, navigate to the database outline beneath the Juggle database you created. Right-click and select Edit . The Outline Editor will open.
2. Right-click in the Outline Editor, and select Add child to add the first dimension, as shown in Figure 2.
3. Type in the name for the member Period .
4. Repeat the steps, using Add child or Add sibling to build the dimensions and members defined in Table 1. (The member selected in the outline will dictate whether to use Add child or Add sibling .)
As an alternative to manually building the members in the Customer dimension, you can import the Customer dimension information by using the bldcust.rul rules file and custdim.txt source file, available for download at o10essbase.zip.
Now that you’ve built the dimensions and members, let’s assign dimension types, member properties, and hierarchy types.
Define Dimension Types
The dimension types in an Oracle Essbase ASO are Accounts, Time, Time Date, and Attribute. You could also assign None or no dimension type. Oracle Essbase provides some built-in functionality associated with each of these dimension types. For this JWC example, you will set the Time and Accounts dimension types.
1. In the Outline Editor of Administration Services, select the Period dimension. Right-click and select Edit Member Properties .
2. On the Information tab, set the dimension type for Period to Time and click OK .
3. Select the Account dimension. Right-click and select Edit Member Properties .
4. On the Information tab, set the dimension type for Account to Accounts and click OK .
Define Member Properties and Hierarchy Types
We’ve already discussed two member properties: member name and alias. Let’s review some other member properties that help define the Oracle Essbase database. Some member properties, such as Expense Reporting and Time Balance, are specific to a dimension type, and others are specific to the Oracle Essbase database type.
Consolidation. Consolidation operators tell Oracle Essbase how to aggregate members in a dimension. Should January, February, and March be added together to reach a total for Q1? Should units sold be multiplied by price to calculate revenue? Valid ASO consolidation operators include the default consolidation property, (+) Addition, as well as (-) Subtraction, (*) Multiplication, (/) Division, (%) Percent, and (~) No consolidation (identified by “Ignore” in the Member Properties window)—in the same dimension.
In most cases, you will use the default (+) Addition consolidation operator. The second most commonly used consolidation operator is (~) No consolidation. Use it in places where it doesn’t make sense to add up members. Would we want to add Actual and Budget together for Scenario? No, so we would assign No consolidation, or ~ (Ignore) in the outline, to both Actual and Budget.
Data storage. The data storage property tells Oracle Essbase how the member should be stored. Valid ASO data storage options are
Shared member data storage is a bit more complicated, so let’s look at it in more detail. Note that in our Juggle outline, we built two hierarchies in the Customer dimension:
Total_Customer and By Customer Rating. In these hierarchies, we are rolling up the same base members in two different ways for reporting and analysis. We will need to tag the second set of base members as “shared” for this outline to validate. Shared members have the same name as another member, belong to the same dimension, and point to the same data values, but they belong to different parents and participate in different roll-ups for alternative views of the same data. The original member contains the value, and the shared member has a pointer to the original member.
Datatype. We’re happy to say that in Oracle Essbase 11, a member in the Accounts dimension may be one of three datatypes: numeric, text, or date. In earlier releases, Essbase stored only numeric data. A new feature introduced in Oracle Essbase 11.1 enables the administrator to create and display text or date values to users for members in the Accounts dimension. Oracle Essbase is still storing a numeric value, but a translation happens between the stored value and a lookup table with the assigned text value.
Member formulas. You can assign a member formula to a member, providing specific logic for how it should be calculated. Calculation logic, written in Multidimensional Expression Language (MDX) for ASO databases, can range from very simple to highly complex.
Time balance. Time balance properties are available only in the Accounts dimension and are used to tell Oracle Essbase how a given member should be aggregated in the Time dimension. For example, should Headcount for January, February, and March be added together for Q1? This definitely wouldn’t make sense. In most cases, you want the Q1 head count to equal the March head count, the last head count in the period. To get Oracle Essbase to do this, you tag Headcount with Time Balance Last (“TB Last”), so that it will take the last member’s value when aggregating time. Depending on your requirements, you could also assign Time Balance First or Time Balance Average, along with specifications for how to handle periods with missing data.
Solve order. The solve order member property tells Oracle Essbase, “Here is the order for completing calculations” for ASO databases. Why is this important? You want to calculate the correct numbers in the correct order. Think order of operations for basic math. 4 + 5 * 2 does not equal (4 + 5) * 2. Using solve order is the way you control the order of calculations in ASO databases.
Hierarchy types. In addition to member properties, you will also set a property called a hierarchy type for each hierarchy within the outline. ASO databases have three types of hierarchies: stored, dynamic, and multiple (“Hierarchies Enabled”). Note in your Juggle outline that each hierarchy is, by default, set to stored. Stored hierarchies will aggregate according to the structure of the outline. In our example, months will roll up to quarters, up to a year total, in the Period member. This aggregation is really fast (that’s the nature of ASO databases), but stored hierarchies may have only the (+) Addition operator for any member and the (~) No consolidation (Ignore) operator for members under a Label Only storage member (other assigned consolidation operators are ignored). Also, stored hierarchies cannot have member formulas, and there are a few other restrictions on Label Only members.
Dynamic hierarchies are calculated by Oracle Essbase, rather than being aggregated in stored hierarchies, so all consolidation tags and member formulas are processed. The evaluation order for the calculation of members is dictated by the solve order, as mentioned above. Dynamic hierarchies, predictably, do not calculate as quickly as stored hierarchies.
You can also have multiple hierarchies within a single dimension. The hierarchies within a dimension can be all stored or all dynamic or have one hierarchy stored and the other hierarchies dynamic. Multiple hierarchies can contain alternative hierarchies with shared members or completely different hierarchies.
Set member properties and define hierarchy types for JWC. Now that you are fully fluent in Oracle Essbase member properties and hierarchy types, let’s update the JWC outline to set member properties and hierarchy types to meet reporting and analysis requirements.
1. In the Outline Editor for Juggle , right-click the Op_Expense member and select Edit Member Properties . The Member Properties window will appear (as shown in Figure 3).
2. Select (-) Subtraction for Consolidation .
3. Click the Next button, set Other Exp to (-) Subtraction for Consolidation , and click OK .
4. Right-click Scenario , and select Edit Member Properties .
5. Set Data Storage to Label Only .
6. Set the remaining member properties:
a. Year: Label Only for Data Storage
7. Right-click Account , and select Edit Member Properties . Note that the Hierarchy Information is set to Dynamic . By default, the Account dimension is always set to Dynamic , allowing member formulas and all consolidation operators.
8. Right-click Customer , and select Edit Member Properties .
9. On the Information tab, select Hierarchies Enabled for Hierarchy Information and Label Only for Data Storage .
10. Click Next until you reach the By Customer Rating member, and set the member properties to (~) Ignore for Consolidation and Store for Data Storage . The By Customer Rating alternative hierarchy will be a stored hierarchy (we need only the (+) Addition consolidation operator, and no member formulas are required).
11. Select all the base members (1001 through 4002) under By Customer Rating , and click the Shared Member icon, setting the storage property for many members in one click.
Verify and Save Your Outline
You’ve added dimensions and members and assigned properties. (As with anything you’re working on, we recommend that you save often.) Now it’s time to verify your outline and make sure you haven’t broken any of the Oracle Essbase rules (for example, every member name and alias must be unique; check out the Oracle Essbase Database Administrator’s Guide for the complete list). Click the Verify button to do this. Once everything checks out OK, click the Save button to save the outline back to the Oracle Essbase server, as shown in Figure 4.
Load Data—Load Data File and Create Rules File
There are many different ways to load data into Oracle Essbase: using Smart View Submit, Oracle Essbase Excel Add-in Spreadsheet lock and send, free-form data loading, data load rules (against flat files and relational tables), Oracle Essbase Studio, or Oracle Data Integrator. The following is an introduction to data load rules files, which are objects containing the instructions for how Oracle Essbase should load a source text file (or SQL query result).
1. In preparation, if you have not already done so, download o10essbase.zip and extract the jugact.txt text file.
2. In Administration Services, navigate to the Rules Files option under the Juggle database. Right-click and select Create Rules File . The Data Prep Editor will open.
3. Select File -> Open Data File , and browse to and open the jugact.txt text file you downloaded in Step 1.
4. Once the datafile is open, select Options -> Data Source Properties . The Data Source Properties window will open. Data source properties tell Oracle Essbase the datasource delimiters, what field edits have been made in the rules file, and what header rows may exist.
5. On the Delimited tab, choose Tab , and on the Header tab, set the number of lines to skip to 1 . Click OK .
6. Select Options -> Associate Outline , and the Associate Outline window will open. Choose the Juggle database, and click OK .
7. Select Options -> Data Load Settings , and the Data Load Settings window will open.
On the Data Load Values tab, you can define whether this rules file should overwrite, add to, or subtract from existing values or if it should perform sign flipping of data values during the load. On the Header Definition tab, you define any headers that may be needed for the source. When data is being loaded, all dimensions must be referenced either in the file or as a header.
1. For our example, select the Header Definition tab. Expand Scenario , and double-click Actual to select it as the header (your datafile contains actual data, even though the datafile doesn’t specifically say so). Click OK .
2. In the Data Prep Editor, select Field -> Properties , and the Field Properties window will open. This is where you will map the columns to dimensions or members and identifying data values.
3. Next select the Data Load Properties tab. (Ignore the Dimension Build Properties tab, because we are focusing on data loading.) Here you select the dimension that maps to the field or select a specific member or select Data Field or select Ignore field during a data load .
4. On the Data Load Properties tab, set the appropriate field name to match your source file column:
Field 1: Year
5. Select Options -> Validate to validate the rules file (check out the Oracle Essbase Database Administrator’s Guide for a complete listing of rules file restrictions).
6. Select File -> Save to save the rules file.
We have the datafile; we have the data load rules file. Let’s load some data!
1. Within Administration Services, select the Juggle database and choose Actions -> Load data for “Juggle” from the menu. The Data Load window will open.
2. Choose Overwrite Existing values , and leave the default values for the other options.
3. Click Find Data File , as shown in Figure 5, and navigate to the dimension datafile (the text file you just downloaded).
4. Click Find Rules File , and navigate to the data load rules file (the rules file you just created).
5. Accept the defaults for the Error File location and name.
6. Check the Overwrite check box if you want the error file to replace an error file that may already exist, and, finally, click OK to load the data.
Congratulations! Thanks to your fine work, JWC analysts can now retrieve and analyze data against the Juggle database via Smart View or other end-user reporting tools. (Oracle Essbase ASO cubes require no further calculation steps.)
While you’ve built the most basic of Oracle Essbase databases, you’ve learned some valuable concepts and initial database creation steps you can apply in your own environment. Next steps include learning ways to dynamically build dimensions with rules files (do you want to manually build a 100,000-customer list?), how to automate processes, and how to design and optimize your ASO databases the “right” way.
You are well on your way to building reporting, analysis, and performance management applications that provide tangible benefits and actionable information to your organization. We’ve covered only the tip of the iceberg when it comes to Oracle Essbase, so we encourage you to deep-dive into Oracle Essbase and learn about all the features and functions this EPM powerhouse can provide. Go forth and aggregate.
Tracy McMullen ( firstname.lastname@example.org ), director, and Edward Roske ( email@example.com ), CEO, deliver EPM solutions for clients at interRel Consulting ( www.interrel.com ). Winner of the 2008 and 2009 Enterprise Performance Management and Business Intelligence Solution of the Year Oracle North America Titan Awards, interRel Consulting provides consulting, training, infrastructure, support, and software services for Oracle EPM solutions.