Skip to main content
N
Nitishkrish16
Newcomer
May 25, 2023
Solved

Can we export a hierarchy/hierarchies into a table?

  • May 25, 2023
  • 2 replies
  • 0 views

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? 

    Best answer by OS_Pizza

    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

    OS_Pizza_0-1685013859310.png

    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_1-1685013985430.png

     

     

    2 replies

    O
    OS_PizzaAnswer
    Veteran
    May 25, 2023

    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

    OS_Pizza_0-1685013859310.png

    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_1-1685013985430.png

     

     

      N
      Nitishkrish16Author
      Newcomer
      May 25, 2023

      Thanks a lot for your response. 🙂

      Terms & ConditionsAccessibility statement