Slow cubeviews for prior year balances
We use the REST api to retrieve balances from our accounting consolidations in onestream.
I've noticed that for the current year, these account balances can be retrieved quickly. But for prior ...
Forum Discussion
Hi, that can have many different reasons.
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)?
I'm forwarding the question to the accountants who manage this environment. I'm a software developer and what you describe isn't totally clear.
Recap:
Whatever the problem with balances, it causes data to be slower to retrieve in prior years. It is not a linear deterioration in performance. It is almost exponential. IE. 2023 is 1.5x slower than 2024. 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. 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. Is that true of the "workflows"? What dynamic work is it doing, while retrieving balances? Is there any option to get the balances *without* doing any unnecessary/additional work?
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.
Hi Henning
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? I'd love to see that in writing. 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.
Would the use of _history tables explain why 2022 is so much slower than 2023? (ie. two years ago vs one year ago).
PS. Here is where we see that workflows are closed (by fiscal week).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)
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:
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.