12-07-2021
11:34 AM
- last edited on
05-24-2023
04:53 AM
by
JackLacava
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
12-07-2021 11:41 AM
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
12-07-2021 11:42 AM
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
12-07-2021 11:42 AM
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
12-07-2021 11:43 AM
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
12-07-2021 11:43 AM
Shameless plug, you can use MMM to extract metadata by the hierarchy as a parent-child file.
MindStream MetaData Manager | MindStream Analytics
12-07-2021 11:44 AM
Thanks Celvin worth knowing its an option for sure!
12-07-2021 11:44 AM
12-07-2021 11:44 AM
Thanks Nicolas will give it a shot!
07-12-2022 02:14 PM
I tried your steps but when I mapped it it just showed blanks.
05-20-2024 03:37 PM
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.