I have two time dimensions, production period and accounting period, and a measure that I want to aggregate with either dimension but not with both when users query the cube from excel. To do that I created a flag measure to check if both dimensions are being used
CREATE MEMBER CURRENTCUBE.[Measures].[AcctProdFlag] AS
IIF (
[DIM Accounting Period].[Accounting Period Hierarchy].CURRENTMEMBER.level.ordinal <> 0 and
[DIM Production Period].[Production Month Hierarchy].currentmember.level.ordinal = 0 ,
1,
IIF ( [DIM Production Period].[Production Month Hierarchy].currentmember.level.ordinal <> 0 and
[DIM Accounting Period].[Accounting Period Hierarchy].currentmember.level.ordinal = 0 ,
2,
3
)
), VISIBLE =0;
Then I use this flag to create my measure as such
CREATE MEMBER CURRENTCUBE.[Measures].[Sales/day] AS
IIF([Measures].[AcctProdFlag] = 1 ,
([Measures].[Sales] / [Measures].[Accounting Period Day Count]),
IIF([Measures].[AcctProdFlag] = 2,
([Measures].[Sales] / [Measures].[Production Period Day Count]),
"NA")),
VISIBLE = 1, DISPLAY_FOLDER = 'Sales\Daily' , FORMAT_STRING = "#,###";
When I use this query from management studio, it works and returns "NA" as expected because both dimensions are being used
SELECT {[Measures].[Sales/day]} ON COLUMNS ,
[DIM Production Period].[Production Month].MEMBERS ON ROWS
FROM [My Cube]
WHERE {[DIM Accounting Period].[Accounting Year].[2014],
[DIM Accounting Period].[Accounting Year].[2015]};
But when I add accounting year to filter in excel and add production period in rows, the measure shows values (it shouldn't as per the logic) when I select multiple accounting years but shows "NA" (as expected) when I select one accounting year. Turns out that when I select multiple years, excel send the following query to SSAS causing it to loose the context
SELECT {[Measures].[Sales/day]} ON COLUMNS ,
[DIM Production Period].[Production Month].MEMBERS ON ROWS
FROM (SELECT (
{[DIM Accounting Period].[Accounting Year].[2014],[DIM Accounting Period].[Accounting Year].[2015]}
) ON COLUMNS
FROM [My Cube]
)
Is there anything I can do to fix this?. I am using SSAS for SQL Server 2008 R2 and Excel 2013.
One way to do it is a dynamic named set as described in this post in the "Using dynamic sets to detect subselects" section.
The other way would be to build a measure group on top of DimAccountingPeriod connected only to that one dimension and build a count measure. Then to test if there are no filters on Dim Accounting Period:
IIf(
[Measures].[Accounting Period Count] = ([Measures].[Accounting Period Count], [DIM Accounting Period].[Accounting Period Hierarchy].[All])
,1
,0
)
To expand further on how you would use that:
CREATE MEMBER CURRENTCUBE.[Measures].[AcctProdFlag] AS
IIF (
[Measures].[Accounting Period Count] < ([Measures].[Accounting Period Count], [DIM Accounting Period].[Accounting Period Hierarchy].[All]) and
[Measures].[Production Period Count] = ([Measures].[Production Period Count], [DIM Production Period].[Production Month Hierarchy].[All]) ,
1,
IIF ( [Measures].[Production Period Count] < ([Measures].[Production Period Count], [DIM Production Period].[Production Month Hierarchy].[All]) and
[Measures].[Accounting Period Count] = ([Measures].[Accounting Period Count], [DIM Accounting Period].[Accounting Period Hierarchy].[All]) ,
2,
3
)
), VISIBLE =0;
You can try the dynamic set approach, but the measures approach perform best.
So I ended up doing the following to resolve the issue. I created two measures as NULL in the data source view and called them; [Measures].[Prod Months Used] and [Measures].[Acct Months Used]
I also created calculated members to count all production months and all accounting months. Those worked as such
-- This measure is created to count the number of production months in the cube
CREATE MEMBER CURRENTCUBE.[Measures].[AllProdMonths] AS
SUM(
DESCENDANTS(
[DIM Production Period].[Production Month Hierarchy].[All],
[DIM Production Period].[Production Month Hierarchy].[Production Month], SELF
)
, 1
), VISIBLE=0 ;
-- This measure is created to count the number of accounting months in the cube
CREATE MEMBER CURRENTCUBE.[Measures].[AllAcctMonths] AS
SUM(
DESCENDANTS(
[DIM Accounting Period].[Accounting Period Hierarchy].[All],
[DIM Accounting Period].[Accounting Period Hierarchy].[Accounting Month], SELF
)
, 1
), VISIBLE =0;
Finally to count the number of used accounting months and production months I did the following:
-- This measure is scoped to count the number of used Production months in excel (including filters)
-- It does that by overwritting all values except the All member.
SCOPE (
([Measures].[Prod Months Used],
[DIM Production Period].[Production Month Hierarchy].[Production Month].MEMBERS)
);
SCOPE DESCENDANTS(
[DIM Production Period].[Production Month Hierarchy].[All],, AFTER
);
THIS = 1;
END SCOPE;
END SCOPE;
-- This measure is scoped to count the number of used Accounting months in excel (including filters)
-- It does that by overwritting all values except the All member.
SCOPE (
([Measures].[Acct Months Used],
[DIM Accounting Period].[Accounting Period Hierarchy].[Accounting Month].MEMBERS)
);
SCOPE DESCENDANTS(
[DIM Accounting Period].[Accounting Period Hierarchy].[All],, AFTER
);
THIS = 1;
END SCOPE;
END SCOPE;
and my flag would be evaluated according to the following expression
CREATE MEMBER CURRENTCUBE.[Measures].[AcctProdFlag] AS
IIF (
[Measures].[Acct Months Used] < [Measures].[AllAcctMonths] AND
[Measures].[Prod Months Used] = [Measures].[AllProdMonths],
1,
IIF ( [Measures].[Acct Months Used] = [Measures].[AllAcctMonths] AND
[Measures].[Prod Months Used] < [Measures].[AllProdMonths] ,
2,
3
)
), VISIBLE =0;
This solution is working perfectly and performance is really good.
