« Dimension Borrowing   |   Scenario, Period, and Fiscal Year »

Importing and Exporting Members

Designer Administrator
This manual is in pilot operation.

Importing Members

It is possible to add or update members in bulk for a dimension based on data registered in an Excel sheet or text file. This feature is referred to as the "Member Import Function".

This function allows not only for the import of member labels and names but also property values and the tree structure (parent-child relationships).

Operation steps:

Design Mode and Administration Mode

There are two modes for importing members: "Design Mode" and "Administration Mode". The former is executed from the "Dimension (Design) Panel" of the [Manager], and the latter from the "Dimension (Maintenance) Panel". The differences between them are as follows:

Design Mode

All member setting information that can be set with Designer Authority can be imported. This includes:

  • All information related to template members and user-defined members

  • User-defined property values of system-reserved members

Administration Mode

Only the information that can be set with Administrator Authority, that is, settings information related to user-defined members, can be imported.

Formats of Importable Files

There are two methods for importing: importing data from a text file and importing from the clipboard:

Importing from a Text File

Import settings content from a text file. Within the file, items are separated by commas in "CSV format (Comma-Separated Values)".

Importing from the Clipboard

Copy a range of settings content described in an Excel sheet to the clipboard, and then import it into the dimension editing screen. On the sheet, each item is described in its own column.

The items and format of the import data are the same for both methods, so below, the format of importable files will be explained. If you find it difficult to understand, try exporting the members of an existing dimension. The exported file can be imported as is (following the rules below).

General Rules on Notation

The file to be imported must be in CSV format (Comma-Separated Values). The file format is designed to be easily edited in Excel (you can open, edit, and save it in Excel - provided the file encoding is set to Shift JIS).

Both LF (Line Feed) and CR (Carriage Return) characters can be used as newline characters. If LF and CR are consecutive (LF + CR, CR + LF), they are considered as one newline.

Values for each field should be separated by commas, with spaces before and after the commas ignored. Values can be enclosed in double quotes (if a value contains a comma, the entire value must be enclosed in double quotes). Newline characters within a double-quoted string are not considered as line breaks for the CSV file. Also, two consecutive double quotes within a value string are read as a single double quote.

Except for member names, upper and lower case are not distinguished.

The Encoding applied to the import file can be either UTF-8 or Shift JIS. Before importing, select the encoding used in the file on the [Manager] screen.

Rules on the Order of Rows

The file consists of a "header row" followed by any number of "detail rows". The header row indicates the meaning of each field in the detail rows. Detail rows each represent the content of addition or update for one member. Empty rows can be placed anywhere in the file and are simply ignored.

The rules on the arrangement of header and detail rows are as follows.

  1. The first line of the file (excluding empty lines) must be the header row.

  2. Any number of "detail rows" can be placed after the header row. The arrangement of fields in these detail rows must follow the instruction of the immediately preceding header row.

  3. Multiple header rows can appear within a single file.

  4. Each detail row represents the content of addition or update for one member. Multiple detail rows for a single member are allowed, and in that case, they are processed in the order they appear in the file.

Using the above rules, you can indicate all content of addition or update for a member in one line, or you can separate the file content by the type of work, such as first registering all member labels and names, then collectively registering all parent-child relationships.

Rules on the Content of Header Rows

The header row consists of the following fields:

Field Name Mandatory Number of Fields Field Position Specified Content (Field Symbol)

Command

✓

1

Always the first field

Fixed value ADD_OR_UPDATE_MEMBER or
UPDATE_MEMBER

Record Type

✓

1

Always the second field

Fixed value HDR

Label

✓

1

Optional

Fixed value LABEL

New Label

―

1

Optional

Fixed value NEW_LABEL

Name

―

One for each defined 'language'

Optional

Fixed value NAME: + Locale
Example)

NAME:ja

Japanese name
NAME:en

English name

Remove Children Instruction

―

1

Optional

Fixed value REMOVE_CHILDREN

Property Value

―

Optional

Optional

Fixed value P: + Property label.

Example 1) For member type property
P:#MEMBER_TYPE

Example 2) For usage classification property
P:#ACTIVE

Example 3) For data type property
P:#DATA_TYPE

Parent Member Addition Instruction

―

1

Optional

Fixed value PARENT

Root Registration Instruction

―

1

Optional

Fixed value IS_ROOT

The command indicates that the following detail rows are instructions for adding or updating members.

When the command is ADD_OR_UPDATE_MEMBER

Update the member if it exists, add if it does not.

When the command is UPDATE_MEMBER

Update the member if it exists, report an error if it does not (the member is not added).

The record type HDR indicates that this row is a header row.

The New Label field is provided when you want to change a member’s label.

The Name field is indicated by the fixed value NAME followed by a 'locale', such as "ja" "en", connected by a colon :. Do not place spaces before or after the colon. The locale can be in uppercase. The Property Value field is indicated by the fixed value 'P' followed by the property label, connected by a colon :. This is the same as the Name field in that spaces should not be placed before or after the colon.

In this table, the three fields "Remove Children Instruction", "Parent Member Addition Instruction", and "Root Registration Instruction" are different from labels, names, and property values, and are fields for describing instructions to construct the tree structure well. Each of these fields is assigned a fixed field symbol. For how to use these fields, see the "Rules on the Content of Detail Rows" below.

Among the fields, the ones always required are Command, Record Type, and Label. The others can be provided or omitted as needed.

The order of fields other than Command and Record Type is optional, but except for the Label, the fields are processed in the order they are arranged. For more on this, also see the "Supplementary Explanation on the Order of Fields" below.

Rules on the Content of Detail Rows

In the detail rows, appropriate values are specified for each field defined in the header row. Thus, each detail row must have the same number of fields as the preceding header row, and the fields in the header and detail rows are matched in the order they appear. The specified values for each field in the detail rows are as follows:

Field Name Specified Content (Field Value)

Command

The same value as the command column in the header.

Record Type

Fixed value DTL

Indicates that this row is a detail row.

Label

The label of the target member to add or update.

If a member with this label exists within the dimension, it will be updated according to the instructions in this detail row.

If there is no corresponding member, the member will first be added to the dimension. Then, like an update, the content of that member is set according to the instructions in this detail row.

New Label

The new label to be assigned to the target member. It can be left blank if there is no need to change the label.

If the member’s label is changed using the "New Label" field, note that the new label must be used to specify that member in the "Parent Member Addition Instruction" field in subsequent rows.

It is generally recommended not to use the "New Label" and "Parent Member Addition Instruction" fields together.

Name

The name of the target member in various languages.
(If the field symbol is NAME:ja, then the Japanese name; if NAME:en, then the English name, etc.)

Remove Children Instruction

TRUE if you want to remove the target member’s child members. Otherwise, FALSE.

⚠ The child members are only removed from under the target member. The child members themselves are not deleted.

Property Value

The property value of the target member

Example 1) For member type property
TEMPLATE, UDEF, etc.

Example 2) For usage classification property
TRUE or FALSE

Example 3) For data type property
NONE,TEXT,ACCOUNT, etc.

Parent Member Addition Instruction

The label of the member to add as a parent member of the target member. Leave blank if no parent member is to be added.

⚠ If the specified member is already a parent member of the target member, no action is taken (the target member will not be duplicated under one parent member).

Root Registration Instruction

If you want the target member to be a root member: TRUE
If you want to remove it from the root: FALSE
If neither adding nor removing from the root: blank

⚠ If TRUE is specified and the target member is already a root member, no action is taken (the target member will not be duplicated in the root). Likewise, if FALSE is specified and the target member is not a root member, no action is taken.
Supplementary Explanation on the Order of Fields

Fields other than Command, Record Type, and Label are processed in the order they are arranged. Therefore, care must be taken, or the intended results may not be achieved. A typical case is when you want to set the Leaf Classification/#LEAF property and give a Remove Children Instruction simultaneously.

Suppose member X currently has child members, and the import file is written as follows:

  ADD_OR_UPDATE_MEMBER , HDR , LABEL , P:#LEAF,   REMOVE_CHILDREN ・・・

  ADD_OR_UPDATE_MEMBER , DTL , X , TRUE , TRUE・・・

The intention is to remove the child members from member X and make it a leaf member, but the above data will result in an error. This is because, in the order of fields above, it first sets the leaf classification value of member X to TRUE and then tries to remove the child members. However, changing the leaf classification value to TRUE while still having child members is not allowed.

In this case, if the field order in the import file is corrected as follows, it will be processed successfully.

  ADD_OR_UPDATE_MEMBER , HDR , LABEL , REMOVE_CHILDREN, P:#LEAF ・・・

  ADD_OR_UPDATE_MEMBER , DTL , X , TRUE , TRUE・・・

Similar issues can occur between multiple properties. For example, the Debit/Credit Type/#DRCR property of the Account Dimension can only be set when the Data Type/#DATA_TYPE property value is FLOW or BALANCE. Therefore, the P:#DATA_TYPE field should be placed to the left of the P:#DRCR field. Note, arranging the fields in the same order as the properties appear on the dimension screen will avoid problems related to property dependencies.

Supplementary Explanation on "Parent Member Addition Instruction"

In fusion_place dimensions, each member can belong to multiple member trees. Therefore, each member can have multiple parent members. If you want to register a member under multiple parent members, provide a detail row for that member for each parent member.

For example, if you want to add a child member (label: C) under parent member A (label: P1) and parent member B (label: P2), write two detail rows for the child member C in the import file as follows:

  ADD_OR_UPDATE_MEMBER , HDR , LABEL , PARENT ・・・

  ADD_OR_UPDATE_MEMBER , DTL , P1 , , ・・・

  ADD_OR_UPDATE_MEMBER , DTL , P2 , , ・・・

  ADD_OR_UPDATE_MEMBER , DTL , C , *P1* , ・・・

  ADD_OR_UPDATE_MEMBER , DTL , C , *P2* , ・・・
Supplementary Explanation for Adding New Members

As described above, if a member with the specified label does not exist in the dimension, the member will be added. However, this does not automatically mean that the member will become a root member, nor does it mean that it will become a child member of any member.

If you want to add the member as a root member at the same time, specify "TRUE" in the "Root Registration Instruction" field. If you want it to be a child member of some member, specify the label of the parent member in the "Parent Member Addition Instruction" field.

Exporting Members

The settings and member tree structure of all existing dimension members can be output in the file format described for member import. (In the case of custom dimensions,) importing this file into another empty dimension will exactly replicate the original dimension’s content.[1]

The Encoding of the export file can be selected as either UTF-8 or Shift JIS. Before exporting, select the desired encoding on the [Manager] screen.

You can also choose the version of the Member Export Format before exporting. Select the desired version on the [Manager] screen before exporting.

Instead of outputting to a file, you can also export the same data to the clipboard and paste it into an Excel sheet. In this case, you can also import the data edited on the sheet into the dimension via the clipboard.

Operation steps:

Points to Note about Exporting Members

(1) When the Same Member Exists Under Different Root Members

In such cases, rows related to that member will appear multiple times in the export file. When importing such data, it is processed in order from the beginning. Therefore, be aware that if a member’s name or property value is changed in a row related to that member, it will be overwritten by the member’s name or property value in a subsequent row related to the same member.

The "Parent Member Addition Instruction" field is an exception to the above, where if different parent member labels are specified in multiple rows for the same member, it will be registered under all those parent members.

(2) Exporting "Members Not in Trees"

During the export process, root members are first output in parent-priority order, including themselves and their descendants, followed by "Members Not in Trees", which are also output in parent-priority order, including themselves and their descendants. "Members Not in Trees" section displays members that are not included in any member tree of root members, but be aware that when exported, their descendant members are also included.[2]

1. If the original dimension has "User-defined Properties", it is necessary to pre-set the same user-defined properties in the importing dimension.
2. The point is that being excluded from a tree does not sever the parent-child relationships underneath. This is intentional. A member cut from the member tree temporarily becomes a "Member Not in Tree", but when that member is pasted somewhere in the member tree, it should be pasted along with its descendants.