Aggregation Rules for semi-additive measure ...

[Alp]

Hi All,

I am just back from vacation and my brain is empty :-)

I have a problem of defining Aggregation Rules for my relation model where I have a fact table (Query subject) and some other query subject play roles of dimensions.
Aggregation for different dimensions for some measures will require different Rules e.g. SUM() for one dimension and MAX() for another.

In the doc I found information about Aggregation Rules but I did not find it in the property view for the measure I need to apply the tweak.

Any help would be much appreciated.

Thanks,
- Alp

[blom0344]

Aggregation rules can be set for Measure Dimensions, not AFAIK for relational metadata associated facts.

[Alp]

Hi blom0344,

Aggregation rules can be set for Measure Dimensions, not AFAIK for relational metadata associated facts.

How would you approach this problem? Does it make sens to deal with the problem on the model level or in the report studio?

In fact if we have both dimensions aggregation we will end up with a need of MAX(SUM()) where we need first to do SUM for one dimension and then MAX on the result.

Thanks,
- Alp

[blom0344]

I'm having a bit of a difficulty to get the picture on your example, but I would try to realize this from the reporting end..

[Alp]

I'm having a bit of a difficulty to get the picture on your example, but I would try to realize this from the reporting end..

Example:
On a logical level I have 3 dimensions:
1. Type of Service
2. Server Name (where the service could be provided from)
3. Time Dimension

The fact table has a measure # of services available (from a particular server, for a particular service type, at a given point in time).

Requirement:
1. Aggregation by Server Name dimension for a specific Type Of Service at a particular point in time would require SUM (# of services available). It simply Total of numbers because they add up. Let us say if you have 3 on one server host and 2 on another server host the total number should be 5.
2. Aggregation over time for a particular server and service type can not be SUM. If we had QTY=6 yesterday and QTY=3 today, we can live with MAX that would give us 6.

Thanks,
- Alp

[blom0344]

It looks to me that you can have both aggregates in the same query:

1. total(services)
2. maximum(total(services for some_date))

Are you familiar with using contexts in aggregates?

[Alp]

Blom0344,

Please shed some light ...

- Alp

[blom0344]

Using contexts makes it possible to aggregate individual measures to a different grain than offered by the fact or by the granularity of the report.

Example:

total ([#services] for report) would yield the overall grand total of # services for the entire report. In most cases not very usefull, but just as example.

Without knowing your exact data I would guess for the highest score of services:

maximum(total(services for some_date))

[Alp]

Hi Blom0344,

Thanks for quick replies!
Sounds like it is what I need.
Where can I find info on the contexts? any links to any docs?

Thanks,
- Alex

[blom0344]

Report Studio help and the notes displayed when you select an aggregate function from the function list..

[Alp]

Sorry, I did not understand you. I thought that you suggest FM solution.

Thanks a lot!
- Alp

[blom0344]

Why define it in the model when Cognos allows playing around with aggregate definitions from the front-end? Once published you are stuck with pre-defined definitions from the model, whereas RS allows dynamic solutions.

[Alp]

Why define it in the model when Cognos allows playing around with aggregate definitions from the front-end? Once published you are stuck with pre-defined definitions from the model, whereas RS allows dynamic solutions.

That is because when you OEM Cognos as a part of an application you may want to invest in the data model design more to hide the complexity from the end user that may or may not have enough knowledge to deal with the issues in the RS.

Thanks,
- Alp

[blom0344]

Fair enough, though I expect 'canned' reports with an integrated solution, not the ability to create new reports from the FM model. Interested though to hear about OEM integration though..

[Alp]

Actually we provide some canned reports, but we encourage our customers to implement their custom reports as well. Therefore we publish the Cognos model as part of our product documentation.

Thanks for your feedback.
- Alp