Dimension Formula versus Script Logic that runs on default

[Former Member]

Hi Experts,

Which is better to use dimension formula or script logic that runs on default? We have a lot of dimension formulas and when noticed that it has a huge impact in the performance of our AppSet. When we try to remove them, the performance seems better. We we're thinking of transforming these dimension formulas into script logic which will run by default instead. Right now I'm hesitant with two things, 1.) Will there be improvement in performance if I all these formulas are run at default via script logic? 2.) The maintenance seems to be much more complex for the user if we use script logic.

Thanks,

Marvin

[sorin_radulescu · Accepted solution]

Hi Marvin,

Actually you pointed very well the advantage and disantavage regarding dimension formula.

Dimension formula are executed into run time which means that value are not calculated. Of course this will have an impact regarding performances.

Usually our recommendation is to use dimension formula just when yoiu have KPI's to calculate which will be very difficult to be implemented into SQL scrip logic or when you have very simple calculation like Account A = Account B+ Account C.

For other calculation we advice our customer to use script logic.

But attention:

Default formula should not become very heavy because you will arrive into other issue with performances.

When you will send data if default formula script will be heavy you will arrive into situation to wait may be minutes just to send one figure and of course this will be not acceptable for users.

So you have to use script logic but you have to run into batch mode (schedule package) that script logic.

Into default formula you have to keep just minimum (you have to calculate just figures which have importance for user imediat, real time).

Also you have to be aware that moving dimension formula into scrip logic actually your fact table will grow a lot because the number of records generated will be probably 3-4 times bigger than number of records from fact table with dimension formula.

Maintenance of script logic is more complex than dimension formula and you pointed very well this.

So it is a balance and I tried o provide you suggestions or best practice when you have to use dimesnion formula and you have to avoid dimension formula.

I hope this will help you to arrive to best combination for your application.

Kind Regards

Sorin Radulescu

[Former Member · Accepted solution]

Hi Sorin,

Thanks a lot, it clears things up for me~!!

Regards,

Marvin