Translation Rules

[wm2]

Using this prompt, what 6 transformation rules need to be built? Currently I have:

1. View YTD to YTD on a one-to-one

2. Account * mask

3. Entities * mask

4. UD 3 * mask

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.

[wm2]

Transformation Rules***

[T_Kress]

You have a couple options.  When bringing in the account field at the data source level you could:

1) Concatenate account with function (so bring in both the account column with an underscore then the function column to the account field (function would go to function as normal at the data source level), something like 56789_COGSFunction and 56789_SGAFunction.  Then in the account mapping you can do one for one or mask so that 56789_COGSFunction goes to 56789C and all else or specifically 56789_SGAFunction goes to 56789G.

2) Or you could bring account in to account and function in to function at the data source level and then in the account dimension, use a composite mapping that says something like:

A#?????:Ux#COGSFunction goes to A#?????C

A#?????:Ux#SGAFunction goes to A#?????G

Composite are when you need to leverage two fields (e.g. account and function) in the mapping of one field (e.g. account).  But they are a slower mapping than a 1:1.

I woudl probably lean towards #1 since handling bringing in both fields at the data source level on import, may make for easier 1:1 mappings in transformation.

But either option should work.

[wm2]

Thank you so much. For number 1, would you create those under account or UD3?

[T_Kress]

Because the account is the dimension you need to drive to two different accounts (depending on function), I would bring in the account at the data source level in 3 parts as example below:

• GLAcctDim first column is column 2 in file in example below
• GLAcctDim second column is a simple "_" static value
• GLAcctDim third column is column 3 in file (aka your Function) in example below

In this way you have concatenated the account and function in your account field.  Then in mapping you now have what you need to drive certain accounts to C and other accounts to G.  For any accounts where this does not matter you can do mask mappings.

So for example if you have an account 12345 which just has one account (with no C or G) then your mapping can be 12345* ==> 12345

[MarcusH]

In the cases where you can describe how you want the maps to work by referring to another dimension's target values, that is when you should think about mapping based on the target values. You will need a conditional rule for this and GolfStream has an example:

Imports System
Imports System.Data
Imports System.Data.Common
Imports System.IO
Imports System.Collections.Generic
Imports System.Globalization
Imports System.Linq
Imports Microsoft.VisualBasic
Imports System.Windows.Forms
Imports OneStream.Shared.Common
Imports OneStream.Shared.Wcf
Imports OneStream.Shared.Engine
Imports OneStream.Shared.Database
Imports OneStream.Stage.Engine
Imports OneStream.Stage.Database
Imports OneStream.Finance.Engine
Imports OneStream.Finance.Database

Namespace OneStream.BusinessRule.ConditionalRule.XFR_Conditional_Target
	Public Class MainClass
		'------------------------------------------------------------------------------------------------------------
		'Reference Code: 	XFR_Conditional_Target
		'Description:		Demonstrates how to map a flow dimenion based on the target account.
		'Usage:				Used for conditional flow member mapping based on target account.
		'Created By:		John Von Allmen
		'Date Created:		5-24-2013
		'------------------------------------------------------------------------------------------------------------		
		Public Function Main(ByVal si As SessionInfo, ByVal globals As BRGlobals, ByVal api As Transformer, ByVal args As ConditionalRuleArgs) As Object
			Try
					
				'Grab the target account value				
				Dim account As String = args.GetTarget("A#")   
				
				'Assign the flow member based on the first character of the target account
				Select Case Left(account,1)
					Case "1","2","3"
						Return "End_Inp"
					Case Else
						Return "None"
				End Select
				
			Catch ex As Exception
				Throw ErrorHandler.LogWrite(si, New XFException(si, ex))
			End Try
		End Function
	End Class
End Namespace

In your case you will want to look up the Target value (ie already mapped) of the FCT member and then get the first character of the account. It looks like the account naming is similar between the source and the target. I would add this as a Mask map that processes near the beginning of the Mask maps. And then you need to check the Transformation Sequence of the dimensions to make sure the FCT dimension is processed before the Account dimension. Cubes | Integration tab | Transformation Sequence