The generic rule in Essbase is that calculations FIX on sparse members because sparse members are what define the number of blocks. When you want to limit the members of the block on which the calculation is executed, an IF statement is appropriate. Read more
Tag Archive for: sparse
Changes to an Essbase outline cause changes to the Essbase index and data files, regardless of the method (Essbase Administration Services, Hyperion Planning database refreshes, or from a script).
Changes that require restructuring the database are time-consuming (unless data is discarded before restructuring). Understanding the types of restructures and what causes them can help database owners more effectively manage the impacts to users.
TYPES OF RESTRUCTURES
Essbase initiates an implicit restructure after an outline is changed, whether done with the outline editor, through an automated build, or some other fashion like a Hyperion Planning database refresh. The type of restructure that is performed depends on the type of changes made to the outline.
DENSE RESTRUCTURE: If a member of a dense dimension is moved, deleted, or added, Essbase restructures the blocks in the data files and creates new data files. When Essbase restructures the data blocks, it regenerates the index automatically so that index entries point to the new data blocks. Empty blocks are not removed. Essbase marks all restructured blocks as dirty, so after a dense restructure you must recalculate the database. Dense restructuring, the most time-consuming of the restructures, can take a long time to complete for large databases.
SPARSE RESTRUCTURE: If a member of a sparse dimension is moved, deleted, or added, Essbase restructures the index and creates new index files. Restructuring the index is relatively fast; the time required depends on the index size.
Sparse restructures are typically fast, but depend on the size of the index file(s). Sparse restructures are faster than dense restructures.
OUTLINE ONLY: If a change affects only the database outline, Essbase does not restructure the index or data files. Member name changes, creation of aliases, and dynamic calculation formula changes are examples of changes that affect only the database outline.
Outline restructures are very quick and typically take seconds.
Explicit restructures occur when a user requests a restructure to occur. This can be done in Essbase Administration Services or via Maxl (and EssCmd for those of you who still use it) and forces a full restructure (see dense restructure above). It is worth noting that this also removes empty blocks.
CALCULATING IMPLICATIONS AFTER RESTRUCTURES
When a restructure occurs, every block that is impacted is tagged as dirty. If Intelligent Calculations are used in the environment, they don’t provide any value when a dense restructure occurs as all blocks will be calculated. When member names or formulas are changed, the block is not tagged as dirty.
WHAT DICTATES THE RESTRUCTURE TYPE
The following outline changes will force a dense restructure, which is the most time- consuming restructure.
DENSE AND SPARSE
- Defining a regular dense dimension member as dynamic calc
- Defining a sparse dimension regular member as dynamic calc or dynamic calc and store
- Defining a dense dimension dynamic calc member as regular member
- Adding, deleting, or moving dense dimension dynamic calc and store members
- Changing dense-sparse properties [Calc Required]
- Changing a label only property [Calc Required]
- Changing a shared member property [Calc Required]
- Changing the order of dimensions [Calc Required]
DENSE (DATA FILES)
- Deleting members from a dense dimension [Calc Required]
- Adding members to a dense dimension
- Defining a dense dynamic calc member as dynamic calc and store member
SPARSE (INDEX)
- Adding members to a sparse dimension
- Moving members (excluding shared members) in a sparse dimension
- Defining a dense dynamic calc member as dynamic calc and store
- Adding, deleting, or moving a sparse dimension dynamic calc member
- Adding, deleting, or moving a sparse dimension dynamic calc and store member
- Adding, deleting, or moving a dense dimension dynamic calc member
- Changing the order of two sparse dimensions
NO RESTRUCTURE OCCURS
- Deleting members of a sparse dimension [Calc Required]
- Deleting members of an attribute dimension
- Deleting shared members from a sparse or dense dimension [Calc Required]
- Adding members to an attribute dimension
- Adding shared members to a sparse or dense dimension
- Moving a member in an attribute dimension
- Renaming a member
- Changing a member formula [Calc Required]
- Defining a sparse dynamic calc member as dynamic calc and store member
- Defining a dense or sparse dynamic calc and store member as dynamic calc
- Defining a regular dense dimension member as dynamic calc and store
- Defining a sparse dimension dynamic calc and store member or dynamic calc member as regular member
- Defining a dense dimension dynamic calc and store member as regular member
- Changing properties other than dense-sparse, label, or shared [Calc Required]
- Changing the order of an attribute dimension
- Creating, deleting, clearing, renaming, or coping an alias table
- Importing an alias table
- Setting a member alias
- Changing the case-sensitive setting
- Naming a level or generation
- Creating, changing, or deleting a UDA
WHAT DOES THIS MEAN
Understanding this can help users and administrators manage applications to better meet the needs of all those involved. When designing an application, knowledge of this topic can be instrumental in the success of the application. Here are some things to keep in mind.
- When updating an outline or refreshing a planning application, it may be faster to export level 0 (or input level) data, clear the data, perform the update, and reload/aggregate the export when changes cause a dense restructure.
- For dimensions that are updated frequently, it may be beneficial to define those dimensions as sparse. Changes to sparse dimensions typically require only restructures to the index file(s), which are much faster.
- If frequent changes are required, enabling incremental restructuring may make sense. Using this defers dense restructures. The Essbase restructure happens on a block by block basis, and occurs the first time the data block is used. The cost is that calculations will cause restructures for all the blocks included and the calculation performance will degrade.
- Setting the isolation level to committed access may increase memory and time requirements for database restructure. Consider setting the isolation level to uncommitted access before a database restructure.
- If multiple people have access to change the outline, outline logging may be useful. This can be turned on by adding OUTLINECHANGELOG = TRUE in the essbase.cfg.
- Monitoring progress of a restructure is possible when access to the server is granted. Both sparse and dense restructures create temporary files that mirror the index and data files. Data exists in the .pag files while indexes are stored in .ind files. As the restructure occurs, there are equivalent files for each (pan for data files and inn for index files). In total, the restructure should decrease the size of the ind and pag files, but the pan and inn files can be used for a general idea of the percent of completion.
Users of Essbase have some control over the performance of a database and how responsive it is when retrieving data. With a basic understanding of how Essbase stores data, users can optimize performance by changing the order of the dimensions and members in a report.
It might be helpful to read our article on sparse and dense dimensions. Here is a brief overview:
An Essbase database is comprised of thousands, if not millions or billions, of data blocks. Each block of data, and its size, is defined by the dense dimensions in the Essbase outline. The volume of blocks is dictated by the unique combinations of sparse dimension members. If Time and Accounts are dense, each block created would hold all the months for every account. If Organization and Product are sparse dimensions, there would be a block for each unique combination of Organization and Product. A block would exist for Center 10 / Product A, as well as Total Organization / Total Product. If the outline has 20 members in Organization and 15 members in Products, the database could have up to 300 independent blocks.
If a report is written to show an entire income statement for all 12 months for Total Product and Total Organization, how many blocks would have to be queried? Remember, there is a block for each unique member combination of Organization and Product. The answer is one, because there is a block for Total Organization/Total Product that includes every account and every member in the time dimension.
How many blocks would be accessed if a report pulled Total Sales (a member in the Accounts dimension) in January for every product? Since the Product dimension is sparse and there are 15 products, 15 blocks would have to be opened to return the results.
Here is where your understanding of what sparse and dense represents will help you improve your reports. Opening a data block, reading the contents, and closing it, is similar to opening, reading, and closing a spreadsheet. It is much faster to open one spreadsheet, or block, than 15 spreadsheets. So, if the database retrieves are written in such a way to minimize the number of blocks that need to be accessed, or the order in which they are accessed, performance can improve.
I will agree that if data for all 15 products is needed for the report, all 15 blocks have to be opened. There is no way around that. That said, often times users will build one worksheet for income statement and one worksheet for balance sheet. This means that the report is making two passes on the same blocks. In theory, it takes twice as long to open/read/close a data block 2 times than it does once. It is faster to have the income statement and the balance sheet accounts in one worksheet, which only makes one pass on the required blocks. One worksheet for Income Statement and one for Balance Sheet can be created with cell references to the worksheet that has the retrieved data, if 2 separate reports are required.
I frequently see another example of a report requiring multiple passes to the same data block. Using our example dimensions above, assume product information is required in a report for multiple accounts.
| Jan | Feb | Mar | ||
| Income | Product A | |||
| Income | Product B | |||
| Income | Product C | |||
| Income | Product D | |||
| Expense | Product A | |||
| Expense | Product B | |||
| Expense | Product C | |||
| Expense | Product D |
The Essbase retrieve above would start from the top of the spreadsheet and move down the rows to retrieve the data from Essbase. This cycle would open the Product A block, then B, C, and D, and retrieve the associated income for each. It would then have to reopen the same 4 blocks to access expenses.
The following example, again going from top to bottom, would access both income and expense while the block is open. The way this retrieve is setup, it eliminates the need to access the same block multiple times, yet still pulls the required information.
| Jan | Feb | Mar | ||
| Income | Product A | |||
| Expense | Product A | |||
| Income | Product B | |||
| Expense | Product B | |||
| Income | Product C | |||
| Expense | Product C | |||
| Income | Product D | |||
| Expense | Product D |
These examples are very small. In a real world example, a report of this size would not produce significant variances in the time it takes to retrieve them. Users often have spreadsheets that are hundreds of rows long and take minutes to retrieve. In these situations, eliminating the need to access the same block multiple times can produce notable improvements in the time it takes to retrieve data from Essbase.
With a basic understanding of how your database is setup, users of Essbase can help themselves with some simple changes to the format of the retrieve worksheet. If access to the dimension properties in your database is unavailable, ask your system administrator to supply them for you.
When I am introduced to business segments that use Hyperion Essbase, I always get asked the same question: “Can you explain what sparse and dense mean?” Although I agree that users don’t HAVE to understand the concept, I contend that it is extremely valuable if they do. It will not only help them become more efficient users, it goes a long way in helping them understand why something simple in Excel isn’t always simple in Essbase. If users understand what a block is, and what it represents, they have a much better experience with Essbase.
If you are a relational database developer or a spreadsheet user, you tend to view data in 2 dimensions. An X and Y axis is equivalent to the rows and columns in your spreadsheet or database table. Essbase is a little different in that it stores data in 3 dimensions, like a Rubik’s Cube, so it has a Z axis. Essbase databases refer to these “Rubik’s Cubes” as blocks. An Essbase database isn’t one giant Rubik’s Cube; it could be millions of them. The size and number of possible blocks a database has is determined by the sparse/dense configuration of the database.
An Essbase outline has a number of dimensions. The number of dimensions can range in quantity and size, but each dimension is identified as a dense or sparse dimension. The dense dimensions define how large each block will be in size (the number of rows, columns and the depth of the Z axis). The sparse dimensions define the number of possible blocks the database may hold. Assume the following scenario: a database exists with 3 dense dimensions and 2 sparse dimensions. The dense dimensions are as follows:
Net Income
Income
Expenses
Qtr 1
Jan
Feb
Mar
Version
~ Actual
~ Budget
~ Forecast
Remember, the dense dimensions define the size of blocks. These dimensions would produce a block that looks like the image below. Every block in the database would be the same.
For those more knowledgeable with Essbase design, this example assumes that no member is dynamically calculated or is tagged as a label to reduce complexity.
The sparse dimensions are below.
Total Product
Shirts
Pants
Total Region
North
South
East
West
The unique combinations of each sparse dimension has its own block. There will be a block for Pants – North, one for Shirts – North, and so on. Since there are 3 members in the Total Products dimension and 5 members in the Total Region dimension, there will be a total of 15 (3 x 5) blocks. If a database has 5 sparse dimensions, all with 10 members, it would have a total possible number of blocks equal to 100,000 (10 x 10 x 10 x 10 x 10). Below is a representation of the possible blocks for Shirts.
When I am introduced to business segments that use Hyperion Essbase, I always get asked the same question: “Can you explain what sparse and dense mean?” Although I agree that users don’t HAVE to understand the concept, I contend that it is extremely valuable if they do. It will not only help them become more efficient users, it goes a long way in helping them understand why something simple in Excel isn’t always simple in Essbase. If users understand what a block is, and what it represents, they have a much better experience with Essbase.
If you are a relational database developer or a spreadsheet user, you tend to view data in 2 dimensions. An X and Y axis is equivalent to the rows and columns in your spreadsheet or database table. Essbase is a little different in that it stores data in 3 dimensions, like a Rubik’s Cube, so it has a Z axis. Essbase databases refer to these “Rubik’s Cubes” as blocks. An Essbase database isn’t one giant Rubik’s Cube; it could be millions of them. The size and number of possible blocks a database has is determined by the sparse/dense configuration of the database.
An Essbase outline has a number of dimensions. The number of dimensions can range in quantity and size, but each dimension is identified as a dense or sparse dimension. The dense dimensions define how large each block will be in size (the number of rows, columns and the depth of the Z axis). The sparse dimensions define the number of possible blocks the database may hold. Assume the following scenario: a database exists with 3 dense dimensions and 2 sparse dimensions. The dense dimensions are as follows:
Net Income
Income
Expenses
Qtr 1
Jan
Feb
Mar
Version
~ Actual
~ Budget
~ Forecast
Remember, the dense dimensions define the size of blocks. These dimensions would produce a block that looks like the image below. Every block in the database would be the same.
For those more knowledgeable with Essbase design, this example assumes that no member is dynamically calculated or is tagged as a label to reduce complexity.
The sparse dimensions are below.
Total Product
Shirts
Pants
Total Region
North
South
East
West
The unique combinations of each sparse dimension has its own block. There will be a block for Pants – North, one for Shirts – North, and so on. Since there are 3 members in the Total Products dimension and 5 members in the Total Region dimension, there will be a total of 15 (3 x 5) blocks. If a database has 5 sparse dimensions, all with 10 members, it would have a total possible number of blocks equal to 100,000 (10 x 10 x 10 x 10 x 10). Below is a representation of the possible blocks for Shirts.