Cognos Cubes and Distinct Counts

I need to add a distinct count of members to a cube. It's not a problem as long as the grain of the cube is the distinct count, such as a distinct count of members by age, location, etc. But when I need to count distinct members by activity, where a member can have multiple activities, the the count distinct won't work.

In banking, for example, If I want to count customers that wrote a check - then no problem. But when I need to count distinct members that used a branch by the type of transaction (deposit, withdrawal, inquiry, etc) it becomes a problem. I thought about adding a row to the Transaction Hierarchy that says - Any Transaction, so that at the same level there is Deposit, Withdrawal, Inquiry, Any Transaction, and at the next level have Deposit, Withdrawal, Inquiry roll up to a bucket called "Detail" and Any Transaction roll up to "Summary" and educate uses that they are to pick one or the other.

Does anyone have any suggetions? Is there another Cube building software that handles "Count Distinct" better? I realize the cube can count distinct if I bring in the data at the member/transaction level, but that be too big and defeat the purpose of the cube - be better off having a drill through a report against the database.

We figured it out. There have been some views but no replies. If anyone is interested in what we are going to try, let me know and I will explain.

Mix the grain is the only way I've seen it done. Is that what you did?

Hi Jeff,

SQL Server Analysis Services has some better options for Distinct Count:
- Multi-Dimensional Mode ("on disk" style cubes, SQL Server 2005 - 2012) - functionality is good but only scales up to around 10 million rows
- Tabular Mode ("in memory" style cubes, SQL Server 2012) - performance is v fast, scale is limited by available RAM

SQL cubes also support Many-to-Many relationships which can provide the same results using simple Count Measures. In those designs, bridge tables provide the filter context to return the correct set of Customers etc to count. These are easier to implement in the Multi-Dimensional cubes, and scale very well.

Good luck!
Mike

Cognos has the ability to create cubes that count distinct. It's basically mixing grains - but Congos is supposed to know that the grain is mixed and can handle it. There are 2 different ways of creating the data for the cube - either have Transformer do multiple passes against the detail or create a summary table with mixed grains. The summary table, in my opinion, is the the way to go. Either method has some challenges. The bottom line is that the dimension has to have a unique key that spans the entire hierarchy. If the dimension has 3 levels and each level has it's own surrogate key, the Values for the Surrogates in Level 1 cannot be used as a surrogate for Level 2 or Level 3. If this is the case, then an aggregate table in the database can be used.

But if this isn't the case and you are using transformer to build the cube from the detailed fact table, then you still need to create a business key that spans the hierarchy. If the hierarchy goes County to State, and it contains the County "Oklahoma" to the State "Oklahoma", then you would have to change the county "Oklahoma" to "Oklahoma County". This is probably a bad example because County Names aren't unique across states anyways, but....

This raises a question about how to assign Surrogates to the different levels of a Hierarchy. One way is to start the surrogates at 1 for the lowest level, 100000 for the next level (assuming the first level will never have more than 99999 records), and so forth.

But I wonder if the hierarchy could be built like a Ragged Hierarchy where each level of the hierarchy is represented in the lowest level of the hierarchy and the surrogate keys in the higher levels are pulled from the lowest level.

Thank you again for your help. It does have a date but I cannot figure out how to do that within the cube.
Usually most of the hierarchy and attributes I can sort them by key and that will do it - this particular attribute does not have a key ( I think).