Concatenating two source fields
When loading source data into OneStream, you may have a requirement where the mapping of a dimension is based on the content of two separate fields in the source. For example, you may have a target Product dimension that is populated based on the combination of the values stored in the source Entity and Account fields. In this case, a single account may represent different products based on the source entity. Let’s say our client is a group of car dealerships. The OneStream application contains a dimension called Brands_Model, which identifies car models by Brand. Account #40000 represents the sale of a car. For Entity A, account #40000 it represents the sale of a DeLorean. For Entity B it represents the sale of a Gremlin. When the data is imported, we will map to the correct Product based on the combination of Entity and Account. Let’s also say that for whatever reasons, we will not be able to use Composite Mapping , and we choose not to use Rules to parse the source data and combine the fields during import. Either of these options can be used in various circumstances. But for our purposes, we will be focusing on concatenating the two fields as part of the Data Source definition. By concatenating the two fields as part of the Data Source definition, we can make use of one-to-one mapping of the data fields, which is known to be the most efficient and fastest method of mapping. The mappings themselves are easy for the end user to read, understand and maintain. And the fact that two fields are being used as the source for the mapping of a single dimension is easily identifiable when viewing the Data Source definition.
 | In order to concatenate the two fields, we will first add Brands_Models as a Data Source dimension (if it does not already exist). In the Data Sources screen, find the Data Source you will be modifying. Highlight the source and then click “Create Source Dimension”. |
 | Select the Dimension you will be targeting (in this case “Brands_Models” and select Data Type “Text”. |
 | Identify the field number or string you will be using as the source. In this case we are suing a delimited file at the source and will be selecting the Entity field (Column #1) as the first field for our concatenation. |
| Click the “Save” button.Now we will add a second instance of Brands_Models. Again, highlight the same source and then click “Create Source Dimension”. |
| Once again, select “Brands_Models” and select the data type as Text. |
 | You should now see two instances of the “Brands_Models” in the list. |
 | Highlight the second instance and select the field or text you will use for the second part of the concatenated field, in this case we will be using Field 2 for the Account |
 | Click Save. If you want to change the sequence, perhaps putting the Account first, simply highlight the Dimension in the list and click the up or down arrows to move it in the tree. |
The next time you import data via this Data Source, OneStream will concatenate the contents of the Entity and Account fields. By default, OneStream will concatenate the fields using a period. Now, you can map the newly concatenated field to the proper Brand_Model member. There are a number of ways you can combine source fields for mapping and loading into OneStream, including rules and composite mapping. Using multiple instances of a Dimension in the Data Source offers a simple “no rules required” option to merge the contents of two or more source fields into a single target dimensions for mapping.
