65K category limit

[redmist]

Hi,

I have a huge table with 555352 records. I have to create 5-6 dimensions from this table. I cannot create separate dimensions as this would create more than 2 million categories in the model which is more than an internal limit in Transformer. This would not fail the cube build but it would create performance issues.

I then decided to create 1 dimension with alternate drill down paths. I realized that there is a 65K categories limitation and i thought i had designed the model to overcome it but apparantly not. I have created the dimension structure below with the category counts

      Level1 (186) | Alternate Level1 (11)
      ----------------------------------
         Level2-KEY (555352)


Even though there are 555352 categories in the unique level, Transformer would do 555352/186 = 2985  and 555352/11 = 50486
which are both less than 65K categories

I'm still gtting the following error

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

Any suggestions would be appreciated

Thanks

[Cannondale23]

you're going to have to generate categories for that dimension on a windows system to see which category in the parent level has too many children (in the next level down), unix throws tr1901 without the actual category.
once I found the culprit category, one thing I use to do is create a level in between the two levels that have a 1:65k parent to child ratio hard limit and called it level_alfa;

decode("tlx.tdlinx_parent_accnt_desc",'non_franchise',((decode((substr(tlx.tdlinx_outlet_desc,1,1)), 'A','A','B','B',......'Z','Z',' ',Other','1','2'.... , "tlx.tdlinx_parent_accnt_desc"))),"tlx.tdlinx_parent_accnt_desc") as  level_alfa

[redmist]

Thank You Cannondale23 for your reply...I think i now see the point that i was missing