PRB: Member Key Column for Parent-Child Dimensions Must Be Unique (317258)

MORE INFORMATION

A parent-child dimension is based on two dimension table columns that together define the lineage relationships among the members of the dimension. The member key column identifies each member, while the parent key column identifies the parent of each member. Analysis Services uses this information to create parent-child links, which it combines into a single member hierarchy that represents a single meta data level. If you create a parent-child dimension, the values contained in the member key column for each member of the dimension must be unique and the data type for the member key column must be the same as the data type for the parent key column.

If the member key column contains non-unique members, an error message similar to the following occurs:
If the data type for the member key column does not match the data type for the parent key column, the following error message occurs when Analysis Services processes the dimension:
The following error message occurs if you try to browse the dimension data:

Steps to Reproduce Behavior

To reproduce the dimension member key with the non-unique error message, follow these steps:

1. Open the FoodMart 2000.mdb database.
2. Create a new table named uniqu_data with the following structure:

   Column Name	Data Type	Field Size
   Member_Key	Text	25
   Parent_Key	Text	25

   
   
   
3. Populate the uniqu_data table with the following values:

   Member_Key	Parent_Key
   Child 1	Parent 1
   Child 2	Parent 1
   Child 3	Parent 3
   Child 1	Parent 2

   
   
   
4. Create a new parent-child dimension and use the uniqu_data table as the data source, with the following properties:

   Member Key  = Member_Key
   Parent Key  = Parent_Key
   Member Name = Member_Key
   					

5. Save, and then browse the dimension.

To reproduce the invalid parent-child error message, follow these steps:

1. Open the FoodMart 2000.mdb database.
2. Create a new table named mixed_data, with the following structure:

   Column Name	Data Type	Field Size
   Member_Key	number	Long Integer
   Parent_Key	Text	25
   Member_Name	Text	25

   
   
   
3. Populate the mixed_data table with the following values:

   Member_Key	Parent_Key	Member_Name
   1	Parent 1	Child 1
   2	Parent 1	Child 2
   3	Parent 3	Child 3
   3	Parent 2	Child 1

   
   
   
4. Create a new parent-child dimension and use the mixed_data table as the data source, with the following properties:

   Member Key  = Member_Key
   Parent Key  = Parent_Key
   Member Name = Member_Name
   					

5. Save, and then browse the dimension.

To avoid these issues when you create a parent-child dimension, make sure that the member key column for each member of the dimension is unique and that the data types for the member key and parent key columns are the same.