Does it make sense to get OneStream Metadata (eg Entity, Cost Center etc) be updated automatically from upstream source system such as SAP?
I would have thought it would be far too risky but just want to check if anyone out there had ever come across such requirement and what approach you deploy so that metadata between source ERP and OneStream are in sync?
Hi NS2025 , the only dimension I would stay away from automating is the Entity dimension. Entities drive Workflows and Security. If your application has Intercompany and FX translation requirements, that adds to the complexity of auto-adding new Entity members. T_Kress beat me to it, the ACM is your best bet. I'd just recommend re-thinking how you want to manage the Entity dimension automation.
In our company we have fully automated Entity update from an external MAster ORG tool/db. But all logic to identify change is built outside OneStream. (or, it is possible to bult it in new tables within Onestream as well).
The principle is:
make 2 snapshots of ORG : one from Onestream previous period, another - from ORG master - latest period
compare snapshots to find delta - NEW, Changes (various fields), MOVEMENTS (in hierarchy)
populate changes into own tables
create xml files based on tables
put files on sftp
trigger powershell script on Azure - log into Onestream and initiate Data Management job "get files from sftp". Business rule with connection to SFTP does already exist in Onestreram (extensibility rule)
This is the traditional approach, but it should be mentioned that, from version 8.5 onwards and only for Account/Flow/UDs, one can now use Dynamic Dimensions to automatically build and refresh hierarchies with just a sprinkle of code.
Full automation of the entity dimension also depends in my view on the type of use.
Planning, Management reporting,... ? I guess I have less issues with that (if that aligns with the security and workflow).
Statutory reporting? That is audited and published data that may be impacted, so I'd be extremely careful with that and - personally - only do it with an auditable process in place such as the one the "Application Control Manager" solution Teresa mentioned offers.
(1) What is ORG that you are referring to? Do you mean entity structure in your environment?
(2) Are you using any text variables in your entity dimension that drives consol, workflow, calc or reporting behaviour? If so, how do you ensure that correct text variables are deployed in OneStream entity structure?
(1) Yes, it is our own ORganisation (Entity) master tool. But it could be any other system - tables within SAP or other.. The thing is that you need tables - in your case in SAP - that contain all information about Entity structure. So, after greenflag from source system - Entity tables with status-quo /snapshot are copied to some db where you also have Entity snapshot from Onestream for previous period. We do export Entity dimension from Onestream as XML, put it on SFTP. We use IICS - Informatica system for ETL - unpack XML, put content to tables. Then runs several SQLs to compare tables to find delta.
So, a lot of processes are done by Informatica. But you can investigate Power platform - Power Automate - (parse XML; SQL connection). Could happen that pack / unpack XML could be doable by PowerAutomate. PowerAutomate has SQL, SFTP connections - so full automation probably could be achieved by using this product.
Here are steps within DataManagement sequence that is being triggered from logic app on Azure (advanced PowerAutomate) . As you can see, we create XML files with security groups, WFs, 2 Entity dimensions, update Transformation rules
(2) in order to keep values from text attributes, you need to have 2 tables - one with latest snapshot, another - with historical values.
Hi T_Kress & MikeG - thanks for your responses - much appreciated. Very helpful.
What the user wants is straight-through automated processing of metadata without any human invervention from the source system (SAP) into OneStream. I just wanted to confirm that although it is doable (for e.g. using APIs) - it is something thta one shouldn't do given the complexity of the application. ACM makes lot of sense - I will explore that.
