Hierarchies in Analysis Serivces 2005

[MakRP]

Hi,

I'm relatively new to SQL Server and Analysis Services. I'm currently having a problem (what else ):

Example: within our company, all employees (belonging to different departments) acquire their hours of work associated to different customers. Now, reports for hierarchies of "department-customer-employee" or "department-employee-customer" are frequently used.

If you just define single dimensions for department, customer and employee, the report takes about 5 minutes to be created which is really painful (though aggregations are up to the max!).

My idea was to generate a (precalculated) hierarchy! Now, how do I do that? I've already experimented within the Dimension Sturcture View but a lot of questions still remain unanswered.

I would really appreciate any help

Thanks a lot in advance.

Mak

 

[kdemulder]

You'll have to take this up on database level en put FK's from the employee-lines that point to the department and customer keys.
Then you can build a hierarchy out of these three after you defined the connection in your data source view.

 

[johnherman]

Also, make sure to build a bridge table between customer and employee. This is needed since the relationship is many to many. (you stated that a customer could have many employees and an employee many customers).

-------------------------
The trouble with doing something right the first time is that nobody appreciates how difficult it was - Steven Wright