Although every OneStream implementation is unique, they share common elements. Data loading is one of these. At the beginning of each project a large amount of time is spent creating data sources and rules, attaching them to Workflows and loading and validating data to ensure accuracy. Today we are going to talk about the data validation step in the data load process.
Once you have your data in stage the next step is to transform and validate this data. What this means is that the source data is being run through a set of rules that have been created in the system that tell OneStream what to do with the data. If my source value is X in the source data, map to Y in OneStream.
As OneStream executes these rules, errors can occur. In the following discussion, we will delve into typical errors and provide troubleshooting techniques to address them.
Transformation vs Intersection
When it comes to errors you will encounter on the Validate screen there are two categories that these errors fall into, Transformation errors and Intersection errors. These are split into two categories because OneStream is checking for two things in this process. Transformation errors focus on verifying that each source dimension for a piece of data has a map. Once this has been confirmed OneStream processes an intersection check. This ensures that the combination of dimensions is allowed to be loaded into the Cube based on the constraints that have been set up.
Transformation Errors
Transformation errors occur when needed maps are missing. These errors will be listed by Dimension. When you select the Dimension, you will see the items that are failing or ‘kicking out’ as it is commonly called. You can see in the window below that I have source values for Account and UD1 that have not been assigned a target value in OneStream.
To toggle between Account and UD1 you just need to select one in the Status panel. Assigning a target map can be done by clicking a row to select it, then double clicking at item in the Dimension panel on the right to assign it, and clicking save.
Note that if you need to add a new member to the Dimension Library for these mappings you will need to use the ‘Refresh Page’ button for these to appear in the Dimension box as a selection.
You also have the option to navigate to the Transformation Rule screen on the Application tab and update the mapping there. In my case I know that I can use a Mask Rule to map this entire group of accounts so I will make my mapping change there avoiding the need for individual account assignments.
If you have an application with multiple mapping tables per dimension, make sure you are selecting the one that is attached to your Workflow. I am using a Mask rule below with a wildcard which will map any accounts that come in, to the “SAP_” version of that account.
Once you have completed your mapping changes you can Retransform your data and if everything is resolved you will see a green arrow next to Transformation.
Intersection Errors
The intersection process focuses on validating that the mapping combinations represent an allowable intersection of dimensions. Think of this as a data integrity check that prevents bad or incomplete data from entering the system.
Common examples of intersection errors include:
- Mapping to a member that does not exist
- Intercompany accounts without a partner assigned
- Accounts constrained by other dimensions; for instance, revenue accounts cannot be associated with an SG&A cost center, or a selling account must be linked to a product
- Mapping to a member that does not allow input
Invalid Member Name
When a source is mapped to an invalid target, an error like the one below will show. Although the transformation rules table prevents you from typing and saving an invalid dimension, ‘bad’ maps may still exist under certain circumstances. This can occur if a member is deleted after the mapping rule has been created or if the TRX upload is used to load a text file of rules. Additionally, invalid mappings can arise when mask rules are used to map * to *.
Below is an example of a situation where I have a * to * rule as mentioned above. To address I will either need to create the missing account in the metadata or add a mapping rule to map this source to a different target that exists in OneStream.
If this was a situation where a * rule was not being used and I needed to see what my source account was I can use drill to stage to find this information.
To do this you will select a line and right click then select Drill Down.
This will open a panel that shows me the current target as well as the source intersection. I see that my source account is 140930.
From here I can right click again and see what rule is processing this.
I can see that as suspected a * to * rule is picking this up.
Now that I have this information I can make the correction in my rules table.
Intercompany Member Not Within the Constraint Settings
When a dimension member has constraints on it that have not been fulfilled you will get a Validation Message that says “____ is not within the constraint settings”. In the example below I have an intercompany account that is constrained to my intercompany entities. I am getting an error because the data is either mapping to a target value of None or coming in with a source value of None. This validation message is letting me know I need to make a correction.
Using the drill down method above I can see that my source data is loading with an IC partner None. This is something I will need to correct in my source ledger as intercompany accounts should load in with an intercompany partner.
Once a journal has been made in the General Ledger to correct this I can reload and transform my data. Now I see my intercompany account data coming in with trading partners and my intersection errors have cleared.
The above is an example of an intercompany account missing partners. It is also possible that an account has a partner, but you are still getting an error. In the error below I can see that the IC member is assigned but is not within the constraints for the account.
When I navigate to this account in the dimension library, I see that this is an Intercompany account but my Is IC Account tag is set to False, therefore the system is not allowing partners. I will need to update this setting to True and revalidate my data.
Other Member Not Within the Constraint Settings
Like the example provided above, this is a situation where I have constraints on my account that are not being met.
As illustrated below, the UD2 Constraint is set to ProductTot. This means that the UD2 on this account must be within the ProductTot roll up to successfully pass the intersection check. The data is coming in with a UD2 of None. This will need to be assigned a UD2 within the ProductTot roll up and then revalidated.
Invalid for Input
Encountering the “invalid for input” error indicates that the member is not configured to allow input.
When I navigate to SAP_11000 in my dimension library I can see that Allow Input is set to False. At this point I will need to determine if this is an error in the dimension set up, or if this account truly should no longer allow input. In this case I will need to go back to the source ledger and reclass this account to an allowable one then reload my data.
If in addition to the invalid for input message you also get a message that your member has Children, this shows that your member is not a base member, and you will need to update the mapping to load to a base member.
Unassigned
When a new source comes in for the first time the Target Value will show as (Unassigned), meaning that no target account has been assigned to this source yet. You can see this in the Transformation Errors below.
One ‘gotcha’ in OneStream’s mapping (which I wish it didn’t allow!) is the ability to assign the (Unassigned) account as a target and save. You may think this is obvious not to do, but when you do this long enough you get to see it all! If you assign this and retransform, the data will get past the Transformation step.
You can see below that now that I have assigned the (Unassigned) account, my errors move from being a Transformation error to an Intersection error. If you want to map an account to nothing in OneStream, you can map it to (Bypass). This tells the system not to load this data in the Cube. However, you should never assign an account the (Unassigned) Target or you will get the below error.
To fix this you can navigate to the Transformation Rules table and delete the rule mapping to (Unassigned) or replace it with the correct map.
Exporting Errors
When you have several pages of errors across multiple Workflows exporting a full list can be a pain. OneStream lets you export only one page at a time so if your errors span multiple pages you will need to export each one separately. This export also does not include the Validation Message so it may be hard to tell what the issue on that specific line is.
At Black Diamond Advisory we believe that if functionality that makes the end user experience better is missing, we should create it. We have an Intersection Error Dashboard we install for all clients to allow a seamless review and export of error intersection messages without clicking through multiple screens. This dashboard allows users to come to one interface and export a table of all errors with the message that tells them what is wrong.
You can also toggle between Workflows if you are in a multi-workflow application and right click to Export the entire list in one shot, no more exporting one page at a time. This is an easy way to review and address errors quickly and efficiently.
Wrapping Up
Addressing mapping errors in OneStream implementations is a step crucial to every OneStream journey. Using the above tips, you should be able to handle a majority of errors that arise! Have you faced an error not listed here, if so how did you resolve it?
