Prepare self-referencing linked dimension data

PerformancePoint Server 2007

更新: 2009-04-30

Linked dimensions reference members that belong to other dimensions. For example, the Entity dimension references the Currency dimension by MemberID in the application and staging database. The following list shows three possible scenarios for linked dimensions.

  • Linked dimensions

    Example: Dim_A -> Dim_B -> Dim_C

    This occurs when dimensions link to other dimensions. To load linked dimensions, you must load dimension C first, and then load dimension B, and then load dimension A. To learn how to prepare linked dimensions, see Prepare data linked to an attribute group table.

  • Dimensions with cyclical references

    Examples:

    Dim_A -> Dim_B -> Dim_A

    Dim_A -> Dim_B -> Dim_C -> Dim_A

    Dim_A -> Dim_B ->..... -> Dim_A

    This occurs when a dimension references another dimension and the last linked dimension is referenced to the original dimension.

    注意注意:

    Creating cyclical references within dimensions is not supported in 监控服务器. Therefore, it is not supported in data integration.

  • Self-referencing dimensions

    Example: Dim_A -> Dim_A

    This occurs when a dimension references itself by a primary and a foreign-key relationship in the same dimension.

    The following procedures show how to load source data in the form of a self-referencing dimension to the application database. The example assumes that the following steps have been completed.

    • D_Product references itself.

    • The Product dimension is synchronized to the staging database in 规划服务器.

    • The ID-based D_Product dimension is converted to a label-based table, by using the bsp_DI_CreateLabelTableForDimension stored procedure in the staging database.

    • The label-based dimension table, D_Product_Label, is correctly populated with the source data that is shown in the following table.

      MemberID Label Self_Referenced_Member_Label BizSystemFlag

      NULL

      Ski Rope

      Snow Skis

      0

      NULL

      Snow Skis

      Snow Skis

      0

      NULL

      Snowboard

      Snow Skis

      0

      NULL

      Ski Tuning

      Snow Skis

      0

Procedures

To load self-referencing dimension

  1. After you have populated the D_Product_Label table, save it as a backup so that you can load Self_Referenced_Member values with valid MemberIDs.

  2. Set Self_Referenced_Member_Label = NULL and set BizSystemFlag = 200 as shown in the following table.

    MemberID Label Self_Referenced_Member_Label BizSystemFlag

    NULL

    Ski Rope

    NULL

    200

    NULL

    Snow Skis

    NULL

    200

    NULL

    Snowboard

    NULL

    200

    NULL

    Ski Tuning

    NULL

    200

  3. Convert D_Product_Label back to IDs by using the bsp_DI_ConvertLabelColumnToMemberIdForDimension stored procedure before you load.

  4. In 规划业务建模器, load the Product dimension so that the MemberIDs are assigned and the BizSystemFlag value is set to 100 as shown in the following table.

    MemberID Label Self_Referenced_Member BizSystemFlag

    1

    Ski Rope

    NULL

    100

    2

    Snow Skis

    NULL

    100

    3

    Snowboard

    NULL

    100

    4

    Ski Tuning

    NULL

    100

  5. Use the bsp_DI_ConvertLabelColumnToMemberIdForDimension stored procedure to convert the ID-based table, D_Product, back to a label-based table. This is so that you can add the Self_Referenced_Member values from the backup table.

  6. Use your backup table and update the Self_Referenced_Member_Label column. Set BizSystemFlag = 300 in order to update the table rows with the self referenced labels as shown in the following table.

    MemberID Label Self_Referenced_Member_Label BizSystemFlag

    1

    Ski Rope

    Snow Skis

    300

    2

    Snow Skis

    Snow Skis

    300

    3

    Snowboard

    Snow Skis

    300

    4

    Ski Tuning

    Snow Skis

    300

  7. Convert the label-based table, D_Product_Label, to an ID-based table with OverWriteExistingData = 'F' as a parameter in the bsp_DI_ConvertLabelColumnToMemberIdForDimension stored procedure as shown in the following table.

    MemberID Label Self_Referenced_Member BizSystemFlag

    1

    Ski Rope

    NULL

    100

    2

    Snow Skis

    NULL

    100

    3

    Snowboard

    NULL

    100

    4

    Ski Tuning

    NULL

    100

    1

    Ski Rope

    2

    300

    2

    Snow Skis

    2

    300

    3

    Snowboard

    2

    300

    4

    Ski Tuning

    2

    300

  8. In 规划业务建模器, load the Product dimension to update the table with the Self_Referenced_Member values. You can synchronize the Product dimension and check the staging database to see that the BizSystemFlag is set to 100 and data has been updated and loaded correctly.

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.

另请参阅

显示: