how to update the cube to get the mdx working fine

Question

I have been trying to get the ValueR column of the following query through the MDX but instead getting ValueW as output of the MDX

select 
exp(Log(sum(MTMROR)+ 1 ))-1 as ValueW,
exp(sum(Log(MTMROR + 1)))-1 as ValueR
from 
Temp_Performance
where Rundate in ('2015-03-01','2015-03-02')

MDX written for the above query is

With 
Member [Measures].[LogValuePre]
as ([Measures].[MTMROR] + 1)
Member [Measures].[LogValuePre1]
as VBA![LOG]([Measures].[LogValuePre])
Member [Measures].[LogValue]
as VBA![Exp](Sum([Measures].[LogValuePre1]))-1

select 
{
    [Measures].[LogValuePre],
    [Measures].[LogValuePre1],
    [Measures].[LogValue]
} on 0,
{
    [Dim Company Fund].[Company Fund Id].&[1274] 
    //* {[Dim Time].[Date].&[2015-03-01T00:00:00] : [Dim Time].[Date].&[2015-03-//02T00:00:00]}

} on 1
from 
(
    select {[Dim Time].[Date].&[2015-03-01T00:00:00] : [Dim Time].[Date].&[2015-03-02T00:00:00]} on 0 
    from [DSV_Cube]
)

[MTMROR] measure has the aggregate function Sum. What i can get from this behavior is MDX is first aggregating the result and default aggregation function is Sum. When i try to see the value with more granular data by having the date dimensions on the row (un-commenting the date dimension) i get the correct log and exp log values. Its showing the correct value as date dimension is most granular level in the fact table. While trying to get the data at less granular level(Fund level), getting the sum function applied automatically.
If i set AggregateFunction to none in the cube structure, i get null as the output. How could i apply the log function before the sum function in the [MTMROR] measure?

Thanks

The fact on which the measure MTMROR is built is related to which dimensions out of the above and how? — SouravA
@SouravA: Fact MTMROR is related to two dimensions [Dim Company Fund] and [Dim Time]. Both are related to fact as the regular relationship. — 107
@SouravA: [Measures].[MTMROR] has aggregate function set to SUM. Desired operation is exp(sum(log( 1 + mtmror)))-1. I tried to achieve with calculated members in above query. Grain of the fact is CompanyFundId ,Date. When i try to get output at less granular level which is CompanyFundId, [Measures].[MTMROR] gets added before the calculated members work on the [Measures].[MTMROR]. This is kind of expected behavior. When aggregate function is changed to None then [Measures].[MTMROR] returns null unless fact grain is defined in the MDX. I want Calculated members to perform at less grain data. — 107

SouravA SouravA · Accepted Answer · 2015-10-07T15:32:38

Keep the aggregation type as None and add a calculated member in cube as below:

SCOPE
    (
    [Dim Time].[Date].[All],
    [Measures].[MTMROR]
    );


This = 
SUM
 (
 NonEmpty
 (
  [Dim Time].[Date].MEMBERS, 
  [Dim Company Fund].[Company Fund Id].CURRENTMEMBER
 )
 * [Dim Company Fund].[Company Fund Id].CURRENTMEMBER,
 [Measures].[MTMROR]
 )

What this does is whenever the date level granularity is not in context(i.e. the ALL member is in), it finds all the dates for this fund ID and for the combination of that fund id and the dates, it sums it up(effectively keeping the AggregationType as SUM). If instead, a date IS on axis, it behaves normally and displays the value.

Hope this helps.

EDIT

If the aggregation type HAS to be SUM, add one more measure in the measure group and just keep it's value as LOG(MTMROR + 1)

Use that in your calculation.

how to update the cube to get the mdx working fine

1 Answers