A common question that we get asked in our Fusion Financials Training at apps2fusion.com is ....Why did Oracle decide to store General Ledger balances in Essbase ?
Essbase is a multidimensional database software that can store values for facts against the various dimensions. The values stored have to be pre-aggregated at various levels. For example, if a Pencil is purchased for $1 and a stationary is purchased for $2, the Cost Controller is usually interested in asking ...where was my $3 spent, and the answer he wants is ...it was spent on Office Stationary Expenses. Now, you can either at runtime summarise the $1 and $2 in a SQL query or store pre-aggregated values in some repository. This repository is called the cube. Another question that might be asked is how much Stationary expense was made in month of March and how much in April. For this, the Essbase cube will contain another dimension named Month or Period.
You may wonder why over-engineer with Essbase when you can build materialised views or run SQL statements with aggregation? Hmm of course you can, but we are talking about the enterprise platform that will have millions of transactions being fed into Oracle Fusion General Ledger via the Fusion Accounting Hub. Therefore to accelerate reporting, Oracle Fusion General Ledger preaggregates balances at every possible summarization level across each dimension of the chart of accounts and accounting periods so that retrieval is quick. During the hands on trainings in apps2fusion.com we do cover the details of how to interrogate the Essbase databsae in Fusion Financials Training.
If you are new to multi-dimensions, you may ask, what is a dimension? Well, a dimension is data category used to organize business data for query and storing of values. For example Account Type is a dimension and so is a GL Period. The Account Type in our example will contain a value of Office Expense. As evident from our example, Dimensions usually contain hierarchies of related members grouped within them. Another example is , in a time dimension likely members would be year, quarter, month, weeks and days.
Dimensions can be sparse or dense. Sparse dimensions do not have data values for the majority of its members. An example would be revenue in which an entry only exists if customer a buys product x, which may only happen once a month or twice a year.
Dense dimensions are characterized by high data volumes, which mean that they have data values for the majority of its members. A classical example is warehouse stock. For each day in the year a stock of product x exists leading to high data volumes.
For someone with relational database experience, they are new to the term outline. Think of outline as a table with a set of columns. In case of Essbase, it is a cube with a set of dimensions. Data is stored in the Essbase according to the structure defined in the outline. The outline is the structure of the multidimensional database, including for example all dimensions.
Now in Fusion Financials database tables, how do you know table stores the linkage of information in Fusion Applications General Ledger with Essbase. Well, when the Chart of Accounts is defined then it is pushed to Essbase and the Cube creation takes place. After that, the process updates the table GL_BALANCES_CUBES. All cubes and their connection information are stored in this table.
In essbase you also have something known as the rules files which are nothing but the schema files for the definition of the cube. There is one rules file per Calendar and Chart of Accounts combination.
For the Essbase lovers, you must note that Fusion Applications only uses cubes with ASO, i.e. Aggregated Storage Option.
For migrating Essbase outline, this happens automatically in Fusion when you migrate Key Flexfield. However in pure Essbase speak, to move outline from one envoronment to anothether, you can do so just as you move database tables from one DB to another. Likewise, you can export the outline from the source server by
1) Logging in to Essbase Administration Console (EAS).
2) Opening the outline in Edit mode.
3) Go to File->Save As
4) Select the tab "File System" and save the outline on the local machine.
Import the outline on the target server:
1) Login to Essbase Administration Console
2) Go to File->Open
3) Select the outline (.otl) from the file system. This will open the outline file in EAS.
4) Go to File-> Save As.
5) Select the tab "Essbase Server".
6) In the Look in dropdown, select the application and database.
7) Select the outline and click on the Ok button. It will prompt for confirmation to replace the existing file. Select Yes. This will overwrite the previous outline.
Migrating outlines to Essbase installations on different machines can also be accomplished using the EAS Migration Wizard.
Apps2Fusion Articles 