I get this error when I ran the below statement what did I do wrong?
"The definition of MonthRange set contains a circular reference"
WITH SET [MonthRange] AS
{
[Dim Originationasofmm].[Dim Originationasofmm].&[200101].PrevMember
:
[Dim Originationasofmm].[Dim Originationasofmm].&[200101].NextMember
}
SELECT {[Measures].[Closing Balance]} on 0,
{[Dim Asofmm].FirstChild : [Dim Asofmm].[200612]} on 1
FROM [ Bond Analytics OLAP]
WHERE ([MonthRange], [Industry].&[Subprime])
[Dim Originationasofmm] is a regular dimension with members like 200101, 200102, 200103 so on.
What I wanted to do (Note that I do not have a Time Dimension in the cube, just a dimension that simulates this, so would this work just as well? )
Can I have a generic set that would take as input current member and give 3 or 6 or 12 rolling months?
(this is assuming that I cannot switch to using time dimension anytime soon and just have to use a regular dimension for now?)
For example,
given 200101 and say 3 for 3 months rolling period, I would get 200012, 200101, 200102
and
given 200101 and say 7 for 6 months rolling period, I would get 200010, 200011, 200012, 200101, 200102, 200103, 200104
Try this version:
WITH
SET [MonthRange] AS
LastPeriods(3,
[Dim Originationasofmm].[Dim Originationasofmm].&[200101].NextMember
)
MEMBER [Dim Originationasofmm].[Dim Originationasofmm].[MonthRange] as
Aggregate([MonthRange])
SELECT {[Measures].[Closing Balance]} on 0,
{[Dim Asofmm].FirstChild : [Dim Asofmm].[200612]} on 1
FROM [ Bond Analytics OLAP]
WHERE ([Dim Originationasofmm].[Dim Originationasofmm].[MonthRange],
[Industry].&[Subprime])
|||Hi Deepark,
This query works! but as soon as I add any other measure below (1 or more) to the query, only column that has data would be [Closing Balance] and all other measures are NULL.
Is it because of some SCOPING issue in the calculation?
--Query only returns data for [Closing Balance], all other measures are NULL when they should have data.
WITH SET [MonthRange] AS
LastPeriods(3,[Dim Originationasofmm].[Dim Originationasofmm].&[200101].NextMember)
MEMBER [Dim Originationasofmm].[Dim Originationasofmm].[MonthRange] AS Aggregate([MonthRange])
SELECT {
[Measures].[Closing Balance],
[Measures].[D BankRuptcy],
[Measures].[D Foreclosure],
[Measures].[Def OTS]
} on 0,
{[Dim Asofmm].FirstChild:[Dim Asofmm].[200612]} on 1
FROM [Bond Analytics OLAP]
WHERE ([Dim Originationasofmm].[Dim Originationasofmm].[MonthRange],[Industry].&[Subprime])
GO
Calculations:
/* Rewrite of this to use SCOPE is below
CREATE MEMBER CURRENTCUBE.[MEASURES].[D BankRuptcy]
AS case when isempty([Measures].[Closing Balance]) Then Null
When [Dim MBA].currentmember IS [Dim MBA].[Bankruptcy] Then
([Dim MBA].[Bankruptcy],[Measures].[% by Delinquincy Currentbalance])
When [Dim MBA].currentmember IS [Dim MBA].[Current] Then Null
When [Dim MBA].currentmember is [Dim MBA].[All] then
([Dim MBA].[Bankruptcy],[Measures].[% By Delinquincy Currentbalance])
When [Dim MBA].currentmember is [Dim MBA].[MBA 30] then null
When [Dim MBA].currentmember is [Dim MBA].[MBA 60] then null
When [Dim MBA].currentmember is [Dim MBA].[MBA 90] then null
When [Dim MBA].currentmember is [Dim MBA].[Foreclosure] then null
When [Dim MBA].currentmember is [Dim MBA].[REO] then null
end,
FORMAT_STRING = "Percent",
VISIBLE = 1;
*/
CREATE MEMBER CURRENTCUBE.[MEASURES].[D BankRuptcy]
AS NULL,
VISIBLE = 1;
SCOPE([Measures].[D BankRuptcy]);
SCOPE(ROOT([Dim MBA]));
THIS = ([Dim MBA].[Bankruptcy],[Measures].[% by Delinquincy Currentbalance]);
FORMAT_STRING(This) = "percent";
NON_EMPTY_BEHAVIOR(This) = [Measures].[Closing Balance];
END SCOPE;
SCOPE ([Dim MBA].[Bankruptcy]);
THIS = ([Dim MBA].[Bankruptcy],[Measures].[% by Delinquincy Currentbalance]);
FORMAT_STRING(This) = "percent";
NON_EMPTY_BEHAVIOR(This) = [Measures].[Closing Balance];
END SCOPE;
END SCOPE;
-
/* Rewrite of this to use SCOPE is below
CREATE MEMBER CURRENTCUBE.[MEASURES].[D Foreclosure]
AS case when isempty([Measures].[Closing Balance]) Then Null
When [Dim MBA].currentmember IS [Dim MBA].[Foreclosure] Then
([Dim MBA].[Foreclosure],[Measures].[% by Delinquincy Currentbalance])
When [Dim MBA].currentmember IS [Dim MBA].[Current] Then Null
When [Dim MBA].currentmember is [Dim MBA].[All] then
([Dim MBA].[Foreclosure],[Measures].[% By Delinquincy Currentbalance])
When [Dim MBA].currentmember is [Dim MBA].[MBA 30] then null
When [Dim MBA].currentmember is [Dim MBA].[MBA 60] then null
When [Dim MBA].currentmember is [Dim MBA].[MBA 90] then null
When [Dim MBA].currentmember is [Dim MBA].[Bankruptcy] then null
When [Dim MBA].currentmember is [Dim MBA].[REO] then null
end,
FORMAT_STRING = "Percent",
VISIBLE = 1; */
CREATE MEMBER CURRENTCUBE.[MEASURES].[D Foreclosure]
AS NULL,
VISIBLE = 1;
SCOPE([Measures].[D Foreclosure]);
SCOPE(ROOT([Dim MBA]));
THIS = ([Dim MBA].[Foreclosure],[Measures].[% by Delinquincy Currentbalance]);
FORMAT_STRING(This) = "percent";
NON_EMPTY_BEHAVIOR(This) = [Measures].[Closing Balance];
END SCOPE;
SCOPE ([Dim MBA].[Foreclosure]);
THIS = ([Dim MBA].[Foreclosure],[Measures].[% by Delinquincy Currentbalance]);
FORMAT_STRING(This) = "percent";
NON_EMPTY_BEHAVIOR(This) = [Measures].[Closing Balance];
END SCOPE;
END SCOPE;
-
/* Rewrite of this to use SCOPE is below
CREATE MEMBER CURRENTCUBE.[MEASURES].[Def OTS]
AS case when isempty([Measures].[Closing Balance]) Then Null
When [Dim OTS].currentmember IS [Dim OTS].[OTS 30] Then Null
When [Dim OTS].currentmember IS [Dim OTS].[OTS 60] Then Null
When [Dim OTS].currentmember IS [Dim OTS].[OTS 90] Then Null
When [Dim OTS].currentmember IS [Dim OTS].[Bankruptcy] Then Null
When [Dim OTS].currentmember IS [Dim OTS].[Foreclosure] Then Null
When [Dim OTS].currentmember IS [Dim OTS].[Current] Then Null
When [Dim OTS].currentmember is [Dim OTS].[All] then
([Dim OTS].[REO],[Measures].[Closing Balance])
/([Dim OTS].[ALL],[Measures].[Closing Balance])
When [Dim OTS].currentmember is [Dim OTS].[REO]
then ([Measures].[Closing Balance]) /([Dim OTS].[REO],[Measures].[Closing Balance])
end,
VISIBLE = 1;
*/
CREATE MEMBER CURRENTCUBE.[MEASURES].[Def OTS]
AS NULL,
VISIBLE = 1;
SCOPE([Measures].[Def OTS]);
SCOPE(ROOT([Dim OTS]));
THIS = ([Dim OTS].[REO],[Measures].[Closing Balance])
/([Measures].[Closing Balance]);
FORMAT_STRING(This) = "percent";
NON_EMPTY_BEHAVIOR(This) = [Measures].[Closing Balance];
END SCOPE;
SCOPE ([Dim OTS].[REO]);
THIS = 1;
FORMAT_STRING(This) = "percent";
NON_EMPTY_BEHAVIOR(This) = [Measures].[Closing Balance];
END SCOPE;
END SCOPE;
Well, I was able to reproduce this behavior in Adventure Works by creating a calculated measure with default value of NULL (as above); then just assigning it a constant value. But setting the new SP2 SCOPE_ISOLATION property seems to solve it - like:
MEMBER [Dim Originationasofmm].[Dim Originationasofmm].[MonthRange] AS Aggregate([MonthRange]),
SCOPE_ISOLATION = CUBE
|||Hi Deepark,
Ok, so I don't know what SCOPE_ISOLATION does, but it works!!!
Perhaps, it is a new feature in SQL Server 2005 SP2.
Maybe it has something to do with calculation order.
Thank you very much.
You have helped me more than once (on many other discussion groups as well) and I am very grateful for that.
I think it's time you get a new title --> SUPER MVP
Hi Deepark,
Ok, so I don't know what SCOPE_ISOLATION does, but it works!!!
Perhaps, it is a new feature in SQL Server 2005 SP2.
Maybe it has something to do with calculation order.
Thank you very much.
You have helped me more than once (on many other discussion groups as well) and I am very grateful for that.
I think it's time you get a new title --> SUPER MVP
by the way, the code that works look like this:
--so if I want another period, I just replace # 3 and [200101] with appropriate member chosen by the user.
--I wonder if I finally do have a Time dimension, would using CurrentMember work so I don't have to hard-code [200101]?
WITH SET [MonthRange] AS
LastPeriods(3,[Dim Originationasofmm].[Dim Originationasofmm].&[200101].NextMember)
MEMBER [Dim Originationasofmm].[Dim Originationasofmm].[MonthRange] AS Aggregate([MonthRange]),
SCOPE_ISOLATION = CUBE
SELECT {
[Measures].[Closing Balance],
[Measures].[D BankRuptcy],
[Measures].[D Foreclosure],
[Measures].[Def OTS],
[Measures].[D30 OTS],
[Measures].[D60 OTS],
[Measures].[D90 OTS]
} on 0,
{[Dim Asofmm].FirstChild:[Dim Asofmm].[200612]} on 1
FROM [Bond Analytics OLAP]
WHERE ([Dim Originationasofmm].[Dim Originationasofmm].[MonthRange],[Industry].&[Subprime])
GO
--this one also works, so if I want another rolling period, I just replace the lag #
WITH SET [MonthRange] AS
{[Dim Originationasofmm].&[200612].lag(1):[Dim Originationasofmm].&[200612].lag(-1)}
MEMBER [Dim Originationasofmm].[Dim Originationasofmm].[MonthRange] AS Aggregate([MonthRange]),
SCOPE_ISOLATION = CUBE
select
{
[Measures].[Closing Balance],
[Measures].[D BankRuptcy],
[Measures].[D Foreclosure],
[Measures].[Def OTS],
[Measures].[D30 OTS],
[Measures].[D60 OTS],
[Measures].[D90 OTS]
} on 0,
([Dim Asofmm].FIRSTchild:[Dim Asofmm].[200612]) on 1
from [Bond Analytics OLAP]
WHERE ([Dim Originationasofmm].[Dim Originationasofmm].[MonthRange],[Industry].&[Subprime])
