Facts Aggregates incorrectly

[Ann]

Hi Gurus,

Need help in solving an Issue.

I have a fact F, and three Attributes (A1, A2 and A3) in my query.

Every time I change the aggregate property to Automatic or Total, there is a inline query created at backend with select sum(F) over (partition by A1,A2), a1, a2, a3 from (select sum(F),A1,A2,A3 from ....
group by A1, A2, A3).

there by repeating same data for each A3.

Can any one help me by telling what exactly might be causing this Issue. Is this anything related to Usage property in Fm.

I do not have access to package so I am not able play around with the model to get this resolved.


Any Clue on this Issue would be really appreciated.

Thanks in Advance!

[navissar]

This sounds like a modelling issue. The relationships between your measure and your attributes are defined in the data model, and also the usage of a certain data item as measure or attribute. From the query created I can only imagine that attribute A3's relationship to the fact isn't well defined (That's why it uses partition by).
The right way to go about this would be to change the model. If you don't have access to it, you probably need to contact the person that does.
If all else is lost, and there's absolutely no way for you to have the model altered, then there are only two ways I can think of:
1. Create queries to correspond with the database table structure (So you'll have a query in the report called fact which will have the fact data, a query called "Dim whatever" which will bring the dim data and so on) and join them properly in RS; alternatively you could write manual SQL (But I really do not recommend it).

[Ann]

Thanks Nimrod for your reply.

I definitely had thought of the solution you suggested, i still am not able to convince myself going for it.
I wanted to have this fixed at FM level.

I actually wanted a solid question which I can ask my FM modeler to check. :(

Few more points on my Model:
My model is  a relational model.
I definitely see all the table joined inside the inline query. The join is something like this:

Assuming A1 come from A1_table, A2 from A2_table resp.
A1_table join A2_table left outer join (c1_table join c2_table )A3_table

[navissar]

That's a bit snowflaky. How are these tables connected to the fact?

[Ann]

A slight mistake there.. Apologies.
The relationship goes exactly like this:

(((A1_Table inner join F on F.key1=A1_table.key1)
inner join A2_table on F.key2=A2_table.key2)
left outer join (C1_table inner join C2_table on C1_table.Key3=C2_table.Key3)A3_table
on A2_table.key3=A3_table.key3)

[navissar]

You didn't describe the cardinality, but I'm guessing the following:
A2-A3 is a one-to-many relationship, A3 on the many side.
If that's the case, Cognos will identify A3 as a fact, and will attempt a stitch query between the fact and A3 based on A1 and A2 (Which is why you see the weird grouping).
You might want to try modelling A3 join A2 and to join the result to F. Something along these lines:
from f
join a1 on a1.key=f.keyA1
join (
select * from
a2 left join a3 on a2.a3key=a3.key) A23 on A23.A2key=F.A2Key

This is basically forcing the snowflake to behave like a star.

[Ann]

Seeing data at the backend for tables A1_table and A2_table , I could say it was many to one.. many at A2.

Does that mean cardinality might be defined wrong at Fm level which might be causing the issue?

[navissar]

Could be. A lot of it is guess work, without access to the actual model...

[cognostechie]

We had a big discussion regarding how to roll up data when you have Facts of multiple grains. You might be
able to find that thread on FM forum. Your modeler has to make sure to join Facts to Dimensions only and then
set proper determinants. Determinants are the key for this. In the report, use the attributes from the Dimension
query subject, not from the Fact query subject. 

[Ann]

I have finally decided to get in touch with out FM Modeler :)

Appreciate all your Inputs. Thank you Guys!!