What do I do about having more than 64,000 members in a dimension or
on a level?

Hi Folks --

In advance, thanks for any help. I've got a three level hierarchy on
web hit data in one dimension. Everythin gbut the most basci
dimension cubes well. The bottom layer of the dimension, with
400,000+ members tubes out the cube processing. It errors it out
because it violated the 64,000 member threshhold.

Category ... 20 Categories

Subcategory ... 120 Subcategories

Word Analysis ... 400,000+ members

What can I do do analyze this situation within a Cube environment?

1. Use 64 bit Analysis Services on 64 bit hardware? Can MSAS (2000
enterprise edition) run on a 64 bit server but the data server be on a
non-64 bit server?

2. Use drill down to detail from subcategory

This doesn't work out very well because I could have multiple 100's
and 1000's and/or 100,000's of rows for any cell for any hour any day.

3. What if I broke out these 400,000+ word/lowest level of analysis
into 7 or more separate dimensions with new table names and new
dimension names?

Like

word analysis 1
word analysis 2
word analysis 3
word analysis 4
word analysis 5
word analysis 6
word analysis 7

Would that work? Of course, I probably give up the ability to
hierarchicalize them.

Thanks.

Pete

Pete Hohenhaus, Kirkland, WA, East Side, Seattle, USA
cam_pete (AT) hotmail (DOT) com

You can either create a "grouping" level or use the automatic member
grouping property.

Sean


--
Sean Boon
SQL Server BI Product Unit

--
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm.

"Pete Hohenhaus" <cam_pete (AT) hotmail (DOT) com> wrote

3 points:

1) The restriction is that no *one* member can have more than *64,000*
children. So you have this design:
geography: All -> Country -> State -> City
The 64K children limit means that All cannot have more than 64,000
countries. It means that no country (e.g. USA) can have more than 64,000
states. This means that no state (e.g. Pennsylvania) can have more than
64,000 cities in it. But dimension-wide, you can have lots more than 64,000
members in the geography dimension -- and level-wide, you can have lots more
than 64,000 cities.

2) Regardless if 64,000 is the limit or not, I have found that a couple
hundred is a better rule-of-thumb guideline. Because, first, most
client-side tools don't perform well if the drilldown is more than a couple
of hundred. It is like populating a dropdown dialog box with thousands of
members -- it works but performance is terrible. I would strongly advise
that you test your design if you go above a couple of hundred members in a
drilldown. Second, it is my experience that most users also don't like a
drilldown with more than a couple of hundred members at a time -- normally
once they experience it, they will find that their comfort level is probably
even less. Having deep rich hierarchies greatly improves an end-users
abilities to pivot more specifically, narrow their focus quicker, only
examine the detail that they really want to look at, etc. I have found that
users typically want higher limits only when they are trying to use Analysis
Services as a replacement for traditional paper-based reporting tool --
rather than an interactive exploration tool.

3) This restriction is true in both 32-bit and 64-bit versions of Analysis
Services.

--
Dave Wickert [MSFT]
dwickert (AT) online (DOT) microsoft.com
Program Manager
BI Practices Team
SQL BI Product Unit (Analysis Services)
--
This posting is provided "AS IS" with no warranties, and confers no rights.

"Pete Hohenhaus" <cam_pete (AT) hotmail (DOT) com> wrote

"Dave Wickert [MSFT]" <dwickert (AT) online (DOT) microsoft.com> wrote


Dave --

Thanks for the info and ideas. I quite agree with you that more than
a couple of hundred members tends to become meaningless and not a good
design approach. Nonetheless, I am faced with trying to find a
tools-based approach to dealing with lots and lots of base level
values. My present idea is to stratify that level by identifying the
impact members of that level, pop them in another table with a new
name and then use that in Cube Analysis.

As always, your thoughts are most appreciated.

Best wishes,

Pete

Pete Hohenhaus
cam_pete (AT) hotmail (DOT) com
Woodinville, WA, East Side Seattle, USA