Forum Discussion
Custom Item Hierarchy
Currently, I have an ITEM_DIM table, and an ITEM_FACT table. My hierarchy was created within my fact table. Should I have made it in my dimension table? My dimension table just has information like item description, etc.
abergman -
Sound like in your project you'll end up with three tables: [Hierarchy Structure_DIM], [ITEM_DIM], & [ITEM_FACT].
In my implementation, as an example, I have [ORG_STRUCTURE], [EMPLOYEE], & [PAYROLL_FACT_DATA].
Ah, I see. So in my ITEM_STRUCTURE table, I would just have Level 1, Level 2, and Level 3, and the other associated measures and columns used in the article, and that's it? How would I join that hierarchy table to my ITEM_DIM table and my ITEM_FACT table?
abergman -
abergman wrote:Ah, I see. So in my ITEM_STRUCTURE table, I would just have Level 1, Level 2, and Level 3, and the other associated measures and columns used in the article, and that's it?
This is how I implemented in my project. Hard to say if similar would work for you.
abergman wrote:How would I join that hierarchy table to my ITEM_DIM table and my ITEM_FACT table?
Likely, again without knowing your details, ITEM_DIM > ITEM_STRUCTURE > ITEM_FACT.
Yeah that makes sense. To give a few more details on my tables:
ITEM_DIM:
- item #
- item description
- item class
- item subclass
- product group
- product code
ITEM_HIERARCHY:
- item #
- parent item #
- isleaf
- hierarchy path
- hierarchy depth
- level 1
- level 2
- level 3
ITEM_FACT
- qty on hand
- qty on order
- qty on work order
- qty committed
- next month forecast
So I would join my ITEM_DIM table to the HIERARCHY table on the item #. Then, I would join the HIERARCHY table to my FACT table through the same item #. Does that sound correct?
abergman -
Sound about right.