Ragged Product Category Hierarchy and Bridge table
2 posters
Page 1 of 1
Ragged Product Category Hierarchy and Bridge table
Hi all, I have question regarding ragged hierarchy and bridge table.
We have product dimension with over 1 Million products and 'n' number of product category with different hierarchy level. Every departments can and have their own unique product categorization and had unique level of hierarchy. And each product belongs to one or many product categories.
What would be the recommended way to design our product dimension? Our Product category is ragged hierarchy, so I would need to build a bridge table for hierarchy, but product and category also needs another bridge table to accommodate 1toM product-category relationship.
Any suggestion or advice would be appreciated.
Thank you.
We have product dimension with over 1 Million products and 'n' number of product category with different hierarchy level. Every departments can and have their own unique product categorization and had unique level of hierarchy. And each product belongs to one or many product categories.
What would be the recommended way to design our product dimension? Our Product category is ragged hierarchy, so I would need to build a bridge table for hierarchy, but product and category also needs another bridge table to accommodate 1toM product-category relationship.
Any suggestion or advice would be appreciated.
Thank you.
shinji23- Posts : 11
Join date : 2012-01-19
Re: Ragged Product Category Hierarchy and Bridge table
Can a product have many categories within the same department? If so then this looks like a m:m relationship rather than 1:m; in which case how are you expecting to report on this? I don't know what your measures are but lets assume they are sales: if you group sales value by product category you will count each sale multiple times (once per applicable product category). I assume this is not the type of result you want so how would you allow for this situation?
If you are using a BI tool then how you structure hierarchies and what tables you use are normally driven by the requirements of the BI tool and what it needs in order to be able to traverse the hierarchies
If you are using a BI tool then how you structure hierarchies and what tables you use are normally driven by the requirements of the BI tool and what it needs in order to be able to traverse the hierarchies
nick_white- Posts : 364
Join date : 2014-01-06
Location : London
Re: Ragged Product Category Hierarchy and Bridge table
Thanks for your reply Nick.
Product can only have one categories within same department. For example, 'Mac Book Pro' can be categorize as 'Notebook', 'Mobile PC', etc. but if marketing department decide to with 'Mobile PC' category, 'Mac Book Pro' can only be identified as 'Mobile PC' category for marketing department. And yes, I do not want any double count of sales. We want report to show consistence sales regardless of product categorization. But if department wants to run report based on their own unique categorization, I want our model to be able to support custom product category sales report without worrying about double fact counting.
This is my current design (Will ER diagram later):
Fact_Sales - Dim_Product <> Product_Category_Bridge <> Dim_Category - Category_Hierarchy_Path
Product can only have one categories within same department. For example, 'Mac Book Pro' can be categorize as 'Notebook', 'Mobile PC', etc. but if marketing department decide to with 'Mobile PC' category, 'Mac Book Pro' can only be identified as 'Mobile PC' category for marketing department. And yes, I do not want any double count of sales. We want report to show consistence sales regardless of product categorization. But if department wants to run report based on their own unique categorization, I want our model to be able to support custom product category sales report without worrying about double fact counting.
This is my current design (Will ER diagram later):
Fact_Sales - Dim_Product <> Product_Category_Bridge <> Dim_Category - Category_Hierarchy_Path
shinji23- Posts : 11
Join date : 2012-01-19
Similar topics
» Ragged Hierarchy with Bridge table
» [Solved] Ragged Hierarchy, Bridge Table and SCD2
» Need help with Bridge Table Design for: 1) Ragged-Hierarchy 2) Parent Nodes can be used more than once.
» Prod Dimension used with Product Hierarchy Bridge table
» Populating the Ragged ORG Bridge table
» [Solved] Ragged Hierarchy, Bridge Table and SCD2
» Need help with Bridge Table Design for: 1) Ragged-Hierarchy 2) Parent Nodes can be used more than once.
» Prod Dimension used with Product Hierarchy Bridge table
» Populating the Ragged ORG Bridge table
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum