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-...
Forum Discussion
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.
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🙂)?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:
- Is there a better method of reporting on the transactions that doesn't require importing them to OneStream
- you could instead display an external report using drill through functionality
- 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)
- Consider your storage limits (typically these are pretty large these days)
- 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.
Thanks Daniel, that's very useful to know. Will read more into those solutions
- Is there a better method of reporting on the transactions that doesn't require importing them to OneStream
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?
