Ragged Product Category Hierarchy and Bridge table

View previous topic View next topic Go down

Ragged Product Category Hierarchy and Bridge table

Post  shinji23 on Fri Jan 15, 2016 6:56 pm

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.




shinji23

Posts : 11
Join date : 2012-01-19

View user profile

Back to top Go down

Re: Ragged Product Category Hierarchy and Bridge table

Post  nick_white on Mon Jan 18, 2016 8:48 am

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

nick_white

Posts : 366
Join date : 2014-01-06
Location : London

View user profile

Back to top Go down

Re: Ragged Product Category Hierarchy and Bridge table

Post  shinji23 on Tue Jan 19, 2016 6:36 pm

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








shinji23

Posts : 11
Join date : 2012-01-19

View user profile

Back to top Go down

Re: Ragged Product Category Hierarchy and Bridge table

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum