Hi,
I need to add special relative date categories in SSAS
that similar to the functionality offered by Cognos/Powerplay. With Cognos, you
can create relative time categories very easily-- like ‘Rolling 3 months’,
‘Prior Rolling 12 Months’ etc.
I created Time Dimension with SSAS BI Studio, added a new
named calculation ‘Rolling 3 Months’ to the Time Dimension. Below is
calculation code:
CREATE MEMBER
CURRENTCUBE.[Time].[Rolling 3 Months].[Rolling
3]
AS
null,
VISIBLE = 1;
Scope(
{
[Measures].[ORDERS]
}
);
// Rolling 3 Months
(
[Time].[Rolling 3
Months].[Rolling 3],
[Time].[Month].[Month].Members
) =
Tail([Time].[Month].members ,3 );
End Scope;
I uesd Tail() function in SS Management studio, it got
the result I want. But it doesn’t work in SSAS BI Studio.
Am I missing something here? Or any suggestions would be
greatly appreciated.
Thanks.
Your syntax for the expression is wrong - you assign set where the numeric value is expected. I suggest that you use built-in Time Intelligence wizard - it supports expressions for Rolling 3 months. It would be something like
([Time].[Rolling 3 Months].[Rolling 3], [Time].[Month].[Month].Members) = Aggregate( [Time].[Month].CurrentMember.Lag(2): [Time].[Month].CurrentMember);
|||Thank
you very much for the help.
I realized something wrong here, but I just do
not know how to correct it. Thank you very much for point out. I tried Time
Dimension wizard, there are ‘month to date’ and ‘Three
Month Moving Average’ in Time Dimension wizard, but our project ask from Cognos Powerplay cube move to SSAS cube. in Powerplay,
‘Rolling 3 months’ will get each month order number for 3 months.
Should be like:
Month|
Orders
January, 2007|12345
December, 2006|14567
November, 2006|33562
Not 3 months Moving
Average. We expect get same
result like powerplay. Is there a way to get this result?
Thank you very much for the help, I really appreciated.
|||Please use the expression provided above.|||Hi Mosha,
Yes. I used the expression you provided above, but got "#VALUE!".
Rolling 3 months | Orders
-
Current Periods | 235263
Rolling 3 months | #VALUE!
What else I can do?
Thanks a lot.
|||Please provide the query that you sent, the exact MDX Script you have and the text of error message in order to find where is the problem.|||The code in BI studio script view:
First try:
CREATE MEMBER CURRENTCUBE.[Time].[Rolling 3 Months].[Rolling 3]
AS ([Time].[Rolling 3 Months].[Rolling 3],
[Time].[Month].[Month].Members)
=
Aggregate(
[Time].[Month].CurrentMember.Lag(2): [Time].[Month].CurrentMember
),
VISIBLE = 1 ;
Second try:
CREATE MEMBER CURRENTCUBE.[Time].[Rolling 3 Months].[Rolling 3]
AS [Time].[Month].CurrentMember.Lag(2): [Time].[Month].CurrentMember,
VISIBLE = 1 ;
Scope(
{
[Measures].[ORDERS]
}
);
// Rolling 3 Months
([Time].[Rolling 3 Months].[Rolling 3],
[Time].[Month].[Month].Members)
=
Aggregate(
[Time].[Month].CurrentMember.Lag(2): [Time].[Month].CurrentMember
);
End Scope;
Got same result in cube browser:
Rolling 3 Months | Orders
-
Current Periods | 123456
Rolling 3 | #VALUE!
no error message or say '#VALUE!' is an error message.
What's wrong?
|||
You still kept the portion with the error. You also need to decide what do you want to do at the levels above Month. I will put the code which keeps it NULL at such levels - up to you what to do there.
CREATE [Time].[Rolling 3 Months].[Rolling 3];
([Time].[Rolling 3 Months].[Rolling 3], [Time].[Month].[Month].Members, Measures.[ORDERS]) = Aggregate(
[Time].[Month].CurrentMember.Lag(2): [Time].[Month].CurrentMember
);
|||Thanks a lot Mosha.
I put the code you provided in to script view.
CREATE [Time].[Rolling 3 Months].[Rolling 3];
([Time].[Rolling 3 Months].[Rolling 3], [Time].[Month].[Month].Members, Measures.[ORDERS]) = Aggregate(
[Time].[Month].CurrentMember.Lag(2): [Time].[Month].CurrentMember
);
result:
[Rolling 3] row is missing.
Rolling 3 Months | Orders
--
Current Periods | 12345
Currently, we just want to get each month's order for last three months with 'Rolling 3 Months' this calculated member.
I am not sure your question for 'at the levels above Month', you mean Year level?
If I need Year level, how to add in?
am I missing some point?
Thanks a lot.
|||Yes, I meant on the Year level. It is not clear how you want to aggregate rolling 3 months to the Year. Anyway, you in order to see rolling sum on 3 month, please slice by some specific month, or add months to the axis.|||Thanks Mosha for the help.
I add specific month, but result some like before -no 'Rolling 3' row.
CREATE [Time].[Rolling 3 Months].[Rolling 3] ;
([Time].[Rolling 3 Months].[Rolling 3],[Time].[Month].[November 2006], Measures.[ORDERS]) = Aggregate(
[Time].[Month].[November 2006].Lag(2): [Time].[Month].[November 2006]
);
Something still wrong?
Thanks.|||The expression in the MDX script should stay the way it was before. It is your MDX query that needs to change to include slice by month.|||
As my understanding(may be wrong), MDX
script run in BI Studio, MDX query run in SS management Studio. In SS
management Studio, I used following MDX query:
SELECT Tail(
[Time].[Month].members,3 ) ON rows,
[Measures].[ORDERS] on columns
from [My cube]
Got
‘Rolling 3 Months’ data I want.
Month|
Orders
October
2006|12345
November
2006|34556
December
2006|23545
In BI Studio, if using filter by Month, it still able
to get ‘Rolling 3 Months’ result.
The
problem is using filter by Month, there are too many steps, our business user
will not accept that. They hope only drag ‘Rolling 3 Months’ from Time dimension drop to the cube browser then
can get above data (like Powerplay does). Is there a way to do that?
How to add MDX query in BI Studio?
Thanks for the help..
