Does Report Builder handle many-to-many relationships within an SSAS cube correctly? I can't seem to get it to. Using Adventure Works, here's an example of the problem I'm seeing. -Create a new report in RB, opening the Adventure Works DW model -Select one of the perspectives that includes the Sales Reason dimension (like Direct Sales) -Select the Sales Reason entity in the Entities list and put the Sales Reason Type attribute into the report (making Sales Reason the primary entity) -The Entities list will be reduced to show the Adventure Works Sales Reasons relationship (which maps to that underlying measure group) -If you select the Adventure Works Sales Reasons relationship, youll see the Internet Sales Order Details entity below it -If you select the Sales Order Details entity, however, youll only see the attributes that are in that dimension; you wont see any of the measures that exist in the measure groups that are related to it -Based on that fact, you cant get to any of the measures in the cube (since the Sales Reason dimension is related to the other measure groups only through M-to-M relationships) Of course, it may be that I just don't understand how the auto-generated report model for the SSAS cube is exposing the relationships and the entities that correspond to the Sales Reason dimension, the Sales Reasons measure group (the intermediate measure group for the M-to-M relationships to the other measure groups), etc. Anyone seen this before or know that this is defined behavior? Seems that for any SSAS cube that includes M-to-M relationships, Report Builder just won't work... Thanks! Dave Fackler

Unfortunately, the SMDL model in the Report Builder sometimes gets in the way when SSAS is used as a data source. To get around similar limitations, I added calculated members in the cube which mapped to the original measures and I assigned them to the first measure group. The net result is that all measures were mapped to only one measure group. Then, I had to manually change the SMDL model to hide the measure groups/measures I don't need.

Hi Teo, I understand most of the limitations with RB against an SSAS-based SMDL model. However, I've never tested trying to build a report that includes dimensions/measure groups that are related via a many-to-many relationship. The fact that this doesn't seem to work is troubling, so I'm trying to confirm that it is indeed an issue (or bug?) with RB and not just me doing something silly... Have you ever tried to create a report using RB that hits the Sales Reason dimension in the AW cube? If not, give it a try and let me know what your experience is... Thanks! Dave F.

The fact that this doesn't seem to work is troubling, so I'm trying to confirm that it is indeed an issue (or bug?) with RB and not just me doing something silly. Not the latter for sure . M-M relationships is not the only case. The same thing happens if you have a multi-grain relationship, e.g. Sales Target in the Adventure Works. Then the model drops the Date relationship so you can't slice by date. I'd recommend you file a bug report with product services for all Report Builder issues that you encounter (I've done my fair share doing so). The more people complain about the SSAS integration scenario the more likely is to get fixed/improved. Meanwhile, consider opening the SMDL model and adding the dropped relationships manually. This isn't a supported scenario but it will probably get you going.

Hi Teo lachev, I have a similar issue. Can you please tell me how to edit the "manually change the SMDL model to hide the measure groups/measures". Thanks Tinku