Essbase “Virtual Cube” BSO Tuning

Essbase “Virtual Cube” BSO Tuning

An Essbase “virtual cube” is a specific design setup where transparent partitioning is used and consists of multiple source databases that are partitioned to a single target Block Storage (BSO) database. The single target BSO database is empty meaning contains no data. The purpose of this design setup is so that end users have one consolidated BSO cube for reporting. In some cases, the target BSO database may have different dynamic calc member formulas then the source databases. Also, the target BSO database may have more dimensions to accommodate the source databases and may have different sparse/dense configuration since source databases are tuned with data accordingly.

The Essbase Database Administration Guide mentions how to tune Essbase BSO databases that contains data; however, there is no documentation on how to tune a BSO database that contains no data like in a “virtual cube” setup. For the most part, the assumption is that little to no tuning is needed with “virtual cubes” because there is no data in the cube. However, we have found from past experience that query performance against an empty target BSO cube may need basic outline tuning i.e. sparse/dense configuration changes.  Since there is no data in the target BSO cube meaning IND/PAG files do not exist and there are no data blocks, “virtual cube” tuning is limited since we cannot rely on statics that are based on data existing in the database. Since we cannot rely on statically data points in this case, the following are some “virtual cube” tuning considerations.

Sparse/Dense Configuration (Dense Block)

In most cases, the virtual target BSO database outline is created based on one of the existing source database outlines. Existing source database outlines sparse/dense configurations are derived from the tuning based on the data distribution for that given database, which may not general best practices where the Accounts and Time dimensions are dense and the outline is in the hour-glass dimension order. Here is an example of what a virtual target BSO outline might have if derived from an existing source database outline:

Dimensions

Type

Total Number of Members

Stored Members


Time

Dense

17

12

<--Dynamic Calc Formulas

Drivers

Dense

230

230


Scenario

Sparse

15

7

<--Dynamic Calc Formulas

Accounts

Sparse

500

250

<--Dynamic Calc Formulas

Version

Sparse

25

25


Years

Sparse

6

5


Currency

Sparse

12

12


Product

Sparse

3000

2500


CostCenters

Sparse

5000

4500


Entities

Sparse

10000

9500


The dense block size for the above target BSO outline is roughly 22 KB with Time and Drivers as the dense dimensions. There are sparse dynamic calculations in the above outline with the Accounts and Scenario dimensions set to sparse.

Here is the same virtual target BSO outline with general outline best practices applied:

Dimensions

Type

Total Number of Members

Stored Members


Accounts

Dense

500

250

<--Dynamic Calc Formulas

Time

Dense

17

12

<--Dynamic Calc Formulas

Scenario

Dense

15

7

<--Dynamic Calc Formulas

Years

Sparse

6

5


Currency

Sparse

12

12


Version

Sparse

25

25


Drivers

Sparse

230

230


Product

Sparse

3000

2500


CostCenters

Sparse

5000

4500


Entities

Sparse

10000

9500


The dense block size for the above virtual target BSO outline is roughly 164 KB with Accounts, Time and Scenario as the dense dimensions. All of the dynamically calculated formulas are now in the dense block, as opposed to being sparse calculations. The query performance of this virtual target BSO outline will far exceed the performance of the previous outline.

Using the above customer test case outlines, a 10 user simulated test case scenario had failures and unacceptable query performance with the original outline configuration. The same testing scenario with the tuned virtual BSO outline configuration could ramp up to 25 users with no failures and acceptable query performance.

The virtual target BSO outline tuning changed the following:

 <!--[if !supportLists]-->1. <!--[endif]-->The number of requests from the target to the sources databases was reduced

<!--[if !supportLists]-->2. <!--[endif]-->The size of each request increased

<!--[if !supportLists]-->3. <!--[endif]-->The total number of cells queried remained the same.

By including the dynamic calculations in the dense block, the query layer processed fewer requests but the queries are extracting data in bigger pieces; Thereby, improving performance by reducing I/O traffic.



Comments:

Post a Comment:
  • HTML Syntax: NOT allowed
About

A blog focused on Tips & Tricks about Oracle Business Intelligence (OBI), Oracle Exalytics and Oracle Enterprise Performance Management (EPM) products.
[Blog Admin: ahmed awan]

Search

Archives
« April 2014
SunMonTueWedThuFriSat
  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today