Metadata management

SKVK
New Contributor III

Team

Am new to the Onestream. Can someone please guide me how to extract and load metadata in Onestream using flat files/Excel files.

Regards

Varun

7 REPLIES 7

TheJonG
New Contributor III

Metadata can only be loaded and extracted via XML. There is a Consulting Utility available on the MarketPlace which converts an Excel file into an XML script which can then be loaded into OneStream. The tool is called 'Excel Metadata Builder'.

SKVK
New Contributor III

Thanks TheJonG. Can you please guide me to the Marketplace where i can download the tool. And does it cost, please.

TheJonG
New Contributor III

Here is the URL:

https://xfmarketplace.com/OneStreamWeb/OneStreamWindowsApp.aspx

Click 'Solutions' and then search for 'metadata' and the Excel Metadata Builder should be the only result.

 

TheJonG_0-1647359453040.png

 

NicolasArgente
Valued Contributor

Varun, you can also have a look at the tool from MindStream : https://www.mindstreamanalytics.com/toolbox/mindstream-metadata-manager.html

Depends on what you want to achieve!

Connect with me on:
LinkedIn: https://www.linkedin.com/in/nicolas-argente/
Website: https://aiqos.io
If you want to lift yourself up, lift up someone else.

rhankey
New Contributor III

If you are relatively new to OneStream, you should start with Application Import/Export of Metadata to extract and import Metadata.  Import/Export generates XML format flat files which you can edit in Notepad, or whatever tool you would like.

 

If you need to be making bigger changes (such as importing entire dimensions of members), then you might want to consider the OS Marketplace Excel tool that will generate XML from Excel sheets.  If you are handy in Excel, it is really easy to build your own Excel sheets to generate XML to match the metadata xml file format.

 

Be aware that when importing XML, you are adding/updating the metadata.  As in, you can't remove relationships or delete members via the out of box Import/Export.  This also means, it is pretty easy to import 10,000 members, but not quite so easy to get rid of those members if you imported them in error.

 

And finally, you can use the OneStream API via a Business Rule to surface anything you want from the dimension metadata, which you can output however you might like.  There are also OS API calls to create/update/delete metadata, which for adding basic members and relationships isn't too involved but can get to be a bit more work if you want to be updating some of the varying properties.  With the API. you can add/update/delete anything (within the same constraints as deleting via the normal user interface).

 

Somewhat along the lines of what you're asking, I'll describe an example of what is possible.  I have a OS Dashboard report that compares all the metadata (members and their properties along with relationships and all their properties) between two OS apps.  The report outputs what differs right down to a member or relationship property level.  if the two apps aren't in the same environment, I have a Data Management package that exports/imports all the metadata via a csv to a custom staging table so I can compare a remote app to a local app.  I went through Data Management package route rather then the out of boxed Import/Export via XML route so that the metadata can be brought over in a fully automated manner.  All this is pretty handy to figure out what has changed between Dev & Prod let's say.  All of this was done using the OS API.  What I am trying to get at is that when it comes to OS Metadata, you can do just about anything you want.

Rhankey, Thanks for the detailed explanation. Even I am doing the similar excersise where i have to compare the UD1 dimension (Customer) with the mapping in SQL and change mapping as per the SQL.  Recenlty we have downloaded the Mindstream Meta Data Manager and tried to alter existing mapping in the OneStream. However, It is ending up in adding new relationships.  For instance I have existing cutomer C1 in Washington and i want to move it to New york. The OneStream is adding the customer C1 in bot Washington as well as New york.

I hope OneStram API call would help me here. Could you please share bussiness rule (by hiding any sensitive information) ? 

 

 

 

 

 

 

 

 

rhankey
New Contributor III

As I mentioned in my prior response, when importing members or relationships, OS adds or modifies.  It does not delete.  You are adding a new relationship.  You have to take care of removing the old relationship.  You can do so manually, or you can use the API to do so.  I have been using a utility I built a number of years ago that will delete orphaned members.  I manually "remove relationship" of the member(s) I no longer want, then run my utility to clear them out of orphans without risking the live hierarchies.  Remove relationships allows you to select parent or child members and select multiple members at once, so in a couple key clicks I can remove thousands of members, unlike delete member which is one base member at a time.