Master Data - Hierarchies Creation, Change and Maintenance

  • Dears,

    Hope this message finds you well

    I am now moving into a Master Data position and my experience with Hierarchies is very low.

    I was requested to create an application that can manage hierarchies and that can help to create new hierarchies based on existing ones

    High level Requirements:

    1. Application should be connected to the datalake (synapse or bigquery)
    2. It shoud allow to create hierarchies with different levels
    3. It should allow as well to create hierarchies based on existing ones, allowing to change the levels. Like, based on one hierarchi, create another where the levels are changed (what was in level 3 is now level 5 and what was level 5 is now level 4)
    4. It needs as well to allow those hierarchies to be sent back to the datalake al in a SQL Structure

     

    Can you please help me understand what can I do to create this? Shall I use some language to built it? or should I use something already built? like an app to manage hierarchies?

    What are you using inside your organizations?

     

    Thank you very much

    Pedro

     

     

     

     

     

    • This topic was modified 1 year, 4 months ago by  river1.
  • Thanks for posting your issue and hopefully someone will answer soon.

    This is an automated bump to increase visibility of your question.

  • This was removed by the editor as SPAM

  • This is a big topic... To big for a simple forum reply. So rather that dive into details, I would suggest you start with an "adjacency list" model. For example, and "Employee" table that has a "ManagerID" column that references the managers EmployeeID. If an employees moves to a new manager, you simply update the ManagerID value accordingly.

    Once you have and understanding of how adjacency lists work, you can delve into more advanced things like "nested sets".

    This, in my opinion, is still the best article about nested sets available anywhere... Hierarchies on Steroids #1: Convert an Adjacency List to Nested Sets

  • If the hierarchies are DAGs (Directed Acyclic Graphs) with no loop backs, no "crossed legs", and are true "Adjacency List" (parent/child only) hierarchies, then I recommend that you study the following two articles.  If nothing else, you'll learn about the advantages and disadvantages of 3 different type of hierarchies.

    If you know me, then you'll also know that there are some serious performance techniques to be had there, as well.  The code on today's machines only takes 19 seconds instead of the 54 (compared to 2 and 1/2 days) advertised in the articles.

    https://www.sqlservercentral.com/articles/hierarchies-on-steroids-1-convert-an-adjacency-list-to-nested-sets

    https://www.sqlservercentral.com/articles/hierarchies-on-steroids-2-a-replacement-for-nested-sets-calculations-1

     

     

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Thank you Jason, but what are you using inside your company to manage hierarchies? are you using SQL Server MDM ? you do you create your own code? thanks

  • Thank you Jeff, but what are you using inside your company to manage hierarchies? are you using SQL Server MDM ? you do you create your own code? thanks

  • river1 wrote:

    Thank you Jeff, but what are you using inside your company to manage hierarchies? are you using SQL Server MDM ? you do you create your own code? thanks

    You've not identified what type of hierarchies YOU need to manage.  I'll also tell you that the hierarchies that we have are trivial.  For example, a table that only contains 2 levels as an Adjacency List.  No need for store-bought code there, for sure.

    You've also no identified what you already know about hierarchies.  For example, do y0u know what an "Adjacency List" hierarchy is?

    On that note, have you studied the first article that both Jason and I have recommended to get your feet wet?

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Jason A. Long wrote:

    This, in my opinion, is still the best article about nested sets available anywhere... Hierarchies on Steroids #1: Convert an Adjacency List to Nested Sets

    1000% this!

    Despite being almost a decade old I use many of the techniques and ideas mentioned in that article today.  The best part is once you get it up and running it just works.  I haven't touched my process since I set it up.  My Developers and Sharepoint Administrator heavily rely on my hierarchy table.  Keep in mind this works for hierarchies in general.  The adjacency list doesn't have to be employees and managers.  My table uses alphanumeric codes for a reporting structure based on job positions.  Then, once you start using nested sets then things really start getting fun!  You can derive so much useful information at blazing speeds and using the simplest queries.


    SELECT quote FROM brain WHERE original = 1
    0 rows returned

  • Thank you both for the feedback and the very kind words.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Jeff Moden wrote:

    Thank you both for the feedback and the very kind words.

    Simply giving credit where credit is due. Prior to this particular article, the benefits of nested sets were known (mainly thanks to he who shall not be named) but they were not a practical option due to the difficulties involved in updating the left/right bower values. Your work and the resulting article changed that.

    And I can assure you that there are far more than the two of us that have benefited from it.

  • Thanks, Jason.  I have to tell you, you've made my month.  I had no idea about that and I sometimes wonder to myself whether or not I accomplished my goal with any article and I end up asking myself "Did that actually help anyone"?  Seriously... thank you again.  You and YB both.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Jeff Moden wrote:

    Thanks, Jason.  I have to tell you, you've made my month.  I had no idea about that and I sometimes wonder to myself whether or not I accomplished my goal with any article and I end up asking myself "Did that actually help anyone"?  Seriously... thank you again.  You and YB both.

    If that's the case, then  I (and others) owe you an apology.

    Speaking only for myself, I never know exactly where the line is between expressing appreciation and sounding like a sycophant, so I end up erroring on the side of saying very little or nothing at all in the comments. For that I do apologize.

    You do deserve to be recognized for the excellent work you do here and you should have no doubt that you are helping people. I've been reading your work for nearly 2 decades now and the information you have deiminated in that time has had a very real impact on my career.

  • Wow.  Thanks Jason.  I seriously had no clue.  Like I said, my aim is to help, not collect accolades for a resume but that comment just made the last 20 years of posting worth it.  Thank you, good sir.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Jeff Moden wrote:

    Wow.  Thanks Jason.  I seriously had no clue.  Like I said, my aim is to help, not collect accolades for a resume but that comment just made the last 20 years of posting worth it.  Thank you, good sir.

    Sorry for the slow reply...

    Of course, you're welcome and thank you for all your good work.

Viewing 15 posts - 1 through 15 (of 17 total)

You must be logged in to reply to this topic. Login to reply