Assessing the Dimension Count and Granularity

The natural instinct is to create a single model and include every possible detail, one data source for every possible scenario that you may require when planning. However, thanks to the powerful allocation mechanisms existing in SAP Analytics Cloud, you are often not aware of the amount of data that is being automatically generated. Data with a level of detail that isn't often needed for analysis. It's important to consider dimension granularity and dimension count when creating or optimizing your planning model.

Dimension granularity is typically concerned with the depth of hierarchies within the dimensions of a model. It’s important to carefully examine the granularity of each dimension. Highly granular dimensions, especially in models incorporating General Ledger (G/L) actuals, can significantly impact on performance. Often, the level of detail in actuals is unnecessary for planning purposes.

When assessing the dimension granularity in order to optimize the planning model, you need to ask yourself if you truly need all the detail that's included. If you are planning on a quarterly basis, do you need granularity in the Date dimension set to Day?

If you assess the model and determine that the detail is required, then there are two key workaround options that you can apply to optimize performance.

1. Create two models. For example:
   • For analysis and planning, you can have one containing detailed actuals for analytics purposes and a condensed planning model for budgets, plans and forecast.
   • A condensed model for annual strategic planning with a more detailed model for monthly budgeting at the product level.
2. Keep transaction data in the source system and use a drill-through process in the SAP Analytics Cloud planning story to query the data from the source system and view the additional details when it’s required.

If you create a 5-year strategic plan with budget on a monthly basis that includes a comparison with 3 years of actual data, that's 96 periods. If you have a dimension's Granularity set to Month and the Default Hierarchy to Year, Quarter, Month, then potentially (for example, with no data security applied in the model), you can generate up to 60 million of data. Much of this generated data will never be analyzed in detail. Performance issues can be compounded if you have a Product dimension with high dimensionality whose disaggregation follows a protocol linked to the previous data without even taking advantage of the power of the predictive algorithms embedded in SAP Analytics Cloud.

Let's take a closer look at the two-model scenario. To demonstrate this scenario, we have created two simplified models:

1. The first model has the Date dimension Granularity and Default Hierarchy set to Year.
2. The second model has the Date dimension Granularity set to Month and Default Hierarchy set to Year, Quarter, Month.

Next, in this video, we use these two models to demonstrate a real example from a customer, who was planning on a monthly level when budgeting and on a yearly level for their strategic plan, with the budget as the first period of the strategic plan. They had performance issues and used the following strategy to optimize performance.

A multi-model architecture for planning and analytics, can help ensure your planning models and associated stories are more in line with the needs and ease of use for your planners alongside performance considerations.

Dimension count is simply the number of dimensions in a model. While there are additional factors such as granularity that can also impact on the model performance, it's a good rule of thumb to try to keep the number of dimensions (including standard dimensions like Account, Date, and Version) between 8 and 12.

In the following below, you have two planning models. To the left, you have a planning model with a higher dimension count, and to the right, a more condensed planning model containing only the required dimensions for the planning activity.

In this video, we will explore a scenario where after review, it was deemed that all dimensions were required in the model, so a two-model approach was created to improve performance for planners who only required a limited number of dimensions to complete their planning tasks.

Keep transaction data in your source system and use the hyperlink functionality to create a drill-through process, connecting your SAP Analytics Cloud planning story to your source system where you can view the additional details.

Dimension count and granularity need to be considered with the creation of every model, but especially if you extend the use of SAP Analytics Cloud for planning to scenarios other than finance.

Consider:

1. Reducing the dimension count in the model.
2. Use a two model approach.
3. Keep transaction data in your source system and use the hyperlink functionality to create a drill-through process, connecting your SAP Analytics Cloud planning story to your source system where you can view the additional details.