Help Populating a Heirarchy
-
We have an Accounts table that has NO predefined heirarchy.
That is to say no UniqueID and ReportsToUniqueID fields exist.
The schema is as follows:
Company
CostCenter
Account
Subsidiary
UniqueId
Level
When the table is sorted by Company, CostCenter, Account and Subsidiary
a positional heirarchy is created.
For Example (note the Levels):
Company CostCenter Account Subsidiary UniqueId Level
2 501 1800 4171494 5
2 501 1801 4171495 6
2 501 1801 ZZZ 4171498 8
2 501 1802 4171496 6
2 501 1802 ZZZ 4171499 8
2 501 1803 4171500 6
2 501 1803 ZZZ 4171501 8
2 501 1804 4171505 6
2 501 1804 DEW 4171506 7
2 501 1804 PRTI 4171507 7
What is the best way to accomplish the following (given that I have close to 5 million records)?
1. Add a ReportToAccountID field
2. Populate new field with the correct parent (from heirarchial relationships)
Desired Result:
Company CostCenter Account Subsidiary UniqueId Level ReportsToUniqueId
2 501 1800 4171494 5 4171494 (self or Null)
2 501 1801 4171495 6 4171494 (to level 5 above)
2 501 1801 ZZZ 4171498 8 4171495 (to level 6 above)
2 501 1802 4171496 6 4171494 (to level 5 above)
2 501 1802 ZZZ 4171499 8 4171496 (to level 6 above)
2 501 1803 4171500 6 4171494 (to level 5 above)
2 501 1803 ZZZ 4171501 8 4171500 (to level 6 above)
2 501 1804 4171505 6 4171494 (to level 5 above)
2 501 1804 DEW 4171506 7 4171505 (to level 6 above)
2 501 1804 PRTI 4171507 7 4171505 (to level 6 above)
THANKS Great SQL Gurus!!!!
-
How deep is the hierarchy, and how will it be queried? Are you certain you want to use an adjacency list for this? Have you considered the Nested Sets Model? I think you're going to have some difficulties querying this efficiently using an adjacency list...
Anyway...
ALTER TABLE YourTable
ADD ReportsToUniqueId INT NULL --? -- is it an int?
and then...
UPDATE YourTable
SET ReportsToUniqueId =
COALESCE((SELECT MAX(UniqueId)
FROM YourTable Y1
WHERE Y1.Level = YourTable.Level - 1
AND Y1.UniqueId < YourTable.UniqueId), UniqueId)
...
You could batch this into smaller chunks if you like. It might perform better. One way would be by level:
UPDATE YourTable
SET ReportsToUniqueId =
COALESCE((SELECT MAX(UniqueId)
FROM YourTable Y1
WHERE Y1.Level = YourTable.Level - 1
AND Y1.UniqueId < YourTable.UniqueId), UniqueId)
WHERE YourTable.Level = n
-- Run this once per level
---
another way would be based on rowcount:
SET ROWCOUNT 10000
UPDATE YourTable
SET ReportsToUniqueId =
COALESCE((SELECT MAX(UniqueId)
FROM YourTable Y1
WHERE Y1.Level = YourTable.Level - 1
AND Y1.UniqueId < YourTable.UniqueId), UniqueId)
WHERE YourTable.ReportsToUniqueId IS NULL
DO WHILE @@ROWCOUNT 0
BEGIN
UPDATE YourTable
SET ReportsToUniqueId =
COALESCE((SELECT MAX(UniqueId)
FROM YourTable Y1
WHERE Y1.Level = YourTable.Level - 1
AND Y1.UniqueId < YourTable.UniqueId), UniqueId)
WHERE YourTable.ReportsToUniqueId IS NULL
END
--
Adam Machanic
whoisactive -
Interesting... Thanks for the helpfull ideas!!!
I have just bought Joe Cleko's Trees and Hierarchies in SQL for Smarties (ISBN 1-55860-920-2)
and Joe Celko's SQL for Smarties: Advanced SQL Programming 2nd Edition (ISBN 1-55860-576-2).
Additionally, I have reviewed Advanced Transact-SQL for SQL Server 2000 by Itzik Ben-Gan and Tom Moreau (ISBN 1-893115-82-8).
So many options....
My particular limitation is that this is an ERP table of which I am limited to only using their predefined fields.
This has definetely ruled out Itzik Ben-Gan's hierarchy column that would hold the chain of the account IDs of all parent accounts.
I am interested in trying to understand how I can implement Celko's Nested Set Model Of Hierarchies.
It is my "basic" understanding that in order to get this to work, I need to:
1. Redefine two integer fields as lft and rgt fields.
2. Populate these lft and rgt fields
3. Maintain these lft and rgt fields (Updates, Insertions, and Deletions to node members)
Do I need to have a completed Adjacency List to create the Nested Sets Model?
Given my schema listed in previous post:
1. If I do not implement an Adjacency List, how could I update the Accounts table with lft and rgt values.
2. If I implement an Adjacency List, how could I update the Account table with lft and rgt values?
NOTE: The Accounts will NOT have just one single parent like an organizational chart, rather each combination of Company and CostCenter will have a root.
This implies many trees in the forest of the Accounts table.
THANKS Great SQL Gurus for your assistance and special thanks for Adam Machanic's posting!!!
-
Joe,
As Celko says in _Trees and Hierarchies_, you should define the lft and rgt columns OUTSIDE of your main table -- keep the hierarchy in a seperate table. That will help you more easily maintain it later.
IMO, it's easier to use the Nested Sets Model if you maintain both it (in its own area) for querying the full hierarchy and an adjacency list in the main table for smaller jobs. Celko may not agree with me on that, but that's not my problem
Anyway, read the appropriate sections in that book and I think you'll know the answer to your questions about how to update the tables, etc.
--
Adam Machanic
whoisactive -
Is there any easy way to convert NO model to a Nested Sets Model?
-
The reason I still think it's better to keep both is efficiency; I worked on a project in which we implemented Nested Sets for a very complex permissions system. A lot of the system was based on "direct reports" (i.e. the next level down the hierarchy only) and figuring those out in real time using only Nested Sets turned out to cause a lot of undue load on the system. Re-adding the adjacency list solved that problem, and we were still able to take advantage of the Nested Sets for the deeper (and much less frequent) queries into the hierarchy.
--
Adam Machanic
whoisactive -
-
Recall from the orginal post: When the table is sorted by Company, CostCenter, Account and Subsidiary a positional heirarchy is created.
Am I correctly assuming that becuase there is no physically implemented heirarchy that this means that I MUST implement an adjacency list within the table in order to provide a manner in which to create the Nested Sets?
-
Joe,
You already have a Nested Sets model -- kind of. You should be able to write a cursor against your ordered select, and use that to insert row-by-row into your nested sets hierarchy table.
--
Adam Machanic
whoisactive
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply