Getting Cognos to understand multiple grain in one query

[RobinvanB]

Hello,

I am working with three query items in a report. The first two query items are on detail level, the third is on header level. I join the first two items using a 1 - on - 1 relation. Consequently I join my third query item on this joined query item using a 1 - on - many relation. All my query items contain measures.

The first page of my report is a high level overview per client. Multiple headers with multiple details belong to a client. So I need to sum my measures according to the level on which they are defined. How can I achieve this? Below is an example of the data the final joined query contains, what Cognos gives and what I expect to see.

header_number	detail_number	count_header	count_detail	value_detail	value_header
1	1	1	1	50	15,7
1	2	1	1	17	15,7
1	3	1	1	61	15,7
2	1	1	1	84	0
2	2	1	1	456	0
3	1	1	1	12	0
4	2	1	1	64	3
4	3	1	1	13	3
4	4	1	1	48	3
4	5	1	1	10	3
total according to Cognos		10	10	815	59,1
total as it should be		4	10	815	18,7

[MFGF]

This really needs to be modelled appropriately in Framework Manager. You should ideally have two "fact" query subjects - one for Header measures and one for Detail measures, linking to the same conformed dimension query subject (maybe you could call it "Level"?) which also has determinants defined for the "Header" level and the "Detail" level.

Once this has been modelled, reporting should be easy and accurate.

Regards,

MF.

[RobinvanB]

MFGF,

The header and detail information is just an example of what occurs. The information is modelled correctly, but the report we are building is a report that contains more information, across multiple stars we have modelled. If I stay within a star, the information is modelled so that the aggregates do go right (so the count_header in the example sums to 4 as long as I stay within the star, the issue is that Cognos loses this contextual information as soon as we join acros multiple query items)

[MFGF]

Hi,

I would argue that your data isn't modelled correctly if you are getting incorrect aggregation, such as the double-counting you are seeing here. :) The query engine is designed to return accurate, consistent results when reporting off multi-fact multi-grain data as in your example (ie spanning multiple stars), but only if you have modelled the objects in FM according to best practice guidelines.

If I had to guess, I would imagine your determinants are either incorrect or missing on the conformed dimension in this case. If you look at the generated SQL from your report, you should be seeing a stitch query, with appropriate aggregations being done for each fact, then stitched using full outer join and an appropriate coalesce on the common dimension(s).

Regards,

MF.

[blom0344]

I second MF.  The model needs some rework.  Cognos can cope with  the header - detail issue with the correct appliance of determinants. (as long as you stay within 1 star)  This is largely undocumented, but it works pretty well

I believe I had some discussion about this in an earlier post:

http://www.cognoise.com/community/index.php?topic=17280.0

With multiple facts you need to work like best practice suggests and MF describes