locked
Parent-Child-Hierarchy with two tables RRS feed

  • Question

  • Hi,

    I need to implement a dimension with products. Products are structured in product lines with parent-child hierarchies.  The products and the product lines are stored in different tables:

    Table_Products (Part_ID, Description, Productline_ID, …)

    Table_Product_Lines (Productline_ID, Description, …)

     

    Is it possible to build one dimension with these tables?

    I was able to build a parent-child-hierarchy  based on Table_Product_Line. But I was not able to connect the products with the product_lines.

     

    Hierarchy should look like this:

    Line 1

       Line 2

          Line 3

             Product 1

             Product 2

            

    Line 4

       Product 10

       Product 11

      

    Line 5

       Line 6

          Product 20

          Product 21

         

     

    Is it possible to do this with the current table structure? If yes, I would be glad if someone could give me advice how to achive this.

     

    thx

     

    Wednesday, December 16, 2009 12:38 PM

Answers

  • If you are not maintaining the Level depth and going only by KEy and Parent Key thats fine.

    Regarding Uniqueness if you already have the keys unique thats fine. But you need to make sure that in future this wont break. Meaning Key of Product Line should not be equal to Product Key any time.

    If you forsee this problem , you might want to append some characters or number (either as suffix or prepix) to your product Line table or always Increment your products by a bigger numbers to get the uniqueness .

    After addressing those 2 items, you should be in a position to get a view or named query as mentioned below

    Select Product_Line_ID,Product_LineName,Product_Line_ParentKEY
    From ProductLineTable
    Union All
    Select Product_ID as Product_Line_ID,Product_name as Product_LineName as , Product_Line_ID as Product_Line_ParentKEY

    Does this work for you?




    Thanks, Ashok -http://dugaputiashok.blogspot.com/
    • Proposed as answer by Sven Bossenmaier Wednesday, December 16, 2009 4:29 PM
    • Marked as answer by hpo_mh Wednesday, December 16, 2009 4:44 PM
    Wednesday, December 16, 2009 1:32 PM
  • I copied your problem to a sample i read in a book, there it's like that:

    EmployeeId
    ParentId
    Name

    There a hierarchy was build where managers have teams, and a manager is in a team. So you have the problem you was refering to, that one EmployeeId is similar to the ParentId.

     It's called an unbalanced hierarchy and is modeld as "Parent-Child-Hierarchy".

    They solved it by putting the Attributes (EmployeeId, ParentId) into the attribute field. Then they set the usage of the ParentId to "parent".
    Then you build your hierarchy like this: ParentId -> EmployeeId


    You should have a setup like that then:

    Mr. Burns
                  |_ Mr. Burns
                  |_ Mr. Smithers
                  |_ Mr. Simpson


    If you want to dissappear the parent in the list you have to set the option "MembersWithData" to "NonLeafDataHidden" then it should be like:

    Mr. Burns
                  |_ Mr. Smithers
                  |_ Mr. Simpson


    Hope i could help you. If you have question ask :-)


    Sven
    • Proposed as answer by Sven Bossenmaier Wednesday, December 16, 2009 4:29 PM
    • Marked as answer by hpo_mh Wednesday, December 16, 2009 4:44 PM
    Wednesday, December 16, 2009 3:02 PM

All replies

  • I think of that layout:

    Table_Line(LineId, ParentLineId, ProductId, Description)
    Table_Product(ProductId, Description)

    Then you should be able to build the hierarchy.

    Between Line and ParentLine you have to set a parent-child hierarchy. To do that you have to include the Attributes to the Dimensionstructure. Then you shuold set the Usage Option of the ParentLine to "parent".


    Sven
    Wednesday, December 16, 2009 12:50 PM
  • Hi ,

    You may have to build a new view or named query to join the Product Line and Product table using Union ALL. But there are 2 key items that you need to handle.

    1. Handling the level depth.
    2. Managing the Product Line and Product Keys unique.

    So to answer these items, What is the strcture of your Product Line table. How are you Identifying the Level  (Level depth) of the Product Line hierarchy?Is your Product Line Key (Surrogate key) & Product keys are unique in 2 dimensions?



    Thanks, Ashok -http://dugaputiashok.blogspot.com/
    Wednesday, December 16, 2009 12:53 PM
  • Hi Sven,

    this means, I have to create a new keyfield, because LineId isn´t unique any longer and the line table will be much bigger (because I need a record for every product, right?)... Ok, I will try it.

    Thanks.

     
    Wednesday, December 16, 2009 1:15 PM
  • Hi Ashok,

    the level could only be identified by the parent-child-hierarchiy. There is no additional attribute like "Level" in the table.
    The surrogate keys are at the moment only unique inside one dimension. This means I have to reload the data into our data warehouse. Thats time consuming, but if theres no other way, I will have to do it.

    Thank you.
     
    Wednesday, December 16, 2009 1:24 PM
  • If you are not maintaining the Level depth and going only by KEy and Parent Key thats fine.

    Regarding Uniqueness if you already have the keys unique thats fine. But you need to make sure that in future this wont break. Meaning Key of Product Line should not be equal to Product Key any time.

    If you forsee this problem , you might want to append some characters or number (either as suffix or prepix) to your product Line table or always Increment your products by a bigger numbers to get the uniqueness .

    After addressing those 2 items, you should be in a position to get a view or named query as mentioned below

    Select Product_Line_ID,Product_LineName,Product_Line_ParentKEY
    From ProductLineTable
    Union All
    Select Product_ID as Product_Line_ID,Product_name as Product_LineName as , Product_Line_ID as Product_Line_ParentKEY

    Does this work for you?




    Thanks, Ashok -http://dugaputiashok.blogspot.com/
    • Proposed as answer by Sven Bossenmaier Wednesday, December 16, 2009 4:29 PM
    • Marked as answer by hpo_mh Wednesday, December 16, 2009 4:44 PM
    Wednesday, December 16, 2009 1:32 PM
  • I copied your problem to a sample i read in a book, there it's like that:

    EmployeeId
    ParentId
    Name

    There a hierarchy was build where managers have teams, and a manager is in a team. So you have the problem you was refering to, that one EmployeeId is similar to the ParentId.

     It's called an unbalanced hierarchy and is modeld as "Parent-Child-Hierarchy".

    They solved it by putting the Attributes (EmployeeId, ParentId) into the attribute field. Then they set the usage of the ParentId to "parent".
    Then you build your hierarchy like this: ParentId -> EmployeeId


    You should have a setup like that then:

    Mr. Burns
                  |_ Mr. Burns
                  |_ Mr. Smithers
                  |_ Mr. Simpson


    If you want to dissappear the parent in the list you have to set the option "MembersWithData" to "NonLeafDataHidden" then it should be like:

    Mr. Burns
                  |_ Mr. Smithers
                  |_ Mr. Simpson


    Hope i could help you. If you have question ask :-)


    Sven
    • Proposed as answer by Sven Bossenmaier Wednesday, December 16, 2009 4:29 PM
    • Marked as answer by hpo_mh Wednesday, December 16, 2009 4:44 PM
    Wednesday, December 16, 2009 3:02 PM
  • Hi Ashok,
    hi Sven,

    with your help I finally managed the problem. I used a view combining the two tables. After defining the relationships between the fact tables and the new view, and setting the option "MembersWithData" to "NonLeafDataHidden" I was able to build a parent-child-hierarchy in that way I need it. 

    Thank you very much!

    Wednesday, December 16, 2009 4:15 PM