What do I need to know to understand Essbase data and cube operations?
Thank you to Jane Story for her presentation on this! Refer to the presentation for detailed screenshots and information!
Understanding Essbase Data
- The Magic of Essbase, ASO and BSO? – Quick Review.
- Data in and out of Essbase – One rule
- Reporting data out of Essbase – Basics of a report/form/retrieve
- What is a Restructure? – Basics of Implicit and Explicit Restructures
- Operational Guidelines – data? – Member properties, dense dynamic XREF, etc.
- Where can I find information about my data? – Activity Reports, etc.
The Magic of Essbase, ASO and BSO?
Data in and out of Essbase
Loading and retrieving data requires these items to load or retrieve data from a cell for both ASO and BSO:
1. Members:
- Members exist in both ASO and BSO.
- Members are grouped into dimensions.
- Within dimensions, members are structured in hierarchies.
- Level0 refers to the bottom of those hierarchies.
- The member (or alias) name within Standard Dimensions (non-attribute) are used to load and retrieve data.
- Note: It is possible to load data at lev0 only for ASO.
2. Dimensions:
- Data is only stored against members in standard dimensions (non-attribute dimensions) in both BSO and ASO.
- Attribute dimensions are not used to load data and are optional to retrieve data.
3. To load and retrieve data in Essbase, there is ONE RULE:
- For every data item, one member from every standard dimension must be included in the load/retrieve.
Reporting data out of Essbase
Anatomy of a Retrieval – Terminology – ROW, COLUMN, PAGE, POV
Every Smart View retrieve and Planning form has the following components:
- ROW
- COLUMN
- PAGE
- POV
Refer to presentation for detailed screenshots.
What is a Restructure?
What is a BSO cube Restructure?
From an Essbase perspective, each BSO Planning cube has 2 main Essbase parts which may be altered by a refresh and/or restructure:
- Metadata, such as dimensions, members, structures, hierarchies. These are stored in an Essbase outline (or OTL file)
- Data is stored in two parts:
- Blocks (consisting of a combination of DENSE members) (PAGE file)
- Index to those blocks (a new block for every SPARSE combination) (IND file)
There are two types of Restructure, depending on the way they are requested/invoked.
- Implicit: An implicit restructure occurs when dimension changes are pushed from Planning to Essbase via a cube refresh and those dimension changes require a restructure to be executed.
- Explicit: An Explicit restructure occurs when a user specifically asks for the data within the cube to be restructured i.e. via Jobs, Calc Manager etc. An explicit restructure always fully rewrites Outline, IND and PAG.
Implicit Restructure
When changes are made in Planning to dimensions/members/member properties etc., these are “pushed” to Essbase in a DB refresh. In this case, the data restructure has been requested Implicitly, i.e., dimension changes mean the restructure has to happen.
The Db Refresh implicitly triggers a restructure in Essbase to update the Essbase elements.
There are 3 levels of restructure:
1. Outline only – The outline will ALWAYS be restructured, every db refresh, even if no data. A data restructure can be “index only” (low impact) or “index and block” (full/high impact) restructuring or not required.
2. Low impact, Index only – if the dimension changes only affect indexing, only the index file (IND) will be restructured. PAG/Data blocks will not be restructured.
3. High Impact – If the changes affect the data/blocks (e.g. moving a sparse member, adding/deleting a dense member) then a full data restructure (IND + PAG) will also implicitly triggered.
Please note:
- A low or high impact restructure can only occur if there is data in the cube.
- An implicit restructure (i.e. via db refresh) will NOT remove #missing blocks.
- A db refresh is executed for all cubes in one operation.
- Time taken for restructure is seen to be relative to the size of the artifacts being restructured AND time since data had last full restructure (i.e. how much re-ordering has to occur in the data blocks).
Explicit Restructure
- A user can also explicitly trigger a Restructure for an individual cube via a Job or via Calc Manager, i.e., they explicitly request that the data blocks are re-written.
- An explicit restructure will NOT push member changes from Planning to Essbase.
- It will ALWAYS re-write the OTL, IND and PAG files.
- An explicit restructure WILL remove #missing blocks.
- An explicit restructure is executed on an individual cube.
Summary of Restructure:
- A db Refresh is a push of changes from Planning to Essbase and will trigger an Implicit Restructure in Essbase
- A user can execute an Explicit Restructure, independent of member/dimension changes and this will execute a full Essbase restructure.
- Only an Explicit Restructure will remove #missing blocks (or blocks tagged for delete by a CLEARBLOCK calc).
What is a ASO Db Refresh
In ASO, there is no concept of blocks. However, some dimension changes will trigger re-indexing of the cells on disk and/or an outline re-write. When this happens, the ASO db refresh may take longer.
- Compact Outline – this operation explicitly restructures the ASO outline.
- Db refresh – Note: this can possibly be impacted by incremental slices and aggregate views if re-indexing is required. Merging incremental slices before a db refresh may help. Same with dropping aggregate views and re-creating after db refresh. Some changes may require the outline to be re-written and this can have a time impact if this occurs.
Operational Guidelines – Avoiding Unnecessary Data
Do not create data you do not need:
- Zeros – Zeros are data values, so if they are loaded to the cube, they will potentially aggregate and make the cube unnecessarily larger. Therefore, it is generally recommended to not load them or, if they exist, to remove them periodically using a business rule to change the zero to a #missing which can then be removed with an explicit restructure.
- Unary Operators/Data Storage – Review member unary operators carefully to ensure that data that is not required at upper levels is not being created, e.g., do not always use addition, if all children are ignore/never, consider use of Data Storage of Label only.
- Difference between IGNORE and NEVER
- IGNORE will stop aggregation up the dimension the member is in but other dimensions will still aggregate a value.
- NEVER will stop aggregation up all dimensions.
Note: Smart Lists, Stored %, Text Members. etc should be set to NEVER.
Refer to Operational guidelines for more information.
Where can I find information about my data?
Activity reports are created daily during the maintenance window and provide a lot of information about the previous day’s activities.
- Navigator->Overview->Activity Report (tab)-> View or Download