Forum Discussion

IL's avatar
IL
New Contributor II
7 days ago

Can ERP transactional data be loaded into OS?

Hi, my company's OS data integration with the ERP system has been set-up to load monthly TB's into OS and for transactional tables to sit outside of OS but with the ability for OS to query and drill-back to view transactions.

The transactional drill-back function is limited in that a drill-down first needs to be performed to get to the detailed dimensional intersections at the lowest UD level before a drill-back to view transactions (at that precise lowest level set of dimensional intersections) can be run. It is therefore easier to use a stand-alone BI viewer outside of OS to view transactions.

Am wondering - is it possible to load ERP transactional tables into OS (this may contain millions of rows spanning multiple years) and for OS to then display transactional level detail across higher-level roll-ups (e.g. viewing transactions across a cost centre parent rather than only at an individual cost centre level), or even display transactional detail in quick views, essentially making OS have similar functionality to a BI viewer?

Data Integration
Data Management
Data Sources
Import

8 Replies

  • sameburn's avatar
    sameburn
    Contributor III
    7 days ago

    I agree with DanielWillis​

    There are so many ways you can report on this external data from inside OneStream that there is absolutely no need to duplicate your entire dataset inside OneStream. Just because something is possible doesn't mean it's necessarily a good idea. 

    IL​ have you consulted with a Partner (if you are a Customer) or Partner Enablement (if you are a partner) for some design advice here?

    Sam

    • IL's avatar
      IL
      New Contributor II
      6 days ago

      Thanks Sam, yes, am currently consulting with a partner. Aim is to try and get as much detailed analysis done within OS as possible, rather than using OS just for high-level reporting (because it's slow and difficult to get to the transactional detail), and then reverting to a datawarehouse table of transactions held outside of OS and producing analysis/reports via Excel instead.

      • TGG_Alex's avatar
        TGG_Alex
        Contributor
        5 days ago

        IL​ - if it is for informational purposes only (e.g., not used for other derived computations or functions like transaction matching, etc.), it may be better to launch a direct query from within OneStream to the narrowed scope of transactions at source if your source ERP allows for that type of query (aka Option 1.1 from DanielWillis​ )

        Cheers,

        A.

  • tschilling's avatar
    tschilling
    New Contributor III
    7 days ago

    Short answer is Yes, and there are multiple options to achieve this.  Data can be loaded into the OneStream staging layer at the detailed level and you can access that detail directly in OneStream.  You could also utilize BI Blend or Custom Tables in OneStream to accomplish this.

    • IL's avatar
      IL
      New Contributor II
      7 days ago

      Thanks. I have heard of other companies using a BI viewer to look at transactions but would you know if it is common to upload transactions into OS instead (given there could be millions of rows) and reasons not to do this? 
      Would you also happen to know if it is possible to report on transactions via quick views or if transactions would just sit in a table that needs to be filtered on and exported into Excel to see transactions across multiple dimensions at the same time (sorry, lots of questions🙂)?

      • DanielWillis's avatar
        DanielWillis
        Valued Contributor
        7 days ago

        Hi IL,

        Many companies import transactions for transaction matching so it is definitely not an uncommon thing to do.

        Reasons not to do it for reporting that I can think of are:

        1. Is there a better method of reporting on the transactions that doesn't require importing them to OneStream
          1. you could instead display an external report using drill through functionality 
          2. Is it possible/practical to report on the transactions on a OS page with them stored externally (usually amount of transactions and time to run report dictate this)
        2. Consider your storage limits (typically these are pretty large these days)
        3. Can you can get all the transactions into the environment at the required frequency

        In regards to displaying table data to users, Version 9 will be your best friend here. You can now report on data in tables as if they were cubes using Dynamic Cube Services. There is some good content on v9 and DCS in Navigator. Also some links below.

        Dynamic Cube Services - an Introduction | OneStream Community

        https://documentation.onestream.com/1375907/Content/Design%20and%20Reference/Financial%20Model%20Guides/Dynamic-Cube-Services/Overview.html

        You can set up drill through to data in a table prior to v9 and you can also show table data in the Excel add-in using Table Views.