MDX Query for Parent Child Relationship

1
votes

I have an OLAP Basically there is a dimension that has parent-child relationship. So the dimension has a parent-id and a child-id.

There is a fact table that exists that has the child-id. I would like to get data for a child and all its given children when I provide the parent id.

How could I achieve this in a MDX query ? 

 <Dimension foreignKey="child_id"  name="SUPPLIER">
  <Hierarchy hasAll="true" allMemberName="all" allMemberCaption="all" primaryKey="child_id" >
    <Table name="suppliers">
    </Table>
    <Level name="SUPPLIER_L"  column="child_id" nameColumn="child_id" parentColumn="parent_id"  
    uniqueMembers="true" levelType="Regular" hideMemberIf="Never" >
    </Level>
  </Hierarchy>
</Dimension>

I have my dimension where this hierarchy occurs.

2
parent-childolapmdxmondrian

2 Answers

2
votes

Take a look at the DESCENDANT MDX function.

You just say where you want to start in the hierachy and then where you want to stop at what level. Then it will give you all the level between start and end points.

Post the part of your mondrian schema cube so I can give you the exact syntax

0
votes

The following MDX should give you the sub-tree under 'your-parent-id' :

SELECT [Measures].[your-measure] on 0, Descendants( [Supplier].&[your-parent-id], [Supplier].&[your-parent-id].level, SELF_AND_AFTER) ON 1 FROM [your-cube]