How you break up your sales area data into dimensions will have an effect on performance.
Dear BW Expert;
I don’t know enough about modeling SAP-specific entities. For example, should sales area (organization, division, distribution) be in a dimension by itself or associated with customer?
BW Consultant
Data modeling is a discipline that affects both end users and back-end system administrators. Do it poorly, and your users might not get their data. Or, they might get the intended data, but it will be retrieved so slowly that they might not wait for it.
The biggest question centers on where the characteristic InfoObject should go:
1. On the InfoCube?
2. As a display or navigational attribute on the master data?
3. Modeled as a hierarchy?
This question (the subject of a longer article) determines how historical relationships should be presented. For example, in the required report “sales volume for old people,” is “old” defined by their age at the time of the order, their age now, or their age 25 years ago?
In regard to your question, assume that the characteristic (customer) is going into the InfoCube. Which dimension table it goes into mildly affects the user (in terms of finding it for a query), but it can greatly affect performance.
The underlying design goal is to keep the dimension tables as small as possible without getting over-zealous about it. Let’s first assume you combine all the data into one dimension table. Using “real” data, it might look like Table 1.
If you instead separate the sales area data from the customer, you would have two entries in the customer dimension table and three entries in the sales area dimension table. If you really have only two customers and three sales areas, it might be better to leave them together, as six records makes a very small table. If you have 10,000 customers in 10 sales areas, on the other hand, it would be a big benefit to split them up. Each query would access these small tables, instead of one behemoth. The result = MFA (much faster access).
| Dimension Key |
Organization |
Division |
Distribution |
Customer |
| 1 |
1000 |
10 |
00 |
Ned |
| 2 |
1000 |
20 |
01 |
Ned |
| 3 |
2000 |
10 |
00 |
Ned |
| 4 |
1000 |
10 |
00 |
Bob |
| 5 |
2000 |
10 |
00 |
Bob |
| 6 |
1000 |
20 |
01 |
Bob |
|
| Table 1 |
All data combined in a single dimension table |
Ned Falk
Ned Falk is a senior education consultant at SAP. In prior positions, he implemented many ERP solutions, including SAP R/3. While at SAP, he initially focused on logistics. Now he focuses on SAP HANA, SAP BW (formerly SAP NetWeaver BW), SAP CRM, and the integration of SAP BW and SAP BusinessObjects tools. You can meet him in person when he teaches SAP HANA, SAP BW, or SAP CRM classes from the Atlanta SAP office, or in a virtual training class over the web. If you need an SAP education plan for SAP HANA, SAP BW, BusinessObjects, or SAP CRM, you may contact Ned via email.
You may contact the author at ned.falk@sap.com.
If you have comments about this article or publication, or would like to submit an article idea, please contact the editor.
