Some questions...

Are any processes accumulating CPU? Analysis Services? SQL Server?

Is the DB server on a different box than Analysis Services?

When does the processing "stop?"...

"why a unique index"

IGNORE_DUP_KEY does not allow duplicates to be inserted. It successfully inserts unique rows while ignoring (not inserting) the non-unique rows. It is one alternative to...

There are several functions I have created to mimic behavior of other DBs' standard functions: LPAD, RPAD, DateTrunc, etc.

Here is the the basis for generic LPAD/RPAD functions....

I don't know MDX well enough (at all!) to know a solution there. But there are often other ways to filet the feline.

Is your data set small enough that...

FWIW, YMMV, etc...

We could not get 32-bitAnalysis Services or SQL Server to use all the memory we were throwing at it. We recently moved to 64-bit for our warehouse...

FYI - we are experimenting with 64-bit SQL and found that nested package error reporting behavior changed slightly. We are investigating to see whether this is a difference in...

Cheat! When it happened to us, our parent-child dimension had one key >64k, and the rest averaging only a few thousand. We redefined the view of the dimension...