| Author |
Topic |
|
dmgilbert
Starting Member
10 Posts |
 Posted - 2004-03-05 : 10:15:40
|
| I have built a cube that has four levels in my dimension. I am trying to get the aggregation to be the average of the elements in the children instead of the sum. Seems like it should be such an easy thing to do, but it has me stumped. Any assistance would be greatly appreciated!!ThanksDave |
|
|
mikepre
Starting Member
5 Posts |
Posted - 2004-03-05 : 10:29:18
|
| This may help. I had the same problem building a cube with warehouse data.If you're trying for example to average warehouse data instead of adding it by date, just do this in the date dimension, levels Year and Month. Not day, since you want to see there the real data.So go to Analysis Manager, go to cube design and place this formula in CUSTOM ROLLUP FORMULA option in ADVANCED TAB in Properties Window for both levels (Year and Month, I'm not using quarter since I'm not in the US.. :) (assuming your date dimension is ... date...)avg(date.currentmember.children)Hope it helps.Michael PrendergastDataMart S.A.Chile |
 |
|
|
dmgilbert
Starting Member
10 Posts |
Posted - 2004-03-05 : 11:33:38
|
| Mike,Thank you for your suggestion. First, I want to apologize as I am quite new to this application, and I am more of a manager than a dba, so forgive my lack of knowledge.Let's use as an example a simple cube that is trying to measure customer satisfaction across geographies. My cube consists of customer data and a customer satisfaction table. My dimension has State, City, and customer name as levels. My measure is customer_sat.I edit the dimension and insert Custom Rollup formula at the state level to be "avg(customer_sat.currentmember.children)". reprocess the cube and it displays ERR in the measurement field at the state level. What did I do wrong?Thanks for the helpDave |
|
|
|
dmgilbert
Starting Member
10 Posts |
Posted - 2004-03-05 : 11:37:57
|
| Mike,Meant to give this info also, the error message in the measure field at the state level says:Infinite recursion detected during execution of calculated member avg(customer_sat.currentmember.children). An error occurred during attempt to get cell value |
|
|
|
mikepre
Starting Member
5 Posts |
Posted - 2004-03-05 : 11:58:08
|
| Dave, the problem is that teh CUSTOM ROLLUP FORMULA works over a dimension, not over a measure.The instruction works like ... average this dimension level making the parent level the average of all the members that are children of the parent level, for all the measures shown in the slice... so change avg(customer_sat.currentmember.children) foravg(customer.currentmember.children) and write formula in the State level as well as the City Level. The reprocess the cube and that should be enough.Good luck |
|
|
|
dmgilbert
Starting Member
10 Posts |
Posted - 2004-03-05 : 13:15:15
|
| That worked great! Thanks for the advice |
|
|
|
mikepre
Starting Member
5 Posts |
Posted - 2004-03-05 : 13:38:06
|
| My pleasure.Have a nice day.Michael PrendergastDataMart S.A.Chile |
|
|
|
gboulton
Starting Member
6 Posts |
Posted - 2005-03-16 : 07:48:09
|
| Mike,Great information. However, I'm gonna take this (I think) one step further. Rather than start a new thread, thought I'd continue this one since it's not TOO old. :)What if one wants a particular measure to ALWAYS be aggregated by average, rather than sum or count, but another measure that should be aggregated as a sum.For example:I have 2 measures...one is a patient count, the other is the amount of time a particular procedure took for that patient, and over 20 dimensions. (Hospital, Patient Info, Physician, etc etc etc). Some dimensions have but one level, others as many as 4.I'd like to have the measures displayed as "Number of Patients" and "Average Time for Procedure X" across all levels of all dimensions.Any help would be appreciated. :)/gb |
|
|
|
gboulton
Starting Member
6 Posts |
Posted - 2005-03-16 : 08:11:29
|
| Well, as it turns out, I stumbled across the answer just a bit ago....searching, oddly enough, for something completely different. I'll share it here should anyone else need it:The way to create a measure that aggregates by Average instead of Sum or Coun t is as follows:Create a measure that counts all records...in my case, the patient count.Create a measure that SUMS the measure you wish to average...in my case, a measure that SUMS Time for Procedure X. Set this measure's visible property to "False", presuming it is a useless measure (as it is in my case).Create a calculated member whose MDX expression is (for example) [Measures].[Patient Count] / [Measures].[Sum of Procedure Time]Simple, really. :) I feel like a moron now. ;)/gb |
|
|
|
|
custom rollup