Calculated Members and Aggregation

Hi,

I am still in AS 2000 and would like to know if it is possible to have calculations performed at lowest granular level of the cube before aggregation.Perhaps I am missing a basic understanding on this but what I am hoping to achieve is to hold parameters at the lowest level in a separate measure ‘Param’ (separate column in the Fact table) and then reference to those parameters in a calculated member with the resulting values held in the default measure of the cube before being rolled up in the aggregations to higher levels.

Currently in the higher levels input parameters are being aggregated before determination of the calculated member value.

Will much appreciate your suggestions on whether it is possible to achieve above, and if not is there another more efficient way to handle.

Barry

Hi Barry,

Could you describe a more concrete scenario - what is the specific data and business problem you're trying to solve, with an example? It is harder to suggest alternative approaches with such an abstract description.

|||

Thanks for getting back to me on this Deepak.

By way of simplified example:

I have a Sales cube with two dimensions ‘Product’ and ‘Account’ and two measures ‘Amount’ and ‘Param’ (‘Amount’ being the default).Lets say there are two rows in the fact table, as follows:

AccountProductAmountParam

SalesApples-2000.1

SalesOranges-2000.2

I have a calculated member for ‘commission expense’ in the ‘Account’ dimension:

-[Account].[Sales] * ([Account].[Sales], [Product].CurrentMember, [Measures].[Param])

After processing I get the following:

All ProdsApplesOranges

Sales-400-200-200

Commission1202040

Obviously, this 'logically incorrect' result at ‘All Prods’ level is due to aggregation occurring prior to commission calc.

In essence what I want to do is hold parameters (in this example ‘commission rate’, that varies by product), at lowest granular level (individual product), then reference to that parameter in calculated member, before aggregation taking place. Might be asked why not have this calc performed in underlying fact table input, but that does not suit my purpose.

I suppose question I am asking:Is it possible to override default order of processing in the cube ?

As already noted perhaps I am misunderstanding a very basic point here… will be pleased if you can put my thinking straight.

Many thanks in advance

Barry