Load dimension members and member sets from a CSV file

In PerformancePoint Planning Business Modeler, you can load dimension properties such as members and member sets from a comma-separated value (CSV) file.

Important: We recommend that you use Unicode encoding to avoid errors. If the CSV file is not encoded correctly, Planning Business Modeler might misinterpret the characters in the file.

Data organization in a CSV file

Data types for properties specified in a CSV

Data type formatting rules

Restrictions on dimension property names

Special property labels for Account type, Currency, and Entity type

Data organization in a CSV file

A CSV file is a textual representation of a table. The text in a CSV file is organized into two sections, a header section and a body section. The header section specifies the labels of dimension properties. For example, the header section might specify the required properties of a dimension member: Name, Label, and Description. The body section contains the values of those properties.

For example, suppose the following table represents a simplified version of a group of Account dimension members. Notice the the first column name is Label; the name of first column must always be Label.

Label

Name

AccountTypeMemberID

ALL Members

ALL Members

Header

5005

Resort Operations

Income IC

5411

Project Sales

Income

6205

Salary

Expense

9930

Indi Tours

Unit

1000

Assets

Asset

1010

Current Assets

ST Asset

1020

Short-term deposits

Cash

This table would be represented in the following CSV file contents:

Label,Name,AccountTypeMemberIDALL Members,ALL Members,Header5005,Resort Operations,Income IC5411,Project Sales,Income6205,Salary,Expense 9930,Indi Tours,Unit1000,Assets,Asset1010,Current Assets,ST Asset1020,Short-term deposits,Cash

Notice that the word Label appears as the first word in the CSV text. The first word in the CSV text represents the name of the first column, and always must be Label.

Top of Page

Data types for properties specified in a CSV

By default, properties that are specified in a CSV file are of type String. However, you can specify a different data type for a property label or a property name. Planning Business Modeler supports the following data types for dimension properties:

DateTime

Boolean

Decimal

Double

Byte

Int16

Int32

Int64

  • To specify a property label as a numeric type, use the keyword and specifier Label: Type, where Type is a supported data type.

  • To specify a property name as a numeric type, use the keyword and specifier PropertyName: Type, where Type is a supported data type.

Note: Names and labels in a CSV file must follow naming guidelines. For more information, see About names and labels.

For example, you could include the system-defined Consolidated property, which is Boolean, in a CSV file for loading into the Account dimension. Suppose the following table represents the properties and values that you want to load:

Label

Name

AccountTypeMemberID

Consolidated

ALL Members

ALL Members

Header

False

5005

Resort Operations

Income IC

True

5411

Project Sales

Income

True

6205

Salary

Expense

True

9930

Indi Tours

Unit

False

1000

Assets

Asset

True

1010

Current Assets

ST Asset

True

1020

Short-term deposits

Cash

True

To load the properties and values in this table, use the following CSV text:

Label,Name,AccountTypeMemberID, Consolidated:BooleanALL Members,ALL Members,Header,False5005,Resort Operations,Income IC,True5411,Project Sales,Income,True6205,Salary,Expense,True 9930,Indi Tours,Unit,False1000,Assets,Asset,True1010,Current Assets,ST Asset,True1020,Short-term deposits,Cash,True

Top of Page

Data type formatting rules

Planning Business Modeler supports only locale-neutral data type formatting when you load data from a CSV file. The following table lists the different data types and their associated formatting rules.

Data type

Formatting rule

Boolean

Must be in True/False format.

Currency

Uses a period (.) as the decimal separator. May use a comma (,) as a thousands separator (that is, every three digits).

DateTime

Must be in MM/DD/YYYY format.

Double

Uses a period (.) as the decimal separator. May use a comma (,) as a thousands separator (that is, every three digits).

Integer

Cannot contain periods (.). Cannot use a comma (,) as a thousands separator (that is, every three digits).

String

There are no formatting restrictions for this data type.

Top of Page

Restrictions on dimension property names

The name Parent is reserved. If you load a member into the All Members member set with this name, it will be ignored. PerformancePoint Planning Server uses the name Parent to specify the label of the parent of a particular member.

Because Planning Business Modeler saves dimension properties into a Microsoft SQL Server database, there are some restrictions on column names. The following names are restricted:

ChangeDateTime

CreateDateTime

OwnerId

LoadingControlId

lvlPosition

If you use one of these names, you cannot save your data. In addition, an error message might appear .

Top of Page

Special property labels for Account type, Currency, and Entity type

The Account dimension, the Currency dimension, and the Entity dimension contain properties that require special handling. Planning Business Modeler displays different column headings for these properties from those in the actual property label.

Dimen sion

Column heading

Property label

Account

Account type

AccountTypeMemberId

Currency

Currency

CurrencyMemberId

Entity

Entity type

EntityTypeId

  • Account dimension    

    Planning Business Modeler displays a property column heading called Account Type for the Account dimension. However, the internal label for this property is AccountTypeMemberId.

    To set the value of one of the properties that is listed in the table by using data from a CSV file, the header section must use the corresponding system-defined property label CurrencyMemberId, AccountTypeMemberId, or EntityTypeId. In addition, for the property AccountTypeMemberId, the value of the corresponding member in the body section must be one of the predefined member names in the Account dimension, such as Interco Rec ST.

    Note:  If the CSV header file specifies a property label called Account Type, Currency, or Entity type in the CSV file, Planning Business Modeler creates a new dimension property that has that name. Planning Business Modeler does not recognize these values as the system-defined property labels: AccountTypeMemberId, CurrencyMemberId, or EntityTypeId.

Top of Page

Share Facebook Facebook Twitter Twitter Email Email

Was this information helpful?

Great! Any other feedback?

How can we improve it?

Thank you for your feedback!

×