The Gateway to DM Sequences via Workflow Profiles – “No Calculate”

Have you ever wondered what you can do with the calculation type “No Calculate” when setting up a Calculation Definition in your Workflow Profile? It’s sort of odd: Calculation Definition implies a calculation; No Calculate does precisely the opposite. “No Calculate”, despite its name, is a powerful technique that can kick off a Data Management Sequence (to, um, calculate) from a Workflow Profile Process step, which opens the world to endless types of different scenarios (or at least calculations running after data loads).

Use Case

In this example, we will be loading new growth factors and executing a business rule to calculate forecasted dollar amounts. The key here is that the calculation ought to fire directly after the data load.

Pre-Req’s

1. A Workflow Profile Child that at a minimum has a “Process” step in it. In my example, we will use the “Import, Validate, Process” Workflow name.

2. You will need to have a Data Management Sequence setup and working (and of course one that calls a Business Rule). In this example, I have created a DM Sequence and Step called “NoCalc_Demo”. Spaces are allowed if you wish!

3. You’ll need to have an Entity to run this job from. I have created an entity called “NoCalc” to launch this rule. While you can use this entity in the DM Step, this is not always the entity that will be calculated in the DM Step. A separate, calculation-only Entity is a good idea because it segregates calculations that run on load from the real consolidation process.

4. Lastly, you will need a Data Quality Event Handler. If you do not already have this Business Rule created, navigate to Application->Tools->Business Rules and create a new rule with a type of “Data Quality Event Handler”. The name of the rule – “DataQualityEventHandler” – is assigned automatically on creation of the rule and cannot be changed. You will only ever have one of this rule type.

Now you will need to place some code into the business rule which will actively identify a “No Calculate” calculation type. Two key areas in the business rule that I would like to call out are on lines 45 and 67 below. The first is identifying a No Calculate process type that is actively identifying the process in the background (automagically) and the second part where it is starting the Data management Sequence with a filter value (this will come into play in a later step).

BDA nor I will take any claim in writing this rule as it was passed down from the Legend himself. No changes are necessary to this business rule.

Where To Find It If You’re Not Keen on Lots of Typing

The above is a bit hard to read and isn’t populated when the rule is created. It is available in GolfStream and is a direct copy and paste (be sure to do a validate just in case). There’s only one DataQualityEventHandler rule so it’ll not be hard to find; look for it under the Extensibility Rules hierarchy.

Connect the Dots

At this point, we have all the necessary components to kick off the DM Sequence from our WF Profile. The linkage between the Workflow Profile Child and Data Management can now be done in just a few easy steps:

1. Within the WF Profile Calculation Definition setup you will need to add an entry with the following details:

• • Entity – NoCalc (Step 3 from the Pre-Reqs)
  • Parent – You can leave this at Unassigned
  • Calc Type – No Calculate – hence the reason you are reading this blog! Seriously, this is not terribly intuitive, but this really and truly is the Calc Type.
  • Scenario Filter Type – If you would like to only run for certain Scenario Types you can select a value.
  • Confirmed – Checkbox Y/N value. In this example we will leave it blank.
  • Order – Defined order of operations that will be unique to your use case. In this case I only have one step so I will leave it at 0.
  • Filter Value – Must equal the Data Management Sequence name. Check line 67 of Tom’s code to see that this value is what is being started in the DM Sequence. You must type in the name of the Sequence name directly so either have a good memory or write it down on a piece of paper; there is no dropdown control to give you the names of the Sequences.

2. You are done!

Final Product

Once we have completed the setup, we can go ahead and step through the process and test it out. So here we go…

1. Imported data successfully:

2. Validated data successfully:

3. Load and Process the cube successfully:

4. Data Management Sequence executed successfully, as shown in the Task Activity monitor:

We can also review our cube views to make sure the numbers are calculated as anticipated post process. In the example for this demo purpose – we have the original data plus a growth factor and then a second slice that will contain the calculated data below it.

Pre Process

Post Process

Automation Nirvana Achieved

The requirement to run a calculation (or a series of calculations) after a data load is a common use case. That calculation must be run manually unless the No Calc-to-Calculate Workflow Process properties are defined and the (written for you on creation) DataQualityEventHandler rule is created.

With those two components in hand, with a simple and reusable Business Rule you can unleash the power of OneStream’s Workflow engine to create simple and integrated processes! What could possibly be easier?