Recommendations for Creating Blocks and Block Size

The recommended block size for optimal performance is between 8 KB and 200 KB. To keep BSO block size optimal, Oracle recommends that you add only the accounts used for planning and forecasting to BSO cubes. When possible, use ASO cubes for reporting by adding all reporting accounts to ASO cubes. Additionally, to streamline the block size, Oracle recommends that all upper levels in dense dimensions that are aggregations of their children be made either dynamic calc (non-store) or label only.

Blocks, generally, are created on the following actions:

• Data load
• DATACOPY
• Sparse calculations, for example, AGG or SparseMember = X * X/X;

A sparse calculation is triggered when:

• Sparse members are on the left of the equal sign (=).
• The formula is within a sparse calc member block; for example, "Budget"("Sales" = "Sales"->"Actual" * 0.95;) assuming that Scenario is sparse and Measures are dense.

Blocks may be created using the calculation commands SET CREATEBLOCKONEQ, SET CREATENONMISSINGBLK, or the calculation function @CREATEBLOCK.

Oracle recommends that these settings be used sparingly and within a tight FIX statement. Test to see if you can avoid using these statements by changing the type of calculation being performed.

When faced with an issue, first determine that it is related to block creation before using these calculation commands or function. You can determine if an issue is related to block creation by submitting a zero (0) into the target block and then rerunning the calculation.

In business rule development, always clear and reload data (instead of submitting #missing or running a clearblock or cleardata script) when testing rules that may cause a block creation issue.