Hi all,
We are currently working to load our Board environment with data from new acquired companies, and so we have to load (and reload) many times significant amounts of data.
The process is quite slow in the first place, and most of the processing time is taken by the sub procedure where we empty the cube slice before loading data into it again.
Even when we reload only one or two companies, (a few thousand lines, the data reader takes 5 seconds to complete), the emptying part can take up to 20 or 30min because it does a clear of each cube (6 cubes for sales values) and for each version (4 to 7 versions per cube).
Narrowing the selection to empty only a small slice does not improve the clearing speed much.
Below an extract of our DB log:
DC 20180503 17:43 fde FAST_001 DataClear S&GM_Quantity Details [Ver:001] 00h00m36s [0/0]Mb
DC 20180503 17:44 fde FAST_001 DataClear S&GM_Quantity Details [Ver:002] 00h01m19s [0/0]Mb
DC 20180503 17:44 fde FAST_001 DataClear S&GM_Quantity Reporting [Ver:001] 00h00m36s [0/0]Mb
DC 20180503 17:45 fde FAST_001 DataClear S&GM_Quantity Reporting [Ver:002] 00h01m19s [0/0]Mb
DC 20180503 17:46 fde FAST_001 DataClear S&GM_Amount GC [Ver:001] 00h00m36s [0/0]Mb
DC 20180503 17:47 fde FAST_001 DataClear S&GM_Amount GC [Ver:002] 00h01m49s [0/0]Mb
DC 20180503 17:48 fde FAST_001 DataClear S&GM_Amount GC [Ver:003] 00h02m29s [0/0]Mb
DC 20180503 17:48 fde FAST_001 DataClear S&GM_Amount GC [Ver:004] 00h03m09s [0/0]Mb
DC 20180503 17:49 fde FAST_001 DataClear S&GM_Amount GC Act@N-1 [Ver:001] 00h00m36s [0/0]Mb
DC 20180503 17:50 fde FAST_001 DataClear S&GM_Amount GC Act@N-1 [Ver:002] 00h01m37s [0/0]Mb
DC 20180503 17:51 fde FAST_001 DataClear S&GM_Amount GC Act@N-1 [Ver:003] 00h02m16s [0/0]Mb
DC 20180503 17:51 fde FAST_001 DataClear S&GM_Amount GC Act@N-1 [Ver:004] 00h02m54s [0/0]Mb
DC 20180503 17:52 fde FAST_001 DataClear S&GM_Amount GC Act@Bud [Ver:001] 00h00m35s [0/0]Mb
DC 20180503 17:53 fde FAST_001 DataClear S&GM_Amount GC Act@Bud [Ver:002] 00h01m37s [0/0]Mb
DC 20180503 17:53 fde FAST_001 DataClear S&GM_Amount GC Act@Bud [Ver:003] 00h02m16s [0/0]Mb
DC 20180503 17:54 fde FAST_001 DataClear S&GM_Amount GC Act@Bud [Ver:004] 00h02m54s [0/0]Mb
DC 20180503 17:55 fde FAST_001 DataClear S&GM_Amount RC [Ver:001] 00h00m35s [0/0]Mb
DC 20180503 17:56 fde FAST_001 DataClear S&GM_Amount RC [Ver:002] 00h01m48s [0/0]Mb
DC 20180503 17:57 fde FAST_001 DataClear S&GM_Amount RC [Ver:003] 00h02m29s [0/0]Mb
DC 20180503 17:57 fde FAST_001 DataClear S&GM_Amount RC [Ver:004] 00h03m08s [0/0]Mb
DC 20180503 17:58 fde FAST_001 DataClear S&GM_Amount RC Act@N-1 [Ver:001] 00h00m36s [0/0]Mb
DC 20180503 17:59 fde FAST_001 DataClear S&GM_Amount RC Act@N-1 [Ver:002] 00h01m37s [0/0]Mb
DC 20180503 18:00 fde FAST_001 DataClear S&GM_Amount RC Act@N-1 [Ver:003] 00h02m17s [0/0]Mb
DC 20180503 18:00 fde FAST_001 DataClear S&GM_Amount RC Act@N-1 [Ver:004] 00h02m56s [0/0]Mb
DC 20180503 18:01 fde FAST_001 DataClear S&GM_Amount RC Act@Bud [Ver:001] 00h00m36s [0/0]Mb
DC 20180503 18:02 fde FAST_001 DataClear S&GM_Amount RC Act@Bud [Ver:002] 00h01m38s [0/0]Mb
DC 20180503 18:02 fde FAST_001 DataClear S&GM_Amount RC Act@Bud [Ver:003] 00h02m17s [0/0]Mb
DC 20180503 18:03 fde FAST_001 DataClear S&GM_Amount RC Act@Bud [Ver:004] 00h02m56s [0/0]Mb
DC 20180503 18:04 fde FAST_001 DataClear S&GM_Amount OC [Ver:001] 00h00m35s [0/0]Mb
DC 20180503 18:04 fde FAST_001 DataClear S&GM_Amount OC [Ver:002] 00h01m14s [0/0]Mb
FR 20180503 18:04 fde FAST_001 S&GM Reading Amount GC - RC 00h00m04s FAST_-9904 16416 16416 0 [0/0]Mb
Any idea to improve the process ?
I've tried to copy 0 into the cube, make a "a=b" dataflow with an empty cube, but the times are the same (not surprinsingly). Also the "replace" option of the datareader is not an option because we load only one phase (actual or budget) at the same time for a given month, and sometimes only one company.
Thanks,
Etienne