Forum Discussion

Nitishkrish16's avatar
Nitishkrish16
New Contributor III
2 years ago

Can we export a hierarchy/hierarchies into a table?

Dear Community, 

Can we export a hierarchy/hierarchies into a relational table? For an instance if I want to export the complete hierarchy of any Dimension into a table view format whether it will be possible?  I could see we have a Load/Extract to extract the metadata for a hierarchy, is there any other way to showcase or export into it in the form of tables? 

Metadata
  • OS_Pizza's avatar
    OS_Pizza
    2 years ago

    Nitishkrish16  There are many ways to achieve this.For now i can think of this-

    1. You can write a sql query using the data adapter to output the records.

      Below example to extract unique members.Modify the query as per your requirement.

    Check the dim table for dimtypeid and name

    SELECT  
Member.MemberId, 
Member.Name
FROM 
Relationship 
RIGHT OUTER JOIN Member 
ON Relationship.DimTypeId = Member.DimTypeId AND Relationship.ParentId = Member.MemberId
WHERE 
(Member.DimTypeId = 0) AND (Relationship.ParentId IS NULL) 
ORDER BY 
Member.Name

     2. You can use Mindstream metadata manager to extract Parent-child file for any dimension

    https://www.mindstreamanalytics.com/toolbox/mindstream-metadata-manager.html

    3. You can use grid view in dimensions to achieve the same. Tick on Parent-child relationship with Name and parent anme in filter -> Export to csv

     

     

  • OS_Pizza's avatar
    OS_Pizza
    Contributor III
    2 years ago

    Nitishkrish16  There are many ways to achieve this.For now i can think of this-

    1. You can write a sql query using the data adapter to output the records.

      Below example to extract unique members.Modify the query as per your requirement.

    Check the dim table for dimtypeid and name

    SELECT  
Member.MemberId, 
Member.Name
FROM 
Relationship 
RIGHT OUTER JOIN Member 
ON Relationship.DimTypeId = Member.DimTypeId AND Relationship.ParentId = Member.MemberId
WHERE 
(Member.DimTypeId = 0) AND (Relationship.ParentId IS NULL) 
ORDER BY 
Member.Name

     2. You can use Mindstream metadata manager to extract Parent-child file for any dimension

    https://www.mindstreamanalytics.com/toolbox/mindstream-metadata-manager.html

    3. You can use grid view in dimensions to achieve the same. Tick on Parent-child relationship with Name and parent anme in filter -> Export to csv