Post reply

Parent Child Dimension > 64000 members

  • October 23, 2003 at 4:06 am

    #151954

    I have a situation where I have a dimension with more than 64000 members (Jobs). I would normally create another grouping level in the dimension and then hide that level.

    Since this is a Parent-Child dimension, I do not have that option. Does anyone have any advice on how to have more than 64000 members in a parent-child dimension or is it impossible?

    Thanks,

    Sami

  • October 23, 2003 at 7:57 am

    #479291

    Cheat! When it happened to us, our parent-child dimension had one key >64k, and the rest averaging only a few thousand. We redefined the view of the dimension table to use a CASE statement to manually move/mask a chunk of the data into a new key. This only works if your data is "predictable."

    Our product dimension has 4 levels - Dept, SubDept, Class, and Product. When we hit > 64K product for a Class in Dept '010', we used the following:

    CREATE VIEW ProductDim_CubeView AS

    SELECT ph.[ProductHierarchyId], [DepartmentCode], [SubDepartmentCode],

    CASE WHEN [DepartmentCode] = '010' THEN LEFT(ItemCode,3) ELSE [ClassCode] END AS [ClassCode].....

    It was fast and low-cost, except for the full reprocess we had to do in a hurry. would recommend monitoring your parent-child dimensions regularly to see if any are approaching the limit. Even beter, design it around this limit the first time instead of getting surprised during daily processing.

    Hope this makes sense.

    Larry

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply