How to create a dimension for n to n parent child hierarchy relationships? RRS feed

  • Question

  • Hello All,

    I am working on a data warehouse project in which we are following the Kimball approach. Recently, I came across a situation that I don't know how to handle. From the source, I am getting some objects data which need to be placed in a single dimension. But the twist in here is, most of the objects can be in N to N parent-child relationships. All the objects have the same kind of attributes except type.

    For suppose, I have Object 1,2,3,4 from source. Here 1 & 2 can be Childs of 3, 3 & 1 can be Childs of 2, They all can be Childs of 4. So any object can be a combination of other objects.

    If I have to maintain this relationship in a single table, then the Dimension can grow at a speed of light in Vaccum :)

    If I continue to store all the attributes in the same dimension

    (my dimension will have only 4 records of Obj 1,2,3,4) and relationship(considering max no (4*4)-4) in another dimension, then it will be purely normalized.

    Can anyone please suggest any alternative fix for this?

    I am glad to hear and looking forward to your valuable suggestions.

    Thank you in advance.

    Best regards,

    Naresh Bandi.

    Friday, April 7, 2017 8:56 AM


  • If you are just designing you can set a table for any dimension and create a FK on the fact tables.
    Friday, April 7, 2017 12:00 PM

All replies

  • Hi Naresh, bear in mind that in SQL there isn't any Parent-Child concept. We don't refer the relationship as Parent-Child. However in your case I'd suggest you to create a view grouping the information you need (you can create a small cascade of Cte's for example) and keep using the view as table. The logic would be the same and the view, of course, would be update automatically; thus you shouldn't longer worried about this situation.

    Please mark as answer if this post helped you

    Friday, April 7, 2017 9:20 AM
  • Hello Diegoctn,

    Thank you for your kind reply. 

    Contextually, I have chosen the parent-child paradigm. I may be wrong here in selection. But, my requirement is to maintain the relationship between objects in a Dimension that can be many to many and it is about the Dimension structure. I appreciate your suggestion, but it can be useful for me when I start loading the hierarchical data into Dimension or to show the hierarchy from Dimension.

    I am just stuck at initial phase of dimension design, should handle the rapid growth of dimension as well follow denormalization. Where can maintain the Hierarchical relation in a dimension? Can you suggest any in this regard?

    Thanks & Regards,

    Naresh Bandi.

    Friday, April 7, 2017 10:59 AM
  • If you are just designing you can set a table for any dimension and create a FK on the fact tables.
    Friday, April 7, 2017 12:00 PM