INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

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

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

(OP)
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:

CODE

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:

CODE

AccountNumber DeltaValue SemiAdditive
100A          120,000    48,000

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

CODE

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

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

CODE

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

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

Have you tried something like the following?

CODE

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]
)

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

Should be

CODE

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

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

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

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

CODE

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

CODE

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

 

EO
Hertfordshire, England

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

CURRENTMEMBER is used like this:

[Dimension].[Hierarchy].CURRENTMEMBER.  

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

CODE

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

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

(OP)
That worked thanks

EO
Hertfordshire, England

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close