Hello Everyone:

We have a situation where we are dealing with a relatively large dimension with
some 200000 members. The dimension is to be organized into about 6 different
hierarchies.

In addition, the members have roughly some 100 properties. which are required
for display purposes and information.

Does anyone have any suggestions on how to best deal with this kind of dimension?

Regards,
Mario

You say 6 different hierachies ? So when you say 200.000 members is that
200.000 combinations or
a flat dimension with 200.000 members.

I believe you need to put in som levels in the dimension one way or another.

\Michael V.


"MT" <MT (AT) discussions (DOT) microsoft.com> skrev i en meddelelse
news:2E279835-4DA2-4B09-9EE9-FAC96DC52358 (AT) microsoft (DOT) com...

The dimension has 200000 different members arranged in 6 distinct hierachies
which I have defined using the dot notation. The six dimensions differ in the number
of levels: some have have as little as 3 some have as many as 8 levels.

My problem is that each member has over 150 properties that the users want
displayed in different circumstances. the raw volume of data alone is in the
order of 100 MB.

If I am not mistaken AS will load the entire dimension into memory. In addition
each hierachy is treated as a separate dimension in that they are also separately
loaded into memory. As a result we have some 600 MB of memory dedicated
to this one dimension with its six variations in hierarchies.

I just wanted to know how other people have dealt with this problem because
I cannot believe we are the first to have it.

Regards,
Mario

"Michael Vardinghus" wrote:

This is discussed in both the Analysis Services Perf and Operations Guides.
http://www.microsoft.com/technet/pro.../ansvcspg.mspx
http://www.microsoft.com/technet/pro.../anservog.mspx
also, to a limited extent:
http://www.microsoft.com/technet/pro.../ansvcs64.mspx

Storage is dependent on the number of *independent* strings in a member
property.
For example, if you had member property called "Gender" which contained one
of two strings: "Male" or "Female", then the strings are stored once, and a
longword identifier is actually stored with the member itself. This is good
usage (in terms of memory) for a member property. It has the side-effect
that the member property can be used as virtual dimension since the end-user
could potentially be doing analysis on it.

A high overhead variant of this would be to have a member property which
contained street addresses. Since every customer would potentially have
their own string this means a lot of storage. And it means even more since
the strings are stored as unicode double-byte characters. The other issue is
that this kind of data would never be used for analysis, but only to be
"viewed". For this kind of situation, I usually recommend that a customer
look at using "Actions", and then have an external application (probably
web-based) which could take a customer# and transform that to a nice data
lookup html page. This is easy to do and makes data lookups have very little
overhead.
--
Dave Wickert [MSFT]
dwickert (AT) online (DOT) microsoft.com
Program Manager
BI SystemsTeam
SQL BI Product Unit (Analysis Services)
--
This posting is provided "AS IS" with no warranties, and confers no rights.

"MT" <MT (AT) discussions (DOT) microsoft.com> wrote