We have a somewhat large dimension (not large in terms of OLAP at
~10000 leaf members) but is way too large to allow a user to drop onto
a pivot table etc without making the results useless. What we are
hoping to do is stratify the dimension based on the begining characters
of the number sequence. In other words:

00 - 06
0083893
0562738
07 - 22
0712537
2271682
23 - 99
2378190
9809101

We would like these stratifications to adjust themselves each month
when new data is loaded so that the members are distributed roughly
evenly with only a couple of hundred in each segment. There are
probably 10-20 members added each month and none are ever removed.

Are we going to have to redesign aggregations for each partition each
time the hierarchy is changed? We are currently using Analysis
Services 2K but will be upgrading to 2005 soon.

Any help on the best way to implement this is appreciated.

Have you looked at the atrribute Discretization option in AS 2005 - it
may help you?

http://msdn2.microsoft.com/en-US/lib...4(SQL.90).aspx

The grouping of members of an attribute into buckets is called
discretization. When end users browse a level of a hierarchy that is
based on an attribute, they see the names and values of the buckets,
instead of the members themselves. To end users, the buckets look like
regular members.

Discretization significantly reduces the number of members that are
displayed for an attribute, without changing the structure of the
attribute. If there are a lot of members in a level, this savings can
greatly facilitate browsing a hierarchy. For example, an end user can
expand a member that contains 30,000 children without waiting for all
30,000 members to expand under the parent.
...

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.developersdex.com ***

You can add a new bucket column in the dimension table to control the
breakdown of your leaf members, then create a user hierarchy using the bucket
column, and left level.

"porter_wss (AT) hotmail (DOT) com" wrote:

How do you this in AS2K?

I created 5 dimension tables that include Account Name which is extremely
large, over 200,000 members.

The first dimension table is just Account Name, I set grouping automatic
however, I still need to add more grouping but not sure how.

Ideally, I want to reduce the time outs the user gets when adding this
dimension to the pivot table.

"Deepak Puri" wrote:

Well, as usggested earlier in this thread, you could manually control
the grouping by introducing a field for this purpose, based on how many
groups you need. This field could be created in a fact table view, to
avoid adding a column to the fact table. But you would have to analyze
the distribution of member data, so that the expression for the field
results in uniformly-distributed groups.


- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.developersdex.com ***

To use Discretization in this case wouldn't you have to have two identical
customer number attributes, one to set the discretization on to get the
ranges and then another to form the second layer of the hierarchy to drill
into the actual customer number from the range.

"Deepak Puri" wrote: