Cube/Dim/Hierarchy design question: about self-referencing and 1-Many relationship.

simply put, there 2 tables: table1 self-referencing; table1 , table2 has 1-to-many relationship.

so:
in table1: id(key), pid(parent), name
in table2: id(key), id1(connect table1.id), value

i create a dim, call dimtable2(main table table2), need hierarchy of name.value
however, the hierarchy doesn't show the parent-child structure! the name.value on same level, wrong because it should show parent-child structure of name, have value each one.

any ideas appreciated!






 

---

jim wang - mvp dynamics crm - http://jianwang.blogspot.com, http://mscrm.cn

".. how represent self-referencing database hierarchy(without using attribute hierarchy(pid in case))? .." - guess i'm drawing blank here. self-referencing hierarchies typically modelled parent-child hierarchies in ssas - asking how model them differently in ssas? if so, there techniques "naturalize" such hierarchies - tool below:

analysis services parent-child dimension naturalizer

parent/child dimensions, wherein each member references id of parent member rather being assigned specific level of hierarchy, can cause poor performance in analysis services databases. 1 way deal problem convert poorly performing p/c dimension table natural one. instead of structure wherein each member id column corresponds parent id column, must traversed recursively until root of hierarchy tree reached in order determine full tree structure, converted table contains 1 column each level of hierarchy in addition member id, , each ancestor id of member specified in column associated level in hierarchy. prevents requirement traversal of hierarchy tree in order identify each member's location in hierarchy.
...

---

- deepak

SQL Server  >  SQL Server Analysis Services