Designing a dimensional hierarchy
Dimensional hierarchy refers to the way we group and organize the dimensional data at multiple levels. In a hierarchical structure, there are usually one-to-many or many-to-many parent-child relationship. Examples of hierarchical structures could be organizational structures, product categories in an online store, a file system and so on. The main characteristic of the hierarchical structure is that all the nodes are identical, and they include pointers to their parent or children's nodes.
In order to achieve a dimensional hierarchy, we can use a technique called as the self-referencing relationship or self-joins within the dimension table. Let us take an example of an Employee dimension.
CREATE TABLE DimEmployee ( [employeeId] VARCHAR(20) NOT NULL, [name] VARCHAR(100), [department] VARCHAR(50), [title] VARCHAR(50), [parentEmployeeId] VARCHAR(20) )
Here, we have a column called parentEmployeeID referencing the employeeID column....