Aggregate() doesn't aggregate over an expression

We use CTP3 of SQL Server 2005 SP2. We have the following scope expression

Scope
(
[Date].[Calendar Year].[Calendar Year].Members,
[Date].[Month].Members
);

(
{
[Measures].[Profit YTD]
}
) =

Aggregate (
PeriodsToDate([Date].[Calendar].[Calendar Year], [Date].[Calendar].CurrentMember)
, StrToMember(Extensibility.Replace([Measures].CurrentMember.UniqueName, " YTD", "")) -- remove " YTD" suffix to aggregate over the corresponding standard measure

);

Format_String ( This ) = "Currency";
End Scope;

However, Aggregate() essentially bypasses the call to the SSAS stored procedure (Extensibiliy.Replace is a custom SSAS stored procedure). Instead Aggregate() uses the [Profit YTD] measure instead. If I replace Aggregate() with SUM() it works correctly. Is this a bug? A workaround?

Is [Profit YTD] a calculated measure itself ? If this is a case, Aggregate would switch solve orders with it in order to try to determine the correct aggregation function. If Sum is what you want to do, then using Sum directly is probably a right thing to do here.

I also want to note, that the expression inside Aggregate (or Sum) is very inefficient. If you have plenty of calc measures with YTD suffix, and you want to compute year to date over corresponding measure without YTD suffix, there are much more efficient ways of doing it.

|||

Mosha,

Thank you so much for looking into this. I really appreciate your help. Yes, Profit YTD is a calculated member.

Here are our requirements. We have a cube with rather large dimensions, e.g. Customer dimension (some 150,000 customers) and Account dimension (represents a customer bank account) with some 1.5 million members. We need to support additive and semi-additive aggregations (e.g. rolling twelve, weighted averages, etc). Considering the fact that the Report Builder doesn't support dimension-level calculated members (time intelligence), we have no other option but to create measure-level calculated members for each calculation, e.g.: Profit R12 for rolling 12 aggregation of the Profit additive measure, Average Balance R12 for rolling 12 aggregation of the Average Balance weighted average, etc. In addition, we have a requirement to provide YTD and QTD calculations.

The idea behind the the script above was to work universally, i.e. to use the corresponding measure underlying function since additional measures will be added to the scope. For example, if we calculate Profit YTD, we need to base our calculation on the Profit measure (hence, we need to remove the YTD suffix).

Here is an example of our script for R12 aggregations:

Scope
(
[Date].[Month].[Month].Members

);

(
{
[Measures].[Interest Paid R12],
[Measures].[Interest Accrued R12],
...
}
) =
Sum (
{
ParallelPeriod(
[Date].[Calendar].[Month],
11,
[Date].[Calendar].CurrentMember
) : [Date].[Calendar].CurrentMember
}
// current measure
, StrToMember(Extensibility.Replace([Measures].CurrentMember.UniqueName, " R12", "")) -- remove R12 suffix to get to the the corresponding regular measure
);

Format_String ( This ) = "Currency";
End Scope;

What will be the recommended approach from a performance standpoint? Should we introduce multiple scopes (for additive and semi-additive measures) that use the respective aggregation function directly? Should we copy the measure before we start aggregating to avoid StrToMember(), e.g.:

CREATE MEMBER CURRENTCUBE.[MEASURES].[Profit YTD] AS [MEASURES].[Profit] , FORMAT_STRING = "Currency";

|||

Considering the fact that the Report Builder doesn't support dimension-level calculated members (time intelligence)

So the root of your problem is that you cannot use utility attribute in Time dimension or separate utility dimension because of Report Builder limitation ? My advice is to do it right way with utility dimension, but instead of creating calculated members, make R12, YTD etc as real members in this dimension, they just won't be associated with any data. Then, you can put the formulas on them either using custom member formulas or inside MDX script.

|||

Thank you.

1. I'v e read your post about this and and David's article but I need to wrap my head about the utility dimension concept. Would mind eleborating a bit more on the utility dimension approach? Are you saying to add the utility dimension to the cube but don't link this dimension to the measure group at all? What changes need to be made to the Time Intelligence script (if any) to re-purpose it to use the utility dimension?

2. In case the end users find the utility dimension approach confusing from an usability standpoint, can we explore a more efficient approach with standalone calculated members?

|||

Are you saying to add the utility dimension to the cube but don't link this dimension to the measure group at all?

Or you can link the "Normal" or "Current" member of this dimension to the fact table by creating a new calculated column in DSV with constant|||

After a few hours of experimenting and brain crunching I am getting nowhere:

1. Introducing an utility dimension that is not linked to the measure group seems to work in the cube browser/Excel but it doesn't work in the Report Builder because the Report Builder model expects a dmension relationship. Otherwise, there is no navigational path in the model and once you drag and drop the dimension, there is nowhere to go to.

2. The utility dimension approach is causing much grief. Here is what I do.

a) I created a named query DimDateCalculations as follows:

SELECT - 1 AS MemberKey, - 1 AS CurrentPeriodID, 'Current Period' AS CalendarDateCalculations, 'Current Period' AS FiscalDateCalculations
UNION
SELECT 1 AS MemberKey, - 1 AS CurrentPeriodID, 'Rolling 12' AS CalendarDateCalculations, 'Rolling 12' AS FiscalDateCalculations
UNION
SELECT 2 AS MemberKey, - 1 AS CurrentPeriodID, 'YTD' AS CalendarDateCalculations, 'YTD' AS FiscalDateCalculations
UNION
SELECT 3 AS MemberKey, - 1 AS CurrentPeriodID, 'QTD' AS CalendarDateCalculations, 'QTD' AS FiscalDateCalculations

b) I added a calculated column CurrentPeriodID to the fact table and defaulted it to -1. In DSV, I joined DimDateCalculations to the fact table on CurrentPeriodID

c) I created a new dimension (Date Calculations) on top of the DimDateCalculations named query. The dimension key is set to MemberKey and it has two attribute hierarchies (CalendarDateCalculations and FiscalDateCalculations) whose default members are set to the Current Period member.

d) I added the following script to the cube (only changed the dimension name in the script that the Time Intelligence Wizard generates):

Scope(
{
[Measures].[Tax],
[Measures].[Profit],
}
);

(
[Date Calculations].[Calendar Date Calculations].[YTD],
[Date].[Calendar Year].[Calendar Year].Members,
[Date].Month.Members
) =

Aggregate(
{[Date].[Calendar Date Calculations].DefaultMember} *
PeriodsToDate([Date].[Calendar].[Calendar Year],
[Date].[Calendar].CurrentMember)

);
End Scope;

e) In the cube browser, I created a report which filters the Calendar Date dimension to a given month. I dropped the Calendar Date Calculations on columns, another dimension on rows, and Profit as data.

However, the YTD column is emtpy (after Show Empty Cells is enabled). What am I missing? Again, our requirement is that the utility dimension cannot have calculated members.