Add, update, delete rows in a dimension
更新： 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.