cube fail to build

[mrmo]

Please bear with me on this, nothing like being thrown in at the deep end, previous admin left and as the only person who knows anything about Cognos I got the job for now!!

The problem is the SQL job had been failing so I did some digging.

I have a Transformer project that when you try and build comes up with the message, 

(TR1901) PDS-PPE-0130 The number of children or parents of a category cannot exceed 65,536. (Drill) in.......

More digging we have c85,000 customers in a one level hierarchy(is that the word)
we also have a similar number of product SKUs,

So I can guess the problem, and using a related project that is no longer in use, I decided to create a new product level within the datasource(this isn't ideal as the source data is actually pretty flat) I alphabetized all the products to create the level. I am wondering if within Transformer there is a better way, archiving old products, etc. ? Or do I really need to go back to the datasource, and possibly back to the database itself and figure out a way of creating some categories to create a hierarchy?

[bdbits]

This technote indicates this applies all the way up to the 10.x versions of Transformer.
http://www-01.ibm.com/support/docview.wss?uid=swg21341611

You can create the artificial levels within Transformer - they don't have to be in the source data. A common workaround if you have something like customer or product names is to alphabetize them. Also note that it is not the total number of members at the lowest level, but the number of children under any one parent. So you can just divvy them up into 'buckets' where each bucket has <64k children, and you will not get the error. For something like customer, you may just need "A-L" and "M-Z" or something similar, rather than for each letter of the alphabet.

It's annoying, but what can you do?

[mrmo]

thanks, so what I did is basically the only option, but I can do it in Transformer. Just have to think of a rational way of divvying the customers and products.