How can Model Employee Dimension?

View previous topic View next topic Go down

How can Model Employee Dimension?

Post  mobzam on Wed Aug 03, 2011 6:33 pm

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

mobzam

Posts : 9
Join date : 2011-07-27

View user profile

Back to top Go down

Re: How can Model Employee Dimension?

Post  jgranden on Tue Aug 16, 2011 12:09 pm

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.

jgranden

Posts : 6
Join date : 2010-07-09

View user profile

Back to top Go down

Re: How can Model Employee Dimension?

Post  mru22 on Tue Aug 16, 2011 5:04 pm

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

mru22

Posts : 34
Join date : 2011-06-14

View user profile

Back to top Go down

Re: How can Model Employee Dimension?

Post  jgranden on Tue Aug 16, 2011 9:23 pm

I believe this covers it:

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


jgranden

Posts : 6
Join date : 2010-07-09

View user profile

Back to top Go down

Re: How can Model Employee Dimension?

Post  mru22 on Wed Aug 17, 2011 1:57 pm

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

View user profile

Back to top Go down

Re: How can Model Employee Dimension?

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum