I'm using VB.NET to connect to and parse data from an Analysis Services database.
The problem I'm having is while trying to find all Measure Groups that are related to a given Dimension. I've been digging through the API and it doesn't seem that Dimensions have any information on Measure Groups at all, but Measure Groups can contain Dimensions.
This makes it easy if I want to get a list of related Dimensions given a Measure Group (measureGroup.Dimensions
), but what I'm looking for is the opposite.
The only thing I can think of is to loop through all the Measure Groups within the Cube and check if it contains a reference to the Dimension in question:
Dim dimID As String = "SOME_DIM_ID"
Dim relatedMeasureGroups As New List(Of Amo.MeasureGroup)
For Each mg As Amo.MeasureGroup In _cube.MeasureGroups
If mg.Dimensions.Contains(dimID) Then
relatedMeasureGroups.Add(mg)
Continue For
End If
Next
This works, but is obviously very slow and inefficient. Is there a more direct way that I am just not seeing?
EDIT: I should say, I need to do this because I am trying to find all potential many-to-many Measure Groups for a Dimension. As you can tell, the way I'm doing it is a bit manual (find Measure Groups related to the initial Dimension, find all Dimensions related to them, and yet again find all Measure Groups related to those), so if there is a more direct way to solve that problem, it could bypass my need to solve the original problem.
You could query the SSAS DMV... $SYSTEM.MDSCHEMA_MEASUREGROUP_DIMENSIONS
SELECT *
FROM $SYSTEM.MDSCHEMA_MEASUREGROUP_DIMENSIONS
WHERE MEASUREGROUP_CARDINALITY = 'MANY' AND
DIMENSION_CARDINALITY = 'MANY' AND
[DIMENSION_UNIQUE_NAME] = '[Business Unit]'
Also, this may come in handy --> http://www.ssas-info.com/VidasMatelisBlog/wp-content/uploads/2008/07/mdschema.jpg
Here's the XMLA-version...
<Discover xmlns="urn:schemas-microsoft-com:xml-analysis">
<RequestType>MDSCHEMA_MEASUREGROUP_DIMENSIONS</RequestType>
<Restrictions>
<RestrictionList xmlns="urn:schemas-microsoft-com:xml-analysis">
<DIMENSION_UNIQUE_NAME>[<<insert dimension>>]</DIMENSION_UNIQUE_NAME>
</RestrictionList>
</Restrictions>
<Properties/>
</Discover>