(Basic?) User-defined hierachy question

Question

Hi Everyone,

 

I'm pretty new to developing with analysis services & OLAP, but was hoping someone here could point me in the right direction.

 

I have two dimension tables (Department, Employee), and one fact table (TelephoneCalls). Using a star schema, the two dimension tables are joined by their PK fields through the Fact table.

 

I'm wanting to drill down through the hierachies set up in Department, but also having the Employee branches populated at the right nodes. (ie a department may have a number of sub-departments, and also employees associated with it).

 

Based on this information, does anyone know how I could achieve this?

 

Much Appreciated,

Andrew

Answer 1

Is an employee associated with one and only one department when a telephone call occurs?  If so, you may want to have a single dimension table for employee that associates the employee with a department.  Otherwise, SSAS 2005 does not support cross-dimensional hierarchies.  That said, you might find an interface that lets you structure something like this, and you can always do a cross-join of department and employee in MDX.

 

Hope that helps,
Bryan

Answer 2

Unfortunately employee's have the freedom to jump around departments, so I can't make a single dimension table for it.

 

I'll try your suggestions to build it using MDX.

 

Thanks for your help.

Andrew

Answer 3

"Jump around" as in "with each phone call" or just every few months or so?  If it's the later, you might consider implementing a Type 2 slowly changing dimension.

 

B.