How can Model Employee Dimension?

[mobzam]

Hi,
How can Model Employee Dimension so that when someone or user queries for the manager it gets all the details of the employees under that manager? Or it gets all the sales done by the sales person under that manager. I have sales, employee and Date as dimensions.

Please help.
Thanks in advance

[jgranden]

You use a bridge table to represent the hierarchy of employees to managers. (This is straight from the book. pg 165)

ManagerKey (FK to Employee)
EmployeeKey (FK to Employee)
LevelsFromParent int
TopFlag
BottomFlag

The bridge table will have a row that links every manager to every subordinate underneath them including themselves.


select ... from Employee E inner join Bridge B on E.EmployeeKey = B.MangerKey inner join FactSales F on F.SalesPersonKey = B.EmployeeKey.

Al -- ceo
Brent -- sales manager1
Bob -- Sales manager2
Carl, Cami -- Sales people under Brent.
Dani, Dave - sales people under Bob.


Bridge would have

Al, Al,0,Y,N
Al, Brent,1,N,N
Al, Bob,1,N,N
Al,Carl,2,N,Y
Al,Cami,2,N,Y
Al,Dani,2,N,Y
Al,Dave,2,N,Y
Brent,Brent,0,N,N
Brent,Carl,1,N,Y
Brent,Cami,1,N,Y
Bob,Dani,1,N,Y
Bob,Dave,1,N,Y
Carl,Carl,0,N,Y
...

Be careful you don't double count.

[mru22]

I have a similar post and want to know how to then use that in SSAS.

[jgranden]

I believe this covers it:

http://www.sqlbi.com/Default.aspx?tabid=80

[mru22]

I believe this covers it:

http://www.sqlbi.com/Default.aspx?tabid=80

I downloaded the samples and looked at the M2M_Hierarchies project. The bridge table they have is not the same structure as the kimball one. The primarykey in that sample is an identity and has a foreign key column referencing the Identity Column's value.

In the Kimball Book there are two Key columns bur they have repeats in both. Am I missing something here ?

[Sponsored content]