Add, update, delete rows in a measure group

Aggiornato: 2009-04-30

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

System and updatable Measure group 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

Enter	Enter your values here.
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

Column	Add	Update	Delete
Scenario_MemberId	Enter	Copy	Copy
Time_Month, Time_Quarter, or other.	Enter	Copy	Copy
Value	Enter	Enter	Ignore
RowId	NULL	Copy	Copy
RuleID	Ignore	Ignore	Ignore
ContextID	Ignore	Ignore	Ignore
AssignmentID	Ignore	Ignore	Ignore
CreateDateTime	Ignore	Ignore	Ignore
ChangeDateTime	Ignore	Ignore	Ignore
LoadingControlId	Ignore	Ignore	Ignore
BizSystemflag	200	300	400
BizValidationStatus	NULL	NULL	NULL
BizSystemErrorDetails	NULL	NULL	NULL
(Optional) Any dimension nor hierarchy memberId	Enter	Copy	Copy
(Optional) Other measures	Enter	Enter	Ignore

Example: Add, Update, Delete Rows in a Measure Group

This example shows how to add, update, and delete a row to an ID-based measure group table, MG_Manufacturing_BikeComponents_MeasureGroup_default_partition_Label.

This example is based on the following assumptions:

• You have created, from an ID-based measure group table, a label-based table by running the bsp_DI_CreateLabelTableForMeasureGroup stored procedure.

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

Add

The example in the following table shows the addition of a new row to the label-based measure group table, MG_CorporateCosts_MeasureGroup_default_partition_Label.

Scenario_MemberId_Label	Time_Month_Label	Account_MemberId_Label	Value	RowId	BizSystemFlag	BizValidationStatus
Forecast	October Year2004	130201	4572860.38	NULL	200	0

Consider the following:

• You must set RowID to NULL for rows that are new and that will be added.

• The MemberIDs of the dimensions in the measure group must refer to a valid MemberID in the corresponding dimension for rows.

• The MemberIDs of the hierarchies in the measure group must refer to a valid MemberID in the corresponding hierarchy for rows.

Update

The example in the following table shows updates of an existing row to the label-based measure group table, MG_CorporateCosts_MeasureGroup_default_partition_Label.

Scenario_MemberId_Label	Time_Month_Label	Account_MemberId_Label	Value	RowId	BizSystemFlag	BizValidationStatus
Forecast	October Year2004	130201	4572860.38	1771	100	0
Forecast	October Year2004	130201	4770000.00	1771	300	0

Consider the following:

• Avoid making any changes to rows where BizSystemFlag = 100; otherwise, the staging and application databases will not be synchronized.

• 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 updating.

Delete

The example in the following table shows deletion of an existing row from the converted label-based measure group table, MG_CorporateCosts_MeasureGroup_default_partition_Label.

Scenario_MemberId_Label	Time_Month_Label	Account_MemberId_Label	Value	RowId	BizSystemFlag	BizValidationStatus
Forecast	October Year2004	130201	4770000.00	1771	100	0
Forecast	October Year2004	130201	4770000.00	1771	400	0

Consider the following:

• Avoid making any changes to rows where BizSystemFlag = 100. Otherwise, the staging and application databases will not be synchronized.

• When you delete rows, you should set system columns to those of the original row (where BizSystemFlag = 100). Do not delete 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.

Vedere anche

Concetti

About setting the BizSystemFlag column