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?

What is the granularity of rates in the fact table ? If they are entered, for example, per Account and Time - you should create new measure group at that granularity.|||

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.

|||What I suggested was to leave all other (additive) measures in the measure group that you already created. Based on your example, the granularity of Rates is at least Account and Time (but possibly other attributes). You need to create NEW measure group and include just these two dimensions (unless rates vary by other dimensions as well). You didn't specify how the rates should aggregate across accounts, but probably they don't at all.|||

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?

|||

I prefer to leave the rates in the same measure group for usability reasons

What are these usability reasons ? By artificially forcing deeper grain on the rates you make your model less natural and it is harder to work with. Why are you so resistant to set correct grain for you rate measures ?

If I have an aggregate function of None, is it possible to bring up the measure leaves to the dimension leaves?

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

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.

|||

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.

Non-additive measure - Actual and Average

I am working on a project at a manufacturing client. The measure I'm having trouble with is LeadTime (the number of days from manufacturing schedule to completion). It is used in a calculation to determine over/under inventory levels.

I need it to use the actual LeadTime for the calc at the SKU level and the average LeadTime at all of the aggregate levels.

Thanks for the help,

Dave

Hello! I am not sure about what you mean with SKU level but if you have two dates in the fact table, ManufacturingScheduleDate and ManufacturingCompletionDate, you can make a named calculation in the data source view(Analysis Services 2005) or a calculation in the ETL-process with SSIS.

Use the TSQL function DATEDIFF() for that and the difference between the MScheduleDate and the MCompletionDate.

I am not sure about your problem with the averages and what you are doing averages of?

You should be able to solve this with the MDX AVG() function.

HTH

Thomas Ivarsson

|||

SKU is the lowest level of data...the individual product being manufactured and sold. The LeadTime does not need to be calculated it is already determined for each product.Here is a table that I hope helps:

LeadTime

Product

Actual

Average

1001

3

4

1002

4

4

1003

5

4

Sub-total Sum

12

Sub-total Avg

4

When I set the measure LeadTime to Sum the calculation that uses LeadTime is correct at the product level, but wrong at the aggregate level.If I set the measure to Average then the calc is wrong at the product and right at the aggregate level.

I need to show the cube data with LeadTime as actual at the product level and average at the aggregate level.

Dave

|||

Hello! Can you also indicate the expected values in the table for Actual and Average Lead time?

From what I have seen with other clients is that you have a manufacturing order id for each production of a product.

It is not a part of your table but can it be a part of the problem?

Regards

Thomas Ivarsson

|||

Here is a little description of the table from my last post.Three product ID’s (1001, 1002, 1003) with actual LeadTime for each product in days (3, 4, 5 – respectively).

The LeadTime measure is used with other measures (On-hand, Daily demand, Safety stock, etc.) in an inventory over/under calculation.But, LeadTime is the only non-additive measure.Because of that the calculation is correct at the Product ID level (3, 4, 5), but wrong at the aggregate level (12).If I set the measure to Average LeadTime at the Product level is wrong (4, 4, 4), but the aggregate is correct (4).I’m looking for a solution that gives me the correct calculation at all levels

This project is only dealing with inventory levels.Orders are not a part of the calculation except as a part of the Daily Demand figure.