Get the relations between dimensions and measure groups

Question

There is the "Show fields related to:" feature in Excel: 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.

really unsure of how to do this - the mdx function EXISTS uses measure group as its 3rd argument - would this function help? — whytheq, Apr 14 '17 at 11:44
Not really, It works with sets and querying huge dimensions is heavy. There is also MeasureGroupMeasures('MeasureGroup1').Item(0), however doesn't work with Visible = False measures. — Danylo Korostil, Apr 14 '17 at 12:42

score 2 · Accepted Answer · answered Apr 15 '17 at 02:41

2

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

select *
from $system.MDSCHEMA_MEASUREGROUP_DIMENSIONS

answered Apr 15 '17 at 02:41

GregGalloway

11,355
3
16
47

Exactly what I want! Shame on me: didn't manage to find it before. Thanks, Greg! – Danylo Korostil Apr 15 '17 at 10:14
There is possible to use xmla for these things, which even is more native for clients like Excel! I've posted the code as an extra answer. – Danylo Korostil Apr 15 '17 at 10:27

score 1 · Answer 2 · answered Apr 15 '17 at 10:25

1

Plus the xmla solution:

<Discover xmlns="urn:schemas-microsoft-com:xml-analysis">
    <RequestType>MDSCHEMA_MEASUREGROUP_DIMENSIONS</RequestType>
    <Restrictions />
    <Properties />
</Discover>

answered Apr 15 '17 at 10:25

Danylo Korostil

1,464
2
10
19

Get the relations between dimensions and measure groups

2 Answers2