I have a 3 step Data Integration process which I need to automate,
1. Extract OS data and push it to DB tables - DM Sequence
2. Execute list of DB Stored Procedures
3. Import data from DB to OS - DM Sequence
I started with an Extender BR calling them using BRAPI.Utilities, but the execution of Stored Procedures itself will take 6-8 hours and the DM Sequence time out before even SP completes.
Appreciate suggestions to automate this process.
I'm not sure anyone can help with this amount of information. It sounds like you've already automated it but your stored procedures are slow? Without any understanding of what they're doing I'd simply say step 2 needs to be redeveloped so it runs faster or an alternative solution be sought, e.g. not exporting to the database.
No this is not automated yet. It's a new development of driver-based Revenue planning application. Currently the 3 steps above are run manually one after the other after successful completion of each step.
Stored Procedures would take 6 hours minimum to complete and there is no scope for improvement at this point, Because those procedures run off of some machine learning models to produce 36 months forecast factors.
I am currently thinking through design options and I know both DM sequences and SPs can be called from Extender BR and that can be attached to a DM step and Sequence. However, the DM sequence timeout after 2 hours. Since my SPs itself take 6 hours to complete this may not be an option for me.
Run parameters for the Stored Procedures are entered in a Cubeview by Users. So I need to extract it from OS and push it to DB tables before the SPs kickoff (which is my Step 1).
ah ok. So your issue was that you've got a long running process and you're OK with it, its just that OneStream is timing out before it finishes.
I had a client that had long running jobs and we were able to extend the timeout by creating a support ticket. That might get you over the line for now.
