Dim. Aggregation

[charon]

• Hi all :)
  
  Small issue...working not a lot with dim models, therefore i need some help.
  im working on a report with a crosstab, which shows following intel
  
                                               Act. Year                                           Last Year
  (month)                      Measure 1      Measure 2              Measure 1           Measure 2
  1  (Status)a                    dd xx              ddxa                       yxx                     xyxqs
                   b                 d   dqw            qwde                       qwd                     asdaw
                   c
  2               a
                   b
                   c
  3               a...
  ...
  
  
  The issue is: Measure 1 is a measure which needs 2 be rolled up, Measure 2 is a percentage.
  Also, Status a & b have to be aggregated 2 gether, means i need
                                   Measure 1    Total          Measure 2  Average
  Month   Status             
  1           (a & b)
                    c
  2            (a&b)
                   c
  3
  
  So far its not working for me...any ideas how to achieve that?
  Thx a lot and cheerz :P

[CognosPaul]

When you say dim model, do you mean OLAP/DMR?

What you're asking for shouldn't be too difficult.

I'm assuming your xtab looks like this:

            |   Year  |  P Year |
            |----+----+----+----+
            | M1 | M2 | M1 | M2 |
-----+------+----+----+----+----+
Month|Status| 123| 123| 123| 123|
-----+------+----+----+----+----+


Assuming that this is an OLAP model, get rid of the status node, replacing it with a calculated item with the expression [Cube].[Dim].[Hier].[Level]->[StatusA]+[Cube].[Dim].[Hier].[Level]->[StatusB], and another item which is [Cube].[Dim].[Hier].[Level]->[StatusA]. Alternately, replace the status node with something like:

set(
member([Cube].[Dim].[Hier].[Level]->[StatusA]+[Cube].[Dim].[Hier].[Level]->[StatusB],'aAndBCode','(A + B Caption)',[Cube].[Dim].[Hier])
,[Cube].[Dim].[Hier].[Level]->[StatusA]
)

Now as measure 2 is a percentage, it will likely rollup oddly for the (A + B) calculated member, set the solve order on that data item to 2.

[charon]

Hi Paul,

ty for your reply. Sadly, this wont the trick. I guess i was not able to describe the situation properly.

The main issue im facing is the fact, that i the two measures M1 and M2 are total sum and a average percentage.
So, after creating a set (what i was able to do before), the set itself will aggregate only a sum.

Means, the total Sum is correct  but the average is not, it will display a sum of all percentages instead the average.
Is there any way to tell Cognos, that the set has to been aggregated in relation to the fact column ?

ty and cheerz
charon

PS: hope u guys enjoy the soccer em as i do :D

[CognosPaul]

Where is the average being calculated? You really shouldn't be doing an average of an average...  I think I'm still missing something though. Can you post a screenshot or a report xml?

[charon]

Jo Paul :D

Thx for reply. Concerning a screenshot, sadly there is no way to post sth like that because the company im working for right now is very strict when it comes to their data (quite understandable i think).

However, i might have not be able to describe the issue im facing so its easy to understand, therefore i will give it another shot.

Imagine i have a product with several status settings, like
1 = new
2 = compareable
3 = retro
and so on...

What i want to do is, to group 2 of these in a new group (set), means i have
Status
1
(2+3)
4
5

The next point is, i want to show for these groups several facts, e.g. revenue and margin.
The one fact is a sum, which has to be aggregated total. But margin is a quote, measured as percentage, therefore i need an average on this fact.
Also, i have several years and months, the crosstab looks like

                                                                                          YEAR
                            1                      2           3     4      5      6      7      8      9       10       11        12   MONTHS[/b])
              Revenue  Margin    Rev  Marg   R   M  ........
Status
1
(2+3)
4
5
6

(Aggregate)    -> Should be (total revenu) and (average margin)

the problem: when i create a set for the two status (2+3), cognos appears not to be able to aggregate the facts correctly. The percentages are no longer average but a total as well...

How do i get cognos to aggregate correctly in this set of different status items for different measures in different months?

Hope i was able to make it a bit more clear...thx for your help, appreciated as always :=)
cheerz :P

[CognosPaul]

Where is the percentage being calculated, in the report or cube? If its in the report then set the solve order on that data item to 2.

[charon]

Gone try it, ty :)
Have a nice (soccer em) evening...oleole

[charon]

Hi,

tried it by changin the solve order of the aggregation, didn resolve the issue :(

Total still summed correclty, average is summed as well instead of getin average :/

[CognosPaul]

Is the average a calculation from inside the cube?

[charon]

Its a DMR modell not a cube, Cog 10.
Tried different aggregations in report studio in the porperty pane, didnt work :(

The main issue is the set of status.
How do i get a set/ group or even data item filled with these 2 status, but receiving an average for margin instead the sum total?
                             Year 2012
Status             Revenue           Margin

1                        33                       0,3
2 + 3                  42                      (0,5)         ---> here is the issue, im gettin not 0,5 = average of 2 and 3, but 1 for the sum of percentages.
4                        11                       0,1

Aggregation:     86                      0,3 (receving not the correct average because in 2+3 i get the total sum of both

Sometimes, just sometimes, i start to hate cognos a LITTLEBIT ;)

[CognosPaul]

DMR!!!

DMR makes everythings more difficult. Are the measures inside a measure dimension or are they coming directly from the fact?

In the (2+3) are you actually doing ([2]+[3])? Maybe try doing aggregate(currentMeasure within set set([2],[3]))

How is Margin being calculated? Is it being calculated in FM? If so, move the calculation to RS and then set the solve order.

[charon]

Hi Paul,

ty for replyoing so fast :)
Facts are from a fact folder (facts only, and for all dimensions).
(2+3) = implemented with a normal set as well as a custom group in the set (1, 2, 3, 4).
currentMeasure ive already tried.

Margin is calculated on server level, not even FM model. Its sth like (rev-cost/ rev)...important thing is, it is on a backend level. So moving it to RS level would be a bit complicated.

Still, ty for your insight...maybe i l go down to etl level and make a new entry instead of a set :S
ty and have a nice day
cheerz charon

[CognosPaul]

Now it makes sense! Since the value is server side (and not additive), it'll never be able to recalculate. You shouldn't be doing an average on something like margin, it should be calculated on runtime. Create it as a calculation in RS and try again.

The reason why I'm saying it's so important not to do an average on this is because averages will give the wrong result:


Status     Rev       Cost    Margin    Margin (Avg)
1        158.34     77.48    51.1%
2         64.93     38.47    40.7%
3         51.89     22.31    57.0%
4         75.46     28.96    61.6%
5         54.07     19.91    63.2%
6         99.11     79.68    19.6%
7        113.54     45.84    59.6%
8         34.87     15.37    55.9%
9         88.97     59.00    33.7%
10        193.16     74.68    61.3%
11        175.36     75.56    56.9%
12         42.11     28.68    31.9%
13         32.70     14.96    54.3%
14         21.64     11.74    45.8%
15        137.13     59.06    56.9%
16         43.52     36.49    16.1%
17         48.79     23.20    52.4%
18         12.19      8.24    32.4%
19         48.67     32.40    33.4%
20         72.05     27.64    61.6%
Total   1,568.49    779.67    50.3%    47.3%


The margin there is rev-cost/rev. The total row is correct (1568.49 - 779.67)/1568.49 = 50.3%. However if you tried to average the margin against all 20 values, you'd get a lower rate of 47.3%. In my experience, any financial analyst that sees an error in financial reporting will throw a major wutausbrüche.

[charon]

Hi,
your absolutly right about the "wutausbrüche", the controller is quite..short tempered :D
But, to tell the truth, its not about a margin but about another quote/ figure that has a quite complex logic. i used "margin" in this case because the idea and the solution should be the same :)

Why i thought it could work is, cognos does have the information about the "margin" status 1 and "margin" status 2, so why can i not aggregate up the average of both if i decide to put both in a custom group/ set?!

[CognosPaul]

Exactly the reason that I said for the margin probably applies to the other figure. Let's say it is margin, you have one status with Rev of 2 and a Cost of 1 (Margin 50%) and another of Rev 100 and Cost of 1 (Margin 99%). If you averaged the two margins you'd get 74.5%, which is completely ridiculous. The correct Margin should be (102 - 2 ) / 102 = 98%.

I recommend explaining the situation to the controller, if you get approval for averaging the values (without knowing the values, I don't know if it's correct or not to do it), there are a few possible solutions. I just don't feel comfortable talking about them on financial data like this.

The suggestions that I was giving was under the mistaken assumption that it was a calculated field, not a value from the db.

[charon]

Ty for your explanation Paul :)