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.

Many to many or not ?

Hello I have a question regarding many to many diemensions.

I have a fact table with the following structure :

- fact_id

- act_cat

- amount

The dimension table 'PL structure' has the following structure :

- pl id

- report_view

- act_cat

- name

One line from the fact table relates to two lines from the dimension table.

Example:

the act_cat 'Holiday' frol the fact table relates to the act_cat 'Holiday' in the dimension table. But in the dimension table the act_cat 'Holdiay' exists two times, one time with report_view 'View 1' and one time with report_view 'View 2'

The report_view will become a parameter on the reports. So the end-user must always select one report_view.

Now is the question. how can I solve this problem in Analysis Services. Can I do this only with many to many dimensions, or is there another solution.

The name "Report_view" suggest me that you may face this scenario in a different way, but assuming that you really have to handle this situation, the many-to-many approach should be working (below I suggest you how).

But please let me explain one thing: it is always strange when you have a dimension (PL structure) that has an ID (pl id) that is not referenced into the fact table. You don't have the star schema, and when your relational model is not star-schema based, you always have some hidden issue that was not solved in the relationa design.

Anyway, if for whatever reason you model is the best one (or is the only you can use...) then this is a possible solution.

You have to define one named query (or a view) that I name Factless_ActCat_PL_Id:

SELECT pl_id, act_cat FROM [PL Structure]

Then you have to define another named query (or view) that I name Dim_Act_Cat:

SELECT DISTINCT act_cat FROM [PL Structure]

At this point you have two fact tables and two dimensions.

You define the Data Source View with these 4 tables/views and you have to define this logical primary key by hand if the wizard doesn't find them:

act_cat must be the primary key for Dim_Act_Cat.

Then you define 2 dimension (PL structure and Dim_Act_Cat) and two measure groups (original fact table and Factless_Act_Cat_PL_Id). Your fact table has a regular relationship with Dim_Act_Cat and a many-to-many relationship with PL Structure - to build that, the Factles_Act_Cat_PL_Id measure group must have two regular relationships with PL structure and Dim_Act_Cat dimensions.

Please read my paper on many-to-many dimensions if you are in trouble with these concepts:
http://www.sqlbi.eu/manytomany.aspx

Let me know if it works as you expected.

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

Many to many or not ?

Hello I have a question regarding many to many diemensions.

I have a fact table with the following structure :

- fact_id

- act_cat

- amount

The dimension table 'PL structure' has the following structure :

- pl id

- report_view

- act_cat

- name

One line from the fact table relates to two lines from the dimension table.

Example:

the act_cat 'Holiday' frol the fact table relates to the act_cat 'Holiday' in the dimension table. But in the dimension table the act_cat 'Holdiay' exists two times, one time with report_view 'View 1' and one time with report_view 'View 2'

The report_view will become a parameter on the reports. So the end-user must always select one report_view.

Now is the question. how can I solve this problem in Analysis Services. Can I do this only with many to many dimensions, or is there another solution.

The name "Report_view" suggest me that you may face this scenario in a different way, but assuming that you really have to handle this situation, the many-to-many approach should be working (below I suggest you how).

But please let me explain one thing: it is always strange when you have a dimension (PL structure) that has an ID (pl id) that is not referenced into the fact table. You don't have the star schema, and when your relational model is not star-schema based, you always have some hidden issue that was not solved in the relationa design.

Anyway, if for whatever reason you model is the best one (or is the only you can use...) then this is a possible solution.

You have to define one named query (or a view) that I name Factless_ActCat_PL_Id:

SELECT pl_id, act_cat FROM [PL Structure]

Then you have to define another named query (or view) that I name Dim_Act_Cat:

SELECT DISTINCT act_cat FROM [PL Structure]

At this point you have two fact tables and two dimensions.

You define the Data Source View with these 4 tables/views and you have to define this logical primary key by hand if the wizard doesn't find them:

act_cat must be the primary key for Dim_Act_Cat.

Then you define 2 dimension (PL structure and Dim_Act_Cat) and two measure groups (original fact table and Factless_Act_Cat_PL_Id). Your fact table has a regular relationship with Dim_Act_Cat and a many-to-many relationship with PL Structure - to build that, the Factles_Act_Cat_PL_Id measure group must have two regular relationships with PL structure and Dim_Act_Cat dimensions.

Please read my paper on many-to-many dimensions if you are in trouble with these concepts:
http://www.sqlbi.eu/manytomany.aspx

Let me know if it works as you expected.

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