Forum Discussion

Grace_Johnson's avatar
Grace_Johnson
New Contributor
2 months ago

Data Dump - Too large for Cube View

Hi All,

I was hoping to brainstorm a solution to a problem. Our auditors have requested a mapping of base-level data to the aggregated, financial statement line item data that appears in our financial statements. This is fairly easily done for the Balance Sheet, which only depends on the Account and Entity members to generate, but for the income statement we simply have too many data intersections (Account, Entity, Cost Center, Line of Business - generates about 15,000 rows of data) for a cube view to be able to process the request, even with suppression enabled.

Currently, to work around the problem I've set up a book that loops through each entity (approx. 200) and exports an excel with each entity's income statement as a separate tab. I then have an excel macro that copies each tab into a single aggregated file that we can then send to the auditors. However, I was wondering if anyone has figured out a way to have OneStream do the work of aggregating all the tabs together so we don't have to use an excel macro at all, which sometimes has caused errors during the copy+paste process. Or, if there's another solution entirely to this problem-- I'm certain I'm not the only person with requests like this from their auditors!

Books
Cube View
  • db_pdx's avatar
    db_pdx
    Valued Contributor
    2 months ago

    Your auditor sounds...bad. I'd push back on their request and have them narrow down what exactly they are hoping to test.

    To answer your question an OneStream Report Book with a Loop component, your entity list, and a standard report can replace your report+macro process.

    Reference Materials:

    Henning's suggestion on FDX's is also solid. If that's too granular, then a more basic Data management export might suffice as well.

    • Grace_Johnson's avatar
      Grace_Johnson
      New Contributor
      2 months ago

      Thanks for the feedback. I currently have a report book set up with loops, but each entity gets exported as a tab, so the book ends up with 200+ tabs that don't clearly show the aggregated picture. It's not clear from the reference materials if the tabs can then combined into one single tab; is that functionality that's available?

      • db_pdx's avatar
        db_pdx
        Valued Contributor
        2 months ago

        I might be missing something. As part of your Member Filter for the Loop, the aggregate  would be E#YourTopEntity? Such as:

        E#YourTopEntity.Member.Base
  • Henning's avatar
    Henning
    Valued Contributor II
    2 months ago

    Hi, not being an auditor myself, I think this request is a bit odd. Am I understanding this correctly, they want all base level data and how it is consolidating to the top (translation, share calculation, elimination) through every entity (base and parent) in a single cube view?

    This is what CPM software such as Onestream is for. To process this and make this auditable and fully transparent in the system, not in a single Excel report IMHO.

    Possible that this is a standard request and I just never heard about it.

     

    Having said that, 15k rows of code seems still managable. Have you tried using FDX rules for that? You can also define a cube view and extract this via FDX.

    Here is the documentation (note that they can be used not only for BI Blend):

    Fdx Specialty Connector BRAPI’s (onestream.com)

    There are also examples in this forum, such as this one:

    Solved: FDX Cube View Rule - OneStream Community (onestreamsoftware.com)

    If you search for FDX or Cube View FDX here, you will find more.

     

    • Grace_Johnson's avatar
      Grace_Johnson
      New Contributor
      2 months ago

      After trying different solutions, I think the problem is less the volume of data and more that we have 3 different "base" cubes that then aggregate into our Company-wide cube; essentially data from 3 ERPs gets loaded into a separate cube, and then those 3 cubes are merged into our Company cube. When I run the TBs in the base cubes they populate quickly, but that still leaves the problem of not having visibility into the aggregate cube.

      I'll likely make 3 base TBs then have a book that runs each base TB as a tab that can then be aggregated; at least this way we'll only have 3 files to copy+paste, rather than 200+.

      • db_pdx's avatar
        db_pdx
        Valued Contributor
        2 months ago

        This is our exact setup, 3 base cubes that correspond to different divisions with 1 top level reporting cube. We can simply do A#IncomeStatement.Tree (or .Base) with any entity from the hierarchy. Edit: we have different base accounts for the divisions, and through extensibility the Base member expansion will resolve to the appropriate base accounts based on the entity selected.

        Can you confirm that extensibility was implemented for cube, entity, and account?