The OneStream Community is temporarily frozen until June 29th due to the ongoing maintenance. Please read the blog post here to learn more.

Forum Discussion

Nitishkrish16's avatar
Nitishkrish16
New Contributor III
3 years ago
Solved

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 b...
Metadata
  • OS_Pizza's avatar
    OS_Pizza
    3 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
3 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