Add, update, delete rows in a dimension

PerformancePoint Server 2007

更新: 2009-04-30

This topic describes what you must do to add, update, or delete rows in a dimension table that is derived from the application database. You can do this by using the BizSystemFlag column in the staging database.

System and updatable dimension columns

The following table key lists the various settings that are available for adding, updating, and deleting rows in the dimension table in the staging database.

Table Key

Entry Description

Enter

Enter your values.

Copy

Use the same value as the 100 row that you are changing.

Ignore

This is a system column and must be ignored.

NULL

Set these columns to NULL.

Recommended settings

The following table contains our recommended settings for adding, updating, and deleting rows in the dimension table in the staging database.

Column Add Update Delete

MemberId

NULL

Copy

Copy

Label

Enter

Copy

Copy

Description

Enter

Enter

Ignore  

Name

Enter

Enter

Ignore  

SequenceNumber

Ignore  

Ignore  

Ignore  

SourceMemberId

Ignore  

Ignore  

Ignore  

OwnderId

Ignore  

Copy

Copy

CreateDateTime

Ignore  

Ignore  

Ignore  

ChangeDateTime

Ignore  

Ignore  

Ignore  

LoadingControlId

Ignore  

Ignore  

 Ignore  

BizSystemFlag

200

300

400

BizSystemErrorDetails

NULL

NULL

NULL

(optional) UserCreatedMemberProperty

Enter

Enter

Ignore  

Example: Add, Update, and Delete Rows in a Dimension

The following examples show how to add, update, and delete a row in the ID-based dimension table, Account.

Add

In the example in the following table, a new row is added to the dimension, D_Account. AccountTypememberId refers to an attribute group table, AG_AccountType.

MemberId Label Name AccountTypeMemberId BizSystemFlag

Null

Legal

Legal

1

200

Consider the following:

  • MemberIDs must refer to valid MemberIDs in a linked dimension.

  • MemberIDs must refer to valid MemberIDs in the attribute group.

Update

The example in the following table shows updates to Label and Name values in the dimension D_Account.

MemberId Label Name AccountTypeMemberId OwnerId BizSystemFlag

5182

Salary

Salary

4

0

100

5182

Salary

Salary

4

0

300

Consider the following:

  • When you update rows, you should enter system columns by copying the original row (where BizSystemFlag = 100).

  • Avoid making any direct changes to the row where BizSystemFlag = 100. Otherwise, the staging and application databases may not be synchronized and the data integration process may be affected negatively.

  • You cannot update rows if they reference MemberIDs that are not valid in corresponding dimension rows.

  • You cannot update rows if they do not reference valid MemberIDs in the corresponding Attribute Groups tables.

Delete

The example in the following table shows deletion of a row (where MemberId = 5203) in the dimension, D_Account.

MemberId Label Name AccountTypeMemberId OwnerId BizSystemFlag

5203

Audit

Audit

1

0

100

5203

Audit

Audit

1

0

400

Consider the following:

  • When you update rows, you should enter system columns by copying the original row (where BizSystemFlag = 100). Do not update the system columns with your own numbers. Instead, verify that they are populated with the original values of the row that you are deleting.

  • Avoid making any direct changes to rows where BizSystemFlag = 100.

Download this book

This topic is included in the following downloadable book for easier reading and printing:

See the full list of available books at Downloadable content for PerformancePoint Planning Server.

另请参阅

显示: