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.
Change the inclusion options to "Always Include".
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!
It depends how do you need to count, but most of the time: yes. And sometime you have to perfrom external rollups.