Back in pre-history, I posted a question about a recursive query to get "course depth/sequence" to determine which courses a student could take etc. It's here (CTE to determine Course Level).
I have a similar question, but this is a single table where Employee(Manager)---Manages===Employee, so the structure is something like this:
CREATE TABLE Employees
(
EmployeeID INT IDENTITY,
ManagerID INT DEFAULT NULL,
FirstName VARCHAR(20) NOT NULL,
LastName VARCHAR(20) NOT NULL,
Position VARCHAR(50) NOT NULL,
CONSTRAINT pk_Employees PRIMARY KEY (EmployeeID),
CONSTRAINT fk_ManagerID FOREIGN KEY (ManagerID) REFERENCES Employees(EmployeeID)
);
and some data... (not sure it matters, but...)
GO
INSERT INTO Employees(FirstName, LastName, Position, ManagerID)
VALUES ('Agnello', 'Dellacroce', 'Mob Boss', NULL);
DECLARE @ManagerID INT;
SELECT @ManagerID = EmployeeID
FROM Employees WHERE FirstName = 'Agnello';
INSERT INTO Employees(FirstName, LastName, Position, ManagerID)
VALUES ('Frank', 'Bruno', 'DiBruno Family Boss', @ManagerID);
INSERT INTO Employees(FirstName, LastName, Position, ManagerID)
VALUES ('Vito', 'Corleone', 'Corleone Family Boss', @ManagerID);
SELECT @ManagerID = EmployeeID
FROM Employees WHERE FirstName = 'Frank'
AND LastName = 'Bruno';
INSERT INTO Employees(FirstName, LastName, Position, ManagerID)
VALUES ('Nicky', 'Scarfo', 'Philly Capo', @ManagerID);
At what point do I have to convert this to an adjacency list? What I'm trying to implement is basically @ManagerID can only see his own underlings. (In theory, I could do this in PowerBI, but I don't have a clue how it would work!) But I can call a stored procedure from PowerBI and let SQL Server do all the dirty work for me, and then I just use PowerBI for reporting.
Is there further reading I should be doing?
What's all this for?
Each Capo can only see his own underlings, so Agnello can see whatever he wants to, because he's the big boss. Scarfo, because he's under Scarfo, can only see his own organization. (Same with Vito... he can see his own family and underlings, but nobody else's). (basically down his organization 'tree' of descendants).
And once I get all this sorted, I need to use that to do Security for PowerBI. <g>
"That's not how any of this stuff works!" right?
Am I even on the right track? If not, please point me in the right direction.
Thanks for reading!
Pieter
March 12, 2024 at 10:05 am
It depends!
Your other options are Hierarchyid or nested sets.
Security can also be done in SQL Server with RLS.
March 12, 2024 at 3:12 pm
Okay, guess I have some reading to do. The basic idea is that any node on the tree can see all his underlings, but nothing above that, and nothing on adjacent trees/branches. It's really for a PowerBI-ish report. And I was reading that DAX can't do a proper hierarchy.
Thanks!
Sort of... the answer (well, it's PowerBI and DAX, so of course Marco Russo answered it on YouTube...)
In case anybody is interested, it's here: Managing hierarchical organizations in Power BI security roles (RLS)
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply