Dimension Design For Product which has assortment RRS feed

  • Question

  • Hi everyone,

    I have a question about a specific dimension design. I'm building cubes for a textile company and I'm new to design. They sell t-shirts, jeans etc. let say like Levi's. There were dimensions related to product in the previous design. The company analyze the product in a breakdown like Brand,Category,Gender,AgeGroup,Style,Material,Type etc. Every products can be described in configuration, color, size, product id. There can be product id's in products table multiple times. But there can not be multiple rows which refers to same config,color,size,product.

    There was dimensions for Brand,Category,Gender etc. I have inner joined all tables and got a view which shows records; color, config,size,product, brand and all the other attributes. And made a standalone dimension named product with a primary key like color + config + productid + size As AsortmentKey. I have build the hierarchies which would be commonly used.

    Then I said what if they want to use something which not fits the existing hierarchies like Config,Color,Brand. At this point I have been inspired by the Time dimension in Adventureworks. I have made two attributes for each column. One for using in hierarchies (for example Category has key columns as BrandId,CategoryId and Categories has only have CategoryId as key). With this design I had around 350k rows which becomes harder to process then before. 

    Is it better to do it in this way by collecting all information about product in one dimension (I think processing became a bit longer but when I use the hierarchies while querying, it seems faster) or should I go with the first design which has multiple dimensions (I have made this dimension to get rid of the crossjoins like Brand * Category * Gender * AgeGroup * ...... It was very painful to query the cube becouse color, size product dimensions were too large to crossjoin (color 300, size 100, product id had 40000 rows). Sometimes there were 10 dimensions in a crossjoin)

    I'm new to cube design and I don't know if I'm in the right way or not. I will be thankful If someone help or comment.

    Best Regards,


    Monday, February 21, 2011 10:04 PM


  • It is always tempting to completely d-normalize an entity or completely have separate dimension for each of things....

    Well I would suggest the things in following way:

    1. If the attributes are to be represented as hierarchy, then always keep the column in same table. 

            2. If the attribute is not used as a filter criteria or is not used for analysis and can be used as an additional info/ property of an product, then keep it in the same table. Expose this as an product attribute property via attribute relationship.

    3. If the attribute is commonly used for analysis and not part of hierarchy and if id's can be directly maintained against the fact, you can possibly have it as a separate dimension

    4. Do not have views to get the desired output into single query, rather have it in table. It will help performance.

    5. If an attribute has a more than property associated with it, then separate it to another dimensional table and dimension. 

    Example: Product Category if separated might just have ProdCatId and Product category name...you may very well have this is product.

    If has some additional attribute like Category Owner, Category Code etc etc then you may separate it out.

    6. Reporting needs do sometime help in deciding the data structure store.

    7. Product dimension in AW could also be used as some kind of template.


    • Marked as answer by Jerry Nee Friday, March 4, 2011 1:52 AM
    Monday, February 21, 2011 11:31 PM