One indicator that sums up on one dimension and Averages on another

Hi everyone,

first hello to all. Then, here is my problem. How to model into a star schema the business needs that poses one indicator X sums on dimension A and averages on dimension B, that this indicator X must be analyzed into a crosstab across dimension A and B.

Below and example of the crosstab :

Over Jan/Total Product it sums.
Over Product 1/Total T1 it averages

To me, the solution doesn't relies on the model. The X value is stored into the fact table as it is and one does hard code into the crosstab the proper aggregation function according to the crosstab's context. That implies that one cannot both use the automatic and native data drill capacities of the tool (namely Cognos) as well as its native aggregation funtions.

So is this a solution to you and is there another one except cubes ?

Thanks a lot for any help and suggestions of you

Hi to all from France

Hi,

do it in Cognos.

If you need to, for performance reasons, you can aggregate your measures up to a common level - in your example group by month and product - in your data warehouse but you wouldn't want to predefine report column/row summaries in your DB

Regards,

It is not a modeling issue. It is a reporting problem. As Nick suggests, you should be able to do this in Cognos.

That's perfect.
Thank you all for your help and answers.