Your Privacy Matters: We use our own and third-party cookies to improve your experience on our website. By continuing to use the website we understand that you accept their use. Cookie Policy
40
many-to-many relationships
posted

Does the control support many-to-many relationships (if using a flat table source rather than an OLAP server)?
Note: Excel PivotTables and PowerPivot do not, but SSAS and other OLAP servers do.

For illustration:
Imagine my source table contains rows for books that can either be blue, green or both. In order to be able to group by color, I would have two rows for a book that is both green and blue – one showing color blue and one almost identical row showing color green (Alternatively I would use two tables connected via a JOIN). A simple COUNT() over the rows does return a number higher than the actual number of distinct books. (Note: This is just an example, I am aware that here one could simple define a new color “blue-green”.)

As the user can either use the “color” dimension or not in the PivotGrid, the control must handle the many-to-many relationship. For sure, as long as the user groups by color, everything is fine, but what will happen in the Pivotgrid if the user does not group by color? Will it show the correct result? How do we tell it which is a many-to-many dimension?

If the control DOES NOT support many-to-many relationships: Do you know of any way to implement many-to-many relationships around the control? I though of having several source tables (pre-grouped) and detecting which one to use after each user interaction. I.e. detecting which many-to-many dimensions have been used in a group by and then choosing the appropriate source table. However, this at least means no many-to-many relation could be used for filtering as in that case one cannot easily pick an appropriate source table.