Custx_hierarchy
The syntax for Microsoft SQL Server is:
select *from cust1_hierarchy()
where top_mem in(
select mem_id
from ctrain.cust1
where mem_name like '{Product}'
)When {Product} is replaced by the consolidated member Game Machines from the Product dimension, the result is:
| mem_id | mem_pid | level | top_mem |
|---|---|---|---|
| 20 | -1 | 1 | 20 |
| 4 | 20 | 2 | 20 |
| 21 | 20 | 2 | 20 |
| 5 | 4 | 3 | 20 |
| 6 | 4 | 3 | 20 |
| 7 | 4 | 3 | 20 |
| 2 | 21 | 3 | 30 |
| 3 | 21 | 3 | 20 |
| 22 | 21 | 3 | 20 |
Tthe top_mem column contains 20 which corresponds to Game Machines. The mem_id column contain 20 (self) as well.