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🙂)?

      • ckattookaran's avatar
        ckattookaran
        VIP
        2 days ago

        Since a lot of people are telling you not to do it, doesn't that mean something? :) Just because it uses SQL Server behind the scenes, it doesn't mean that it can be a warehouse. Yes, there are ways to do it. However, you need to remember that the more data you add to the server, the higher SQL server tier you will have to go to if you are on the cloud.

        Quick views and Cube views are cube-only options, so the items in relational views won't show up there unless you come up with a relational blend, but that won't work if you want to report on transactions. A dynamic cube is an option. However, it is still bound by all the cube constructs, so you won't get much leverage there. Keep in mind transactions are not cube-based. Why don't you add the database as an external source and use dashboards to show the detailed information when needed, as a linked dashboard in CubeViews?