Skip to main content
T
Tahir2061
Newcomer
December 22, 2021
Solved

Exporting Data automatically to outside of OneStream

  • December 22, 2021
  • 5 replies
  • 0 views

HI

I am trying to automate a data extract process to send data from OneStream to another system eg Anaplan. I have a data Mgmt job to Export the data to a CSV on the file share. I now want to move that CSV file from the file share to say my desktop for example (or any other file storage place). Has anyone done any routine like that? If so is there any BR in OneStream that can be used for that. I havent done this before but sure others may have come across this so just wondered what the best approach would be.

Thanks!
Tahir

    Best answer by ChristianW

    If you are not afraid of Business rules, you should try to use the Fdx functions within BRAPI, they are creating a datatable object and they are retrieving the data very fast, they are all starting with

    BRApi.Import.Data.Fdx<Something>

     

    ChristianW_0-1642591590545.png

     

    5 replies

    S
    scottr
    Newcomer
    December 23, 2021

    Are you on prem or in the cloud?

    We are cloud for OneStream and need many different applications to consume the OneStream data.  I set up automation to export to SQL Server so that we don't have to deal with the hassles of files.  If you don't have an external SQLconnection, one would need to be set up.  Next create a business rule with a query to get the data you want and write it to the SQL table.  Set up a Data management step and sequence.  Finally, create a OneStream Scheduled task to run the data management sequence for the daily/weekly/monthly schedule you want the process to run.

    T
    Tahir2061Author
    Newcomer
    January 13, 2022

    HI Scott

    Many thanks for replying! We are on the Cloud. We have a connection to SQL for my Bi Blend job which spits out data to a View table. 

    One question - what do you use the DM for in this process?

    Thanks

    Tahir

    S
    scottr
    Newcomer
    January 13, 2022

    The data management sequence is needed in order to use the task scheduler to run the export each day/week/month.  The DM sequence calls a DM step which runs a business rule.  the business rule contains the logic to pull the data out of the cubes/BIBlend and replicate the data out of onestream's database into our internal database server.

    G
    Gidon_Albert
    Contributor
    January 3, 2022

    scottr's approach is the way to go. Exporting base level data to a file and then pushing it into an external SQL database is very fast. The only down side of this approach is that exporting parent members can really put a damper on performance. To avoid this, we developed a two step process: 

    1. Export the parent child relationships for all the dimensions into external SQL tables
    2. Export base level data from the cube to an external table

    This method is much faster than exporting all data out of OneStream but puts the burden of doing the aggregation math on the external SQL table.

     

    T
    Tahir2061Author
    Newcomer
    January 13, 2022

    HI Gidon

    Thanks for replying - that sounds like a good approach will have a tinker!

    Thanks!

    Tahir

    C
    OneStream Employee
    ChristianWOneStream EmployeeAnswer
    OneStream Employee
    January 19, 2022

    If you are not afraid of Business rules, you should try to use the Fdx functions within BRAPI, they are creating a datatable object and they are retrieving the data very fast, they are all starting with

    BRApi.Import.Data.Fdx<Something>

     

    ChristianW_0-1642591590545.png

     

      T
      Tahir2061Author
      Newcomer
      January 19, 2022

      Hi,

      Many thanks for the suggestions will have look into that!

      Thanks

      Tahir

      K
      Krishna
      Expert
      April 26, 2022

      You can extract the data through DM Base level or Consolidated Level based on POV and you can read the each row and either you can use RESTAPI to send it to the Target System. Yo have to pass the CSV output to JSON body so it can import into target system and another cool thing is you can also export to datatables. 

       

       

      K
      Krishna
      Expert
      July 14, 2022

      You can do this multiple ways.

      1. Export the DM Job to csv to an external storage like AWS or Azure Storage using a Extended BR.

      2. Export the DM job to Data Table and use the RESTAPI from of your Target (ANAPLAN) to call directly from OS Business Extended BR.

      3. It can be exported to Database As well.

      Terms & ConditionsAccessibility statement