Building Your Essbase Database: from Source to Completed database

Build Essbase Database in Oracle StudioLet me introduce potential architectures for Essbase implementations, components of the overall solution (both required and optional), and fundamental design methodology. Now we present a hands-on demonstration of how to build an Essbase database.

To get started, we'll start with an overview of the components and files used in the demonstration. During the demonstration, we walk you through how to build and deploy an Essbase database using Oracle Essbase Studio. Then we talk about how to calculate the database and validate the data in the database using reports. After the demonstration, we discuss some Essbase features you can take advantage of, such as custom load rules and member calculations. 

 


Table of contents[Show]


Demonstration Overview

Our goal for this demonstration is to show you the overall process for building, deploying, calculating, and validating an Essbase database. By the end of the demonstration, you will be able to deploy a simple database, and you will have a foundation from which to explore more advanced topics.


NOTE Unless otherwise specified, the term database in this carticle refers to an Essbase multidimensional database. In the Essbase Studio console, an Essbase database is also called a cube.


In this demonstration, we build an Essbase database in five steps:

After building the Essbase database, we will calculate it and show you how to validate it. Along the way, we will discuss related aspects of an Essbase database.


NOTE The instructions and screen captures in this blog article reflect a Windows-based environment. For instructions for UNIX-based systems, see the Oracle Essbase documentation set.


This demonstration is for illustrative purposes only. There are a many ways to build an Essbase database. The specific methodology you use will depend on your data sources, the data-integration components that are available to you, the versions of your software, and your overall comfort level with Essbase.

While there are external aspects of building a database, such as extract, transform, and load (ETL) processes, the components discussed are those that are provided out of the box with Essbase. As indicated in Figure 5-1, the following components are required for this demonstration:

  • Essbase server
  • Essbase Administration Services server and console
  • Studio server and console

 

Essbase architecture used for the demonstration

FIGURE 5-1. Essbase architecture used for the demonstration

 

Because we use a relational database as the data source in this example, we had a choice of build tools for this demonstration: Essbase Studio, Integration Services, or Administration Services. Both Essbase Studio and Integration Services are much faster to deploy against this type of data source than Administration Services. We selected Essbase Studio because it is likely to replace Integration Services in the future, as discussed in this blog.

Essbase Studio also provides a significant advantage over Integration Services, in that it generates data-load rules—the underlying files used to build and update a database -automatically. Essbase Studio provides a graphical means to generate load rules. Instead of needing to map each individual field and select a variety of specific settings, you can accomplish these tasks using simple drag-and-drop actions. In short, Essbase Studio simplifies the process of creating dimensions and building a database. At the end of the process, Essbase Studio not only builds the Essbase database, but it also places the load rules it generates into the database directory for use in batch processes.

If you intend to follow the steps in this article's example, you need to create the relational database to be used as a data source. First, you need a working installation of one of the following relational databases: Oracle Database, SQL Server, Teradata, or DB2. You then run the SQL scripts that ship with Essbase Studio to create all of the tables, data, and metadata in the relational database. The SQL scripts are located in the sqlscripts subdirectory of your Essbase Studio installation. The sample relational database is called TBC (for The Beverage Company).

 

From Source to Database with Essbase Studio

In this section, we demonstrate an efficient method for building and deploying an Essbase database using Essbase Studio. We start with an overview of the Essbase Studio console. We then walk through the steps required to build and deploy an Essbase database, as outlined in the previous section. Then we will take a quick look at one of the generated load rules, summarize the building process, and calculate and validate the completed database.

 

Overview of Essbase Studio

Introduced in version 11.x of Essbase, Essbase Studio represents a much simplified methodology for creating Essbase databases compared to previous methods. While it is always possible to create a complete model using load rules created manually in Administration Services, Essbase Studio provides distinct advantages:

      •  Greater reusability of metadata (including dimensions) and data across analytic applications without requiring the user to model twice

  • Lineage tracking of a database and its parts (dimensions, members, and so on), which allows administrators to perform impact analysis of changes to underlying data sources and elements on analytic applications
  • Unification of database-building processes, regardless of the data source type
  • Enhanced ability to drill through from Essbase to supporting data sources
  • Simplification of federated data source environments
  • Ease of integration into existing batch processes

To start the Essbase Studio server on a Windows-based system, if the server was installed as a service, open the Windows Services management console and start the service named Hyperion Essbase Studio Service. Otherwise, from the Windows taskbar, select Start | Programs | Oracle EPM System | Essbase | Essbase Studio |

Start Server. The server starts in the foreground after a few seconds.


NOTE On a Windows system, the display may show error messages as the Essbase Studio server tries to load drivers that are not installed. For example, you will see an error message if you do not have the MySQL driver installed. The messages are a normal part of the Essbase Studio startup process. They will not interfere with the server's operation.


To start the Essbase Studio console, select Start | Programs | Oracle EPM System | Essbase | Essbase Studio | Essbase Studio Console. When prompted, enter your user name and password, and then click OK.

As shown in Figure 5-2, the Essbase Studio console contains three distinct areas:

  • Metadata Navigator The left panel contains all analytic objects created based on the data sources and database schemas that are selected in the Data Sources panel on the right. These include (but are not limited to) hierarchies, cube schema, drill-through reports, and metadata elements (which are used to create hierarchies).
  • Work Area In the central panel, you can edit objects selected in the Metadata Navigator or the Source Navigator.
  • Source Navigator The right panel lists data sources and data source internal mappings (minischemas). You can add, delete, and edit data source connections in this area. You can also add tables from an existing source, modify joins, and edit or create a minischema.

 

The Essbase Studio Console

Figure 5-2. The Essbase Studio Console

 

Mapping Data Sources

At the time of publication, Essbase Studio supports the use of data from any of the following sources for an Essbase database (a JDBC driver is required for each source):

  • Oracle Database
  • OBIEE
  • Oracle Enterprise Performance Management Architect
  • Microsoft SQL Server
  • IBM DB2
  • Teradata databases
  • Sun Microsystems MySQL
  • Delimited text files

NOTE Microsoft Excel cannot be used directly as a data source for an Essbase database. The data must first be saved to a delimited text file (a comma-separated values, or CSV, file).


As noted earlier, for this demonstration, we use a relational database as a data source. Therefore, while the following procedures are generalized, the examples and screen captures represent a relational database as a data source.

Follow these steps to map a relational data source from the Essbase Studio console:

1.In the Source Navigator, select the Data Sources tab.

 

Source Navigator

 

2. Right-click the Data Source root node and select New | Data Source.

 

Data Source

 

3. The Connection Wizard starts. Specify the parameters—database type, server name, database SID, and so on—applicable to your local environment and data source. In the both the Connection Name and Database Name fields, enter TBC for this example. Then click Next to continue. The following image is for illustrative purposes only; do not copy these values.

 

The Connection Wizard starts

 

4.On the Select Tables page, select the following tables, and then click the Add button:

  • TBC.Family
  • TBC.Market
  • TBC.Measures
  • TBC.Population
  • TBC.Product
  • TBC.ProductDim
  • TBC.Region
  • TBC.Sales
  • TBC.Scenario
  • TBC.Supplier

5. Click Finish. In the Source Navigator, the database is added to the Data Sources tree.
6. If necessary, expand the Data Sources root node to see the new data source. Then expand the TBC database tree to view the tables in the database.

 

Data Sources root node

 

7. Expand one of the table trees to view the columns in the table.
8. Right-click the Family table and select View Sample Data. A sample set of records from the table is displayed. Note that the column headings match the column names in the table.

 

Family table


9. Close the Sample - Family tab by clicking the X icon beside the tab name.

 

Modeling the Data Source

In a sense, modeling the data source is optional. The reason we model the data source is so that we can load data from a relational source. Therefore, if you want to build an outline from the relational source without loading data, you can skip this step. However, you will need to create custom load rules to load data into the database later. For more information, see the "Creating Custom Load Rules" section later in this blog. For this demonstration, we will model the data source and let Essbase Studio do the work of generating the load rule files.


NOTE If data is stored in flat files, the wizard used to map the data source does the modeling automatically.


This step is not required.

 

Minischema Basics

You model a relational source by creating an Essbase Studio minischema. In the minischema, you create the join relationships between tables. The minischema does not contain hierarchy information; it simply provides the mappings so that Essbase Studio can traverse the data source. The following are other important points regarding minischemas:

  • Text files can be added to a minischema for logical grouping only. They cannot be joined to other text files or relational tables.
  • Essbase Studio supports the use of multiple fact tables in the minischema.
  • There is no requirement to have a fact table for hierarchy creation or Essbase database deployment. To that end, you can use columns from a relational source to build the model without a minischema, and use a flat file to load the data.

Creating a Minischema from a Relational Source

Follow these steps to create the minischema for this example:

1. In the Source Navigator, select the Minischemas tab.

 

Minischemas tab

 

2. Right-click the Minischemas root node and select New | Minischema.

 

Minischemas root node and select New

 

3. Specify a name for the minischema and, optionally, a description of its purpose. For this example, enter the name SalesAnalysis. You do not need to enter a description. Then click Next.

 

Specify a name for the minischema

 

4. On the Add/Remove Tables page, ensure TBC is selected and add all tables to the minischema.

5. Click Finish. The tables in the database are laid out in the schema viewer.

 

tables in the database in the schema viewer

 

6. Right-click in the schema viewer and select Add Joins by Inspection.

 


CAUTION While the Add Joins by Inspection option is selected here for simplicity, adding joins by inspection can potentially create numerous repetitive joins between tables. For a production database, it is generally better to inspect the keys and foreign keys and build the joins manually.


7. In the Create Joins by Inspection dialog box, select Select all Items, and then click OK. Like columns across tables are joined.

8. Right-click in the schema viewer and select Layout Schema to view the current schema.

 

Adding a Recursive Join to the Minischema

 

Adding a Recursive Join to the Minischema

 

 

Now we will modify the schema to include a recursive join for the Measures table. Adding a recursive join lets us do parent/child dimension builds from the table.

  1. Right-click the Measures table and select Add Join. The Properties dialog box appears. Here, you can edit the properties of a minischema join.
  2.  Select the Measures table from the second drop-down list.
  3.  In the Column area, click in the first cell and select CHILD from the drop­down list.
  4. Click in the cell to the right and select PARENT from the drop-down list.

TIP Alternatively, in the Measures table (in the schema viewer), you can select PARENT and drag and drop it on top of CHILD


 

Measures table (in the schema viewer)

 

 

       5. Click OK to add the join.

       6. Save the changes to the SalesAnalysis minischema and close it.

 

Building Dimensions (Hierarchies)

When using other Essbase components, we use the term hierarchy to refer to a specific portion of a dimension. In Essbase Studio, hierarchy means dimension. Therefore, this section focuses on building the dimensions—in other words, creating the hierarchies— for our database. Hierarchies are built using metadata elements, such as column names in a relational source and fields in a text file.

In this demonstration, we begin by creating a folder structure in the Metadata Navigator. The folder structure enables us to organize the elements for the new database, including metadata elements and hierarchies. You can create as many folders and subfolders as desired to organize your deployment. You can add folders at any time, but we recommend that you organize the metadata elements before creating hierarchies, because it simplifies the process.

A metadata element is anything that you can use to create an analytical model.

A metadata element in Essbase Studio is any of the following:

  • Relational column (based on the source or user defined)
  • Field from a text file
  • User-defined element (such as a variance that does not exist in the relational source)
  • Hierarchy
  • Drill-through report
  • Cube schema

Hierarchies are built by dragging and dropping metadata elements into the hierarchy. In our blogs we talked about dimensions organized from top to bottom (generations), from bottom to top (levels), or in a recursive fashion (parent/child). Essbase Studio removes the necessity to think about a dimension build in this way. Instead, you drag and drop the sources for each level either from the Metadata Navigator (the easiest way) or directly from the Data Sources tab in the Source Navigator. The term level is used here in a very generic sense, in that there is no requirement to understand the concepts of levels, generations, parents, or children. You can create custom members, hierarchies, attributes, and alternate hierarchies in the same fashion. At any point during the process, you can see a full preview of the resulting hierarchy.


NOTE The hierarchy corresponds to the Essbase outline discussed in the "Designing an Essbase Database" . If you like, you can use the Administration Services console to view and edit the hierarchy in the Outline Editor.


Creating a Folder Structure in the Metadata Navigator

To create a folder structure, follow these steps:

  1. In the Metadata Navigator, right-click the Root folder and select New | Folder.
  2. In the Properties dialog box, name the folder SalesAnalysis, and then click OK.
  3. Create the following additional folders under the SalesAnalysis folder: Dimension Elements, Hierarchies, Cube Schemas, and Drill Through Reports.

    Create the following additional folders

Selecting Metadata elements

Follow these steps to add metadata elements to the Dimension Elements folder:

  1. In the Source Navigator, select the Data Sources tab.
  2. Expand the Market table.
  3. Drag STATE from the Source Navigator to the Metadata Navigator, and drop it in the Dimension Elements folder.
  4. In the Source Navigator, expand the Region table and drag REGION to the Dimension Elements folder.

 

expand the Region table

       5. Repeat the process to add the following columns to the Dimension Elements folder:

 

Table Name Column Name
ProductDim FAMILY
Product SKU
  CAFFEINATED
  OUNCES
  PKGTYPE
Region  DIRECTOR

 

 Creating Hierarchies

Follow these steps to create the hierarchies for this example:

  1. In the Metadata Navigator, right-click the Hierarchies folder and select New | Hierarchy.

 

Metadata Navigator, right-click the Hierarchies

 

  1. Specify a name for the hierarchy. For this example, enter Product.
  2. Drag FAMILY from the Dimension Elements folder and drop it in the first empty cell in the data table.
  3. Drag SKU from the Dimension Elements folder and drop it on top of the FAMILY entry in the first cell. SKU becomes a child of FAMILY.
  4. Add the following columns to the Product hierarchy at the specified level:

 

Hierarchy Name First Level Second Level
Product OUNCES

SKU

Product PKG TYPE

SKU

Product CAFFEINATED

SKU

 

Add the following columns to the Product hierarchy at the specified level

  1. Click Save and Preview. The Product hierarchy is displayed in a tree format.

 

The Product hierarchy is displayed in a tree format

 

  1. Following the same procedure, create a Market hierarchy with the following levels:

 

Hierarchy Name

First Level

Second Level
Market

REGION

STATE
Market DIRECTOR STATE

 

  1. Click Save and Preview. The Market hierarchy is displayed

 

The Market hierarchy is displayed

 

  1. Create the following additional hierarchies and levels:

 

Hierarchy Name First Level Second Level
Accounts PARENT CHILD
Scenario Scenario N/A

 

Building a Time Dimension

Our demonstration requires a time dimension, but our data source does not enumerate year, quarter, and month columns. However, it does contain a column with a date-time stamp. Through a simple wizard, Essbase Studio can extract the individual date elements from a single record to create the time dimension.


TIP If your data source contains columns or fields for years, quarters, months, weeks, days, and so forth, you can build a time dimension in the same fashion as you would any other hierarchy.


  1. In the Metadata Navigator, right-click the Dimension Elements folder and select New | Dimension Element. The Properties dialog box is displayed.

 

The Properties dialog in Metadata Navigator

 

  1. Specify a name for the new element. For this example, enter Quarter.
  2. Select the Functions tab below the Formula list box.
  3. Expand Date.
  4. Select the QuarterAsString function and click the Add button beside the Caption Binding area.
  5. Select the Source tab.
  6. Expand TBC, then Sales.
  7. In the Caption Binding area, select the string $$DateOperand$$. With the string selected, select Transdate from the Source tab, and then click the Add button beside the Caption Binding area. Transdate should overwrite the $$DateOperand$$ string

 

Caption Binding area

 

  1. Click OK to add the element.

 

add the element

 

  1. Repeat the process to create a new dimension named Month. Specify the MonthShortName function.
  2. In the Hierarchies folder, create a hierarchy named Year. Make Quarter the top of the hierarchy, and nest Month underneath it.

 

Modeling the Essbase Database

In this part of the demonstration, we select the hierarchies (dimensions) to include in the Essbase database. The Essbase Studio console refers to this process as "creating a cube schema and model." Recall that cube is another name for a multidimensional database.

Let's take a step back for a moment. A cube schema is simply a representation of the actions you want Essbase Studio to take. This is different from a minischema, which describes how Essbase Studio should navigate the relational structure in order to retrieve numeric data across the set of tables. In the case of a cube schema, you are describing which dimensions you would like Essbase Studio to deploy to an Essbase database. The cube model is where you specify properties for deployment, such as the following:

  • Which dimensions are dense or sparse
  • The consolidation operator assigned to a member
  • Whether the resulting database should contain unique member names or allow for duplicate names

Depending on the nature of the data source, it is possible that all of the properties are being housed in the relational source. We have worked on a number of deployments where customers store all of the Essbase database information (metadata) in columns of the relational model. This might include consolidation operators, member formulas, member aliases, and user-defined attributes.

A centralized data warehouse or data mart makes it easier to manage changes to this information and to push them out to existing models. As the information changes in the data warehouse or mart, Essbase Studio automatically picks up those changes when it deploys the Essbase database. Once deployed, the implemented changes are available to the end users of the system. This way, you do not need to manage these changes on each deployed database individually.

The following procedures walk through the creation of the cube schema/model, as well as the assignment of various Essbase properties. Many of the Essbase properties for this model are stored in the relational database source, and the steps show you how to map these into your Essbase database.

Creating a Cube Schema and Model

Follow these steps to create the cube schema and model for this example:

  1. In the Essbase Studio console's Source Navigator, select the Data Sources tab.
  2. Expand TBC, and then the Sales table.
  3. Drag AMOUNT from the Source Navigator to the Metadata Navigator, and drop it in the Dimension Elements folder.
  4. In the Metadata Navigator, right-click the Cube Schemas folder and select New | Cube Schema.
  5. Specify a name for the cube schema. For this example, enter TBC1.
  6. Expand the Hierarchies folder and add the following hierarchies: Accounts, Year, Scenario, Product, and Market.
  7. Expand the Dimension Elements folder and add Amount to the Measures area.

 

add Amount to the Measures area

 

     8. Click Preview Hierarchies. Review the dimension.

 

Review the dimension

 

  1. Click OK to close the preview, and then click Next.
  2. On the Cube Schema Options page, select Create Essbase Model.
  3. From the Accounts Dimension drop-down list, select Accounts.
  4. Click Finish to create the cube schema.

The next task is to set all the Essbase properties in the Essbase model.

 

Setting Properties in the Essbase Model

To set properties for the model, follow these steps:

  1. If the TBC1 model is not displayed, double-click TBC1 in the Metadata Navigator.
  2. Right-click in the model and select Essbase Properties to display the properties of the model.

 

Essbase Properties

 

         3. Expand Accounts and PARENT, and then select CHILD.

         4. Select the Info tab and specify the settings as follows:

               • Consolidation Select External source and then select CONSOLIDATION.

               • Two Pass Calculation Select External source and then select TWOPASSCALC

               • Data Storage Select External source and then select Storage.

 

Select External source and Storage in Data Storage

 

         5. Select the Account Info tab and specify settings as follows:

              •  Time Balance Select External source and then select TIMEBALANCE.

              •   Skip Select External source and then select SKIP.

               •   Variance Reporting Select External source and then select VARIANCEREPORTING.

 

Variance Reporting

 

  1. Select the Formula tab. Select External source and select Formula from the
    drop-down list.
  2. Select the UDA tab. From the External source drop-down list, select UDA
    and click Add to List.
  3. Click Apply to apply these member properties.
  4. Select PARENT from the navigation tree. Repeat steps 4 through 8.
  5. Expand the Product dimension and select OUNCES.
  6. On the General tab, select Essbase Attribute for SKU.
  7. Change the Attribute Type to Numeric.
  8. Specify the remainder of the settings for this model as follows:

 

Hierarchy Column Tab Property Setting
Year Year Info Dimension Type Time
Year Year Info Dimension Storage Dense
Scenario Scenario Info Consolidation ~
Product PKGTYPE General Select Essbase
Attribute for SKU
String (default)
Product CAFFEINATED General Select Essbase
Attribute for SKU
Boolean
Market STATE
(under REGION)
UDAs External Source UDAMKTSIZE
Market STATE
(under REGION)
UDAs External Source UDAMKTTYPE

 

  1. Click Close. If you are prompted to validate the model, select Yes. Fix any issues identified. When the validation is complete, click Close.

NOTE If you receive a message stating that the selection of External for data storage for the column Child can cause trouble, ignore this message. This message indicates that if not configured properly, the external settings can cause invalid configurations on cube deployment. The sample database source is free of data errors.


 

Deploying the Essbase Database

The actual deployment is the simplest of all steps. At this point, all you need to do is specify a destination for your database, select a few deployment options (if desired), and let the process run.

For the deployment target, you can specify an existing Essbase server that you previously defined in Essbase Studio, or you can define a new Essbase server directly from the Cube Deployment Wizard. For this demonstration, we will define the Essbase server during deployment to preserve a logical flow through the article. Alternatively, you can define a deployment target using the data source window when you map data sources. There is no advantage or disadvantage to defining the Essbase server at deployment or earlier in the process.

The deployment options allow you to specify how you want Essbase Studio to treat the build process. You can choose to deploy only the outline (metadata only), load data to an existing outline, or both. If you are updating an existing database, you can tell Essbase Studio to remove all members and rebuild, or tell it to build a dimension incrementally and update the numeric data accordingly. Many other deployment options are available. The intention of this demonstration is to provide a framework for the overall process. For specific information on the deployment options, see the Oracle Essbase Studio User's Guide.

 

Deploying the Database to an Essbase Server

Follow these steps to deploy the sample Essbase database:

  1. With the TCB1 model open (if necessary, expand Cube Schemas in the Metadata Navigator and select the model), right-click TCB1Model and select Cube Deployment Wizard.

 

Deployment Wizard

 

2. Click New Connection.
3. Define a new Essbase deployment target by completing the fields in the Essbase Login dialog box as follows, and then click Login.

 

Name

The name you want to display in the interface (for this example, specify Demo)

Description

An optional parameter allowing you to provide a description about the Essbase target (for this example, you can leave this field blank)

Server

Hostname or IP address of your Essbase server

Port

Port on which Essbase is listening (1423 is the default port; you should not change it unless you have been told specifically that the default Essbase port was not used)

User

 

User name that you specify when connecting to Essbase

Password

 

Password that you specify when connecting to Essbase

 

The following image is for illustrative purposes only; do not copy these values.

 

Essbase login

 


NOTE The login information you supply in the Essbase Login dialog box needs to reflect your implementation of Essbase. If you do not know this information, contact the person responsible for the Essbase server in your organization.


       4. Select the new connection from the Essbase Server Connection drop-down list. In this case, select Demo.

       5. Specify names for the application and database. For this example, name both of them TBC1.


TIP Although the Application and Database fields are drop-down lists, you can also type the name of a new application and database into the fields.


       6. Under Load Task Type, select Build Outline and Load Data.

 

Build Outline and Load Data

 

  1. Click Finish. Essbase Studio initiates the deployment of the database.

 

Taking a Look at the Load Rules

As mentioned earlier in this blog, Essbase Studio is a graphical load rule generator. You can use the Administration Services console to look at the TBC1 database we just deployed and see the load rules that were created by Essbase Studio. Figure 5-3 shows the Administration Services console with the rules files displayed in the left navigation panel. The TBC1 rules file is open in the Data Prep Editor.

In the Data Prep Editor, the upper half of the window shows the original data source. The lower half shows the load rule that was created based on the data source and the selections made while building the database. The rows represent records, and the columns are the dimensions. There is a single metric per record, which is located in the last column, entitled *Data*.

You can use these load rules to automate your processes, as discussed in the "Automating Processes" section later in this article. You can also create custom load rules, as explained in the "Creating Custom Load Rules" section later in this blog.

 

Creating Custom Load Rules

Figure 5-3. Autogenerated rules files for the database

 

Summary of the Database Building Process

At this point, we have successfully deployed an Essbase database from scratch. Before we move forward, let's review the steps that we have covered so far and look at how they map to our overall architecture.

Recall the first four steps:

  1. Map the data source.
  2. Model the data source.
  3. Build dimensions (hierarchies).
  4. Model the Essbase database


Figure 5-4 shows the components used in the process. We used the Essbase Studio console to complete all the steps. First, we mapped the data source, which connected the Essbase Studio server to the data source. We then modeled the data source so that load rules could be created, and we created the hierarchies that represent our dimensions. Finally, we modeled the Essbase database and pushed information about our dimensions and the Essbase database structure into the metadata repository.

 

Components used in the first four steps of the demonstration

FIGURE 5-4. Components used in the first four steps of the demonstration

 

 

Components used when the Essbase database is deployed

FIGURE 5-5. Components used when the Essbase database is deployed

 

In the fifth step, the Essbase server enters the picture. As shown in Figure 5-5, when the Essbase database is deployed to the Essbase server, a physical database is created on that server.

Figure 5-5 also includes the Administration Services server and console. After deployment, we used the Administration Services console to view a generated load rule.


NOTE So far, we have worked completely in the user interfaces. This is not, however, how deployments operate after the prototype stage. Essentially, you would use the Essbase Studio console for modeling, but leverage the standard Essbase automation capabilities to deploy a production-level database.


For more information, see the "Automating Processes" section later in this article.

The next steps are to calculate the Essbase database and then validate the data.

 

A database needs to be calculated to be usable

FIGURE 5-6. A database needs to be calculated to be usable.

 

Calculating the Essbase Database

You now have a database with data loaded. However, as illustrated in Figure 5-6, your database is generally not usable until you run a calculation process.

It is important to understand why calculation is required. To make this clear, let's take a look at the simple hierarchy shown in Figure 5-7. If you load the sales value 10 into all of the bottom level (leaf node) markets, then a generated report might look like the report in Figure 5-8.

If the values at East and Market are not dynamically calculated (derived at request time), then there is no total at East or Market. To get a value to appear at that level, you need to execute a calculation to tell Essbase to, at the very least, read the outline and do what the consolidation operators tell it to do. From Figure 5-7, you see that this is simple addition. The value for East is the sum of the values for the individual states—that is, 50. This total is then added to the totals for the other regions (not pictured) and rolled up to produce a Market total.

 

Figure 5-7. A simple hierarchy

 

 

 Report showing the output of the simple hierarchy and its data

FIGURE 5-8. Report showing the output of the simple hierarchy and its data

 

To accommodate simply moving data through the model, every database comes with a prebuilt calculation, called the default calculation, which reads the outline and does exactly what the consolidation operators say to do. Aggregate Storage databases calculate all upper level intersections dynamically at query time. As such, there is no need to run a calculation to see data at a given intersection. Performance is greatly improved by running at least the default aggregation set. Running the default aggregation set after a build is the recommended process. For block storage databases, you need to run the default calculation yourself, as follows:

  1. In the Administration Services console, expand Essbase Servers, then your server, then Applications, then Sample.
  2. Right-click the database node and select Execute Calculation.

 

Execute Calculation

 

       3. In the Execute Calculation dialog box, select Default calculation, and then click OK.

Sometimes you need to do something more complex than just aggregate the model. You might want to clear data in a certain portion of the database, and aggregate it in a different fashion. For example, you may need to recalculate a portion of the model when comparing exchange rates between actual and forecast scenarios. To handle situations like this, you can create member formulas and custom calculation scripts. Both of these topics are discussed in the "Creating Member Formulas and Calculation Scripts" section later in this blog.

Note that when we built the dimensions for the database, we linked a formula that is defined in the metadata repository to the PARENT and CHILD members of the Product hierarchy. When the database is calculated, these formulas are also processed.

Validating the Essbase Database

The model is built, the data loaded, and a calculation is run. The next step is to validate the data and metadata in the database by running a series of reports on the Essbase database and comparing the results to the original data and reports. Be sure to share the results with your business users and investigate any analytic challenges. You should expect to need to tweak dimensions, members, hierarchies, attributes, and formulas based on their feedback. This will drive the greatest flexibility for future use.

You can create reports for Essbase in many ways, including with Oracle Hyperion Smart View, Oracle Essbase Spreadsheet Add-in, Oracle Business Intelligence Answers, Oracle Hyperion Financial Reporting, Oracle BI Publisher, plus a variety of third-party reporting tools. Most of these tools make use of the built-in Essbase reporting languages to query the Essbase database. If you want, you can use the built-in reporting languages directly to create your reports.

 

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

Big Data Use Cases and NoSQL d...
Big Data Use Cases and NoSQL d... 503 views Андрей Волков Thu, 14 Jun 2018, 05:08:53
Oracle OLAP and Essbase archit...
Oracle OLAP and Essbase archit... 2120 views Андрей Волков Thu, 19 Sep 2019, 14:35:37
OLAP concepts, history and imp...
OLAP concepts, history and imp... 1131 views Akmaral Wed, 03 Jan 2018, 06:21:53
Introduction to OLAP: basic co...
Introduction to OLAP: basic co... 1111 views Akmaral Wed, 03 Jan 2018, 06:21:04

Comments on Building Your Essbase Database: from Source to Completed database

Be the first to comment
Please login to comment