Add, update, delete rows in a measure group
更新： 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:
See the full list of available books at Downloadable content for PerformancePoint Planning Server.