I have a requirement and I am not sure if this can be done using
multidimensional DB or whether it is a case for RDBMS reports. Can someone
please help me with this?

The requirement is to compare a sales person's perfromance with the
performance of all other sales persons as well as with a select peer group.
This peer group is user defined and may change at any time

Sales Person Performance
SalesPerson A 20%
SalesPerson B 30%
SalesPerson C 10%
SalesPerson D 10%
SalesPerson E 10%


Sales Person Peer Group
SalesPerson A SalesPerson B
SalesPerson A SalesPerson C
SalesPerson B SalesPerson A
SalesPerson B SalesPerson C
SalesPerson C SalesPerson D


the reports needed are someting like this
when SalesPersonA logs in, he sees

SalesPersonA, Others, PeerGroup
(A) (B+C+D+E) (B+C)
20% 60% 40%

these reports couls be in the form of charts as well as tabular reports

Is this something that can be done in a cube? How can I create the dimensions
and roll ups for this?
Or should this be based on Reporting Services querying preaggregated tables?

thanks very much!


--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums...-olap/200510/1

Good selection of titles here...

http://spaces.msn.com/members/cwebbbi/Blog/cns!1pi7ETChsJ1un_2s41jm9Iyg!322.entry

"tarana h via SQLMonster.com" wrote:

Hi,

Yes this is something that can be done in a cube. Creating the dimensions
and roll ups is a much bigger question and you'd be best off getting a book
like Microsoft's SQL Server 2000 Analysis Services - Step by Step. Reporting
services sits on top of the cubes and displays the data any-which-way you
want with graphs and charts and makes it all look pretty. PS - using phrases
like 'preaggregated tables' is practically swearing in the news group!

Ta
Paul

"tarana h via SQLMonster.com" wrote:

You could set this up as a 2 level dimension with sales group as the
first level and sales person as the second.

eg.

All Salespeople
Group 1
SalesPerson A
SalesPerson B
SalesPerson C
Group 2
...

You can then use the natural aggregations in the cube to get totals at
the Group level and compare these to the salesperson or the set of other
groups.

I'm not sure exactly what you meant by "may change at any time", but if
you are considering pre-aggregated SQL tables as an alternative there
will obviously be some processing time involved. If need be you could
set this dimension up as a "changing" dimension which would mean that
you could move people between groups without having to do a full re-
process (at the cost of some performance)

HTH

--
Regards
Darren Gosbell [MCSD]
<dgosbell_at_yahoo_dot_com>
Blog: http://www.geekswithblogs.net/darrengosbell

In article <5672b759f7b61@uwe>, u2790@uwe says...

Thank you!
But there Sales Person B could also appear under Group 2
And how can I view A's performance against Group 1 (B+C-A)?

I do understand that I need to read books, but wanted to know if I should be
looking at AS books or RS books .

Is this something I should try to implement in a cube or as an RDBMS query?

thanks once again



Darren Gosbell wrote:

--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums...-olap/200510/1

Hi,

I think you need an MDX book - Spofford's MDX solutions is a good start.

If you have sales people in different groups at the same time one solution
might be to create some 'Named Sets' in a cube - e.g. you could have a named
set called Group 1, and put P1, P2 and P3 in it and then have another Named
Set called Group 2 in which you have P1, P3 and P4. A Named Set can be
changed without the need to reprocess the cube, but users will need to
reconnect to see the changes as Named Sets are resolved when a user connects
to AS.

Ta
Paul
"tarana h via SQLMonster.com" wrote:

I managed to get a small solution working using custom roll up formula

the dimension looks like this
all sales persons
group
sales person a
sales person b
sales person c
sales person d
sales person e

the custom rollup for group level is based on the USERNAME logged in - iif
username = 'x', then (measure.a, [all sales persons].[group].[b])+(measure.a,
[all sales persons].[group].[c]), ....

But this means the grouping gets hardcoded.
Also I am not sure how much nesting this can handle - how large the iif can
be
and I guess I could use custom member formula from a table column, but there
also I would hit a upper limit block?

Any sugestions?
I am trying out a virtual cube solution, but i am not sure how that will work.


thank you!






Paul wrote:

--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums...-olap/200510/1