GENERAL CONSIDERATIONS
Without going into overly theoretical considerations, one must consider that there is no such thing of a good or bad design when it comes to a planning model. Just as an illustration, some models with large volumes when it comes to number of dimension members and records can generate good response time while others, with limited dimensions, can alternatively generate performance concerns.
Overall, tuning a model requires to look at several aspects. Please note that one essential aspect of this topic, namely calculations, has its own dedicated guide as this topic has obvious do’s and don’ts.
Below is a list of several variables that have an impact to query response time:
Please note that there is no individual threshold for a single variable above. The way to view this is that all these variables cannot be measured irrespective of the others. As an example, if a model has a limited number of members, this can allow for more complex formulas like exception aggregations. The opposite is also true.
In other words, the number of shades based on the many variables above is somewhat endless.
As an illustration, below’s chart is depicting 2 very different types of models:
The above blue outline model, despite its limited dimensionality, is processing intensive as it has numerous on the fly calculations and specific aggregations.
As opposed to that, the orange outline model has larger dimensions but fewer real time calculations.
There is an infinite number of model designs that could bring variations to the examples above (which do not intend to set any sort of boundary, but are purely illustrative of the number of variables that must be taken into consideration).
Generally speaking, it can be considered as a good practice for customers to draw their models in a chart similar to the above, in order to understand better how models stand in terms of complexity.
As a result, multiple criteria must be considered in order to improve performance. They may sometimes appear quite straight forward. The model layout which will give the best results is generally not predictable. However, there are some pitfalls that you may want to review. This guide will therefore provide a checklist of all items you may want to review when an acceptable performance threshold is passed.
MODEL HEALTH CHECKLIST
1. Dimension granularity validity check
One should assess whether the granularity of each dimension is actually required for the planning process. As a matter of fact, due to their nature, G/L actuals may often hold a huge amount of details which is not required for planning.
Workaround: An alternative design could also be to load detailed actuals for analytics purposes (if at all necessary) in a dedicated model, and then leverage a more condensed planning model for Budgets, Plans and Forecast. Alternatively, transaction details my continue to reside in the source system, and a drill-through process can be used from within the SAC story to see the additional details when required.
2. Dimension count
In additional to dimension granularity, which is typically concerned with the number of dimension members and depth of a hierarchies within dimensions, the number of dimensions within a model is also important. Excessive dimensionality makes overall model navigation more challenging, can create additional complexities for data entry (e.g. data validation), and often results an unnecessarily large and sparse data models. It is advisable then to try and keep planning models to between 8-12 dimensions (inclusive of standard dimensions such as account, date, and version). Where models exceed this recommendation, use of alternate hierarchies, navigational attributes, and commenting (via column comments) should be evaluated by the solution designer to rationalize the dimension count.
3. Public / Private version data load
When starting a data input session for the first time, public data is – behind the scenes – cached to a dedicated private version for that user. This may result in huge amount of data being copied.
Workaround: a private version can be created beforehand, with the appropriate filters, so that only a subset of data is copied but not all. Working with such a private version will result in very noticeable performance gains when entering new data.
As a general recommendation, one must insist that every new private version must be created with filters. Then, the more discriminating these filters, the better.
4. Account formulas
On the fly calculations provided by account formula is an extremely powerful feature. However, this requires processor intensive cube calculations, especially when formulas are either numerous, or with nested calculations (one formula depends on the result of another formula, and so on). As a result, performance may be impacted by such circumstances.
Workaround: You can consider using Advanced Formulas, within data action, which are not executed in real-time, but only at user request. This may prove to reduce the overall system burden (see also the calculations guide in this series).
5. Exception aggregations
A natural behavior of a multi-dimensional engine is to sum up data along multiple hierarchies. If you think about it, the number of aggregations performed by the engine in any model is quite phenomenal. Suppose 2 dimensions with hierarchies that are 9 levels deep (with 10 leaf members). This already creates 100 potential aggregations. Fortunately, the SAP HANA database that SAP Analytics Cloud is leveraging can perform these calculations at the speed of light. However, when it comes to specific aggregations, such as averages or min/max values, etc.. these calculations require not only that standard aggregations are performed, but also specific aggregations get calculated in a second pass. This will result in a performance hit.
Workaround: some of these calculations may be moved to advanced formulas.
6. Zeros
Zeros can actually be stored and are different from null values. Once in a while, you may delete zeros from the planning database by using native system functions in the modeler to delete facts. NB: in case you create advanced formulas for which you have to reset to zero some values prior to the new calculation to take place, please consider assigning a NULL value to the data region you wish to erase, and not assigning zeros to it (please see calculations guide)
7. Security
Data access profiles do have an impact on story/query performance. As a general rule of thumb, the more restrictive a user’s data access, the better the performance. The rationale behind this is that a query output will only return data that a user is allowed to view or edit. However, the complexity and size of data access configuration may have an impact for the functional administrator as modeling rights will be refined depending on a power user privileges.
CONCLUSION
By reviewing all the elements above, you should be able to better understand the type of model you designed, and review specific areas that you may enhance to maximize your model landscape.
USEFUL LINKS
- Blog: SAP Analytics Cloud for Planning - Optimizing Calculations
- Welcome guide to Planning
- Introducing SAP Analytics Cloud for Planning
- Planning – Modeling Learning tracks
This blog is part of the Best Practice series. If you want to discover more best practices, please check out these blogs as well:
https://community.sap.com/search/?ct=blog&mt=819703369010316911100650199149950&q=%23best_practices
- SAP Managed Tags:
2 Comments
