What is a Parent-Child Dimension in SQL Server Analysis Services? Explain with an example.

A Parent-Child Dimension is a Dimension in which two attributes in the same dimension are related to each other and they together define the linear relationship among the dimension members. The first attribute which uniquely identifies each dimension member is called the Member Key Attribute and the second attribute which identifies the parent of a dimension member is called a Parent Key Attribute.

Example:- 

Consider an Employee Dimension which has EmployeeID as a Primary/Unique Key which uniquely identifies each Employee in the organization. The same Employee Dimension contains another attribute as ManagerID which identifies the Manager of an Employee and ManagerID contains one of the values present in the EmployeeID since Manager is also an Employee, hence he/she also has a record for self in the Employee Table. In this scenario, EmployeeID is the Member Key Column and ManagerID is the Parent Key Column.

Here are some highlights of a Parent-Child Dimension:

Both the Parent and the Child members exist in the same dimension.

There is a Parent-Child relationship between different members (rows) of the same dimension.

Parent Key Column in the dimension table is a Foreign Key Column (Can be physical or logical) which refers to the Member Key Column which is a Primary/Unique Key Column in the same dimension table.