Hello,

We are using SSAS 2005 SP1 Entreprise Edition x64 bit
The main fact table has over a 100 milliion rows.
The entire SSAS database, both dims and facts, are MOLAP.

We have several many-to-many (M2M) dimensions connected to the main fact
table via an intermediate measure group (IMG).
The M2M dim and the IMG are using the same database object as their source
and are thus linked as fact relationship. This shared object is a 'normal'
view not a table or indexed view.

We need to be able to process the M2M dim on a regular basis as user defined
members change - sometimes every few minutes.

To do this we need to
1. Process Update the M2M dim
2. Full (or incremental) process the IMG
3. Process Indexes on the main fact

Steps 1 and 2 seem quite quick and arent currently thought to be a problem
Step 3, index processing, is taking too long, a few minutes or more, which
our user base wont accept - we need to reduce this time.

Question
----------
How can we reduce the time to process the indexes? Can we remove this step
altogether in some way? Are there properties I can set to remove the process
index necessity?

Please help if you can

--
Thanks in advance
Mgale1

Probably, it is an hard problem.
I did some test on these volumes for my paper
(http://www.sqlbi.eu/manytomany.aspx) and a test I missed is the
partitioning of the fact table. I'm not sure it can be helpful, I can't
do test in these days, if you have feedback on that please let me know.

Marco Russo
http://www.sqlbi.eu
http://www.sqljunkies.com/weblog/sqlbi

mgale1 wrote:

Marco,

Your document is excellent work - it gives clear guidance on how to model
various complex scenarios. Its very thorough and well laid out - good work.
I dont think it helps me resolve the issue I have however.

--
Thanks for your help
Mgale1