Dimension import file formats

When you import a file into a dimension, the file must be structured and formatted in a certain way to get the results that you want.

File formats

The import data must be saved in a format, such as comma-separated (.csv) or with any other standard delimiter such as tab, space, semicolon, colon, vertical line, or caret (^).

Note: Files that contain a Byte Order Marker (BOM) might be corrupted when the file is imported.

Parent-child dimension structure

If you want to create a dimension with a parent-child structure, the import file must have two columns, and the levels are defined in a parent-child relationship. Any extra columns are ignored. Headings are optional. A member can be both a child and a parent; the members Wales, England, Scotland, and Northern Ireland are both children and parents. An example of a dimension that was created by importing a file that is structured to have parent-child relationships is shown in the following picture.

United kingdom dimension with parent-child relationship

The example can be created with a .csv file that contains either of the following formats.

Column-separated:

The first column contains the child dimension members; the second column contains the parent dimension members.

Column 1	Column 2
Belfast	Northern Ireland
Birmingham	England
Bristol	England
Cardiff	Wales
Dundee	Scotland
Glasgow	Scotland
York	England
Inverness	Scotland
London	England
Manchester	England
Newcastle	England
Swansea	Wales
Wales	Great Britain
England	Great Britain
Scotland	Great Britain
Northern Ireland	Great Britain

Comma-separated:

The value preceding the comma contains the child dimension members and the value after the comma contains the parent dimension members.

Belfast, Northern Ireland
Birmingham, England
Bristol, England
Cardiff, Wales
Dundee, Scotland
Glasgow, Scotland
York, England
Inverness, Scotland
London, England
Manchester, England
Newcastle, England
Swansea, Wales
Wales, Great Britain
England, Great Britain
Scotland, Great Britain
Northern Ireland, Great Britain

Tip: Some text editing applications may add additional metadata to your .csv file, which can affect the import process. For the best results, use a simple text editor for data that's comma-separated.

Importing attributes, weights, and member type

You can import attributes, weights, and member types alongside members. Your file should have a column of data for the attribute, weight, and member type. Weight can only be imported for parent-child dimension structures.

The following example has 4 columns of data, with Family as the Parent, Model as the child, Engine Size as attribute, and Weight. You can import multiple attributes, and you can import into existing attributes, or you can create a new attribute during the file import.

Type,Model,Engine Size, Weight
Budget,L Series 1.8 L Sedan,1.8,1
Budget,L Series 2.0 L Sedan,2,1
Budget,L Series 2.5 L Sedan,2.5,1
Budget,S Series 1.8 L Sedan,1.8,1
Family,L Series 1.8 L Wagon,1.8,1
Family,L Series 1.8 L Wagon 4WD,1.8,1
Family,L Series 2.0 L Wagon,2,1
Family,L Series 2.0 L Wagon 4WD,2,1
Family,S Series 3.0 L Wagon 4WD,3,1

The following example has 2 columns of data. The first column is a leaf member, and the second column is the member type. The member type can be string (s), or number (n).

Name/Desc,s
Job Type,s
Job Code,s
FTE,n
50P Salary,n
Current Salary,n
Merit Pd,s

Member names

The member names that are contained in the source files should follow Naming conventions.

If the source file has multiple occurrences of a member name, the member is created by using the first occurrence of the member name.

You can have both mixed-case characters and spaces in member names, but case and spaces are ignored when member names are stored. This means that North America, NorthAmerica, and north america are equivalent member names in a database. If you dropped the following file onto the dimension editor, you see the member North America in your hierarchy, as that is the first occurrence that is encountered during file processing.

.csv file showing North America in mixed case, NorthAmerica without spaces, and north america in lower case with a space

File size

No file size limit applies when you drop text files into the dimension editor, but large files might cause your web browser to timeout while you wait for a response. The text file is still processed and the members are inserted into the dimension hierarchy.