MDX Calc measure to accomdate Smi-Additive and delta at the same time? 1

[eo]

Hi,

SSAS 2008.
SQL Server 2008 Standard Edition

I have two measure types in my cube, standard sum aggregated measures (which are baased over deltas/ changes from one day to another), and semi additive.
The semi-additive have been implemented succesfully (using MDX calc measures) and so has the deltas (normal sum aggregation - no MDX). The problem is that:

when the two meaasures are viewed by a dimension member the semi-additive and the sum(delta) display as expected, but when an As At Date dimension member is added, then the semi-additive shows the value as at that date as expected, but the delta now only shows the delta recorded at that As At Date, and not the sum from the beginning of time untill that As At Date.

I will show by example. Example source data:

AsAtDate   AccountNumber   DeltaValue    SemiAdditive
01.01.2010 100A            100,000       45,000
02.01.2010 100A             10,000       46,000
03.01.2010 100A             15,000       46,000
04.01.2010 100A             -5,000       48,000

If the cube is viewed by slicing both Delta and SemiAdditive by AccountNumber only, then no problem, I get the following:

AccountNumber DeltaValue SemiAdditive
100A          120,000    48,000

but when the AsAtDate component is added, then te DeltaValue no longer makes sense

AsAtDate   AccountNumber   DeltaValue    SemiAdditive
04.01.2010 100A             -5,000       48,000

...as opposed to what I am aiming for...

AsAtDate   AccountNumber   DeltaValue    SemiAdditive
04.01.2010 100A            120,000       48,000

Is there a way the Delta can be implemented using MDX calc measure to work with when the AsAtDate is used and when it is not used (i.e. then for all dates)

EO
Hertfordshire, England

 

[RiverGuy]

Have you tried something like the following?

WITH MEMBER [Measures].[LTD Sum] AS SUM([Date].[Date].FIRSTCHILD:[Date].[Date].CURRENTMEMBER, [Measures].[Your Additive Measure])

SELECT {[Measures].[Your Semi Additive Measure], [Measures].[Your Additive Measure]} ON 0
FROM [Your Cube]
WHERE (
[Date].[Date].[Some Date]
)

 

[RiverGuy]

Should be

WITH MEMBER [Measures].[LTD Sum] AS SUM([Date].[Date].FIRSTCHILD:[Date].[Date].CURRENTMEMBER, [Measures].[Your Additive Measure])

SELECT {[Measures].[Your Semi Additive Measure], [red][b][Measures].[LTD Sum][/b][/red]} ON 0
FROM [Your Cube]
WHERE (
[Date].[Date].[Some Date]
)

 

[eo]

I am actually looking for a calculated measure. So I have used what you said and tried

CREATE MEMBER CURRENTCUBE.[Measures].[LoanRequested]
AS
  SUM({[DateAsAt].[HierarchyDateAsAt].[DayNumber].FIRSTCHILD:[DateAsAt].[HierarchyDateAsAt].[DayNumber].CurrentMember}
     , [Measures].[Loan Requested]
  );

...this returns a error #VALUE!

and also

CREATE MEMBER CURRENTCUBE.[Measures].[LoanRequested]
AS
  SUM({NULL:[DateAsAt].[HierarchyDateAsAt].[DayNumber].CurrentMember}
     , [Measures].[Loan Requested]
  );

...this returns a null



EO
Hertfordshire, England

 

[RiverGuy]

CURRENTMEMBER is used like this:

[Dimension].[Hierarchy].CURRENTMEMBER.

You're adding the extra [DayNumber] level to it. Take it out.

CREATE MEMBER CURRENTCUBE.[Measures].[LoanRequested]
AS
  SUM({NULL:[DateAsAt].[HierarchyDateAsAt].CurrentMember}
     , [Measures].[Loan Requested]
  );

 

[eo]

That worked thanks

EO
Hertfordshire, England