Hello. I need a help with creating OLAP cube which allows to analyse many-to-many relationship between products and parent-child hierarchy of categories.
I have next relational structure:
- table "Products": "ProductID", "Name"
- table "Categories": "CategoryID", "Name", "ParentID" (where "ParentID" has FK reference to "CategoryID")
- table "ProductCategories": "ProductID", "CategoryID" - many-to-many relationship between products and categories
- table "Facts": "ProductID", ... - fact table
I need to have ability, while browsing cube, to drill-down from root categories through subcategories to associated products, which are, at the end, related to the fact table. Actually, I need to use products as leaf level of categories hierarchy. Finally, I use Dundas OLAP Services for ASP.NET to display chart, based on this analysis, and drill-down from categories to products is required.
I've created dimension "Products", dimension "Categories" with parent-child hierarchy and join them as many-to-many via intermediate measure, built on "ProductCategories" table. But if I put in cube browser "Categories" hierarchy as rows and then add here "Products" dimension, drill-down by cetegories is not working properly - I see only root level of hierarchy and all products as leafs - so intermediate subcategories are missed. Otherwise, if I put only "Categories" hierarchy as rows - it works correct, all subcategories are shown and expandable.
Could you suggest a correct way to do this? Thanks in advance.