I am working with two large dimensions. Can anyone suggest how to optimize
large dimension tables?

I am using Excel for reporting against the virtual cube. When I add one of
the dimension tables, it either times out or take a very long time to bring
back the data.

I'm not sure what to do. I have searched and searched through resources but
haven't found anything that will help with this issue.

How big is your dimension? You always want to build a hierarchy to explore
the large dimension instead of pulling all the members at the same time.

"SAM" wrote:

One of the dimensions has over 200,000 members. I created a group level to
overcome the 64K member issue; however when adding the dimension even based a
filter it still takes a very long time to bring back the results or it never
comes back.
For instance, one of the dimensions stores just the account names.

"yongli" wrote:

I even tried filtering down to one employee and a month or day to pull the
account names assigned to this employee and it still takes a very long time.

I added a member key to the fact table that reference the dimension table to
see if that speed up and it helped a little.

"SAM" wrote:

Doesn't Excel have a ~65k row limit? Not sure what effect it has if
you are trying to return more rows than Excel will accept, it could
just "sit and spin" for a while or it may put up an error.... (We use
panorama so I'm in unfamiliar territory)

Also, what is your query response time through built-in MSAS Interface?
MSAS caches (loosely defined) so if you've retrieved the table of
values (you intent to view in excel) through the local AS cube
interface and its still returning your values slowly in excel, then I
doubt its an optimization issue with AS. Just a thought....
Good luck!

Byron

Thanks.

It is slow returning the results in MSAS. I was looking for a way to
optimize the dimension because I didn't think it was a excel problem but how
large the dimension is optimized or not.

"BK" wrote:

Hi Sam.

If you can get your hands on Excel 2007 Beta or ProClarity, try either of
those first, but, you really should consider creating hiearchies for the
dimensions. It sounds like your performance issue is not with the client
but with the return of all of the rows in the dimension.

-Tim

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

hi Sam, your dmension is tiny compared with our dimension wich has about 4.5
million members, but we expend extra time designig an hierarchy for our
members spliting them in blocks of 3000 members per group.
We has 3 levels por that dimension.

Level 0: First letter of th Member --> 26 members in this level. ie.
[LARGE_DIM].[b]

Level 1: Second level, a group wich the member belong. members may vay per
each Level. ie. [LARGE_DIM].[b].[B00001],[LARGE_DIM].[b].[B00035].

Level 3: third Level wich has the Key of the member, the key joins the group
key plus a correlative number from 1 to 30000 per each level.
ie.
[LARGE_DIM].[b].[B00001].[B000010001],[LARGE_DIM].[b].[B00001].[B000013000],[LARGE_DIM].[b].[B00025].[B000250758].

With this arragment we solve the problem of display large dimensions.


"SAM" wrote: