cancel
Showing results for 
Search instead for 
Did you mean: 

Before and After Aggregation

Former Member
0 Kudos

Hi,

I have some "before and after aggregation" enquiries in BPC 5.1

In the calculation of Net Income per head as NetIncome / Headcount. This calculation must be run after aggregation in order to generate the correct results using dimension logic.

And in the calculation of Revenue as sales unit * sales price. This calculation must be run before aggregation in order to generate the correct result using script logic.

How are the 2 scenarios above done in BPC 5.1?

Cheers

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

Hi,

You shoul put all the calculations that needs to be run after aggregation in dimension logic (in the Formula property of the dimension).

You should put all the calculations that need to happen before aggregation (like your unit * price calculation) in logic script files. The output of the logic script is stored in the database (used also for example for currency conversion)

Best practice is to put as many calculation as possible in script logic, as dimension logic is only calculated at execution (MDX formulas are evaluated by the cube directly at run-time).

Regards,

Marcel

Former Member
0 Kudos

Hi,

Hence in the spirit of complying to best practice (i.e. performing calculation with script logic), can calculations that need to be done after aggregation be done in script logic?

And why do dimension logic and script logic work the way they do that gives the right result for calculation that needs to be done after aggregation and after aggregation respectively?

Cheers

Former Member
0 Kudos

No, calculation that need to be done after aggregation can only be done in the cube directly. The logic scripts always generates new records that are stored in the fact tablesa and there are no aggregated data stored in the fact tables.

Dimensions logics are executed directly in the cube (you can even see the fomulas if you open the cube in Analysis Services), so they are applied to the aggregated data of the cube. They are also calculated on the fly.

Script Logics are generating records in the fact table (base level data) and this data then gets aggregated by the cube (and dimension formulas are also applied to the records that are generated with Script Logic)

Regards,

Marcel

Former Member
0 Kudos

Hi Marcel,

I need to do "after aggregation" dimension logic and i have these combinations of dimensions. (Account_A, Entity_A) divided by (Account_B, Entity_B) gives (Account_C, Entity_C), how do you cater for this in the "formula" column of dimension "Account".

Thanks for your help.

Cheers

Edited by: lip chean soh on Jun 24, 2009 6:31 AM

Answers (0)