Topic
  • 1 reply
  • Latest Post - ‏2012-10-07T20:33:45Z by JohnWilkinson
jsellner
jsellner
35 Posts

Pinned topic How to handle Dimension with measure (Fact) information in it when Modeling

‏2012-10-05T20:47:31Z |
I know the first response is why didn't you put the measure into the fact instead of the dimension. Answer is the consultant setup it up this way and I have to deal with it.

If I change the usage on measure field to attribute instead, I can't aggregate the measure when doing a grouping like by employer or any other grouping.
Results of this
Employer 1 15
Employer 2 10
Employer 1 13
Employer 1 20
instead of
Employer 1 48
Employer 2 20

If I change the usage on the measure field to fact, it aggregates but when I try testing the aggregate for by employer or any other grouping it is giving me the total for the whole file across each grouping instead of the total for each grouping.
Results of this
Employer 1 68
Employer 2 68
instead of
Employer 1 48
Employer 2 20

I looked at the Native SQL and it is correct, e.g.
Select F.grp, sum(d.measure) from fact f, dimension d, employer e
where f.key1 = d.key1
and e.key2 = f.key2
group by f.grp

The Cognos SQL thou isn't correct, e.g
Select f.grp, xsum(d.measure at d.key) fact f, dimension d, employer e
where f.key1 = d.key1
and e.key2 = f.key2
group by f.grp

I was expecting
Select f.grp, xsum(d.measure for f.grp) fact f, dimension d, employer e
where f.key = d.key
group by f.grp

Each of the tables has a determinant set based the primary keys on the file. I get the same results with or without the determinant.

Suggestions on what changes I need to make in Framework manager to get this to work?

Thanks.
Updated on 2012-10-07T20:33:45Z at 2012-10-07T20:33:45Z by JohnWilkinson
  • JohnWilkinson
    JohnWilkinson
    62 Posts

    Re: How to handle Dimension with measure (Fact) information in it when Modeling

    ‏2012-10-07T20:33:45Z  
    The simple answer here is that for the aggregation to work correctly, it needs to be in a fact table. Framework manager decides what is a fact and what is a dimension purely based upon the join - the "n" side of a "1-n" join is always the fact, and the "1" side is always the dimension. Often, it is quite valid to put numeric measures in a dimension table if it is only ever going to be treated as an attribute, and never aggregated.

    Determinants won't help here - they purely tell Cognos how to react when dealing with fact tables at different levels of granularity.

    Your option? Either find a suitable "fact" query subject that can be used to hold the measure or worst case create a new "fact" query subject that will hold the single fact that you need to aggregate, and it's associated dimension keys. Ensure that the new fact table is joined to the existing dimension on the correct keys with a 1-n join (n on the fact side of the join). Or question the consultant as to why he set it up in that way?