This article includes suggestions and workarounds. Content may not be accurate for all use cases or represent best practices for the latest release.
Are there any characters that cannot be used for account, level, and dimension names?
The pipe character (the vertical line symbol | ) should not be used in used in element names or memo/note fields because it is a reserved character in the Adaptive Insights API that acts a delimiter to separate data. Pipe characters are not supported in the Integration for Planning tool, and their inclusion in element names or source data can cause columns to be delimited incorrectly resulting in values being posted to the wrong columns.
We recommend completely avoiding/removing the use of pipe characters from the fields in your source system that you will be importing into Adaptive Insights.
To remove pipe characters in your data source, you can create a SQL column that removes pipe characters from the staging table. Please see an example SQL expression below which may need to be modified depending on your integration setup.
CASE WHEN "ExampleField" LIKE '%|%' THEN SUBSTRING( "ExampleField" FROM 1 FOR (POSITION('|' in "ExampleField") - 1 ) || SUBSTRING( "ExampleField" FROM ( POSITION('|' in "ExampleField") + 1 ) ) ELSE "ExampleField" END
This expression will remove exactly one pipe character from the target string if it is present. If there are records that have more than one pipe character in the column, you could try creating a nested SQL statement with several similar expressions to continually search and remove each pipe character and return the resulting string.