Aggregation not working properly

[willg]

Hi all,

I have a problem when trying to total values, where the value it's returning far exceeds what it should.

Below is a picture of an example list prior to aggregation;



And here is a picture after setting the aggregation function of 'linecost' to 'Total';



How/why is this happening?

Update: This is only happening after a join, prior it totals correctly

[Lynn]

Sounds like your join is incorrect. It seems your one row for 201212 has a value of 4,300.6, but you then join to another table that causes that one row to come back 74,422 times so that when you total it all up you get 322,291,913.2.

Your join has affected the granularity of your metric. You may need to correct the cardinality of the join or else create a query expression to use a total calculation with the scope defined with the "for" clause.

I
P.S.
'm assuming this is a relational source.

[willg]

I'm pretty sure it's a relational database, I have tried a few different ways of carrying out the join without much success, can you just clarify what you mean about a total calculation using a query expression & the 'for' clause?

Thanks

[Lynn]

If you google "Cognos total function" you will find a variety of links that explain it better than I could. Sorry I don't have time to go into more detail at the moment.

[willg]

Thanks for the reply Lynn, I have made some progress but still have not quite got the result I wanted.

Currently I have

Code Select Expand

total(distinct [column1] for [column2]) However that misses out some values for [column1] when there are duplicates.

What I would like to do is something along the lines of getting a total of column1, but only when column2 is distinct (the primary key);

Code Select Expand

total([column1] where [column2] is distinct)

If anyone can help me out with this it would be really appreciated.

[blom0344]

'distinct' is a clause that allows to filter out duplicates. It refers to the entire set of retrieved data, not to an individual column.
A table with a primary key automatically returns distinct sets if you incorporate the key itself (make the row unique). I suspect you have some serious model issues, possibly a n:m relationship in the model that triggers the overcounting of data

[willg]

It does return distinct sets like you say, but the problem is that I join the table with another large table, and where one table is slightly larger than the other, it is causing rows to duplicate.

Unless I use 'total(distinct' which then doesn't total all the data since some rows have the same value (cost) as others.

[blom0344]

Yea, but adding a distinct is not the solution. To me it seems that you are joining 2 facttype tables (hence the n:m cardinality), which basically is a modelling issue not resolved with adding clauses to the SQL. Facts should converge on a conformed dimension allowing Cognos to create stitch queries to return proper results..

[willg]

So with the problem of the duplicating rows, I cannot do a correct total (total distinct misses out some values) and total without distinct just totals all of the rows causing a much larger number than there should be.

Blom, sorry for the late reply but I did not really understand your post, is this a problem with the cardinality/type of the join I am using, or does the problem lie elsewhere?

And can anybody give me some advice on how to rectify the problem?

Thanks,
Will.

[blom0344]

Cardinality in this sense is simply a way for Cognos to distinguish between fact and dimension and a way to enforce an outer join. If the 2 tables joined are both facts and the relationship is many to many  (n:m) then 'overcounting' is a direct result.  The solution normally would be introducing a conformed dimension between the two facts. If the facts are at a different grain , then you need specific determinants for Cognos to know how to split , aggregate and restitch the seperate queries. There must be 100's of queries about this and FM user guide is a good point to start too..

[willg]

Ok thanks for the reply, so just to confirm, I would need to sort this out in Framework manager? Just because I do not currently have access to FM and could take me a little while to get it

[blom0344]

Well everything really starts with a sound model, so fixing this in FM is vital!