Extract Metadata to Excel

[Tahir2061]

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

[NicoleBruno]

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

[Tahir2061]

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

[AndyR]

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

[Tahir2061]

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

[ckattookaran]

Shameless plug, you can use MMM to extract metadata by the hierarchy as a parent-child file.
MindStream MetaData Manager | MindStream Analytics

[Tahir2061]

Thanks Celvin worth knowing its an option for sure!

[NicolasArgente]

Easy way : Export the Relationship of your dimension as XML. Open the XML in Excel and select parent/child

[Tahir2061]

Thanks Nicolas will give it a shot!

[marisolbritton]

I tried your steps but when I mapped it it just showed blanks.