Forum Discussion

wm2's avatar
wm2
New Contributor
3 days ago

Mapping Transformation Rules

Hi all,

I am stuck on this prompt. I need to build 6 transformation rules but am not too sure where to start. Could someone help me lay out the 6 I should be using please without business rules or coding.

Huron Energy Services (HES) is a global energy services company. Their User-Defined 3 dimension in OneStream is used for their Function code. The Function code represents the job function that a value falls under. For example, Function 62 is associated with Human Resources. So, salaries for employees in the Human Resources department will be coded to Function 62 (FCT_62).

When loading data to OneStream, the project team has encountered a difficulty. For every expense account in their source system, there are two accounts in OneStream. These accounts are suffixed with "C" or "G", for Cost of Goods Sold (COGS) or Selling, General & Administrative (SG&A) expenses. For example, in their source system they have account 56789, but in OneStream they have accounts 56789C, which is part of the COGS rollup, and 56789G which is part of the SG&A rollup.

The way they map the data from the source to the target is by using the function code. For source accounts that start with 5,6, or 7, Functions 59-69 map to the "C" version of the account, while functions 70-98 map to the "G" version of the account. For example, data with Account 56789 with function code 83 maps to Account 56789G in OneStream.

Data Integration
Transformation

1 Reply

  • T_Kress's avatar
    T_Kress
    Icon for OneStream Employee rankOneStream Employee
    3 days ago

    You have a couple options:

    1. If your account mapping depends on the U3 dimension, at the data source level you can concatenate the account with the U3 field together in the account field. Then in the account field you would have everything you need to build out the mapping OR:
    2. You can do a composite mapping which means you can base the Account mapping off another field like the U3 mapping

    Which one you chose depends on how you want to maintain your mappings and performance. 

    Here is an example of a composite account mapping for the U2 dimension where the U2 dimension mapping is reliant about the A# account dimension:

    Again, either way should work so it is for you to decide the best approach for your use case.