Slow cubeviews for prior year balances

Question

We use the REST api to retrieve balances from our accounting consolidations in onestream.
&nbsp;
I've noticed that for the current year, these account balances can be retrieved quickly. But for prior years, it takes a lot longer to retrieve them.&nbsp; For example, I can run cubeviews against the 2024 data and get ten branches in about 80 seconds.&nbsp; But if I run the exact same cubeviews against 2023 or 2022, then it gets increasingly slower (160 seconds for the same ten branches in 2022).
Is there some reason on principal why the balance data would take so much longer to retrieve in past fiscal years?&nbsp; Is the cubeview doing some sort of dynamic calculation to retrieve balances?&nbsp; (maybe it is incrementally subtracting activity from balances in 2024, and that dynamic calculation takes longer as we report further back in time)?
We are running OS&nbsp;8.2.2.16127.
The difference in execution time seems specific to G/L balances.&nbsp; For G/L activity (the movement of the accounts), the performance is similar when running reports on the current or the prior years.&nbsp;

Henning · Answer

Hi, that can have many different reasons.&nbsp;
My first high-level gut feel is that the workflows may have been closed in prior years, which could explain the difference in data retrieval performance.
Are the workflows closed (see screenshot)?

&nbsp;

dbeavon · Answer

I'm forwarding the question to the accountants who manage this environment.&nbsp; I'm a software developer and what you describe isn't totally clear.
&nbsp;
Recap:
Whatever the problem with balances, it causes data to be slower to retrieve in prior years.&nbsp; It is not a linear deterioration in performance.&nbsp; It is almost exponential.&nbsp; IE. 2023 is 1.5x slower than 2024.&nbsp; And 2022 is 1.5 x slower than 2023.
Most storage formats that I've ever worked with would NOT cause us to pay such a substantial penalty in performance for a prior year.&nbsp; Especially given that OS is a CPM tool, it should be more natural to retrieve a historical trend of our balance sheet accounts, without worrying about the differences in performance by year.
In my experience, the only thing that might incur an exponential performance impact is some sort of dynamic calculation that has MORE work to do for prior years than it does in the current year.&nbsp; Is that true of the "workflows"?&nbsp; What dynamic work is it doing, while retrieving balances?&nbsp; Is there any option to get the balances *without* doing any unnecessary/additional work?
&nbsp;

Henning · Answer

The reason why I am asking about Closed Workflows is that this moves information to _history tables which are not queried in memory like non-closed workflow related data. So if all workflows have been closed, that would be the easiest explanation for what you observed.
Other than that, it can be many things and one would need to be looking at how the data is queried in the first place and then dissect the different pieces.

dbeavon · Answer

Hi&nbsp;Henning&nbsp;Yes, they say they are closing workflows.In the online/hosted version of onestream, is there a way to monitor performance details, in order to see that _history tables are used?
Are there reference materials which would confirm the fact that prior year data is expected to be consistently slower than current year data?&nbsp; I'd love to see that in writing.&nbsp; I don't think customers want year-over-year comparisons to take a big performance hit, and is probably just as popular as budget-vs-actual.
&nbsp;
Would the use of _history tables explain why 2022 is so much slower than 2023?&nbsp; (ie. two years ago vs one year ago).PS. Here is where we see that workflows are closed (by fiscal week).

&nbsp;
&nbsp;
&nbsp;
&nbsp;

Henning · Answer

Hi, prior year data is not expected to be slower in Onestream. Only data from closed workflows may feature longer retrieval times. The data itself is not getting moved to history tables, but some workflow related information (which is queried too, hence the possible impact).
Here is the documentation:
Using Workflow Profiles (onestream.com)
&nbsp;
However, the screenshot you provided only shows a locked workflow, not a closed one. There is a huge difference between a locked, certified and a closed workflow. A closed workflow would look like this:

&nbsp;
So back to square one. As I said, it can have many different reasons which require a deeper investigation. Maybe the customer can open a ticket and ask support to take a look at the possible issue.&nbsp;

Forum Discussion

Slow cubeviews for prior year balances

Related Content

Performance issue with Cubeview

Cubeview Error

Dynamic Prior

CubeView audit report

CubeView Row Collapse