OLAP Design Cycle

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

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 :

• Creation of the Essbase - Outline (Database Outline) (Essbase - Dimension, Essbase - Members, Essbase - Hierarchies)
• Input data is loaded to the lowest levels in Essbase - Hierarchies
• Essbase - Calculations consolidate input data in the upper levels of Essbase - Hierarchies

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).

Analysis Business Need and Plans

User Requirement to determine the Dimension and Members

User Requirement to determine the Dimension and Members

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.

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?

Creating Database outline

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

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 dimensionsto 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?

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?

To split ?

• Did you avoid repetition in the outline? : Essbase - Suppression of repetitions in Outline by adding a dimension
• Did you avoid interdimensional irrelevance? : Essbase - Suppression of interdimensional irrelevance by splitting the database
• Did you split the databases as necessary? : Reasons to Split Databases

Performance

• Essbase - Designing an Outline to Optimize Query or Calculations Performance
• Essbase - How to determine if the dimension must be of a type standard or attribute ?
• Decide whether a single-server approach or a partitioned, distributed approach better serves your needs. For criteria that you can review to decide whether to partition an application, see Guidelines for Partitioning a Database.
• Compressing data and optimizing your database, see Data Compression.

Checklist for Consolidation

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

• Did you identify the consolidations in the outline?
• Did you tag each member with the proper Essbase - Consolidation Operator?
• Did you specify a Essbase - Shared Members tag for designated members?
• Would Essbase - Shared Members be more efficient if designed within an Essbase - Attribute dimensions (other than shared)?

Checking System Requirements

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

• Ensure that you have enough disk space. See Determining Disk Space Requirements.
• Ensure that you have enough memory. See Estimating Memory Requirements.
• Ensure that your caches are set correctly. See Optimizing Essbase Caches.

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:

• Understanding Data Loading and Dimension Building
• Working with Rules Files
• Using a Rules File to Perform Operations on Records, Fields, and Data
• Performing and Debugging Data Loads or Dimension Builds
• Understanding Advanced Dimension Building Concepts

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.

Defining Calculations

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

• Does the default calculation logic achieve accurate results?
• Which members require Essbase - Formulas?
• Which members require time balance tags?
• Which members require Essbase - Variance Reporting?
• Which members require Essbase - Two-Pass Calculations ?
• Which members can be tagged as Dynamic Calc?

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

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

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.

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.