Hi all
Can anybody help me on this situation.
calculated cells is taking very long time on the front-end
OWC.

here is my structure
employee - parent-child dimension 5 levels
errors - measure with count as aggregation
book - measure with count as aggregation
pages - measure with sum as aggregation
book - dimension - star
error type - dimension - star

i have created 2 cubes one for errors and other for pages.
cube1 has error as measure with error type as one of the
dimensions, this is not used in pages cube..

i created a virtual cube and brought all the data to it..
this is working fine..

Now the issue:
my requirement is something like this, visually

employee errors books pages page(calculated member)
a 30 5 550 325
b 30(c+d+e) 5 550 325
c 10 2 150 75 (books/pages)
d 11 1 100 100
e 09 2 300 150

this is working fine,if i keep book dimension in the row
level.. incase i wish to see the data employye-hierarchy
wise, then the system sinks.. processing will be 100% for
about 10-15 minutes. i was unable to understand this
behavior..

Can anyone please help me on this..i tried using
calculated cells, it was better than calculated member,
but still the query time is 4-5 minutes..

will be pleased to provide more info..

Same problem with a different front-end?

Does the server have sufficient ram? Client?

Care to post the formula?

public @ the domain below
www.tomchester.net

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

Thanks Tom

Actually I am browsing through your site to get some info
on calculated cells.. It was very informative.

server is Windows advanced server with 1GB RAM.. it has
only SQL server with Analysis and IIS along with visual
studio and office XP

[book] dimension has books and grouping level.. i need to
have this because there is a limit on dimension members.

here is the formula i used for calculated cells
calccell 1
subcube : [measures].[pages1]
condition: isgeneration([book].currentmember,0)
value : [measures].[page]

[measures].[page] is calc member with expression
[measures].[pages]/[measures].[books]

calccell 2
subcube : [measures].[pages1]
condition: isgeneration([book].currentmember,1)
value : sum([book].children,[measures].[page])

calccell 3
subcube : [measures].[pages1]
condition: [book].currentmember.name = "All Book"
value : sum([book].grplevel.members, [measures].
[pages1])

calccell 1 and 2 are very fast.. the system sinks only on
execution of calccell 3.. is this got to do with solve
order and passorder.. I've not changed these values.. left
them as given by AS

Hope u got the info required

waiting for ur reply

Guru

You need to test performance with a different front-end. Try Excel if
nothing else at hand. Have you monitored performance to if ram exceeded on
server and/or client? How many members in this set: ([book].grplevel.members
?

You can achieve a teeny performance gain in calccell3 by changing this:
[book].currentmember.name = "All Book"

To this:
[book].currentmember IS [All Book]

public @ the domain below
www.tomchester.net

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

Hi Tom
I donot have any other front-end option other than OWC.

I tried changing the calcell 3 condition as suggested, its
better to a little extent.

Does Passorder and Solveorder got anything to do with the
query time?? Can u suggest some useful sites for reference
on this, maybe with some examples. I glanced through your
site(tomchester.net) and got some useful info on this and
that is how i came up with these 3 calculated cells.

is there any property i can set on the front-end OWC to
increase the cache size of OWC? i assume that AS is
calculating fast, but pasting the data on OWC is taking
more time..

Thanks for those replies

Guru

What about Excel?

public @ the domain below
www.tomchester.net

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

Did you mean building a pivot table in Excel using Get
External Data option in Excel?

I tried this option, but unfortunately it is taking more
than 10 minutes to build the pivot table. I think OWC is
better off as I was able to put the data in around 2
minutes.

I got around 135 members in [book].grplevel.members. This
is an automatic grouping level AS made because i got more
members than the limit.