Instant

Everyone likes fast except when it comes to slow cooking or taking a nap or when pursuing a hobby or simply enjoying oneself. Of course, instant is even better than fast. No, not instant coffee (I can only drink it while camping – I think I somehow enjoy straining out bits of dirt from the tin mug), nor instant pot noodles (a staple of my university life, never to be repeated), but instead instant aggregations. I’m a busy geek; you are too. And I want it all. Now. Instantly.

Generally, in OneStream that’s not an issue as most dimensions are dynamically aggregated with the exception – till now – of Entity. By that I mean: input data at an Account, Time, Custom Dimension (and the other required dimensions – see my previous post for an overview of them in all of their glory) intersection and a single Entity and the numbers at an aggregate level are instantly available across all of those dimensions. That’s lovely in the non-ironic sense. However, that Entity dimension, the spine of every OneStream app, is a stored dimension, i.e. the upper level members must have materialized aggregate views. To get those aggregated – I am going to use that word instead of consolidations as this is aimed at planning and budgeting – requires a consolidation (yes, consolidation but for the purposes of this blog post I just mean, usually, rolling up the numbers in Entity) to occur.

Kick off a consolidation (again that term) via a right click in a Cube View or via the process cube button on every guided workflow (doable, but not really my preference) or a Data Management job (definitely my preference as it is easily automated, can be driven from a dashboard button, and it just “feels” right) and OneStream whirs and comes up with those aggregated values. Before OneStream’s beloved competitors and potential customers lose their collective marbles over this statement, just like all cube based products, performance is dependent on a slew of factors, some of which are: data volume, number of Entity members, number of other dimensions, how many subtotals and how deep they are, hardware profile, design, etc., etc., etc. so don’t think slow. That consolidation can be quite fast. But still, it isn’t instant. While fast is great, like everyone else you’re busy and who wants to wait? Not me. Not you either, I’ll reckon.

GetEntityAggregationDataCell

I’m happy to relate that there is now a way to get round all of that and it is ridiculously OMG easy. All it takes is one simple line of code. That’s it. Really. Here’s the relevant bit in the Design and Reference guide:

Yes, yes, yes, it isn’t a truly full financial consolidation but for us in planning-land, that likely isn’t a consideration. There’s aggregation. There’s fx. There’s instant. What’s not to like?

How to do it? Even that is practically instant.

Here’s the API call that is delivered in the MarketPlace solution called Snippet Editor, just copy and paste:

Ugly? Not really when you understand what the properties are and which ones you have to use. It’s all pretty brilliant – really, it is – because the function allows you to decide:

• What member(s) you want to conisder for instant aggregation.
• If you want to use dynamically aggregated amounts if their calc status is set to okay, aka don’t agg on the fly if the same results already exist in a stored fashion.
• If there is an override fx rate.
• If that override fx rule applies to Revenue and Expense account types.
• If that override fx rule applies to Asset and Liability account types.

That’s it.

So what might that look like in A Very Basic Sample dynamic member formula? Like this:

api.Functions.GetEntityAggregationDataCell(“UD8#None”, True)

That’s it, redux. So simple it’s as near as damn it to instant.

Let’s take a look at this for real.

Here’s the formula in all its succinct glory:

Note the Return method to, uh, return the value to the grid.

Take that formula and plop it into a UD8 dimension named, “DataView” and within that a member called, “DynamicAgg”:

And that really is that.

The proof of the pudding is in the eating

Let’s try it out.

Type in some lovely numbers:

Send the data to OneStream:

Et voilà!

Just because you’re paranoid doesn’t mean they’re not out to get you

I hate to be like Marvin the Robot aka Debbie Downer, but while dynamic aggregation is awesome and all, what happens when a true consolidation process is run and the base data changes? What I mean by that is: if a stored consolidation is run and Pennsylvania’s number is changed from 3 to 7?

Let’s create and run a Data Management consolidation job to do just that:

We now have matched results:

Everything is ship-shape and Bristol fashion as the numbers match.

Ah, but if PA is changed:

Submitted and retrieved:

Ah, bugger. No value is far, far, far better than a wrong value. Talk about your data quality issues if a planner pulls None instead of DynamicAgg.

What to do?

The outlook is not decidedly blue as there are several options that range from a doddle to not half-bad to write-code-and-then-punish-the-guilty pour encourage les autres. Let’s review.

Training

While I have faith in my fellow man, long and bitter experience has suggested that given an opportunity to blithely and completely ignore clear, simple, and explicit instructions, your average human will snatch at said chance with blinding alacrity. By that I mean, if you tell your planners to, “For the love of all that is good in this world, submit to None and retrieve from DynamicAgg” you will be as lonely in your pursuit as an empty bottle of beer at a summer picnic.

You could try to build cubeviews/dashboards/whatever that drives results to the DynamicAgg member but you’d come a cropper when it comes to ad hoc Quick Views in Excel. Bugger.

An ounce of prevention

This is as simple as never, ever, ever, ever giving anyone the ability to perform a consolidation. And this is as simple (to repeat a theme) as making sure the functionality isn’t available in a Cube View:

Only administrators would have access to a Data Management job. Simply don’t create a Consolidation step and it simply can’t happen.

And that pound of cure

Simple? It is simply possible that some well-meaning developer – who could never be Yr. Obt. Svt. – might go off and “helpfully” run a consolidation in his simple-minded way. Oops.

After applying a pound of remediative and presumably incredibly painful correction to whoever may have run a consolidation, it’s time to write a single line of code within a Data Management Clear Job.  Note that unlike some other tools, OneStream does not require code (well, if you consider a 60 character member filter built dynamically via mouse clicks within Excel code then yeah, I suppose code but c’mon) to perform comprehensive data clears.

NB – I could and will in a future installment in A n00b’s Journey show how to write this in a more flexible manner in a Custom Calculate Business Rule but for the time being, we can get away with (and may never need more) doing this the simple way.

The problem

Level 0 data isn’t the issue – the whole point behind this exercise is that upper level Entity data shouldn’t incorrectly reflect the totals of the base data. It’s that upper level stored Entity data that needs to be removed.

Selecting level 1, 2, 3, ad infinitum members

OneStream allows member selection through Member Filters. Think of them as simple (well, I’ve seen some complex ones but that’s beyond my ken/the scope of this post) metadata queries that return a member set. It’s in all parts of the developer UI as well as Quick Views within Excel. The latter is a boon because it allows quick prototyping.

Here’s what I prototyped in Excel:

Success! Boil in bag!

With a parent-only member selection snippet in hand, I can use that in a Data Management Clear Data step to remove that erroneous data.

To review, here’s the consolidated data we don’t want:

Run the ClearConsolidatedData step:

Gone Daddy Gone, your love is gone

Use cases

Planning and budgeting applications don’t typically (Ever? Surely there must be some apps that do, but I daresay they’re more akin to a consolidations app with a Budget scenario tacked on.) worry about the qualifications and limitations spelt out in the Design and Reference Guide’s re the comprehensiveness of GetEntityAggregationDataCell when it comes to true consolidations.

Given that GetEntityAggregationDataCell provides currency conversion and aggregated results instantly, I cannot imagine why it wouldn’t be the first choice for all planning and budgeting OneStream projects.

There. I said it. Yup, a n00b is laying down the law and we all know that ends in tears. While I’m surely wrong for edge use cases, I cannot see why in general GetEntityAggregationDataCell isn’t that instant solution to a timely problem. I calmly await my doom at the hands of outraged and indignant parties who are free to leave messages of hate and scorn via this blog’s comment function.

But I would consolidate/aggregate anything Actual or closed or nevah gonna change

A note: If you care, really care, about having stored aggregate values in Entity and yes, I concede there must be reasons to have both a stored consolidated result as well as a dynamic one, have at it but be sure to have a mitigating strategy of managing data quality between the stored and reporting member either through data clears or reconsolidations.

If the numbers don’t change, and you can run a consolidate/aggregation, why not? The function has that ability to use stored members when they’re already aggregated. Use it and make those instant aggregations that much more instant.

Be seeing you in almost an instant.