Master Data - Hierarchies Creation, Change and Maintenance

Question

Post reply

Master Data - Hierarchies Creation, Change and Maintenance

river1

SSC-Insane

Points: 24309
More actions
February 23, 2023 at 2:30 pm

#4153295
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, 9 months ago by river1.
February 28, 2023 at 12:03 pm

This was removed by the editor as SPAM

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

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

Site Owners SSC Guru Points: 80330 More actions · Answer 1

Thanks for posting your issue and hopefully someone will answer soon.

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

Jason A. Long SSC-Insane Points: 23786 More actions · Answer 2

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

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 3

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)

river1 SSC-Insane Points: 24309 More actions · Answer 4

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

river1 SSC-Insane Points: 24309 More actions · Answer 5

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

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 6

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)

Y.B. SSChampion Points: 11649 More actions · Answer 7

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

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 8

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)

Jason A. Long SSC-Insane Points: 23786 More actions · Answer 9

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.

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 10

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)

Jason A. Long SSC-Insane Points: 23786 More actions · Answer 11

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.

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 12

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)

Jason A. Long SSC-Insane Points: 23786 More actions · Answer 13

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.