Many-to-many dimension processing performance

Hello,

I have a problem regarding M2M dimension processing performance that I would like some help with please.

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

You can try and skip step 3 all together and enable Lazy processing. This allows you to let users query the cube right after step 2. Analysis Server will be processing indexes in the background for you while users query.

This has obvious peroformance impact on the cube, users will be getting slower performance while indexes are not there and lazy processing is still going.

Another way to minimize time for building indexes is to patition you cube and let build index for several partitions to run in parallel. This should shorten processing times.

Edward Melomed.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

Edward,

Lazy Processing is enabled on the server; it is the default.setting.
I have also noticed that each fact or dim has a 'processing mode' available from the properties window.
I am confused as to where I should set this.
Do I need to set it on the M2M dimension, the Intermediate Measue Group, the 'local' dimension or the main fact table?

Please help if you can
Thanks
Mgale1

|||

As far as I understand the main time in your schenario goes for processing indexes for the partitions in the main fact measure group. These partitions the candidates for setting processing mode to LazyAggregations.

The rest of the objects should be relatively fast to process.

Edward Melomed.
--
This posting is provided "AS IS" with no warranties, and confers no rights.