Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    Join Date
    Feb 2004
    Location
    Cleveland, OH
    Posts
    10

    Unanswered: Dynamic Heriarchical Data

    I have a situation where I have a lot of dynamic heirarchical data. I work for a manufacturing company, and as such, items we produce have Bills-of Materials (BOM). Some of our BOMs have items on them that have BOMs as well. This nesting can go as deep as 20 levels. Some components on each of the BOMs have a flag set on them.

    I need to know the best way to drill through all of these BOMs to get a list of all the items that are flagged.

    I tried to sketch out what I'm talking about in the attached image.
    Attached Thumbnails Attached Thumbnails hier.jpg  

  2. #2
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401

    Re: Dynamic Heriarchical Data

    Originally posted by joshplummer
    I have a situation where I have a lot of dynamic heirarchical data. I work for a manufacturing company, and as such, items we produce have Bills-of Materials (BOM). Some of our BOMs have items on them that have BOMs as well. This nesting can go as deep as 20 levels. Some components on each of the BOMs have a flag set on them.

    I need to know the best way to drill through all of these BOMs to get a list of all the items that are flagged.

    I tried to sketch out what I'm talking about in the attached image.
    Can you provide us with DDL and some sample data to play around with ??
    Get yourself a copy of the The Holy Book

    order has no physical Brett in The meaning of a Kaiser . -database data

  3. #3
    Join Date
    Feb 2004
    Location
    Cleveland, OH
    Posts
    10

    Further Description

    I don't know if I can get exactly what you need. I can describe it further if that would help. It is fairly complex:

    Table
    -BOM_Master
    Fields
    -BOM Version [PK] (varchar(20))
    -BOM ID [PK] - Is actually an Item # from our Inventory DB (varchar(20))
    -BOM Date (Date)

    Table
    -BOM_Version
    Fields
    -BOM Version [PK][FK-BOM_Master] (varchar(20))
    -BOM Version Date (Date)

    Table
    -BOM_Detail
    Fields
    -BOM Version [PK][FK-BOM_Master] (varchar(20))
    -BOM ID [PK][FK-BOM_Master] (varchar(20))
    -BOM Line [PK] (Int)
    -BOM Line Description (varchar(50))
    -BOM Flag (bit)

    This is a simplified version of our actual tables, and since this is our business system, I shouldn't post DDL or data since the sample data we use is our real data, just in a test environment.

  4. #4
    Join Date
    Jan 2004
    Location
    Ottawa, Canada
    Posts
    53
    First I am assuming that all components at each level have the same structure and that the BOM cannot be recursive (ie BOM1 cannot be called at a lower level); this makes logical sense BUT data entry is data entry.

    If you create a parent-child relationship on each BOM the level which it is at could become irelevant.

    By passing the BOM_ID of the first level you want to search, a recursive query (read CURSOR -- Sorry Brett) can return a recordset (temporary table? - sorry again Brett) including the search level defined as the recurrsion number in the CURSOR. This recordset can be used to build your output. The output would be based on passing the Child_BOM_ID as the parameter of the next query.

    I don't have time right now to example the query but I am doing something similar for an organization hierarchy.

  5. #5
    Join Date
    Feb 2004
    Location
    Cleveland, OH
    Posts
    10
    You are correct. A BOM cannot call itself. So, BOM1 cannot have BOM1 as a component.

    I thought about the recursive function, but haven't attempted it yet. I have done that before for a location heirarchy. But, for some reason, this seems more complicated, and I can't nail down exactly what to do.

  6. #6
    Join Date
    Nov 2003
    Location
    Edinburgh
    Posts
    149

    Cool

    Transact-SQL Cookbook by Ales Spetic & Jonathan Gennick.

    This book has a whole chapter devoted on hierarchies in SQL Server.

    Check out www.oreilly.com for more information.

    Hope this helps.

  7. #7
    Join Date
    Jan 2004
    Location
    Ottawa, Canada
    Posts
    53
    You will need to create a reference table with BOM_ID,Child BOM_ID.
    The BOM_ID can be duplicates to include multiple Children. the joint BOM_ID + Child BOM_ID will create a unique record.

    When a BOM is added, the subsequent children must be added. Peferably this table should be populated with the subcomponents first then referenced by its parent component.

    Running a CURSOR through this table will allow you to build all of the required BOM entries in a temporary table which can then be linked with the master components table to generate your output.

    It is important that each "widget" is regarded as a potential parent and a potential child. This should not be any more complex than any other Cursor based query.

    The only concren that I have is for the following example:
    the inital BOM calls for 3 typeA screws a sublevel BOM calls for 2 more typeA screws.

    Do they get reported seperately or within a common pool?

  8. #8
    Join Date
    Feb 2004
    Location
    Cleveland, OH
    Posts
    10
    Unfortunatley, I don't have control over how the data is created. I have to work with the structure that is created by the applicaion, which isn't very friendly with objects outside of it's own little world.

  9. #9
    Join Date
    Jan 2004
    Location
    Ottawa, Canada
    Posts
    53

    Re: Further Description

    Josh,

    This is ugly but it could work.

    The flag is actually irrelevant. The inner join of BOM_Detail to BOM_Master will supercede it (or match it)

    It should be possible to do this but you will need to maintain three tables in your cursor:

    the accumulated BOMS [ACCUM_BOM_DETAIL]
    the BOMS to search on for children [SEARCH_BOM_DETAIL]
    the BOMS returned from the search [RETURNED_BOM_DETAIL]

    Thus, start with the initial BOM as the search in [SEARCH_BOM_DETAIL]
    1 - WITH CURSOR, return all BOMS at the next level (append to [RETURNED_BOM_DETAIL])
    2 -when CURSOR EOF ([SEARCH_BOM_DETAIL]) append [SEARCH_BOM_DETAIL] records into [ACCUM_BOM_DETAIL]
    3 - clear [SEARCH_BOM_DETAIL]
    4 - copy [RETURNED_BOM_DETAIL] into [SEARCH_BOM_DETAIL] (next
    5 - clear [RETURNED_BOM_DETAIL]

    run steps 1-5 until the SEARCH returns a NULL Recordset.

    The result will be the BOM DETAIL for every sub level. You should be abe to manage the inner joins and versioning through your query.

    Good Luck

    Originally posted by joshplummer
    I don't know if I can get exactly what you need. I can describe it further if that would help. It is fairly complex:

    Table
    -BOM_Master
    Fields
    -BOM Version [PK] (varchar(20))
    -BOM ID [PK] - Is actually an Item # from our Inventory DB (varchar(20))
    -BOM Date (Date)

    Table
    -BOM_Version
    Fields
    -BOM Version [PK][FK-BOM_Master] (varchar(20))
    -BOM Version Date (Date)

    Table
    -BOM_Detail
    Fields
    -BOM Version [PK][FK-BOM_Master] (varchar(20))
    -BOM ID [PK][FK-BOM_Master] (varchar(20))
    -BOM Line [PK] (Int)
    -BOM Line Description (varchar(50))
    -BOM Flag (bit)

    This is a simplified version of our actual tables, and since this is our business system, I shouldn't post DDL or data since the sample data we use is our real data, just in a test environment.

  10. #10
    Join Date
    Feb 2004
    Location
    Cleveland, OH
    Posts
    10

    Smile Sample Please...

    Could you post some sample code that does what you're suggesting. I don't think I've done anything like that using cursors...

    That sounds like a good idea. Any indications of performance? I know cursors can be a little cumbersome.

  11. #11
    Join Date
    Jan 2004
    Location
    Ottawa, Canada
    Posts
    53

    Re: Sample Please...

    Unfortunately I cannot.
    Paid by the hour. and don't want to lose my contract. I'll see what I can do tonight but no promises.


    Originally posted by joshplummer
    Could you post some sample code that does what you're suggesting. I don't think I've done anything like that using cursors...

    That sounds like a good idea. Any indications of performance? I know cursors can be a little cumbersome.

  12. #12
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    You don't need a cursor to do this (unless you are paid by the hour).

    In the BOM_Master table, you say that ID represents an item from you inventory. Is this the value that may also represent another BOM through which you need to drill down?

    BOM_Version > BOM_Master > BOM_Detail
    Last edited by blindman; 02-25-04 at 17:43.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  13. #13
    Join Date
    Jan 2004
    Location
    Ottawa, Canada
    Posts
    53
    The only issue is the BOM within a BOM within a BOM ...

    The query needs to be recursive until there are no more BOMS (i.e. returned recordset is null). I agree that the internal queries will not need to be CURSORS, but to build the n layers of the hierarchy you need to rerun the query.

    The biggest problem will be how to handle duplicates. For example a screw is a component of the main BOM and of a level 3 component.

    Unfortunately for joshplummer this is nota simple task.


    Originally posted by blindman
    You don't need a cursor to do this (unless you are paid by the hour).

    In the BOM_Master table, you say that ID represents an item from you inventory. Is this the value that may also represent another BOM through which you need to drill down?

    BOM_Version > BOM_Master > BOM_Detail

  14. #14
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    This can be accomplished by creating a table variable, seeding it with the initial value, and then running this single skeleton SQL statement:

    while @@Rowcount > 0
    insert into TableVariable
    select Datatable.ChildData
    from DataTable
    inner join TableVariable on DataTable.ParentID = TableVariable.ID
    where not exists (select ID from TableVariable where TableVariable.ID = Datatable.ChildData)
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  15. #15
    Join Date
    Feb 2004
    Location
    Cleveland, OH
    Posts
    10

    Cool Solved!!

    I appreciate everyone's help with this. I was able to get the process working using a recursive cursor based stored proc. It's fairly simple, to be honest, and it's very quick. It screams through a 3000+ line BOM in less than a second. And, the results are spot on!

    Once again, thanks for the help everyone.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •