OneStream Stupid Trick No. 1 – Aggregating (or Consolidating) On Data Load

Is it stupid?

Oh dear, there’s nothing quite like being completely out of touch with popular culture or rather having what remains of your popular culture references be from a prior century.  Just wait, young’uns, it’ll happen to you.

Let us wind back the clock to the glory days of the 1980s

You weren’t there?  You were and don’t remember?  Just think of this and rejoice or shudder:

Cue apoplectic rage (or fond memories and a wish for those days to return, if only they could – this blog caters to all) on both sides of the Big Pond.  Do try to remember SDI and the end of the Evil Empire and think about what it took to get there.  Also, take into account photojournalists who have a knack for photographing people at their very worst.

Now that your interest has been piqued, let’s think about something a certain kind of American found funny:

You either get it or you don’t.  Having a sense of humor that approaches the dryness of a very dry martini helps.  You may have noticed (despite your best intentions and in the face of a rather large amount of apathy) that Yr. Obt. Svt.’s somewhat jaundiced perspective on life is largely congruent with David Letterman’s – my one claim to show biz fame and to (prior) popular culture.

On to the point, if there is one, and there probably is

Unlike Late Night With David Letterman, this blog series’ OneStream Stupid Tricks are not exactly stupid, they are more like OMG-I-cannot-believe-that-it-works-that-way-whoever-would’ve-guessed-so-I-suppose-that-makes-me-stupid.  Truly, sometimes I sit and think and think and think about what seems to be a simple need and sooner or later, usually later, the answer pops into my head.  My inability to figure them out in a timely manner is stupid, and they are generally simple solutions, hence the moniker “Stupid Tricks”.  It has ever been thus.

Load and Agg

So easy, so straightforward, so simple: “I need to load data and then I need to roll up the data”.  Where oh where does one do this?

I chase it here, I chase it there

Data Management (not) to the Rescue

Data Management has the word Data in its very title.  Data is what I am loading.  An aggregation is what is needed after that.  Surely then a Data Management step must exist that performs this.  Surely?

Of course, there is a Calculate Step/Consolidate Calculation Type that will perform the aggregation I want, but las and alack, there is no step type that imports data.  Oh sure, there’s one that exports data but that’s the * -1 version of what I need:

Sigh.  I am looking in the wrong place.

Here it is, Stupid Trick No. 1

Don’t tie data loading to aggregation, tie aggregation to data loading.  Pretty obvious (and thus stupid), right?  But where oh where oh where does one do that?

In a (non)intuitive manner, the correct, pretty easy, and AFAIK, undocumented, way to do this is to:

  1. Create or modify a Workflow Profile Child to perform the data load.  Make sure that Workflow Settings’ property Workflow Name is set to Import, Validate, Process.  I guess I understand where this is being defined because the first step is to load data and one does that through Workflow.  It still bothers me that this cannot be done in a feature called, after all, Data Management, but it is what it is and one must do things the OneStream Way.
  2. Select the Calculation Definitions tab and add a row.
  3. Take the deepest of deep breaths (eh, this isn’t so hard to understand) and then:  chose an Entity, leave Parent as (Unassigned), set Cons to Aggregated, set the Calc Type to one of the four Consolidate properties (Consolidate, Force Consolidate, Consolidate With Logging, Force Consolidate With Logging),  set the Scenario Type Filter to the type (you can get away with all (All) if you so choose, skip Confirmed, provide an Order value, and ignore Filter Value.

When the data is Imported, Validated, Processed, the aggregation detailed above will fire.

What could possibly be easier?

Let’s do this step by step in a profusely illustrated way.

Workflow Profile Child and Calculation Definition


No, not this:


The Workflow Name currently known as Import, Validate, Process

Be sure not to use Import, Validate, Load as it’ll do just that, and not run (eh, Process) an aggregation.  It only took me three tries to figure that one out…

Calculation Definition

  1. Select the Entity.
  2. There’s no need for the Parent, just like it need not (at least in this use case) be defined in a Consolidate Data Management Step.
  3. Select Cons.
  4. Define Calc Type.  Note that I am using Force Consolidate With Logging to clearly see log entries but this isn’t necessary.
  5. Set the Scenario Type Feature.
  6. Ignore Confirmed.
  7. Set Order.
  8. Ignore Filter Value.

Lock and Load

The proof of the pudding is in the eating, so let’s give it a try.

You, Gentle Reader, have likely done this eleventy million times, so this will, for once, be brief.

Load and Transform



Load Cube does just exactly what is says on the tin, which means the aggregation doesn’t fire.

Use Load And Process Cube for that happy feeling:

Oooh, two, not one, steps:

What on earth is that second step?  Let’s have a looky-loo:

And is that really the whole shebang?

Yes, indeedy

See, that wasn’t so stupid, was it?

The 80s were great.  So too is this Stupid Trick.  I think.  Probably.

I came of age in the 1980s (Drexel class of ’90) and like all generations, have found that the world I encountered as a young adult (barely) largely informs my worldview even today.  I suppose part of that is being suspended in aspic culturally, combined with comedy that still resonates.

A wee bit of cynical despair (that’s just about as much Gen X as anyone could ask for) coupled with a generally robust determination not to give up makes things not so awful and incidentally solves OneStream puzzles.  Is that Stupid or just Successful?

I leave it up to you to decide what to do with the two dominant figures of Anglosphere politics of that decade, but note it’s not quite as an uncomplimentary picture:

As stated, this blog caters to all, no matter their political bent.  The next time around I’ll feature Walter Mondale and Michael Foot.

I leave you with this gem from 1987: 

Be seeing you.

Share This: