Skip to Content
author's profile photo Former Member
Former Member

Best Practice

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?

Add a comment
10|10000 characters needed characters exceeded

Related questions

2 Answers

  • Best Answer
    author's profile photo Former Member
    Former Member
    Posted on Jul 06, 2009 at 11:37 PM

    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.

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Dec 13, 2010 at 09:53 PM

    Answered, thanks

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.