How can Model Employee Dimension?
3 posters
Page 1 of 1
How can Model Employee Dimension?
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
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
mobzam- Posts : 9
Join date : 2011-07-27
Re: How can Model Employee Dimension?
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.
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.
jgranden- Posts : 6
Join date : 2010-07-09
Re: How can Model Employee Dimension?
I have a similar post and want to know how to then use that in SSAS.
mru22- Posts : 34
Join date : 2011-06-14
Re: How can Model Employee Dimension?
I believe this covers it:
http://www.sqlbi.com/Default.aspx?tabid=80
http://www.sqlbi.com/Default.aspx?tabid=80
jgranden- Posts : 6
Join date : 2010-07-09
Re: How can Model Employee Dimension?
jgranden wrote: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 ?
mru22- Posts : 34
Join date : 2011-06-14
Similar topics
» Modeling Employee and Employee Role dimension.
» Employee Dimension and Employee "Profile" Dimension?
» Modeling an Employee Dimension to a Fact which has two columns relating to the Dimension
» Attributes as part of employee dimension and/or own dimension
» Multivalued Dimension - Employee Role
» Employee Dimension and Employee "Profile" Dimension?
» Modeling an Employee Dimension to a Fact which has two columns relating to the Dimension
» Attributes as part of employee dimension and/or own dimension
» Multivalued Dimension - Employee Role
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum
|
|