Announcement:
wanna exchange links? contact me at sapchatroom@gmail.com.
Posted by
Admin at
Reply from sen_suparman on Jul 28 at 6:53 PM Hi,
Just a general question, do you really need that much record in the cube for reporting? If the report only show a few lines of summaries info, I would redesign the cube to only store the summaries data. This way the data load will run faster and report runtime will shorter as well. Thanks.
| | | ---------------Original Message---------------
From: whirlybird
Sent: Tuesday, July 28, 2015 9:49 AM
Subject: Cube Design Constraints
Hi Team,
One our BI developers has asked me (a basis bunny) the following question..
for which I haven't a clue. Does anyone else have a view?
The cube ZSDL3C01 has 42 Characteristics spread across 7 dimensions. There are 4 million fact table entries. Two of the dimensions have over 3 Million entries one is at 2.8 Million another two at 1.5 M and the remainder have 0.5 M and 0.25M. This cube took
4 Hours 33 Minutes to load today.
By contrast ZSDL4C02 has only 25 characteristics spread across 9 Dimensions. There are 2.5 million fact table entries. One dimension has 3.8 M entries (Because it has old unused entries, dropping the Dimensions then reloading would reduce this to equal or below the fact table ). The other 8 dimensions however are surprising, the largest count is 158,212 in the material dimension the next largest is 3,505 and the remainder are less than 400. The cube took 1H 9 Minutes to load today.
The second cube has been designed with the common 'filters' in their own dimensions and these dimensions are therefore relatively small. All the 'rubbish' is in a single dimension which is therefore likely to match the fact table in record count. But, when loading a source row, the system must first generate SID's for each characteristic in the dimension and then search for an existing dimension with a similar pattern of SID's . The serial scan for this is amazingly quick, you can see them happening in SM50, but I would argue that being serial the time taken must vary in proportion to the number of records being searched. So, one slower search and 8 that will be very fast.
The first cube will suffer from slower dimension searches on 5 of the 7 dimensions.
The first cube could be re-organised to resemble the second but the 'rubbish' dimension would hold 31 characteristics (if I utilize the maximum 12 dimensions). So here is the 64 M Euro question, is it almost as fast to serial scan for one record comprising 31 numeric fields as it is to search for a record comprising 25 numeric values if the total record count is the same?
Thanks for your input. |
| Reply to this email to post your response. __.____._ |   _.____.__ |
