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
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
Thank You Cannondale23 for your reply...I think i now see the point that i was missing