In this blog written by Taysols Principal Consultant Ian Trevena, we will explore how to configure an OneStream connector to return records relevant to the active workflow. More specifically, to only return records that will map to the workflow’s assigned entities, without hard-coding the source entities into the connector.
We will detail a single connector that can be invoked by any number of workflows, where neither the connector nor its author has any prior knowledge of the entities assigned to those workflows.
Connectors Are Tops
Connectors are extremely handy. Connectors provide multiple benefits, such as:
- Removing the need for pre-extraction of data (CSVs) from source systems
- Provide a seamless and therefore secure end-to-end (source-to-OneStream) pathway for data
- Form the backbone of any mechanism that drills back to the source system
- Eliminate double-handling of data during the OneStream load process
- Can provide almost zero latency on data
- Provide a way of capturing additional information about the workflow process using database and OneStream API calls
-
Lend themselves to automated (lights-out) execution of workflows because no source file needs to be supplied
A Challenge, and an Orthodox Solution
But, what about workflow assigned entities?
How should a connector be written to only return the records relevant to the workflow that invoked it? That is, how do we ensure that all source entities returned by the connector will be mapped to entities assigned to the workflow? There is little to be gained by importing records that are rejected upon intersection validation because the target entity is not assigned to the workflow.
One solution is to hard-code the range of source entities (business units) into the SQL executed by the connector. While this works, hard-coding is not a great solution As we will end up with a case statement that must be maintained in sync with OneStream dimensions and entity transformation rules or a suite of connectors all doing the same job for different business units. Either way, it is nightmare material for anyone charged with keeping the OneStream system humming along.
A OneStream Solution
A better solution to the above challenge is to use the entity transformation rule to determine the source business units. Basically, run the transformation rule backwards to derive the source system business units (source entities in the entity transformation rule) from the workflow’s assigned entities (targets in the entity transformation rule). By running the transformation rule backwards, it will allow us to write a single connector that loads data for all workflows for the dataset in question.
Let’s invent a scenario – one that is well within the bounds of reality.
In this scenario we’re loading GL balances from our ERP. Our fictitious client has 400 business units for which GL balances must be loaded. In OneStream, 100 entities are assigned across 20 workflows. The workflows are clones of a common template, and therefore will all use the same connector.
The connector logic for deriving ERP business units for the workflow is portrayed below in Figure 1, and shows the logic embedded in the workflow connector that imports GL balances data from JDE.
Figure 1. Constructing the extract SQL from workflow assigned entities
It is worth noting that the link to the transformation rule is live. If the transformation rule is modified, the connector will immediately use the new rules without intervention or intermediate processes.
Furthermore, the components are standard OneStream. We are not using bespoke or customised APIs. The key components are:
- The OneStream API that returns the entities assigned to the workflow
- The OneStream Entity Transformation rule attached to the workflow
-
Code within the connector to construct the required entity filter and insert it into the SQL view that returns the JDE data
Summary
Using the approach outlined above, a single connector—which itself does not know which business units should be extracted by any given workflow—can successfully identify the BUs it should extract from a source system irrespective which workflow is executing it. It does this by using the transformation rule that is already being maintained to facilitate the workflow, meaning no additional logic or maintenance is required.
This class-leading approach is available for all Taysols clients. Find out more by contacting us.