Cameron Lackpour, Author at Black Diamond | OneStream Solutions

Keeping Your Balance With Unbalanced Math

Cameron Lackpour — Tue, 15 Mar 2022 17:06:00 +0000

The Riddle of the Docs

Gentle Reader, if you – as your author most definitely was when he first researched it – are somewhat puzzled when reading about Unbalanced Math, this is post is for you as it is a powerful method that illustrates OneStream’s flexibility and utility, prevents pretty dramatic error messages, and stops you from writing ~~stupendously ill thought out~~ nonoptimal solutions to simple problems. Seriously, if you write Finance Business Rules, you need to understand this.

Oh sure, I know all of this now, but as I first read through the documentation, I simply couldn’t figure it out. Why is it broken out separately from plain old api.Data.Calculate? “Unbalanced” is such an odd name. How do I use it? In short: what on earth are they going on about in the in the Design and Reference Guide and why should I care?

I’m sad to relate that as I read (and re-read) on, I became more confused. In particular, the references to Data Buffers just seemed…odd. I had to – as with practically everything else that involves OneStream and Yr. Obt. Svt. – figure this out by actually using it in a concrete functional use case of my own.

TL;DR, but you should

“Unbalanced” simply means that when a target member tuple in an api.Data.Calculate statement does not mirror the dimensions in the source member tuples, it is unbalanced in its dimensionality and when the tuples – which translate to Data Buffers – are unbalanced, a standard calculation will fail.

In Itsy Bitsy Words

A simple example is a centrally stored rate that is applied to multiple Entities and UD members. In pseudocode, it would look like:

Distribution = Sales * Distribution_Rate at No Geography at No Product

This looks simple enough. Sales is in multiple States (sorry, international readers) and Products. There is a single rate for the entire country by month. Multiplying Sales by that centrally stored expense rate for all States and Products should be a trifle.

Balanced Tuple Dimensions

A#Sales could be at O#Import and O#Forms, so O#BeforeAdj will be the Origin dimension member. These rate calculations only make sense at V#Periodic so that too will be explicitly set in the method. All calculations go to O#Forms. So far, so good.

First Pass, But Fails

That formula might look something like this:

Note that the U1#Total_Products.Base member filter will apply A#Distribution’s calculated results to every Product where A#Sales exists.

Also note that U1#No_Product is in A#Distribution_Rate’s tuple but not in the target A#Distribution’s tuple; this is no accident as the calculation must write to many Products using a rate stored at a single calculation-only driver Product.

Data Management

The super simple Data Management job cycles through just two States – enough for illustrative purposes and no more.

The data is equally simple as is the Excel proof of concept math:

Seriously, this is x = y * z. How hard could this be?

Harder Than Anticipated

No, OneStream doesn’t throw an error quite like that, but it’s almost as bad:

Ouch.

Despite the error message’s length, OneStream is pretty clear about the issue: A#Distribution_Rate has an explicit U1#No_Product member definition and A#Distribution does not; the member filter of U1#Total_Products.Base does not balance U1.

The error message states that either a specific target U1 member should be used or U1#All could apply the calculated results to, well, all U1 members.

Please Do Not Do This

U1#All will work in this very specific context:

Ta da:

Don’t, Just Don’t

There are warnings in the Design and Reference Guide to be very, very, very careful when using the All keyword because it can lead to data – perhaps quite a lot of data – being in places neither you nor anyone else might expect which is generally viewed as a Bad Thing. OneStream are not shy about pointing this out:

The author in your, um, author appreciates the “please do not do this” note which he suspects came from Product Support or Product Development or practically everyone who works for OneStream.

Having shown you the wrong way to do this, let’s try the right way: Unbalanced Math.

The Four Faces of Unbalanced Math

There are four unbalanced functions: AddUnbalanced, SubtractUnbalanced, DivideUnbalanced, and MultiplyUnbalanced, the last of which is the focus of this blog post. See the Design and Reference Guide for more detail on the first three.

I think of the functions as following (super roughly) this pattern:

x = y, z that is out of balance with x, the unbalanced bits of z that aren’t mentioned in x

In this use case, the x, y, and z as well as the missing bits must be surrounded by MultiplyUnbalanced and of course the whole thing is encapsulated within a api.Data.Calculate statement.

In All Its Glory

What does it take? Is it as complicated as I first thought?

Repeating U1#No_Product in that third parameter is all it takes. Easy-peasy.

NB — E#No_Geography isn’t out of balance because E#Pennsylvania and E#South_Carolina as defined in the Data Management Step are implicitly in the target Data Unit tuple.

I’ve modified A#Distribution_Rate to illustrate the impact:

That’s all there is to it. Also, this prevents OneStream’s documentation team (and the rest of that company) from a deep existential despair that ensues when #All is used. Win, win.

As Always, Easier Once Done

OneStream’s functionality can sometimes be difficult to suss out but with a bit of experimentation, it will give up its secrets. The reward is usually worth the struggle.

Rate calculations are common across all OneStream application. If you have not yet run across a requirement to perform Unbalanced Math, you will. It’s easy and powerful. Use it, and don’t use #All.

Be seeing you.

The post Keeping Your Balance With Unbalanced Math appeared first on Black Diamond | OneStream Solutions.

What’s in a (Workflow) Name

Cameron Lackpour — Wed, 09 Feb 2022 17:01:01 +0000

Many names, much confusion, and it’s all rather important

It has been your author’s observation that the glue that holds OneStream applications together – Workflow – is a victim of terminological inexactitude. No, not that odious euphemism, but instead the common usage of just one term – “Workflow Profile” – for the four (arguably five) different Workflow Profile types. When we OneStream practitioners use the same word to mean many things, we confuse ourselves, make mistakes, and generally make everyone who touches the application unhappy. Happy is more fun.

To avoid that state of Workflow-induced despair, we need a commonly agreed upon taxonomy and then we must use it. Happily, OneStream has created those Workflow types and definitions (they could not do otherwise) so the path to understanding then is to get all OneStream practitioners to comprehend and adhere to those taxonomical definitions. Working with a tool as sophisticated as Workflow requires terminological exactitude so that we all understand what on earth we’re talking about.

Four, just four

As noted, there are four main Workflow types: Cube Root Workflow Profile, Default Workflow Profile, Workflow Profile, and Workflow Child Profile.

How hard could it possibly be? Let’s find out.

A note

This post is not a comprehensive guide to Workflow. For that, see the OneStream Design and Reference Guide and its Workflow Guides section.

Cube Root Workflow Profile

The Cube Root Workflow Profile is defined at the Cube Level by Scenario Type via Scenario Suffixes. Think of using Scenario Suffixes at the Cube level as a sort of extended (OneStream uses the term “varying”) Workflow as it allows your application to segregate Workflow by Scenario Type.

In the above example, the Scenario Type Suffixes are Actual and Forecast. The values are arbitrary – they could be Potato or Happy or more likely the ones shown or Budget or LRP. Try to use something meaningful.

Missing Scenario Types

Scenario Types are good practice because they allow explicit assignments of an Entity to more than one Workflow Profile (more anon on this term). Even if there is no immediate need for them, applications have a way of growing and it’s best to have Scenario Types in place when they do. There’s no need to assign Suffixes to each Scenario Type, just one will do as a start and then expand as many times as needed.

Naming confusion

A note about Scenario Types – don’t conflate a Scenario named “Plan” with a Scenario Type of “Plan” as the only logical and functional link one you, Gentle Reader, should define in the tool. Although a “Plan” Scenario Type can certainly have a Suffix of “Plan” and be used in the “Plan” Scenario, it isn’t required. This sort of identical naming convention, while appealing on its face, breaks down if there is more than one Scenario that logically shares a Scenario Type which is often the case in planning applications. Whew.

As with everything OneStream, there are many ways to approach a requirement, none of which are exactly wrong but some of which are not quite as good as others. Your application’s needs will dictate what is best.

Using the example below of three Scenario Types (Actual, Forecast, and Plan) with two different Scenario Suffixes (Actual and Forecast), when a new Cube Root Profile is created, the two Scenario Types of Actual and Forecast appear; the Workflow Scenario Type Suffix defines the Workflow Cube Root Profiles, not the Scenario Types themselves.

Creating a Cube Root Workflow Profile

The naming convention is CubeName_ScenarioType.

Clicking on either choice will create the Cube Root Workflow Profile Name. For the purposes of this post, only Sample_Forecast will be used.

It’s easy to identify in the Workflow Profile editor hierarchy as it’s at the very tippy top and has a cube icon to the left of the name:

Default Workflow Profile

Once the Cube Root Workflow Profile is created, the Default Workflow Profile Sample_Forecast_Default appears automatically.

A Default Workflow Profile connects the Cube’s Entities and Workflow itself. All Entities are by default assigned to the Workflow – note that the Entity Assignment property sheet does not exist in the Default Workflow Profile.

Some of its salient characteristics are:

It is named CubeName_WorkflowSuffix_Default.
It joins Cube Entities to Workflow.
It cannot be deleted.
Only Administrators should be able to see it.

As with all Workflow Profiles, the Workflow Child Profiles of Import, Forms, and Adj appear below the Workflow Profile name.

A click on the Import Child Profile (this is just illustrative – don’t actually use the Default Workflow Profile) shows that two Scenario Types are available: Forecast and Plan. These are the Scenario Types that share the Workflow Suffix “Forecast”.

Scenarios with Scenario Types

The Plan Scenario has a Scenario Type of “Forecast”. (Remember what I said about the potential for confusion? Here it is.)

Scenario Types are linked to the Scenario Type property in the Scenario itself. This relationship defines the Scenarios in OnePlace. Whew, again.

All you really have to know is that if a Cube’s Workflow has defined Scenario Types, the Workflow is extended and a Scenario tagged with that Scenario Type is now part of that Workflow; only Scenarios with that Scenario Type will appear in OnePlace.

Whew, again and again.

Workflow Profile

Workflow Profile Types

There are three Workflow Profile Types: Review, Base Input, and Parent Input.

As this post is written by Mr. Planning, I’ll confidently state that Base Input is the overwhelmingly most used type in planning applications although of course Review and Parent Input are used as well; Consolidations applications are far more likely to use all three types As the purview of this post is not All Things Workflow but instead Workflow terminology, only Base Input will be examined.

Workflow Child Profile

We have now almost reached the end of our Workflow taxonomical journey.

Note that by default, the Workflow Child Profiles of Import, Forms, and Adj have been automatically created.

Workflow Child Profile types are tied to the Origin dimension, with a fairly logical grouping of Import with Import, Forms with Forms, and Adj with AdjInput.

That’s it – we are at the bottom of the Worfklow Profile tree with Workflow Profile as the most atomic.

There is one more we-call-it-Workflow-Profile-but-really-it’s-something-else element: Workflow Names.

Workflow Names

Workflow Names are confusingly called “Default Workflow” when a Workflow Child Profile is created:

They are called Workflow Names within a Workflow Child Profile:

Think of Workflow Names as the actions that drive Workflow. Given that the property sheet for a Workflow Child Profile uses “Workflow Names”, it seems most logical to use that term when referring to the many, many, many actions (almost 60) they support. Whew, one last time.

As an example, in the Workflow Child Profile Import, I can use the traditional Import, Validate, Load Workflow Name to load data:

Or I can use Direct and change the way data is loaded into the Cube:

Do we have unanimity? Close to it? We should.

Workflow is the core structure OneStream application data processing. Workflow is sophisticated and powerful. Its potential is great, as is its potential to go sideways if discussed and thought about incorrectly.

To use it correctly, we must mean what we say by using the right terms in the right place.

Thus:

Cube Root Workflow Profiles are the topmost level of the Workflow hierarchy. They are tied to Workflow Types. Scenarios that have a matching Scenario Type are visible in OnePlace.
The Default Workflow Profile is automatically generated when a Cube Root Workflow Profile is created. It bridges Cube Entities and Workflow. Do not use it.
Below the main Cube Root Workflow Profile parent, Workflow Profiles join data, metadata, and users.
Workflow Child Profiles are where users interact with Workflow be it data loads, forms, or adjustments via Workflow Name action types.

That’s it.

Be seeing you.

The post What’s in a (Workflow) Name appeared first on Black Diamond | OneStream Solutions.