Call Us: 1-800-497-0151

Blog - OneStream Data Import

Concatenating two source fields

  • , Senior Consultant

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.

onestream data source screen

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".

onestream create source dimension

Select the Dimension you will be targeting (in this case "Brands_Models" and select Data Type "Text".

onestream select entity field 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.
data source screen

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".

create source dimension

Once again, select "Brands_Models" and select the data type as Text.

onestream field instances

You should now see two instances of the "Brands_Models" in the list.

onestream highlight 2 instances

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

onestream order target dimension

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.

onestream concatenate

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.

Contact MindStream Analytics

Fill out the form below if you have questions about OneStream.

Partner SpotLight

OneStream Diamond Partner

OneStream CPM

OneStream aligns to your business needs and changes more quickly and easily than any other product by offering one platform and one model for all financial CPM solutions. OneStream employs Guided Workflows, validations and flexible mapping to deliver data quality confidence for all collections and analysis while reducing risk throughout the entire auditable financial process.

OneStream Profile