I am processing cubes using AS2000 and SQL2000. I am experiencing a tempdb
data file growth of over 27 GB during our processing of cubes. Does anyone
know some pitfalls to avoid that I might not be aware of and what effect
processing cubes might have on the tempdb? Thank you for any insight.

James

do you use complex queries to load your cubes? (specially if you use views
instead-of tables)
does your cubes are optimized to minimize the number of joins?


"Hex Caliber" <hex (AT) caliber (DOT) com> wrote

In this case we are using some semi-complicated views with a number of
subviews. Also, are you refering to views in the cube design or within the
views? Thank you.

"Jéjé" wrote:

I think the "problem" come from the view used as the fact table in the cube.
if you have a complex view then the tempdb database is used.

if your cube is not optimized, then there is a lot of joins between the fact
table and the dimension tables which add some complexity in the final query
executed in the database.

get the query executed by AS and analyze it using the query analyzer to
identify what's appends.
try to load the result of some views into a physical table using DTS for
example.


"Hex Caliber" <hex (AT) caliber (DOT) com> wrote

The other possibility is that you have distinct count measures. The algoithm
that we use forces an ORDER BY clause at the end, which will force tempdb
use. If you have lots of big partitions/cubes, then it will be a lot.

The best practice to minimize the impact is to group the distinct count
measure into its own cube and to then join the two physical cubes together
into a virtual cube for querying.
--
Dave Wickert [MSFT]
dwickert (AT) online (DOT) microsoft.com
Program Manager
BI Systems Team
SQL BI Product Unit (Analysis Services)
--
This posting is provided "AS IS" with no warranties, and confers no rights.


"Hex Caliber" <hex (AT) caliber (DOT) com> wrote