SensibleAI Forecast Mapping to Onestream Metadata
One of the main benefits of utilizing SensibleAI Forecast (FOR) is to be able to forecast at a lower granularity of metadata than can be done manually or via out-of-the-box OneStream functionality.
However, our customers still want to be able to consume and report the FOR forecast in OneStream. We’ve discussed in prior articles how the connection is made from FOR to OneStream and how to build out items to load the data into the Cube, but mapping metadata is a whole process in itself.
It’s assumed in this article that there is a general understanding of the items necessary to load FOR data to the Cube. Please refer to the articles titled “SensibleAI Forecast Data Load Build Items“ and “SensibleAI Forecast Connector Business Rule” if you have not done so already.
Walkthrough
All mapping is created within the Transformation Rules page in OneStream.
As stated before, each line of data being loaded from a SensibleAI Forecast table must be defined for all 16 Cube dimensions. Therefore, each dimension needs to have its own set of transformation rules that tell OneStream where to send each member.
Let’s begin by looking at the two most used types of Transformations Rules for SensibleAI Forecast mapping:
One-To-One
One-To-One mapping rules are created when there are specific members we want to map to specific members. For example, if we want all data for WarehouseA to map to Entity EUS01, we will create a One-To-One rule. The screenshot below is an example of what this rule would look like in OneStream:
For this type of rule, the defined Target value must exist in OneStream and be in the dimension that is assigned to our Cube. Otherwise, OneStream will throw an error and not allow you to save/create the rule.
Mask
Mask mapping rules allow for what we call “wild cards”. In OneStream, the wildcard characters are “” and “?”. The * is used to represent any number of characters. 27 would capture 270, 2709, or 27XX-009. The ? character acts as a placeholder for a single character. 27?? would capture 2709 but would not capture 27999.
The most common kind of Mask rule is what we call “star to star” (* to *). This rule tells OneStream to send all members through as one-to-one. Below is a screenshot of this rule:
The wild card characters are only meant to be used in the Source Value column of Mask rules. The * can be used in the Target Value column, however only as a standalone or as a suffix character. It is recommended to only be used in the Target column with a * to * rule.
Composite, Range, and List type rules can be used, but are not common so the specific use cases for each can be researched more in the OneStream Reference Guide.
The final state of the Transformation rules is for each of the following dimensions shown below to have its own set of rules, either One-To-One’s, Masks, or a combination of the two kinds.
Transform vs. Map
Earlier in the article I stated that all mapping happens in the Transformation Rules. However, not all transformations happen in the Transformation Rules. This can be confusing, but some transformations can occur in the Data Source before any mapping is done.
One common occurrence of this was discussed in the Data Sources section of the article SensibleAI Forecast Data Load Build Items. Often, we want to send all of our data to a single Cost Center, therefore, we hardcode a specific dimension to a single member in the Data Source. Now in our transformation rules, all we have to do is create one rule that maps this single member to itself.
Another common occurrence of this is with the Time dimension. The Time members in OneStream are always in the format of “YYYYMM” (2022M1, 2022M2, etc.), and the Time members in the SensibleAI Forecast table are typically in the format of “MM/DD/YYYY 12:00:00 AM”. Since the Time dimension is one of the dimensions that only accepts one-to-one mappings in the transformation rules, it would be a lot of work to map each month/day/year combination to a single month/year. To ease this process, we do a transformation of the time members in the data source to make our transformation rules easier.
To do a more complex transformation such as this within the Data Source, we create something called a Complex Expression, which is essentially just a Business Rule. First, change the Logical Operator to “Complex Expression”.
Then, in the Logical Expression, insert the following code:
Dim fields As List(Of String) = StringHelper.SplitString(args.Value, "/", StageConstants.ParserDefaults.DefaultQuoteCharacter)
Dim monthNum As String = fields(0)
Dim yearNum As String = Left(fields(2),4)
Return yearNum & "M" & monthNum
This code, when assigned to the Date field of this Data Source, will take the member “1/17/2022 12:00:00 AM“ and transform it to “2022M1“. Then in the transformation rules, we need a one-to-one rule that sets 2022M1 to 2022M1.
The key is to remember the order by which OneStream ingests and processes the data from the SensibleAI Forecast table. Data first gets ingested by the Connector Business Rule, then by the Data Source, then by the Transformation Rules before being loaded into the Cube. Therefore, any filtering or modifications done in a prior step is the state in which the data is passed into the following step.
Appendix
Troubleshooting
You’ll know you have an issue with your transformation rules when you try to load data into the Cube via the workflow and get the following error:
This is a generic error and does not always mean that there is an issue with the Time or Scenario member rules. This is an indicator to check those two dimensions first, but if the problem persists, check all other dimensions and their rules as well.