SQL Server Performance Forum – Threads Archive
OLAP – Dimension
Hi Folks, We have problems with our Cube.One of our dimension has more 64K (64,000) members. We had tried to use grouping but it does not meet our needs.
Can somebody suggest another way to to resolve this limitation problem besides grouping.
Any comments/suggestion (even redesigning the dimension or fact tables) will be welcome. Thanks and have a great day. rn
I’m afraid there’s no solution other than using Member Groups to create a higher parent automatically or you choose your own grouping scheme manually. For example, if you have an IP dimension, the leaf members are gonna exceed 64K. So you can either reverse-lookup and group them under the parent DNS name or group them under IP subnets. Anyway, having more than 64K is a bad idea if your clients are using free-drill browsers. You don’t want a funky user to drill to a level with hundreds of thousands of members. It will choke AS and hog the network.
Hey Revi, When yuo say that grouping doesn’t meet your needs, what did you mean? Do you not like the way it is implemented? Steve
Revi, We had a 64K issue with a Parent-Child job dimension. In my scenario, we had over 65K jobs with about 1.5K jobs that were a child. Since all jobs without an explicit parent default to the all jobs level for a parent, the dimension blew up because all jobs had over 64K children. Since it is a parent-child dimension, there is no opportunity to create an additional grouping level. All Jobs 65K+ total
Level 02 – 64,000 (Jobs with no explicit Parent, all 64,000 with all jobs parent)
Level 03 – 1,500 (Jobs with an explicit Parent, no more than 20 children to one Level 02 parent) The solution that we reached involves creating additional jobs that are going to be used for parent jobs. We have over 30 companies so we created 1 "parent" job for each company. All of the jobs that did not have a parent were assigned to the parent job for each company. We created the new parent relationship in the Dimension table in the datamart. All Jobs 65K+ total
Level 02 – 30 (Parent Job for each Company)
Level 03 – 64k+ (Jobs with Default Parent, no more than 13,000 per Level 02 Parent)
Level 04 – 1,500 (Jobs with explicit parent, no more than 20 per one Level 03 parent) The only downside to this scenario is that our reports now include an additional grouping level (the Level 02 parent). We have had to modify our MDX queries to filter out the Level 02 entries. In any event, this is the problem we had and solution that we came up with. If anyone has a better suggestion for a Parent-Child 64K member issue, let me know. Thanks,
Sami
If you are unable to come up with groupings that are logical or make sense you could rely on the automatic grouping feature it is located under the advanced tab. I believe that the 64 bit version of SQL Server suports larger dimensions; however large dimensions(>64k) such as this are not just a perfromance problem but present a usability problem as well.
]]>