HI All
Does anyone know a way to extract a given Hierarchy to Excel broken out by columns for each layer ie a parent in one and children in the other. Using HFM in the past, I recall being able to extract the different hierarchies to Excel but thus far havent found a nice way to do this in OS. Of course I can pull the hierarchies into a cube view and export to exel which does have some visual indentation but its all in one column.
Appreciate any ideas!
Thanks
Tahir
Hi Tahir,
It’s not exactly as you’re asking but you can pull the parent/child relationship from the Dimension Library > Grid View tab in OS workspace. Click the “Grid Settings” button and check the box at the top to “display parent child relationships”. Unfortunately this only shows one column for child and one for parent which will not show the full hierarchy with a column for child level.
The other way to see this visually is to pull a QV that’s unsuppressed and set the expansion filter to Tree Descendants" to see the indentation but without the separate columns (it will be indented within one column).
Hope that’s a useful starting point!
-Nicole
HI Nicole
Many thanks for these suggestions, i have used the QV method but wondered if there is something better. I’ll have a look at the Dimension library method.
Cheers
Tahir
Hi Tahir,
Once you get your metadata to Excel either through a Cube View or a Quick View (as Nicole suggested), you should be able to build a function in Excel and use that in a formula to break your information into columns. Something starting from a function like this: https://www.mrexcel.com/board/threads/formula-to-find-format-alignment-indent-level.620757/
Another possibility to look at would be Application Control Manager. It’s a free download in the Marketplace.
Alternatively, a Google search should turn up a tool that could be used. You’ll need to have access to extract application elements (based on your title I would assume you could do).
Thanks,
Andy
HI Andy
Many thanks for the suggestions I will take a look. I have heard of ACM but havent used it as yet.
Thanks
Tahir
Shameless plug, you can use MMM to extract metadata by the hierarchy as a parent-child file.
MindStream MetaData Manager | MindStream Analytics
Thanks Celvin worth knowing its an option for sure!
Easy way : Export the Relationship of your dimension as XML. Open the XML in Excel and select parent/child
Thanks Nicolas will give it a shot!
I tried your steps but when I mapped it it just showed blanks.
Hi Marisol - Not sure if you figured it out by now and maybe to help whoever else comes across this thread, but to get the parent child members to show in the table, you have to right click in the table Excel creates then go to XML > Import and it'll import from the file. The drag and drop exercise only creates the table layout.
