Different operations along dimensions

I am having a stupid problem with my cube...

The data I have is a floating point number between 0 and 1, representing the utilization of an item (machine, production line), in percent for a time period. Let's say, it was 0.5 for Monday and 0.1 for Tuesday.

Not really the 99.9% reliability we usually look for, hehe - but that is another story. For the examples these low numbers are better and easier.

Anyway, if I want to see the average for Monday and Tuesday I use exactly that function: AVG as aggregation in my cube, and I am done. With the example above, I would get a 30% usage of my production machine. (0,5 + 0,1) / 2 in math speak.

My question now is this: I have not one machine, but several. Along this "machine number" dimension, I do NOT want to average, but really sum up the values: Let's say my 2nd machine did 0,8 for Monday, together with the #1 machine that did 0,5 I'd like to see the Monday really as 1.3, or 130% - the actual display of the number is no issue.

Sounds easy, but somehow I have no idea how to do that. Can I have different functions for each dimension that aggregates a value? Or is that a custom MDX script?

Have you tried changing your measure to use the AverageOfChildren aggregate function (instead of the Sum function)? (This is a measure property, not something in the calc script.) What you have is a semi-additive measure... it should be summed across all dimensions except for the Timem dimension. That's what the AverageOfChildren, LastNonEmpty, etc. aggregate functions are for.|||

Spot on! THANKS!

Defining the dimension as Time and using AverageOfChildren did solve it.

But...

What if I have several time dimensions? I might be doing something wrong, but it seems the AverageOfChildren does the Avg only on the FIRST time dimension in the cube - the other time dimensions are treated as the regular ones... does that make sense?

In my case, having date (year/date) and time (hour/minutes) in separate dimension I think I can solve by cross joining the tables and use that as source for one dimension. But what in a case where you have truly different dates in your cube, say an order date, one delivery date and maybe one invoice date, yet you need your counter to be "avg" or "last" or so along EACH of these datetime dimensions?

|||

You're correct that semi-additive measures sum across all dimensions except the first time dimension. Usually this is what you want... for instance, take an Inventory Status measure group which is a daily snapshot of all your inventory. Besides your main Date dimension, you might also have a Received Date dimension. If you sliced by Received Date, you're wanting to slice down to inventory which was received on that date, but the semi-additive behavior should operate on the main Date dimension not the Received Date dimension.

Your situation is probably not the common one, so you may have to resort to using the calc script to detect which date dimension you have sliced by.

I would avoid putting the days and time of day within the same dimension if you can. It's a simple dimension size concern... a normal Date dimension which goes down to day has 365 members per year. But if you go down to second, it has 31,536,000 members.