Creating Essbase Cube- Multidimensional Database

Hyperion Essbase Architecture:

Essbase go with three tier Architecture.

You must have seen below generic architecture diagram of Oracle Hyperion EPM (Enterprise Performance Management) , Essbase is the key components as a MOLAP database sits in the Database tier.

EPM Client/End User:

• Excel Addin- Almost going to retried 
• Excel Smartview
• Hyperion Workspace
• Hyperion Reports
• Interactive Report
• Financial Report
• SQR Production report
• Web Analysis
• OBIEE Dashboard
• Tableau Dashboard
• Several Other third part Reporting Tools.

EPM Application Layer:

• Hyperion Planning
• Out of Box Module
• Workforce Planning
• Capital Asset Planning
• Project Financial Planning
• Public Sector Planning and Budgeting
• Hyperion Financial Management
• Hyperion Financial Close Management
• Hyperion Profitability Management
• Hyperion Strategic Finance
• Integration(ETL/ELT)
• Financial Data Quality Management
• Oracle Data Integrator

Database Layer:

• Hyperion Essbase

Hyperion Essbase itself follow three tier architecture to build,load and Access data and metadata.

Essbase as 3-T

• Client
• Application
• Database

End user Stand point- They will be accessing Stand Alone Essbase cube using MS Office Integrated Smartview or legacy Essbase Excel-Addin . But from Developer Stand point Client tier consist of 

Essbase Admin Console- This is the Developer GUI to design a multi dimensional database /Essbase Cube

• Essbase Administration Server
• Essbase Server
• Provider Services(Broker between MS Office and Back end database)

In One Session of EAS Console- you can add more than one Administration Server but at one point of time only one can be connected. You can add more than one Essbase Server(Development, Test, Production etc) You can access to respective Essbase server by connecting via right Administration Server.

Security and User/Group level access to server and application controlled in Hyperion Shared Service. You can native users,groups etc and you can also configure LDAP or MS Active Directory to enable Single Sign-on to access the Installed  Hyperion product components.

Creation Essbase Cube:

We Can create two type of essbase Database- Single Currency and Multi Currency with different Storage Type Application. Maximum Char of Application name is 8.

• Aggregate Storage Application
• Block Storage Application
• Unicode
• Non-Unicode

To know the high level difference between ASO and BSO database: several blog exist but just i found this one handy and having good explanation in layman terms.

http://hyperiontools.blogspot.com/2011/02/aso-and-bso.html

One Essbase Server--> Multiple Application

One Application--> More than One Database

One Database--> One Outline(.otl file)

Creating ASO Database:

Application Name:

Create Database:

Database Name:

Click OK and database got created.

Different method to Build the Dimension - Using Essbase Build Rule, but at this point you can use the Outline how to create it manually.

Creating BSO Cube:

Right click on Application and create Block Storage Essbase App

Enter database Name: 8 char Maximum

It creates a Blank database

Within the Database- It is having a blank Outline

Open the Outline in Edit mode then you will see the toolbar menu icons will be enabled. Keep your mouse cursor on top of each icon to know how to add Child, Siblings ,changing member properties etc. I just added 6 base dimensions. then we can add members under the dimensions. Save the otline. We call it Restructure the Essbase database.

Build the members using Essbase Build rule or create Manually for practice.

In BSO cube: You will see different type of dimension

Dense - Probability of getting Data is High, Ex: Account, Period

Sparse- Probability of getting data is Low  Ex: Product, Customer

It is not always true you will get data for all product,customer combination where as it is true even if you get one product or one customer data, all the measure will get the data. So i said in above example as Account and Period is dense whereas product and customer Sparse.

Attribute - Used as a Reporting dimension for Grouping the data with certain attribute Ex: Product Type, Customer Type

Essbase Database Objects:

After we create Essbase application, these are the objects created in the back end Essbase Server in File system.

        Database Outline(.otl file)
Data Source
Rules Files(.rul)
Calculation Scripts(.csc)
Report Scripts(.rpt)
Security Definition/Filters(.sec)
Linked Reporting Objects
Partitions

Close the EAS console:

Continue reading the Next Blog to get more on Hyperion EPM 11.1.2.4 :)