Non-additive measures

We need to support non-additive measures in our cube, such as rates. The rates will be averaged across time by using a custom formula (weighted average). The rate measures cannot be aggregated by any other dimension. However, when the end user browses by the Account dimension (each member in the Account dimension relates to exactly one record in the fact table), the user must be able to see the rates.

I set the average function of the rates measures to None. How can I surface the rate measures from the measure leaves to the Account dimension leaves

Hi, Mosha. I appologize for not making this clearer. The measure group is already created. The lowest grain is the Account dimension. The measure group intersect with other dimensions too, including Time. Besides non-additive measures, the measure group has additive and semi-additive measures.

If the aggregate function of a measure is None, is it possible to show up that measure at the Account level so I can apply the custom aggregation formula across time to get:

Jan Feb Mar Apr .... Total

Acct A 0.45 0.5 0.6 0.4 = (0.45 * 31 + 0.5 * 28 + 0.6 * 31 + 0.4 * 30 + ...) / Total days for selected months

Acct B 0.30 0.35 0.75 0.8 = <same custom formula>

Again, an Account member has no more than one corresponding record in the measure group always.

Thanks Mosha. I prefer to leave the rates in the same measure group for usability reasons and I think I have a workaround by setting the Account All member to NULL and the measure aggregate function to SUM.

Let me ask a more general question. If I have an aggregate function of None, is it possible to bring up the measure leaves to the dimension leaves I haven't been successful doing so. At the same time, this is what BOL states for the None aggregate function:

"No aggregation is performed, and all values for leaf and nonleaf members in a dimension are supplied directly from the fact table for the measure group that contains the measure."

Why I don't see the non-additive measure values when browsing at the Account leaves then

What are these usability reasons Why are you so resistant to set correct grain for you rate measures

B/c the semi-additive and additive measures will be organized in display folders under one measure group while the rates will be under another. If you are building off-the-shelf solution, things like these matter especially when your end users haven't heard about OLAP. Besides, I need to worry now about partitioning and processing a separate measure group.

I think you misunderstand the notion of "measure group leaves" and "dimension leaves". Perhaps the following blog will be of help: http://www.sqljunkies.com/WebLog/mosha/archive/2006/04/29/leaves.aspx

I've read the article (thanks for sharing) and anything written on this subject but I couldn't find answers to the questions "what happens when the aggregate function is set to None, where is the data stored, and how the heck I can get to it" (hint, hint :-).

Anyway, if you were to follow my advice about creating measure group with Account and Time, and set custom formula to aggregate Time as Average - you wouldn't have had this issue.

It is a good advice and I can see the simplifications it brings. However, setting the aggregate function to Average won't help anyway since I need a weighted average calculation over time. Hence, I still need to overwrite the total over time.