Count distinct metrics in the OLAP cube.

[venkatesancs]

We are trying to definine the cube definition. Some of our metrics are of count distinct nature. That is , we need to go the lowest grain to calculate this metric for any higher level dimensions.

The idea of keeping the lowest level of data in the initial cube build may not be feasible as the number of records are huge (25 millions per week).

Any suggestions on how to tackle this in OLAP solution? Your help is very much appreciated

 

[PacmanUser]

Use different levels of aggregation levels (different tables) and put them into the schema so when you go to an upper level it will use a not to long table.

 

[bi4fun]

I like MicroStragy.