You are here: Blog Hyperion Building a OLAP Cube using Essbase Studio Tool

Building a OLAP Cube using Essbase Studio Tool

Written by  RV
Rate this item
(18 votes)

 

Objective: Overview of Essbase Studio Tool and building of OLAP Cube using Essbase Studio Tool with the presumption that the reader having basic understanding on Data warehousing concepts and Hyperion Tools


Overview of EssbaseStudio Tool:

In 2007, Oracle acquired Hyperion, a leading provider of Performance Management Software to provide business users Oracle BI end-to-end solution.

In general, Essbase Application will be created from different data sources i.e.; it could be text files or Relational Data sources having many dimensions which build with many rule files and sql procedures. Essbase Studio Tool which provides users with a graphical interface to develop, deploy, and maintain Essbase OLAP cubes based on one or more data sources.
            Essbase Studio is the new emerging tool meant for Cube Modeling and will deploy the cube physically Essbase Administration Services Console. Essbase Studio Tool will become a future replacement of Essbase Integration Services [Cube Modeling + Drill-Through Reports] and Essbase Administration Services Console [Cube Building using Rule Files].


Essbase Studio Architecture:

Just for Knowledge Purpose, Here is the Essbase studio Architecture

Preferred Version for Essbase Studio:

We can download the Essbase Studio software from the Oracle E-delivery website.

 http://edelivery.oracle.com/

http://www.oracle.com/technetwork/middleware/epm/downloads/index.html

In the following image, we have selected Oracle Enterprise Performance Management System as the Product Pack. In our case, the system is 32 bit. Hence, we select Microsoft Windows (32 bit).


Files Required:

Hyperion Product

Part Number

Oracle Hyperion Enterprise Performance Management System Installer, Fusion Edition Release 11.1.2.1.0 for Microsoft Windows (32-bit)

V25454-01

Oracle Hyperion Foundation Services Release 11.1.2.1.0 for Microsoft Windows (32-bit) Part 1 of 7

V25455-01

Oracle Hyperion Foundation Services Release 11.1.2.1.0 for Microsoft Windows (32-bit) Part 2 of 7

V25456-01

Oracle Hyperion Foundation Services Release 11.1.2.1.0 for Microsoft Windows (32-bit) Part 3 of 7

V25457-01

Oracle Hyperion Foundation Services Release 11.1.2.1.0 for Microsoft Windows (32-bit) Part 4 of 7

V25458-01

Oracle Hyperion Foundation Services Release 11.1.2.1.0 Part 5 of 7

V25459-01

Oracle Hyperion Foundation Services Release 11.1.2.1.0 Part 6 of 7

V25460-01

Oracle Hyperion Foundation Services Release 11.1.2.1.0 Part 7 of 7

V25461-01

Oracle Essbase Release 11.1.2.1.0 Part 1 of 2

V25464-01

Oracle Essbase Release 11.1.2.1.0 Part 2 of 2 for Microsoft Windows (32-bit)

V25465-01

Oracle Essbase Clients Release 11.1.2.1.0

V25466-01

Oracle Essbase Spreadsheet Add-in Release 11.1.2.1.0 for Microsoft Windows

V25467-01

Oracle Hyperion Enterprise Performance Management System Additional Content Release 11.1.2.1.0

V25494-01

Check Points before building the cube:

After Installation, Verify that the required products installed or not.

Also Ensure that services are up and running fine.

Path to run the services manually:

Since we are referring to Windows as the platform where Essbase Server is installed. Following is the path to run the services manually if they encountered any issue.


Steps for Cube Modeling in Essbase Studio:

  1. Create a Data source to fetch data
  2. Create a Mini Schema from the existing Data source to form a Star Schema
  3. Form a Star Schema by joining tables with their relationships
  4. Identify the dimension elements that  requires to create hierarchies
  5. Assign the key binding properties
  6. Create the Dimensions and Measure Hierarchies
  7. Create a Cube Schema and Cube Schema Model
  8. Assign the Essbase Properties
  9. Deploy the Logical Cube Model from Essbase Studio to EAS Console
  10. Generate the reports or Dashboards from Client Software(Excel-Addin,SmartView or OBIEE)

1. Easiest Way to login is following path

Start-> All Programs ->Essbase-> Essbase Studio -> Essbase Studio Console

Logon to Essbase Studio

Datasources can be of any type, it could be Essbase, OBIEE, Relational or Flat Files. Here we are building the Essbase cube using SQL server 2005 as relational Source.

Create a Data Source Connection for Essbase Server to deploy the Essbase cube model in Essbase Administration Services Console.

For that, Right Click on Data sources->New->Data source

It will open the connection wizard. Make sure you select Data Source type as “Essbase Server” and provide the valid credintials.Click on “Test Connection” for Authentication Successful.

It will use Default port as 1423 and it will be editable if it is on different port.

Let’s create a Data Sources connection for Relational Database. Here I am planning to build the cube on TBC Sample Database which comes with Essbase Installation.

Before that, run the Scripts which was available in the following path

Select the Data Source type as “Microsoft SQL Server”. Give the valid credentials and select the database

Select the required tables use to build DWH from left pane to right pane in the window

 2. Select “Create a new schema diagram” and click on Next Button. This is create the Minischema

Purpose: We will keep in all the required tables in one box from different data sources

Follow Navigate Process Just Click on “Next” and Finish. Don’t select any option for “Introspection” which will explain you in next article in detail.

 3. If the relationship between dimension and facts already exists in database then it will appears with imported table joins as follows

 

If we want to create join between two tables. Below Image explains join creation between Sales and Market Tables.

Right Click on Sales Table -> Add Join

 

 

Select the PK and FK from Sales and Market Tables and select the join type. If you want to display the dimensional (Market) data that doesn’t have fact data you can go for outer join otherwise usually inner join will be preferred.

 

 

If you observe the TBCSample Database, Measures Table having Parent and Child columns which represents recursive hierarchies. For that need to create Self-Join for the Measures Dimension

 

 

Select Parent Column from Measures Dimension and select child column from Measures Dimension and click on “OK”

 

 

4. Create a User-Defined Folder under “root” of “Metadata Navigator” Pane
Right Click on “Root” folder -> New -> Folder

 

Generally we defined 4 folders for each Essbase Cube Model

a.      Dimensional Elements

b.      Hierarchies

c.       Cube Schemas

d.      Drill-through Reports

Let’s create a two hierarchies and a Fact Measures

Drag the Required Dimensional Elements from left pane of “MinSchemas” to “Dimensional Elements” of left pane window

 

5. Since the database follows Kimball Methodology, We can assign surrogate key to “Scenario” Dimension as key binding. Same will follow for remaining tables.

We can see the advantage of this key binding mostly for “Duplicate Member Name Support” databases as well as Slowly-Changing Attribute Dimensions Scenarios.

Right Click on Scenario Dimension Element, Click on Edit

Here in key binding we will be having 3 options. For now we will assign the surrogate key “ScenarioID” as Advanced Key binding. Click on “OK” button.

6. Let’s Create the Scenario Dimension. Right Click on Hierarchies Folder->New->Hierarchy

 

Drag the required Dimension elements to form Hierarchy.

Similarly, Let’s Create the Measures Dimension by taking parent and child columns for Dim_Measures Hierarchy.

Now Create a Measure Hierarchy that contains Fact or Measures.

Click on Hierarchies Folder. Right click on Hierarchies -> New-> Measure Hierarchy

 

In this Scenario, Amount is the Measure.

7. Let’s create a Cube Schema for the created Hierarchies

Drag the Metadata or Dimensional hierarchies to “Hierarchies” folder and Facts to “Measures/Measures Hierarchy”

Click on Next and Check the “Create Essbase Model” checkbox to create Essbase Model.

Click on finish. Automatically Essbase will create the Logical Model

8. Lets Assign the Essbase Properties to Cube Model

Select the Essbase properties whether it is ASO or BSO Cube, Attribute Dimensions, and Consolidation Operators etc

Essbase Studio is so smart it can automatically identify table’s distinction and will generate the SQL Query for Data loading. We also customize the SQL query generated by Essbase Studio by clicking on “Customize Data Load Settings”.

Verify if there is any error or warnings of the cube

9.  Deploy the cube to Essbase Administration services console

Right click on Cube Schemas -> Cube Deployment Wizard

Give the valid Essbase Application (must be <8 characters) and Database with the Essbase connection which we created earlier.

In Cube Deployment Options, We can select number of options,

Build Outline-> Only Dimensional Loading

Load Data -> Fact Data Loading

Build Outline and Load Data -> Dimensional and Fact Data Loading

Add to Existing Data -> Can be used for Incremental build

Overwrite Existing Data -> Used for Full Load

Essbase Studio will automatically generate the scripts which will be used in Essbase Cube Loading Automation. We can find this option under “Scheduling Options -> Save as Maxl Load Script”. Click on “Finish” Button.

From 11.1.2 it will shows the dimensional and data loading progress as well as error file location if there any during Cube Loading.


Once the cube deployment is successful, we will able to see the cube existence physically with the defined application name in Essbase Administration Services Console. Following is the path to navigate the EAS Console

We can view the Dimensional data. Right Click on Outline under “Cube Database”->View 

10. Essbase-Excel Add-in comes with Essbase Installation.

Here I am trying to show you another Reporting tool called Smart View which will appears in Excel Sheet only after Smart-View Installation

Top of the Excel sheet you will be able to see Smart View

Smart View-> Connect. On the left pane you will able to see “Shared Connections”. Connect to Essbase Server with valid Credentials and click on Essbase Cube and click on “Adhoc Analysis” which will appear on right hand down corner of Excel sheet.

You can analyze the data by changing the hierarchies Selection in Pivot Table. You can do slice and dice, Drill-down, Drill-up and many features with Smart View + Essbase Cube


'If you found this article useful, please rate the same"

 

Read 16094 times