CODEWITHSUNDEEP
ssasmdx
whytheq
whytheq

Reputation: 35557

Issue with SSAS: Definition of a set causes a circular reference

This is ok and returns the sum of the default measure for the past 30 days:

SELECT
FROM    [OurCube]  
WHERE   (
        closingperiod(
            [Date].[Date - Calendar Month].[Calendar Day],
            [Date].[Date - Calendar Month].defaultmember
            ):
        closingperiod(
            [Date].[Date - Calendar Month].[Calendar Day],
            [Date].[Date - Calendar Month].defaultmember
            ).ITEM(0).LAG(30)
            )

How do I transfer the set of dates in the WHERE clause into the WITH clause ?
I attempted the following but it creates the error message detailed:

WITH 
    SET [30Days] AS
    {
    closingperiod(
            [Date].[Date - Calendar Month].[Calendar Day],
            [Date].[Date - Calendar Month].defaultmember
            ):
    closingperiod(
        [Date].[Date - Calendar Month].[Calendar Day],
        [Date].[Date - Calendar Month].defaultmember
        ).ITEM(0).LAG(30)
    }
SELECT
FROM  [OurCube]  
WHERE (
      [30Days]
      )

Executing the query ...
The definition of the 30Days set contains a circular reference.
Execution complete

Upvotes: 1

Views: 1573

Answers (1)

Marc Polizzi
Marc Polizzi

Reputation: 9375

I believe this is a limitation of SSAS and has something to do with the execution processing of the different parts of the query; even using a SUB-QUERY (i.e., FROM ( SELECT ... )) instead of a WHERE would not solve the issue.

You can define your set at schema level : 

CREATE STATIC SET [OurCube].[30Days] as ...

Or depending on your client application at session level:

CREATE SESSION STATIC SET SET [OurCube].[30Days] as ...

Upvotes: 3

Related Questions