Essbase - The OLAP Design Cycle (to create an optimized database)

> Database > Oracle Hyperion - Essbase

1 - Short Cycle

A Essbase - Block storage Essbase - Database (cube) contains two types of values :

  • the values that you enter or load (input data)
  • and the values that are calculated from the input data (calculated data)

Then a typical Essbase - Database (cube) conforms to the following process :

2 - Elaborated Cycle

The process of creating a production database involves these tasks:

  • Analyse Business Need and plans
  • Creating a Database outline, defining database dimension and hierarchies
  • Checking System Requirement
  • Loading data by using rules files to map to the database dimension
  • Defining Report
  • Verifying the design

Defining only one database per application enables enhanced memory usage and ease of database administration.

Applications that use the optional Essbase Currency Conversion module are an exception to this recommendation. Currency conversion applications generally consist of a main database and a separate currency database (see Designing and Building Currency Conversion Applications).

Advertising

3 - Analysis Business Need and Plans

3.1 - User Requirement to determine the Dimension and Members

3.2 - User Management and security

  • Do some users require access to information that other users should not see?
  • Who are the users and what permissions should they have?
  • Who should have load data permissions?
  • Which users can be grouped, and as a group, given similar permissions?

Essbase - User management and security.

3.3 - Analyzing Source Data

Determine the location of the current data.

  • Where does each department currently store data?
  • Is data in a form that Essbase can use? see Data Sources.
  • Who updates the database and how frequently?
  • Does the data support the desired analysis and reporting goals?
  • Do those who need to update data have access to it?
Advertising

4 - Creating Database outline

This section contain some guideline to model the database Essbase - Outline (Database Outline).

4.1 - Checklist for Dimension and Member

Most businesses analyse the following areas:

  • Time periods
  • Accounting measures
  • Scenarios (to give different point of view - actual vs budget vs forecast)
  • Products
  • Distribution channels
  • Geographical regions
  • Business units

If you need to analyze a business area by classification or attribute, such as by the size or color of products, you can use attribute dimensions to represent the classification views.

A typical Essbase database contains at least seven Essbase - Standard dimensions (non attribute dimensions) and many more attribute dimensions.

  • What are the candidates for dimensions?
  • Do any of the dimensions classify or describe other dimensions? These dimensions are candidates for attribute dimensions.
  • What are the candidates for members?

For each Essbase - Combinations/Intersections of Essbase - Dimension, ask three questions:

  • Does it add analytic value?
  • Does it add utility for reporting?
  • Does it avoid an excess of unused combinations?
Advertising

4.2 - Checklist for Dimension and Member Properties

  • Can you identify a time dimension?
  • Can you identify an accounts dimension?
  • Does the data include foreign currencies? If so, did you identify a currency partition dimension?
  • Can you identify qualities or characteristics of dimensions that should be defined as separate attribute dimensions?
  • Which members require special data storage properties?

4.3 - To split ?

4.4 - Performance

4.5 - Checklist for Consolidation

Use the following checklist to help define Essbase - (Consolidations|Aggregations):

5 - Checking System Requirements

Now you are ready to determine the system requirements for the database.

6 - Loading Test Data

Before you can test Essbase - Calculations, consolidations, and reports, you need data in the database. During the design process, loading mocked-up data or a subset of real data provides flexibility and shortens the time required to test and analyze results.

Detailed instructions for loading data are in the following chapters:

If you are satisfied with your database design after the preliminary test, test load the complete set of real data with which you will populate the final database. Use the test Essbase - Rules File if possible. This final test may reveal source data problems that were not anticipated during earlier design process phases.

7 - Defining Calculations

Use the following checklist when you define a Essbase - Calculations:

The Essbase triggers feature enables efficient monitoring of data changes in a database. See Understanding Triggers Definitions.

8 - Defining Reports

To ensure that the design meets user information requirements, you must view data as users view it. Users typically view data through spreadsheets, printed reports, or reports published on the Web.

Many data access tools are available for producing the reporting systems that users use. EPM have its own line of products : Hyperion Reporting and Analysis Tools

9 - Verifying the Design

Ensure that the database satisfies all of their goals

  • Do the calculations provide the information they need?
  • Are they able to generate reports quickly?
  • Are they satisfied with consolidation times?

In short, ask users if the database works for them.

Near the end of the design cycle, test with real data.

  • Does the outline build correctly?
  • Does all data load?

If the database fails in any area, repeat the steps of the design cycle to identify the cause of the problem.

10 - End, support

Most likely, you will need to repeat one or more steps of the design process to arrive at the ideal database solution.

Essbase provides several sources of information to help you isolate problems :

  • application and Essbase Server logs,
  • exception logs,
  • and database information accessible from Administration Services.