Best Practice

[Former Member]

I have started working on my first BPC project. The entire team is very new to the product and so I am not certain that we are following best practice in BPC.

In the past the OLAP product I normally use for budgeting applications has the ability to physically hold rows at out put level in all dimensions. This contrasts with SSAS / BPC which holds data at the base level data and only creates outputs by grouping at query time.

Imagine a piece of the accounts dimension as follows

Net Salary Costs

....PayRollTax

....WorkCover

....CoreSalaryCosts

.... ....Wages

.... ....Bonuses

.... ....HolidayPay

.... ....Commisions

.... ....FringeBenefitsTax

.... ....Entertainment

.... ....CompanyCars

In the past my process would be the following steps

1. Roll up the data in this dimension

2. Calculate Payroll Wax as a percent of CoreSalaryCosts and post it to PayRollTax

3. Calculate Work Cover as a percent of CoreSalaryCosts and post it to WorkCover

4. Reconsolidate so that the Net Salary Costs were available to reports such and a P&L statement.

The project is using a different approach. It uses logics to calculate WorkCover and PayrollTax. In each case the logic is defining the CoreSalaryCosts calculation without making use of the hierarchy in dimension.

My questions are;

u2022 Is this a normally accepted practice?

u2022 It seems wasteful to have the same roll up rule defined in both of the logics and in the dimension. Is there a better way to do this?

u2022 Should there be a way to have the logics make us of the hierarchy in the dimension rather than redefine it?

[Former Member · Accepted solution]

I often have issues with defining various design methods as best practice, but I understand your concern and request for ideas for clarifying your current design. So I will try to provide a few ideas and also ask a few questions. For example, are all the values to the accounts below Core SalaryCosts going to be input during a planning process? Do you already know the values or percentages needed to calculate the PayrollTax and WorkCover?

It is normal in most BPC projects to use script logic to perform many of the calculations, but you must balance the performance with the cost to the design. For example, I have used processes to actaully aggregate the CoreSlaryCosts and store it as a seperate value that may then be used faster during a calculation, using the CalcAccount business rule. But it is also, depending on volume easy to write logic that states,

*WHEN ACCOUNT.ID

*IS WAGES, BONUSES.........COMAPNYCAR

*REC( )

Basically using anychanges to any of the values to then multiply the value by a stored PCT, called PAYROLLTAX_PCT, which is an input stored value. (Unless you have another way to calcuate it) You can also use the logic statements that build the aggregate on the fly of the CoreSalaryCosts, using DUMMY_ORG statements. Each method is trying to calculate the end results the same, just using different methods. The issue is to determine which is faster, based on what is stored versus what is calculated on the fly.

The other thing to remember is that Script logic ONLY uses base level members, which as you stated is where the data is stored. What we found wa sthat when you try to build complex calculations using OLAP (hierarchy points) the calculation slow down as teh volume of data increases and the requests for aggregations increase. But using the base details is faster since the values are stored and then aggregated when needed for a calculation.

So, is there a better way....not always, but there are different methods to try to see if one is faster or more efficient than another, based on the volume, frequency and need for immediate calculated data for feedback to a user. (ie realtime vs batch logic).

Hope this helps.

[Former Member · Accepted solution]

Thread closed - thanks

Edited by: Cliff Stinson on Aug 10, 2010 4:53 AM

[Former Member]

Answered, thanks