[thrive_leads id=’384′]

---

Here’s the thing. Your data model is changes.

Your data loaded quickly and your reports were fast at go-live. Now, it’s chugging along and your users may be complaining (just maybe not to you).

Your company may have acquired a new line of business. Or your model was so well received that additional features were requested.

The pristine design that you built some time ago is now showing it’s age and the unbalanced dimensions are slowing down the reports and data loads.

It’s time for a tune-up

In the e-book Run Faster SAP BW, I covered the ‘Quick & Dirty’ approach to performance optimization for a quick turns and reduced risk. I won’t repeat it here.

Instead, we’ll elaborate on the ‘Rebalance Dimension’ section.

When grouping items together into a dimension, your goal is to make the size of the dimension as small as possible. In other words, the cross-product or possible combinations of the info-objects in the dimension should be small (so that the record count is less than 10% of the fact-table).

Here are some examples:

+ Dimension: Organization
– Info-Object: Company Code
– Info-Object: Sales Organization
– Info-Object: Plant

+ Dimension: Time
– Info-Object: Calendar Year
– Info-Object: Calendar Month
– Info-Object: Calendar Day

+ Dimension: Product
– Info-Object: Material Number
– Info-Object: Batch Number
– Info-Object: Vendor

In the above example, a parent company will have a small list of sales organization groups and plant locations. It makes sense to group those items into one dimension.

The same thing can be said about the time dimension, since each calendar year will have only 12 months and (generally) 365 days. Because the list is relatively small (and finite), the time dimension is a great for date-related items such as calendar day and fiscal period.

If your company procures raw material, then associating the vendor number to the product makes sense. The finished good could also be related to a certain batch. From a business process stand-point, this makes sense so we will continue the relationship between the items in our model.

What does not make sense is to mix objects as shown in the example below:

+ Dimension: Bad-DIM
– Info-Object: Calendar Day
– Info-Object: Material Number

If the above two fields were keys in a table, then you can imagine the possible day-to-material combinations. On a single day, a large manufacturing company could produce thousands of different products.

A bad design like this could lead to very long run-times during the reporting phase. A simple query should need to scan a large table for calendar-material combinations instead of reading a much smaller table for calendar day and another smaller table for material.

Two quick reads on small tables (in parallel) is much faster than one long read on a large table (in sequence).

So give that a try.

1. Create a copy of an infocube
2. Rebalance the dimensions in the new info cube
3. Load the new infocube
4. Update the statistics of the new infocube
5. Run the program SAP_INFOCUBE_DESIGNS
6. Confirm the dimension size has decreased

Let me know if that helped.

Until next time,

Hau Ngo
Founder + Principle Consultant

---

If you found this useful, get the free e-book and to catch up with the previous lessons that elaborate on fine-tuning the critical parts of your design.

[thrive_leads id=’377′]