how can I optimize my procedure?

Noura BOURRAH

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.

 

 

 

 

Previous Member

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

Noura BOURRAH

Hi Ray Martens,

Do you mean loading the cubes C and D using ETL instead of Dataflows within the procedure ? 

Best regards.

Previous Member

yes

Noura BOURRAH

Thank you so much that was really helpful, I did it with the ETL and it worked.

Best regards.

Leone Scaburri

Hi noura bourrah,

 

here you can find some tips on dataflow optimization:

Dataflow execution methods: high performance in depth 

Björn Reuber

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

Noura BOURRAH

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.