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