Hyperion Essbase - Designing a Single Server, Multidimensional Database

For creating the application, we need database to store data.We may require single server or multi-server(Distributed approach) or partitioned database.

Hyperion Essbase - Designing Database

Contents : 

1. Steps to design a database
2. Analyzing and Planning
3. Identifying User Requirements
4. Planning for Security in a Multiple User Environment
5. Creating Database Models
6. Identifying Analysis Objectives
7. Determining Dimensions and Members
8. Analyzing Database Design
9. Repetition in Outlines
10. Inter-dimensional Irrelevance
11. Reasons to Split Databases
12. Checklist to Analyze the Database Design
13. Dimension and Member Properties
14. Designing an Outline to Optimize Performance
15. Loading Test Data
16. Defining Calculations
17. Accounts Dimension Calculations
18. Formulas and Functions
19. Two-Pass Calculations
20. Defining Reports
21. Verifying the Design

Following are steps to design a database.

Analyzing and Planning:

Given the size and performance volatility of multidimensional databases, developing an optimized database is critical..

• First, evaluate the source data to be included in the database.
• Carefully define reporting and analysis needs.
• How do users want to view and analyze data?
• How much detail should the database contain?
• Does the data support the desired analysis and reporting goals?
• If not, what additional data do you need, and where can you find it?
• Determine the location of the current data.
• Where does each department currently store data?
• Is data in a form that Essbase can use?
• Do departments store data in relational databases on Windows or UNIX servers, or in Excel spreadsheets?
• Who updates the database and how frequently?
• Do those who need to update data have access to it?
• Ensure that the data is ready to load into Essbase.
• Does data come from a single source or multiple sources?
• Is data in a format that Essbase can use? For a list of valid data sources that you can load into Essbase.
• Is all data that you want to use readily available?

Identifying User Requirements:

Discuss information needs with users.

• What types of analysis do users require?
• What summary and detail levels of information do users need?
• Do some users require access to information that other users should not see?

Planning for Security in a Multiple User Environment:

Here we identifying which user will get what permission.

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

Creating Database Models:

Creating database model is a process of identifying the perspectives and views which are important to a business. Which will be translated to the dimensions of the database model.

Common areas where the businesses analyse are-

• Time periods
• Accounting measures
• Scenarios
• Products
• Distribution channels
• Business units
• Geographical regions

Identifying Analysis Objectives-

After the determining the dimensions, next step is deciding through which view we going to analyse the data.

• If analysing through time, then what will be the format of the time.Weeks,Qtr or year.
• If analysing through geographical regions, then which cities , state or country.

Depending on the view data will be shown. So Deciding view is the important part of designing the database.

Determining Dimensions and Members-

When you know approximately what dimensions and members you need.

• Relationships Among Dimensions – A analyst may want to know the data in different perspectives.So relationships among dimension is important to display data in a sensible way.Also if there any classification can be done in the dimension, it can be achieved by attribute dimension.

• Checklist for Determining Dimensions and Members Use the following checklist when determining the dimensions and members of your model database:
  • What are the candidates for dimensions?
  • Do any of the dimensions classify or describe other dimensions? These dimensions are candidates for attribute dimensions.
  • Do users want to qualify their view of a dimension? The categories by which they qualify a dimension are candidates for attribute dimensions.
  • What are the candidates for members?
  • How many levels does the data require?
  • How does the data consolidate?

Analyzing Database Design-

While the initial dimension design is still on paper, you should review the design according to a set of guidelines.

Use the information in the following topics to analyze and improve your database design.

• Dense and Sparse Dimensions- Dense and Sparse dimension affects the performance of the database.
• Standard and Attribute Dimensions- Chosing standard and attribute dimensions affects the complexity of the POV.
• Dimension Combinations-
  For each combination of dimensions, ask three questions:
• Does it add analytic value?
• Does it add utility for reporting?
• Does it avoid an excess of unused combinations?

Repetition in Outlines-

The repetition of elements in an outline often indicates a need to split dimensions. The following examples show you how to avoid repetition.

Inter-dimensional Irrelevance-

Interdimensional irrelevance occurs when many members of a dimension are irrelevant across other dimensions. Essbase defines irrelevant data as data that Essbase stores only at the summary (dimension) level.

For example, TBC considered analyzing salaries as a member of the Measures dimension. But salary information often proves irrelevant in the context of a corporate database. Most salaries are confidential and apply to individuals. The individual and the salary typically represent one cell, with no reason to intersect with any other dimension.

Reasons to Split Databases-

For a organisation empolyees details are irrelevant to the other database.So spliting the database by creating the HR database will help in saving storage and time of a database.

Consider another example if a company is handling multiple time zone calculation databases, Then it will be idle to split the database. You can split the database for groups of subsidiaries in the same time zone to ensure that financial calculations are timely. You can also use a partitioned application to separate information by subsidiary.

Checklist to Analyze the Database Design-

Use the following checklist to analyze the database design:

• Have you minimized the number of dimensions?
• For each dimensional combination, did you ask:
• Does it add analytic value?
• Does it add utility for reporting?
• Does it avoid an excess of unused combinations?
• Did you avoid repetition in the outline?
• Did you avoid interdimensional irrelevance?
• Did you split the databases as necessary?

The following topics present a review of the basics of dimension and member properties and a discussion of how outline design affects performance-

Dimension and Member Properties-

The properties of dimensions and members define the roles of the dimensions and members in the design of the multidimensional structure.

Dimension Types-

A dimension type is a property that Essbase provides that adds special functionality to a dimension.

• Time
• Accounts
• Attribute
• Country
• Currency

Member Storage Properties-

You can specify data storage properties for members; data storage properties define where and when consolidations are stored. For example, by default, members are tagged as store data. Essbase sums the values of store data members and stores the result at the parent level.

Essbase storage properties-

• Store data
• Dynamic calc
• Dynamic calc and store
• Shared member
• Never share
• Lable only

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?

Designing an Outline to Optimize Performance-

Position attribute dimensions at the end of the outline. Position dense dimensions before sparse dimensions.

The position of dimensions in an outline and the storage properties of dimensions can affect two areas of performance—how quickly calculations are run and how long it takes users to retrieve information.

Use the following topics to understand performance optimization basics.

Optimizing Query Performance -

To optimize query performance, use the following guidelines when you design an outline:

• If the outline contains attribute dimensions, ensure that the attribute dimensions are the only sparse Dynamic Calc dimensions in the outline.
• In the outline, place the more-queried sparse dimensions before the less-queried sparse dimensions.
• Because the outline contains attribute dimensions, the storage property for standard dimensions and all standard dimensions members is set as store data.
• As the most-queried sparse dimension, the Product dimension is the first of the sparse dimensions. Base dimensions are typically queried more than other dimensions

Optimizing Calculation Performance-

To optimize calculation performance, order the sparse dimensions in the outline by their number of members, starting with the dimension that contains the fewest.

• The smallest standard dimension that is sparse, Market, is the first of the sparse dimensions in the outline.The largest standard dimension that is sparse, Product, is immediately above the first attribute dimension.
• If the outline did not contain attribute dimensions, the Product dimension would be at the end of the outline.

Checking System Requirements-

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

• Ensure that you have enough disk space.
• Ensure that you have enough memory.
• Ensure that your caches are set correctly.

Loading Test Data-

Before you can test calculations, consolidations, and reports, you need data in the database.

Defining Calculations-

Calculations are essential to derive certain types of data. Data that is derived from a calculation is called calculated data; basic noncalculated data is called input data.

Consolidation of Dimensions and Members-

Consolidation is the most frequently used calculation in Essbase,Consolidation operators define how Essbase rolls up data for each member in a branch to the parent.

Essbase calculates the data of a branch in top-down order. For example, if you have, in order, two members tagged with an addition (+) operator and a third member tagged with a multiplication (*) operator, Essbase adds the first two and multiplies that sum by the third.

Consolidation of Shared Members-

Shared members also affect consolidation paths. The shared member concept enables two members with the same name to share the same data. The shared member stores a pointer to data contained in the other member, so Essbase stores the data only once. Shared members must be in the same dimension. Data can be shared by multiple members.

Checklist for Consolidation-

Use the following checklist to help define consolidation:

● Did you identify the consolidations in the outline?

● Did you tag each member with the proper consolidation operator?

● Did you specify a shared member tag for designated members?

● Would shared members be more efficient if designed within an attribute dimension (other than shared)?

Accounts Dimension Calculations-

This topic discusses two forms of calculations for a dimension tagged as accounts, time balance properties and variance reporting.

Time balance properties- The first, last, average, and expense tags are available exclusively for use with accounts dimension members.

Variance Reporting-One TBC Essbase requirement is the ability to perform variance reporting on actual versus budget data. The variance reporting calculation requires that any item that represents an expense to the company must have an expense reporting tag. Inventory members, Total Expense members, and the COGS member each receive an expense reporting tag for variance reporting (@VAR and @VAPER)

Formulas and Functions-

Formulas calculate relationships between members in the database outline. You can apply formulas to members in the outline, or you can place formulas in a calculation script. This topic explains how TBC optimized the performance of its database by using formulas.

Functions are predefined routines that perform specialized calculations and return sets of members or sets of data values. Formulas comprise operators and functions, as well as dimension names, member names, and numeric constants.

Two-Pass Calculations -TPC indicates that some member formulas must be calculated twice to produce the desired value. The two-pass property works only on members of the dimension tagged as accounts and on members tagged as Dynamic Calc and Dynamic Calc and Store.

Checklist for Calculations-

Use the following checklist when you define a calculation:

● Does the default calculation logic achieve accurate results?

● Which members require formulas?

● Which members require time balance tags?

● Which members require variance reporting?

● Which members require two-pass calculation?

● Which members can be tagged as Dynamic Calc?

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. Oracle and its partners offer many tools for producing the reporting systems that users use.

Several tools can help you display and format data quickly, and test whether the database design meets user needs. You can use the Report Script Editor in Administration Services Console to write report scripts quickly. Those familiar with spreadsheets can use the Spreadsheet Add-in or Smart View (Smart View requires Provider Services).

Verifying the Design-

After you analyze the data and create a preliminary design, check all aspects of the design with users. You should already have verified that the database satisfies the users’ analysis and reporting needs. Ensure that the database satisfies all of their goals.