Forum Discussion

VishalPai's avatar
VishalPai
Contributor
2 years ago

External Database Connection for Dashboard - Data Volume Restriction

Hi OSAdmin,

We have an external database connection setup with OneStream and the external database has both detailed(transactional) and Summary data table/views

We have a requirement from Business to get the detailed data dump within OneStream as a report which the users can download for further analysis and restrict the user based on their OneStream Workflow/Entity access 

To achieve and restrict the detailed (transactional) data duplication at OneStream end, we have created a data adaptor that fetches the detailed data based on Parameter selected by user from External Database and the data adaptor was used in to dashboard through grid view, pivot grid and BI Viewer components, for low volume retrieval are working as expected, as soon as the volume goes beyond 95K records we get "Stream was too Long" error.

Any insights from Admin and OS Community would be helpful

 

Thanks

Vishal Pai

 

 

Dashboards
  • ChrisLoran's avatar
    ChrisLoran
    Valued Contributor
    2 years ago

    We don't normally recommend using OneStream as a repository for downloading huge amounts of data to the clients.
    The grid view components aren't really optimized for large amounts of rows.
    In this case, I would consider using the embedded spreadsheet and a TableView. There should be an example BR on the Golfstream application. This way you get a dump of data directly into a native spreadsheet layout that you can instantly save as an offline Excel workbook.   I would avoid exporting dashboard components to CSV because you get date and number formatting issues, especially where user's MSOffice regional settings can vary.  

  • JackLacava's avatar
    JackLacava
    Honored Contributor
    2 years ago

    In addition to what Chris suggests, I would add to also consider BiBlend. BiBlend allows you to fetch data from external sources, and aggregate it according to OneStream hierarchies (or specific branches of them), saving it into an external database. That typically results in a smaller amount of records, which are then usable from reports. It's typically used to aggregate transactional data using OS structures.

  • ckattookaran's avatar
    ckattookaran
    VIP
    2 years ago

    If they are looking for a data dump, why even show it in OneStream? Why not just create a dashboard button and make a rule that writes it into an EXCEL file and downloads the file with the same button?

    The problem with a data adapter approach is it will bring all the data at once and then show it. You can use a Large pivot grid which will support paging. However, since the purpose is a data dump, this approach is not practical.

    Even Table View is not going to work since you have a large number of records to deal with. I've seen clients always asking for this, and I've always questioned why you need it. In most cases, the user runs a pivot grid and performs a sum. Which is what OneStream is doing, so why? Is it that you don't trust the system?