Excel Mappings sheet

The mappings sheet defines operations that must be applied on each data row (e.g. set a constant value to a site/source/user property, convert data). The sheet name must be Mappings and it must contain a header row with 4 columns:

1. SheetName: the name of the sheet on which the operation must be applied. The name MUST exactly match one of the sheet name specified in Excel Template Config Sheet
2. Field: the expression field targeted by the operation
3. Operation: one of the operations below
4. MapTo: the value to use during the operation

Force operation

Unconditionally set the value of the property specified in Field to the value specified in MapTo column.

The value to force may be expressed in multiple ways.

Single forced value

The value specified in MapTo is treated as a single value that must be set to the target property. It is suited for a destination property that can only contain a single value (e.g. _opSite.TypeId, _opSource.GatewayId, ...)

Set Site.TypeId = 15 and Site.TimeZoneId = “Romance Standard Time” when a site is created or updated.

Multiple forced values

The value specified in MapTo must be expressed as a JSON array ''propertyName/propertyValue'' that must be set to the target property when such property may contain an array of values (e.g. _opUser.Claims).

Set claims for all users.

Convert operation

Threat the expression specified in MapTo column as a list of key/value pairs. When the field specified in the Field column is about to be created/updated, it’s value is searched in the list of keys from MapTo and substituted by the corresponding value. There are multiple ways to specify the key/value pairs list.

Comma-separated value

The key pairs are in the form ''key = value'' and are separated by a comma

When setting Source.EnergyTypeId, substitute the string “ELECTRICITY” by 1, “GAS” by 2 and “Water” by 3

Important

In this mode of expression, none of the left or right string may contain character ''='' or '','' because they would be interpreted as separator.

Tip

This is a legacy mode that should not be used in Master Data definition

JSON object with simple type value

The key pairs are in the form

{
   "alias1" : "value1",
   "alias2" : "value2"
}

Important

This is the preferred mode to define key/value pairs

When setting User.Roles, substitute the string “Data Analyst” or “Data Manager” by "Manager" and “Maintenance” by “User”

JSON object with complex type value

The key pairs are in the form

{
   "alias1" : [
      "key1" : "value1", 
      "key2" : "value2"
   ], 
   "alias1" : [
      "key1" : "value1", 
      "key2" : "value2"
   ]
}

Important

When MapTo contains a string representing a JSON object, any JSON-reserved character must be escaped.

Online tools such as https://www.freeformatter.com/json-escape.html allow you to escape/validate JSON strings

When setting User.Claims, substitute the strings “Admin”, "Expert", "Basic" with the corresponding list of claims

Append operation

When the field specified in the Field column is about to be created/updated, it’s value is appended (with a single space) to the value specified in MapTo. Although this operation is mainly designed to append tags, it may be used on any string property

When setting Source.Tags (imagine “A_TAG_VALUE”), substitute “A_TAG_VALUE” with “A_TAG_VALUE raw”

Query operation

When setting the list specified in the Field column, the MDE will treat the value provided in the Master Data Upload file as an Opinum Data Hub custom filter expression and fill the destination list with the result of the filter evaluation.

Note

• The QUERY mode is compatible with UserSitesAndSourcesMergeMode mode, meaning that the query result could be appended to the an already existing list.
• LIST and QUERY modes can be mixed, e.g. defining QUERY mode for sites and LIST mode for sources

Important

This mapping operation is only valid for _opUser.Sites or _opUser.Sources

Warning

There are some limits for this query

1. The complexity is limited but unpredictable because it depends on the query itself. As an example, a query like ''Name = xxx OR Name = yyy OR ...'' will fail, let's say, after 200 terms (number of ''OR'' occurrences) but the failure could happen earlier for a more complex query
2. The total query length may not exceed 1 megabyte. Query lenght above this limit may produce unpredictable results

List operation

When setting the list specified in the Field column, the MDE will treat the value provided in the Master Data Upload file as a discrete list of business keys separated by the string specified in column MapTo and will fill the destination list with the corresponding Opinum Data Hub entities.

• The separator is case sensitive
• The separator must be a string that does not appear in any of the business keys provided in the list
• The separator may contain more than one character: the separator “my_sep” will correctly split “SITE1 my_sep SITE2”
• Pay attention not to surround the business keys by single or double quotes (e.g. ‘Site1’,’Site 2’) because the quotes will be considered as being part of the business key
• The LIST mode is compatible with UserSitesAndSourcesMergeMode mode, meaning that the list content could be appended to the an already existing list.
• LIST and QUERY modes can be mixed, e.g. defining QUERY mode for sites and LIST mode for sources

Important

This mapping operation is only valid for _opUser.Sites or _opUser.Sources