How can we copy just data from an application to another?
We have an on-premise setup and would like to copy data from prod environment to lower environment application.
I know we can do simply do entire DB copy for that application, but it'll replace everything (like app artefacts).
Is there a way we can just copy underlying data of an application? I noticed some data tables (BinaryData20xx, DataRecord20xx), but not sure if simply copying them will work.
Through the UI you could do a data extract sequence to get the data out and reload it. But the best way is what you mentioned and probably have already done, just do a full Application backup with Data. Since you're on-prem and the year is 2023 and space is probably not an issue as it relates to cost, just do the full application backup and date-time stamp the application copy. Say Production Backup 2023June. Eventually you'll have a few date-time stamped back ups and using that backup naming methodology, you can delete old back ups and just keep the latest 2 or 3.
It is simpler to do that. Then, migrate application artifacts as needed such as your specific BR's, Reports, other code to your backup application. That will be easier than reloading data.
OneStream probably has a way to do the data (just the data) migration/backup using SQL Server DB magic, if so please post that here!
To answer the other question regarding BinaryData20xx, DataRecord20xx, these refer to member IDs which will likely be different in the target application, so if you simply replicate those tables then it will create a massive integrity mess because the memberIDs in the [Member] table will be completely out of sync. (Not to mention this selective table copying is not supported)
If you can't copy the database because you want to preserve artifacts in the target application, then that could also mean that the data won't fit into the target application, because different artifacts may mean different metadata, rules, constraints etc.
Or is it only dashboard items or extender rules that you mostly want to preserve? In that case it would be easier to extract those dashboards/rules to an XML file, then do a database copy (and maybe shrink it while you are at it), and then re-load the artefacts over the top of it using XML.
If the target application is structurally different (i.e. metadata) then there is no way around the need to map/transform the data extracted from the source application ; but that isn't a OneStream-specific limitation.