Analysis Services- Temp db overflow

[Lakshmanan]

Hi

We are using Analysis Services for OLAP , relational end
being Sybase.

In one of our cubes where the source table is huge in
volume, we have a measure for which the aggregation type
is "Distinct count" of a column in our fact.

We have noticed that while processing the cube, since its
a distinct count measure, analysis server builds a query
with an order by clause on the distinct count source
column.

Since the table is huge and the query tries to
order by , the temp db becomes full and the process gets
killed.

One of the reason for having it as a base measure over a
calculated measure is the users want to drill through on
it.

Is there any way by which the above can be accomplished
with out the temp db getting filled? Has anybody
experienced it before?

Any help will be appreciated.

Thanks in advance,
Lakshmanan

[mountain man]

"Lakshmanan" <babu...@rediffmail.com> wrote in message
news:03ba01c365d9$ff248d40$a601...@phx.gbl...

1) Allocate a great space for temp.db

and/or

2) Isolate and build the distinct count out
of temp.db in a standard table.

[Donald]

I think it may be better if you split the distinct count
measure of into its own cube, then build a virtual cube to
combine the 2. This is better because:
1) Distinct count measures have a huge impact on cube
performance. By putting it in it's onw cube and accessing
it via a virtual cuber means that it is only queried when
it is actuall needed, rather than every time the main cube
is sliced.
2) The query on your db server can be split into 2 smaller
queries - you could in fact use 2 different views on the
same data.

Cheers

>.
>

[Lakshmanan]

Thanks a lot for all your suggestions.
I will try this out

Thank you again
Lakshmanan

>.
>

[Lakshmanan]

Hi

Thanks for your response. How do I split the source of
data into two as you have mentioned in your second option

Thanks
Lakshman

>.
>