on 10-17-2008 10:30 AM
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
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
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
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
User | Count |
---|---|
6 | |
5 | |
2 | |
1 | |
1 | |
1 | |
1 | |
1 | |
1 | |
1 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.