I've got a database with a single Date dimension that is used as a role-playing dimension in the cube. Measure groups have 2-4 different role relationships with the date dimension (e.g. sell date, ship date).
I've got some calculated members, defined in the style used by the Time Intelligence wizard, roughly:
Scope(
{
[Measures].[Amount],
[Measures].[Count]
}
);
( [Period].[Period].[60 calendar days],
[First Date].[Date].[Date].Members ) =
Aggregate(
{ [Period].[Period].DefaultMember } *
{ [First Date].[Date].CurrentMember.lag(59) : [First Date].[Date].CurrentMember }
);
( [Period].[Period].[60 calendar days],
[Second Date].[Date].[Date].Members ) =
Aggregate(
{ [Period].[Period].DefaultMember } *
{ [Second Date].[Date].CurrentMember.lag(59) : [Second Date].[Date].CurrentMember }
);
End Scope;
Here [First Date] and [Second Date] represent two of my role-playing date dimensions.
What I'm noticing is that this doesn't work: the calculated members are calculated correctly for whichever date dimension is listed last in the script and are apparently erased for whichever one was listed first.
The particular measure group I'm using in my sample query has a relationship with both date roles, and in fact they're identical for this measure group - both are based on the same underlying column in the relational data (that's not true for all measure groups, it just happens to be in this case).
Can someone explain what I'm seeing? Is this a bug, or a subtety that I haven't accounted for? How can I make this work for 2 or more role-playing date dimensions at the same time?
Well, if you will position current coordinate on the [First Date].[Date].[Date] level - then you will see that the first formula is applied correctly. What you probably mean by saying "last formula erases whatever was listed first" is that if you look at the aggregate level - then the aggregation is computed from the last formula. Obviously, at the aggregate level there is a conflict - which formula should be used to aggregate - it could be either first or second. The precendence rules in AS say that the later assignment wins. This is not a bug, after all, single cell cannot be calculated using two different formulas !
Perhaps if you will explain what exact results you would like to see for specific queries - this forum will be able to craft MDX script to solve it.
|||
I neglected to include representative queries, so here's one that corresponds to the names I used in the first post:
select
{
[Measures].[Amount]
} on columns,
{
[Period].[Period].&[60]
} on rows
from
[Database]
where
(
-- [First Date].[Date].&[20061228]
-- [Second Date].[Date].&[20061228]
)
([Period].[Period].&[60] is the same member as [Period].[Period].[60 calendar days])
Given the computations given above, and the fact that [First Date] and [Second Date] actually refer to the same column in the fact table for this measure group, I'd expect that the above query would return the same results with either of the two lines in the where clause uncommented, but that's not the case.
Whichever date dimensions corresponds to the second set of calculations in the script produces the correct result while the other produces a wrong result - I'm not sure exactly what the wrong result represents - it's a larger number than anything I can think up that might make sense (e.g. it's not the aggregation totally ignoring the date dimension in question - it's something else).
|||
 Mosha Pasumansky wrote: |
|
| Obviously, at the aggregate level there is a conflict - which formula should be used to aggregate - it could be either first or second. The precendence rules in AS say that the later assignment wins. This is not a bug, after all, single cell cannot be calculated using two different formulas ! |
Why is this obvious? As far as I can see, the calculations are not referencing the same locations in the cube. Please enlighten me! :)
|||
Sorry, I thought it was clear :(
Both calculations apply to the leaf level of their respective Date dimension, i.e. [First Date].[Date].[Date] and [Second Date].[Date].[Date]. So the question is, how the value at ([First Date].[Date].[All Dates], [Second Date].[Date].[All Dates]) should be computed. It can aggregate from either one of lower levels - but depending on which one it will aggregate from, the results will be different, so AS has to choose one or the other.
|||
OK, I can see that there's an ambiguity on those particular members, but I'm not querying those members (or am I?).
So how can I get the results I want?
|||
> but I'm not querying those members (or am I?).Can you please provide the query you are using
> So how can I get the results I want?
Can you please describe what exactly result do you want.
|||I already did - see my second post in this thread.|||I see, that you added the query later, although it is still not clear from your description what is the expected result. But I am going to guess that what you want is either LastChild or LastNonEmpty semiadditive aggregation. If this is true, you need to define your measure as semiadditive with this aggregation function. Note, that you still will only be able either First Date or Second Date, but not both, because there is still a conflict at aggregate level.|||Just to add my 2 cents' worth - based on your comment that "[Period].[Period].&[60] is the same member as [Period].[Period].[60 calendar days]", is this a physical (place-holder) rather than calculated dimension member, to which the trailing-60-day calculation is applied? If so, my guess is that "the fact that [First Date] and [Second Date] actually refer to the same column in the fact table for this measure group" could be significant. If you instead create a [Period].[Period].[60 calendar days] calculated member of the [Period].[Period] hierarchy, do you get the expected results?|||
I'm quite certain that I don't want LastChild or LastNonEmpty (especially since those are EE only features).
What I want is for the two dimensions to act as two independent dimensions without regard for the conflict at ([First Date].[Date].[All],[Second Date].[Date].[All]) since I will never query any value in that slice.
I'm not sure what it is I'm failing to communicate or understand - this seems like it should be straightforward and not particularly unusual. Lots of cubes have multiple date dimensions (and I expect lots of those have those date dimensions all backed up by a single role-playing dimension in the database).
Any suggestions are welcome - for now, I've abandoned the whole notion (being able to get time-based aggregations based on multiple time dimensions) as being inherently not supported by MDX/SSAS.
|||
It must be my failure to understand what you are trying to achive, and I appologize for that. It must something really simple, since it looks straigtforward to you, yet I fail to grasp it. Perhaps you should start a new thread, since this got plenty of replies leading nowhere, and people don't look at threads with complicated histories but do look at fresh threads.
One last note - the cubes with multiple Time dimensions are certainly not unusual, I've seen many of them working fine (even Adventure Works sample features 3 Time dimensions).
|||You didn't mention what the results of trying my earlier suggestion were - ie. creating a calculated member, rather than using an actual member, for [Period].[Period].[60 calendar days]?|||
Sorry, I forgot to reply to your suggestion.
This [Period] dimension is a real, physical dimension in the cube - and is so because I have some measure groups that associate directly with the period dimension (those "limit" measures that Mosha helped me with in another thread).
So, when I say [Period].[Period].[60 Calendar Days] is the same member as [Period].[Period].&[60], that's exactly what I mean - the member with key 60 has the name '60 Calendar Days'.
I really can't experiment with having this be a pure calculated member - I need that physical dimension to be this same dimension so that the relationships within the cube are correct.
Other than getting the "time based intelligence" to work in two time dimensions in the same cube, this all works beautifully. I have an inkling of how to get what I wanted - at the moment, I don't actually need to solve the problem right now, but sooner or later, I'll have to confront it.
I think that the solution is something like this:
Scope(
{
[Measures].[Amount],
[Measures].[Count]
}
);
( [Period].[Period].[60 calendar days],
[First Date].[Date].[Date].Members,
[Second Date].[Date].DefaultMember
) =
Aggregate(
{ [Period].[Period].DefaultMember } *
{ [First Date].[Date].CurrentMember.lag(59) : [First Date].[Date].CurrentMember }
);
( [Period].[Period].[60 calendar days],
[Second Date].[Date].[Date].Members,
[First Date].[Date].DefaultMember
) =
Aggregate(
{ [Period].[Period].DefaultMember } *
{ [Second Date].[Date].CurrentMember.lag(59) : [Second Date].[Date].CurrentMember }
);
End Scope;
If I get a chance to try it, I'll post back with the results.
|||
FYI - I put a similar test case together in Adventure Works, relating [Date] and [Ship Date] to the same fact date field. Using the [Scenario] dimension &[2] (Budget) member to hold trailing 60-day calculations, only calculations for the 2nd dimension (Ship Date) work. But if a new calculated member like [Scenario].[Scenario].[Trailing60] is used instead, calculations on both [Date] and [Ship Date] seem to work fine.
Since you might not wish to go down the calculated member path, another approach which seems to work is "freezing" the first calculation, to prevent its results being changed by the next calculation. In your scenario, something like:
Scope(
{
[Measures].[Amount],
[Measures].[Count]
},
[Period].[Period].[60 calendar days]
);
Scope( [First Date].[Date].[Date].Members );
this =
Aggregate(
{ [Period].[Period].DefaultMember } *
{ [First Date].[Date].CurrentMember.lag(59) : [First Date].[Date].CurrentMember }
);
freeze(this);
End Scope;
( [Second Date].[Date].[Date].Members ) =
Aggregate(
{ [Period].[Period].DefaultMember } *
{ [Second Date].[Date].CurrentMember.lag(59) : [Second Date].[Date].CurrentMember }
);
End Scope;
