Add, update, delete rows in a hierarchy

更新： 2009-04-30

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

System and updatable hierarchy columns

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

Table Key

Enter	Enter your values.
Copy	Use the same value as the 100 row 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 hierarchy table in the staging database.

Column	Add	Update	Delete
RowId	NULL	Copy	Copy
MemberId	Enter	Copy	Copy
ParentMemberId	Enter	Enter	Copy
OwnerId	Ignore	Ignore	Ignore
SequenceNumber	Ignore	Ignore	Ignore
CreateDateTime	Ignore	Ignore	Ignore
ChangeDateTime	Ignore	Ignore	Ignore
LoadingControlID	Ignore	Ignore	Ignore
Bizsystemflag	200	300	400
BizSystemErrorDetails	NULL	NULL	NULL

Example: Add, Update, Delete Rows in a Hierarchy

The following examples show how to add, update, and delete a row to an ID-based hierarchy table, H_Product_Components. This hierarchy already exists as part of the Product dimension. For more information about how to convert hierarchies to label-based tables, see About preparing hierarchy data.

This example is based on the following assumptions:

• You have created from an ID-based hierarchy table, a label-based table by running the bsp_DI_CreateHierarchyLabelTable stored procedure.

• You will convert the label-based table back to an ID-based table by running the bsp_DI_ConvertHierarchyLabelColumnToMemberId stored procedure before you validate and load data.

Add

This example in the following table shows the addition of a new row to the label-based hierarchy table, H_Product_Components_Label.

RowID	MemberId_Label	ParentMemberId_Label	OwnerId	BizSystemFlag
NULL	F08	F	0	200

Consider the following:

• MemberIds must refer to valid MemberIds of a dimension.

• The ParentMemberId of the row that you add must reference a valid MemberId in the same hierarchy.

• To add a new row, the RowID must be NULL.

Update

The example in the following table shows updates to an existing row in the label-based hierarchy table, H_Product_Components_Label.

RowID	MemberId_Label	ParentMemberId_Label	OwnerId	BizSystemFlag
62	F08	F	0	100
62	F08	H	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 may affect the data integration process negatively.

• You must not update MemberIds.

• The ParentMemberId of the row that you update must reference a valid MemberId in the same hierarchy.

Delete

The example in the following table shows updates of an existing row to the label-based hierarchy table, H_Product_Components_Label.

RowID	MemberId_Label	ParentMemberId_Label	OwnerId	BizSystemFlag
62	F08	H	0	100
62	F08	H	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.

Download this book

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

• Planning Server Planning and Architecture Guide for PerformancePoint Server 2007

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

另请参阅

概念

About setting the BizSystemFlag column