ado.netssasmdxxmla

Get the relations between dimensions and measure groups

There is the "Show fields related to:" feature in Excel: enter image description here I want something like this, but return in the following form:

MeasureGroup1: Dimension1, Dimension2, Dimesion3
MeasureGroup2: Dimension2, Dimesion3
...

Can't find out how Excel retrieve this data. I need to write a script which will automatically generate the relationship data for a chosen cube. It can be MDX or XMLA. ADO.NET in the last resort (it should be a portable script after all).

Hope you can help.

Solution

  • The following returns a recordset which Excel uses to determine which dimensions are related to which measure groups:

    select *
from $system.MDSCHEMA_MEASUREGROUP_DIMENSIONS