Aggregation of Calculated members of form: MEASURE op ATTRIBUTE VALUE

hi there. i do not understand how to use an attribute in a calculation. In the query below, I'm trying to say:

for all members of the date.calendar hierarchy, show me the sum of (sales amount fact * dealer price attribute for the product sold)

with

member [measures].[fact times attribute]

as cdbl([Measures].[Sales Amount]) * cdbl([Product].[Dealer Price].CurrentMember.memberValue)

select [measures].[fact times attribute] oncolumns,

[Date].[Calendar].allmembersonrows

from [Adventure Works]

where [Geography].[City].&[Beaverton]&[OR]

this gives a type mismatch error complaining that "All Products" cannot be cast to double. But I don't understand how to force the calculation to occur at the lowest level and for aggregation to be done subsequently.

Product is currently at the "All Product" member. What you are actually wanting to do is get the price for each product and multiply that by the sales of that product and then aggregate (i'll assume sum) those values. That query looks like the one below. (Actually, the one below is kinduva shortcut to the solution. Instead of going to the individual product, I just went to the product price. If two products have the same price, the query kinda says that we treat them the same. Nit-picky, I know.)

Good luck.

Code Snippet

withmember [measures].[fact times attribute] as

SUM([Product].[Dealer Price].[Dealer Price].Members,

[Measures].[Sales Amount])

select [measures].[fact times attribute] oncolumns,

NONEMPTY [Date].[Calendar].allmembersonrows

from [Adventure Works]

where ([Geography].[City].&[Beaverton]&[OR])

;

|||Dear Bryan,

Thanks for your pointers. I actually work in hong kong so were ~12 hours ahead of you. I'm checking this from home and so can't test it. but i'm reviewing it now, because I'd love to get this right for work tomorrow.

Looking at your solution, one thing I find very conceptually troubling:

>>I don't see a multiplication sign anywhere!!!!<<

So at the very point where I would imagine attribute and fact really connect, the code is silent! Also when you refer to a shortcut and being nitpicky, that is all just going straight over my head - my knowledge of mdx (rather like my knowledge of cantonese just isn't evolved enough to understand the subtleties here. I can just about order lunch with a lot of gesticulation but that's it.

i will of course kick the tires on this tomorrow when I get into work. But [embarrassingly] I've been trying to figure out what the missing link is here for almost 3 days without success! Would you mind just leaving a working "best practice" solution for me? I feel like I sound like a lazy person - I'm not, I just don't grock this yet and its driving me nuts.

Yours,

John G.
|||

Sorry about that. I was pushing to get the code assembled and totally dropped that one critical part of the code. Try this one. I use the CurrentMember of the Dealer Price to get to the value.

Code Snippet

withmember [measures].[fact times attribute] as

SUM(

[Product].[Dealer Price].[Dealer Price].Members,

[Measures].[Sales Amount]*[Product].[Dealer Price].CurrentMember.MemberValue

)

select [measures].[fact times attribute] on columns,

NON EMPTY [Date].[Calendar].allmembers on rows

from [Adventure Works]

where ([Geography].[City].&[Beaverton]&[OR])

;

|||

Jo,

Try to use named calculations in the datasourceview of your factTable... it would be a better performance, because you only run the formulas once when the cube is processing.

Regards!

|||

ok it works fine - THANKS!

I also tried this which returns the same results - is it logically identical?

with

member [measures].[fact times attribute]

asSUM([Product].[Product].[Product].Members,

[Measures].[Sales Amount]*[Product].[Dealer Price].CurrentMember.MemberValue

)

select {[measures].[fact times attribute]} oncolumns,

NONEMPTY [Date].[Calendar].allmembersonrows

from [Adventure Works]

where ([Geography].[City].&[Beaverton]&[OR])

i'm not sure if you'd have the patience of a saint to wade through what follows, but I'm trying to understand the execution of the query. Would you mind critiquing the below, or if it's easier for you just describe the above query in pseudo code for dummies

For my general understanding when you refer to [Product].[Dealer Price].CurrentMember.MemberValue,

what is the context of the CurrentMember? Is it:

1. The current member in the context of traversing the set of members of the product hierarchy as defined by the set argument passed to the SUM function.

or 2. the currentmember in some wider context of the query (actually I'm not sure if that makes sense, so I'll go with answer 1).

Assuming 1. above, then I would interpret the query as follows:

1. slice by beaverton

2. iterate through all members of the date hierarchy.

3. for each sales fact within the intersection of beaverton and the current date, calculate [fact times attribute] as follows:

a) for each member of the product hierarchy ...

i) ... extract the tuple set defined by the intersection of that product within the current region [for many product members this will be a null set]

ii) for that set of tuples, sum up (sales amount * dealer price).

iii) keep running total of that sum, and the final total will be your result of the current date member.

|||

thanks - this did occur to me, since as i understand it calculated members (even cube scoped ones) are calculated at runtime not at processing time, correct?

however, in my real calculation I'm using another calculated member which doesn't exist in the underlying table so this makes it tricky.

Would you say that in general, one should strive to do as much data cleaning / preparation / precalculation as possible outside the cube, and then just leave the cube to prepare aggregations and pure analysis calculations that are hard to do outside of mdx?

|||Yeah joGo, I'm with you! If you have other calculated members that you cannot replace for named calculation, so you are right...|||

Your code is logically similar and should produce the same result. You may want to test for performance differences, but the only way I can imagine a performance difference would exist would be under a very particular situation I suspect does not exist in the cube. (In other words, they probably have the same performance so don't sweat it.)

Regarding the CURRENTMEMBER question, you have to keep in mind context at all times. In the SUM function, you generate a set. That set definition is in the context of the cube as a whole. If I want to limit that context based on my slicer (WHERE clause), I can use the EXISTING keyword in the set definition.

So, now I have a set. Then, for each member in that set, I will return and/or calculate a value. That value is determined in the context of the member from the set I am currently working with. At this point, how that set came to be is unknown to me. The set has been determined and I'm just working through it blindly. I think this is what you're saying in the section at the bottom of your email.

Sorry I am being slow to respond today. The MSDN forum email seems to be jammed up a bit and I'm not getting alerts like I should.

Thanks,
Bryan

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.

Advice required: Matrix report with a static column

First off, I apologize if this is an elementary question. Thanks for your
patience.

I have a requirement to develop a report that shows by calendar day how much
effort a resource has expended on what tasks. So, I have a stored procedure
that returns data that looks like this:

Resource calendarDay description etcHours effortHours
- - -- - --
Smith, John 2006-08-01 Requirements 20.00 8.00
Smith, John 2006-08-02 Requirements 20.00 8.00
Smith, John 2006-08-03 Design 80.00 8.00
Doe, Jane 2006-08-01 Requirements 5.00 4.00
Doe, Jane 2006-08-01 Design 30.00 4.00

And I've developed a matrix report that looks like this:

AUGUST
1 2 3 4 5
-
Name 1 Requirements 8.00 8.00
Design 8.00
Name 2 Requirements 4.00
Design 4.00

What I need to do, however, is add the "ETC" value at the END of the matrix
row for each Task for each Resource, so that it looks like this:

AUGUST
1 2 3 4 5 ETC

Name 1 Requirements 8.00 8.00 20.00
Design 8.00 80.00
Name 2 Requirements 4.00 5.00
Design 4.00 30.00

I can't seem to add a column to the matrix to represent the ETC column. What
would be the best way to accomplish this?

Thanks in advance for any replies,
Dr Jazz

Dr.

You can right click on Column 5 and select "Add Column" this will create ETC, this will add a static column to the matrix.

Hammer

|||

Hammer2 wrote:

You can right click on Column 5 and select "Add Column" this will create ETC, this will add a static column to the matrix.

Thanks for the reply, Hammer. Unfortunately, "Column 5" doesn't exist in the report designer because this is a matrix control. The only items available are "Column Groups" and I have two defined: one for the month, then another for the actual day (since the date range is a parameter specified by the user). I can right-click and add more Column Groups, but I cannot add a single static column, thus my dilemma.

Cheers,
Dr. Jazz

|||

Humm,

Puzzling because I have multiple Matrix reports and a number of them have static column, you should be able to "Add a column" this is a static column - you can also google matrix reports and static columns, this should bring up some examples.

Hope this helps

Hammer

|||

Hi Hammer,

Thanks for your continued patience and assistance. Check out this web page that will hopefully illustrate my problem better:

http://www.duotronic.com/matrix/matrix.htm

As you can see, I am able to get an "Add column" menu item, but I don't want it to be part of the group because this value will then be displayed for every day in the group. I just want it displayed at the end of the row. Is there a way to do this?

Cheers,
Dr Jazz

|||You arent using your query correctly as you should be using aggregates on that column as well.
SQL cannot tell if you just want a column there since your report is a grouping report.

Can you post your SQL query.

http://jhermiz.googlepages.com|||

Hi jhermiz. Thanks for your reply. I have no doubt that the structure of my query is not exactly compatible with how Reporting Services requires it. My query is listed below:

SELECT
emp.LastName + ', ' + emp.FirstName AS Resource,
cal.calendarDay,
task.taskID,
task.taskCode,
task.description,
tass.etcHours,
-- Get billable hours for this user on this day
SUM(tae.effortHours) AS effortHours

FROM
dbo.udf_getDateRangeTable(@.startDate, @.endDate) AS cal
CROSS JOIN HR.dbo.Employee AS emp
LEFT JOIN dbo.TaskAssignment AS tass ON tass.resourceID = emp.employeeID
LEFT JOIN dbo.TaskActualEffort AS tae ON tae.assignmentID = tass.assignmentID
AND tae.startDate = cal.calendarDay
LEFT JOIN dbo.Task AS task ON tass.taskID = task.taskID
LEFT JOIN dbo.TrackingItem AS ti ON task.trackingItemID = ti.trackingItemID

WHERE
ti.masterProjectID = @.masterProjectID
AND emp.employeeID IN (SELECT employeeID FROM HR.dbo.ProjectEmployeeAssignment WHERE projectID = @.masterProjectID)
-- Only return resources who have actually billed time in the reporting period
AND EXISTS
(
SELECT 1
FROM
dbo.TaskAssignment AS tass1
INNER JOIN dbo.Task AS task1 ON tass1.taskID = task1.taskID
INNER JOIN dbo.TaskActualEffort AS tae1 ON tass1.assignmentID = tae1.assignmentID
INNER JOIN dbo.TrackingItem AS ti1 ON ti1.trackingItemID = task1.trackingItemID
WHERE
tass1.resourceID = emp.employeeID
AND tae1.startDate BETWEEN @.startDate AND @.endDate
AND ti1.masterProjectID = @.masterProjectID
)

GROUP BY
emp.LastName + ', ' + emp.FirstName,
calendarDay,
emp.employeeID,
task.taskID,
task.taskCode,
task.description,
tass.etcHours

ORDER BY
task.taskID,
Resource,
cal.calendarDay

Cheers,
Andre

|||

Hey Dr.

Nice pages by the way, Okay, I understand the nature of your problem - When you "Add the column" you can use the "inscope" expression to tell the which group to display for.

Does that make sense?

Hammer