Measures in dimension tables

[gjholland]

I have a simple relational test model.

Table A has measures and keys. 

Table B has a dimension attributes, but also additive measures.

The FM join is a simple one-to-many.  Single key, no outer joins.

Building a simple report or query with 1  A measure and 1 B measure results in correct A measure results, but the sum of all B measure results on each row.  Examining the SQL shows a SUM function on the B measure.

I can't rationalize why B measure would be aggregated.

I can resolve this by coding the join in SQL at the data layer, but there must be a better solution.

[wyconian]

Hi

Sounds like there may be a couple of things going on here.

You've possibly got a stitch query.  Check the generated SQL for a coalesece statement.  If you find one of those check the cardinality of the join.  It sounds like you may have many to many joins so cognos thinks you're trying to report against 2 fact tables so it rolls up values on one of them.

Check the granularity of the measures in both tables, are they the same (1:1) based on the key used in the join.  If you have records with different granularity then one set of values will get rolled up (aggregated) against the other set.

There are ways around these issues but (possibly the most important thing) you shouldn't really be mixing measures and attributes.  One of the basic concepts of a (Kimball) data warehouse is that you separate dimensional attributes and fact records.  If you follow Kimball 100% (not always the best solution) you should have absolutley no attributes on the fact table (apart from keys) and no measures in the dimension table.

It may be a good idea to look at splitting the meaures out of table B so you end up with another fact table.  It sounds like table B could then be your conformed dimension between the 2 facts.

There is always a risk of stitching when you report against 2 fact tables.  The 'correct' thing to do is to add determinants, these tell cognos how to aggregate records with different granularities (take a look at the user manual for more details).

A quicker, 'dirtier' way of achieving this is to change the cardinalty of all joins to be 1:1 (cognos identifies a fact table as a table where all the incoming cardinalities are many).  If you do that cognos will think you haven't got any facts so won't try to stitch the queries. 

I'd recommend the best way forward is to change the model so you have clean fact and dimension tables.

Good luck

[blom0344]

I had a pretty extensive discussion a while back on exactly this issue.  To make a long story short:
Cognos can compensate for overcounting when a unique determinant exist at the 1 side of the relationship. If no determinant is defined then you will experience the problem you describe