how can I optimize my procedure?
hello,
Well, I use 4 cubes in my procedure :
Cube A (type: Single,structured on 6 entities + Year) : Yearly Budget
Cube B (type:Date,structured on same 6 entities + Month) : Due Month
Cube C (type:Single,structured on same 6 entities + Month) : Month flag
Cube D (type:Single,structured on same 6 entities + Month) : Monthly Budget
A and B are loaded with datareaders.
My procedure is build like the following:
1- data flow: C= if (B<>0,1,0)
2- data flow: D= A*C
The issue is that my procedure takes a lot of time to run ( an hour and a half)
I tried to use sparsity on 3 entities but It's still very slow.
My question is what can I do to optimize my procedure so it can run faster ?
Thanks and regards.
Answers
-
Hi noura bourrah,
I was wondering if you could use the Board ETL functions instead of having datareaders. That should increase performance a lot, as the other cubes could be computed when the first ones are processed.
See Defining an ETL transformation and validation rule and ETL Function Reference
BR, Ray
1 -
Hi Ray Martens,
Do you mean loading the cubes C and D using ETL instead of Dataflows within the procedure ?
Best regards.
0 -
yes
1 -
Thank you so much that was really helpful, I did it with the ETL and it worked.
Best regards.
0 -
Hi noura bourrah,
here you can find some tips on dataflow optimization:
Dataflow execution methods: high performance in depth
1 -
Hi,
which is the duration of each steps?
If it's the first one you maybe can optimize it with C=B/B (so if b contain a value the result is allways 1, otherwise its 0), which can run in HPM instead of C= if (B<>0,1,0) (which can't run in HBM)
Regards
Björn
1 -
Hello ,
Thank you so much for your help, but the issue is that my cube B is of type Date and this doesn't work.
I have tried your method with a cube of type Single and it works perfectly (the procedure takes seconds to run) .
Thanks and regards.
0
