Prepare self-referencing linked dimension data

Aktualisiert: 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.

  Hinweis:
  Creating cyclical references within dimensions is not supported in Monitoring Server. 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 Planning Server.

  • 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 Planning Business Modeler, 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 Planning Business Modeler, 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:

• Planning Server Planning and Architecture Guide for PerformancePoint Server 2007

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

Siehe auch

Konzepte

About preparing dimension data