Hi All,
We need to know, for the cube building functionality which software provides the better feature. Is it db2 olap server or Cognos Transfornmer.
Customer count is one of the requirement, for which we tried to use cognos transformer and IBM DB2 OLAP Server. I found that congos transformer has a built in feature to count for no. of customers (Unique and non unique). To acheive the functionality of a unique or distinct customer count, we created a dimension as customer which has unique customer ids and have a measure which will count on that dimension. This exactly solves our problem.
BUT the catch is, it will not allow more than 65536 members or records under a dimension. The no. is derived from 2 power 16 and the field type of cognos dimension I assume to be of short integer. As we have more customers than 65536, it gives the following error,
"[TR1901] the number of children (or parents) of a catgory cannot exceed 65,536 [Drill] in Customer_Age_band_Fact.mdc."
And also we tried to solve the customer count problem in DB2 OLAP Server. The solution we derived was a very very complicated solution using db2 features like rollups and groupping sets. And there also another limitation like it will not allow to have more than 5 rollups in a single sql statement stating that it is a product limitation.
(
Any idea on how to solve the problem. Or have I reached the limits of the product.? Any Comments/Suggestions/input are most welcome.
Thanks and Best Regards
GK
We need to know, for the cube building functionality which software provides the better feature. Is it db2 olap server or Cognos Transfornmer.
Customer count is one of the requirement, for which we tried to use cognos transformer and IBM DB2 OLAP Server. I found that congos transformer has a built in feature to count for no. of customers (Unique and non unique). To acheive the functionality of a unique or distinct customer count, we created a dimension as customer which has unique customer ids and have a measure which will count on that dimension. This exactly solves our problem.
BUT the catch is, it will not allow more than 65536 members or records under a dimension. The no. is derived from 2 power 16 and the field type of cognos dimension I assume to be of short integer. As we have more customers than 65536, it gives the following error,
"[TR1901] the number of children (or parents) of a catgory cannot exceed 65,536 [Drill] in Customer_Age_band_Fact.mdc."
And also we tried to solve the customer count problem in DB2 OLAP Server. The solution we derived was a very very complicated solution using db2 features like rollups and groupping sets. And there also another limitation like it will not allow to have more than 5 rollups in a single sql statement stating that it is a product limitation.
(Any idea on how to solve the problem. Or have I reached the limits of the product.? Any Comments/Suggestions/input are most welcome.
Thanks and Best Regards
GK