hierarchy appearance without consolidation?

[Tilo]

Hello *,

does somebody know a way to have the appearance of hierarchy ("+"/"-" signs and trees and so on) without the consolidation?
To have it dynamic?
The other way for me would be canceling the consolidation by adding a rule, but this has influence on performance what I try to avoid as much as possible.
Thanks in advance.

Tilo

[daveed]

Tilo,

You can create a hierarchy without consolidating values by using an element weighting of zero for the component elements of the consolidation.

Regards

Dave.

[Tilo]

Hi Dave,
thanks.
This would mean entering 0,083333333 (=1/12) would bring the yearly average with acceptable accuracy.
Tilo

[Martin Ryan]

While this is correct it's not usually the way I've seen averages done. For one, the year figure will not be an average until the year is completed.

Generally the way I have seen averages implemented is by having an extra dimension that has the elements "Input", "Counter" and "Average". For simple averaging you can write a rule that says

Code: Select all

['Counter'] = N: if(['Input']<>0, 1, 0);
['Average'] = ['Input'] \ ['Counter']; (note \ not / - this means it doesn't error if it divides by zero).

Assuming you have Year as separate dimension from Month you could also write a rule that says

Code: Select all

['All months', 'Average'] = ['Input'] \ 12;

or to cater for the possibility that the current year hasn't had 12 months yet you could use an attribute

Code: Select all

['All months', 'Average'] = ['Input'] \ attrn('Year', !Year, 'NumMonthsCompleted');

Hope that proves food for thought.

Cheers,
Martin

[Tilo]

While this is correct it's not usually the way I've seen averages done. For one, the year figure will not be an average until the year is completed.

Why? It serves the average of the 12 months.

The described solution seems not to work if you enter "0"-inputs as the counter then is "0".

I also thought about "['Total year']= consolidatechildren()/elcompn()" or a counter in an attribute instead of "elcompn()" in order to prevent an average in consolidations except in the time dimension.
I would expect that the missing elements "counter" and "average" would bring performance advantages this way???
Also the consolidations (and only one cell is calculated) instead of calculations in all elements of the time dimension.

I have a moving average (where I could link the Total year to the actual month or always the last month of the year, if I want to).
http://forums.olapforums.com/download/file.php?id=79
from
http://forums.olapforums.com/viewtopic.php?f=3&t=53

I would like to find out what solution have what impact on performance as I will probably have dimensions with millions of elements with the client.

Greetings from Hamburg
Tilo

[Martin Ryan]

Why? It serves the average of the 12 months.

True. I guess it's not usually the way it's done because generally people want to be able to see the actual numbers and the averages. By setting the weighting factors to 0.083333 you will be unable to sum the numbers. This may be fine for your current cube (e.g. if it's forex rates so the year should be the average of the months), but if you subsequently use the same dimension in your sales cube, you will not be able to sum up the months because the weightings will be the same in every cube that uses the dimension.

If you really, really only ever intend on using this dimension to get an average then your method is fine. If you wish to reuse your dimensions (which is the general idea, to cut down on maintenance) then a method like mine allows that.

Cheers,
Martin

[Tilo]

By setting the weighting factors to 0.083333 you will be unable to sum the numbers.

Do you that mean the weights do not only have impact on consolidations but also on rules referencing the weighted dimension?
I guess I can test that on my own later...

[Martin Ryan]

Correct. If your rule references 'All months' and all months is a consolidation with weightings of 0.8333 for all its children, then the rule will use that averaged figure. There will be nowhere in your cube that you can find the sum of all the months.

Martin

[Tilo]

I mean if I create a rule "jan"+"feb" will this be correct?

[Martin Ryan]

I mean if I create a rule "jan"+"feb" will this be correct?

Oh I see. Yes, this will be correct as they weighting is only taken into account when it does the consolidation up into 'All months'.

Martin

[paulsimon]

Tilo

I have used the 1/12 weight method to get a yearly average in the paste. To get over the problem that you have been discussing with Martin I just used two consolidation hierarchies:

eg

C Y2005-P12-MAT (Moving Annual Total)
Y2005-P01 1
...
Y2005-P12 1

C Y2006-P01-MAT
Y2005-P02 1
...
Y2006-P01 1


C Y2005-P12-MAA (Moving Annual Average)
Y2005-P01 0.08333
...
Y2005-P12 0.08333

C Y2006-P01-MAA
Y2005-P02 0.08333
...
Y2006-P01 0.08333

I then make the MAAs join to MAA Years so you have

C Y2006-MAA
Y2006-P01-MAA 0
Y2006-P02-MAA 0
...
Y2006-P12-MAA 1

Then to

C All Moving Annual Averages

Then up so that at

C All Months
All Discrete Months 1
All Moving Annual Totals 0
All Moving Annual Averages 0

In this way the user can easily pick the hierarchy that they need to get the view that they want. As Martin says, the use of zero weighting allows you to group things wihout having duplicate consolidation.

I have some VBA that generates the hierarchies for me. It can easily generate hierarchies with different financial year starts as well as the conventional calendar month hierachy. It also does YTD, Cumulatives, etc.

It is true that the Moving Annual Average is no good if you use it when you only have 7 months of actuals in a 12 month year, but then the point of a MAA is that you use the MAA for July if you only have actuals to July. Obviously it is more of a problem if you have separate Year and Month dims.

If you do just want an average for year so far, then you could use a rule to take the YTD consolidation value and divide by the elcompn of the YTD consolidation, or just reference a control cube that holds your current month and its month number. Or you could add another consolidation hierarchy YTDA (YTD Average) and divide by the appropriate number of months in each consolidation using an appropriate weighting on the members, eg 1/7 for the July YTDA. Consolidation will give you faster performance than rules, and without any need to feed.

I also set up various subsets from the monthly load such as rCurrMth, and rCurrMthYTD to hold the relevant elements for the latest month. The users then used these in their spreadsheets, which then automatically updated as the month changed.

If you are doing real averaging such as the average value for products sold, then you would need to use the method that Martin described whereby you have a count for each product sold and its value. You could populate the count using a rule to put in a 1 if the sales value is not 0. However, I think that you can get away with something simpler and less feeder intensive if you just want averages over dates.

Regards


Paul

[Tilo]

Tilo

I have used the 1/12 weight method to get a yearly average in the paste. To get over the problem that you have been discussing with Martin I just used two consolidation hierarchies:

eg

C Y2005-P12-MAT (Moving Annual Total)
Y2005-P01 1
...
Y2005-P12 1

C Y2006-P01-MAT
Y2005-P02 1
...
Y2006-P01 1


C Y2005-P12-MAA (Moving Annual Average)
Y2005-P01 0.08333
...
Y2005-P12 0.08333

C Y2006-P01-MAA
Y2005-P02 0.08333
...
Y2006-P01 0.08333

I then make the MAAs join to MAA Years so you have

C Y2006-MAA
Y2006-P01-MAA 0
Y2006-P02-MAA 0
...
Y2006-P12-MAA 1

Then to

C All Moving Annual Averages

Then up so that at

C All Months
All Discrete Months 1
All Moving Annual Totals 0
All Moving Annual Averages 0

Thanks Paul, but your post is cryptic to me for the moment. What stands "P" for and what is it counting when it does not refer to months?

[paulsimon]

Tilo

P stand for period so Y2005-P01 is Period 1 in Year 2005. Period 1 is usually January, but sometimes companies have different financial years.

If you want a fuller example, there are some files on my yahoo group PaulSimonOLAP.

Regards

Paul Simon