Uniqueness Violation Problem

[APS734]

Hi,

I am new with Cognos and am learning to build a cube. The first problem I have is the distinct category count not producing the right numbers. I have noticed the pattern in the cube data for the problem, but do not know how to resolve it. When all the measures present a zero for a particular level member, the category count produces an zero. To illustrate the problem with data:

InvoiceID         Payment Type       InvoiceAmount           ServiceAmount            InvoiceCount
12345              VISA                      $240.00                     $60.00                        1
12346              CASH                     $0.00                         $0.00                          0
12347              AMEX                     $96.00                       $17.00                        1

The second row above should show InvoiceCount 1 for invoiceID 12346, but it shows 0. One may say "Why the second row is there anyway?" Well, it is just part of the customer data.  I would appreciate any advices on this issue.                     

[Darek]

Change the inclusion options to "Always Include".

[APS734]

Thank you Darek.

When using the distinct category count in Transformer, there is a limit of 2 million members, according to Cognos. I vaguely remember reading from some where that a separate table only for the purpose of distinct count can be created. I wonder if anyone has used this method to handle very large size of database that can easily out size 2 million category members. How should this table be structured and used in transformer for the count to be right for all the dimensions? Does it need a separate table for each category countm, ex. Invoice count, store count, product count, etc?

Thank you!

[Darek]

It depends how do you need to count, but most of the time: yes. And sometime you have to perfrom external rollups.