Big Cubes, Big Problems
In order to visualize the importance of having well designed InfoCubes, let’s imagine we have a backpack with five text books inside. Now, throw it on your shoulders. This backpack has more space to fit additional books, but we’re wearing this thing and it’s starting to get heavy. How fast do you think you could run a mile while having this monstrosity attached to your back? How about if we add a few more books into your bag?
Much like how the laws of physics cause heavier objects to accelerate at a slower speed, the same goes of bloated InfoCubes. Overstuffed InfoCubes take much longer to read data and write data. If two robots have the same backpack, same books, and we add an additional five books to just one of their bags, which will finish reading first? Would the overstuffed bag robot be able to retrieve a book as fast as the lighter bag robot? No, it will take twice as long.
The key to ensuring optimal InfoCube performance is to make sure they are as nimble as possible. What does that mean? Let’s find out!
Oversized Dimension Tables
Dimension table greater than 15% Fact Table
If any of your dimension tables are greater than 15% the size of your InfoCube’s Fact Table, both read and write performance will likely suffer. You’re probably thinking to yourself, “How do I calculate this? I have 100+ InfoCubes in my environment, I don’t have time for this.” No worries, SAP created a program to quickly analyze your whole environment. Go to transaction SE38 and run program:
The program tends to run in about five minutes, but obviously this runtime will be dependent on the size of your installation. Once complete, a report will be displayed outlining every InfoCube and its associated tables (Fact, Dimensions) along with the DIM to Fact %. Anything over 15% will be highlighted in red. The InfoCubes marked in red, are your best candidates for redesign.
If you have a dimension where there are a ton of unique values being loaded (i.e. Invoice Number, Social Security Number), convert the Dimension to be a Line Item Dimension. Why? Because you’re unnecessarily adding in another layer of processing that will slow both read and write speed. Having a large number of unique records defeats the purpose of SID generation for a dimension table. The Line Item Dimension bypasses the need for a Dimension Table which stores Dimension ID’s and instead writes SIDs (surrogate IDs) directly in the Fact table.
- Enable this by right clicking on a dimension and changing it’s properties to use a Line Item Dimension.
- Only works with a dimension that has one characteristic assigned.
- Large dimension tables are normally associated with having too many unique values
- The InfoCube fact table structure changes for a Line Item Dimension. If you look at your Cube’s Fact table is SE11, the column associated with the Line Item Dimension will display RSSID as the data element in place of the traditional RSDIMID because the DIM is bypassed.
When your InfoCube contains Time dependent InfoObjects (and has time dependency enabled), it’s going to write many more records to keep historical (time-dependent data) using DATEFROM and DATETO. These InfoObjects will cause tables to store more data than necessary, so only enable Time Dependency if it’s absolutely required.
Min and Max Aggregation
Try to avoid using Min or Max aggregated Key Figures in InfoCubes you expect to grow into large InfoCubes.
Characteristic Ordering within Dimension Tables
Place your most commonly used characteristics first in a given Dimension. This allows the database to find these values faster than having to read through everything.