Period_hierarchy
The syntax for Microsoft SQL Server is:
select * from period_hierarchy()
where top_mem in(
select mem_id
from ctrain.period
where mem_name like '{Period}’)When {Period} is replaced by a selected member Quarter 1 from Period dimension, the result is:
| mem_id | mem_pid | level | top_mem | top_name |
|---|---|---|---|---|
| 4 | -1 | 1 | 4 | Quarter 1 |
| 8 | 4 | 2 | 4 | Quarter 1 |
| 9 | 4 | 2 | 4 | Quarter 1 |
| 10 | 4 | 2 | 4 | Quarter 1 |
The top_mem column contains 4 which corresponding to Quarter 1. The mem_id column contains 4 (self) as well.