(Basic?) User-defined hierachy 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

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

|||

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

|||

"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.

"Show Group Subtotals" Reappear when opening a report

This should be pretty simple...

I have a report that I've designed in Report Builder with many groups. I added an aggregate. By default, it subtotals at each level. For all the groups (but 1) I right clicked on the group header and unchecked "Show Group Subtotals".

I save the report to the report server in "My Reports". When I open the report, all of the subtotals return.

Does anyone know why this occurs? I'm going to have a hard time selling this to business users if they have to go through each group and uncheck the "Show Group Subtotals" every time they want to modify a report.

Regards,

Dan

This is a known issue that is fixed in internal builds. The release in which this fix will be available has not been determined yet. If it is a showstopper for you, you might consider calling customer support and requesting a hotfix.

|||

Thanks for the quick response! I'll wait to see how users react before pursuing it further.

Thanks again,

Dan