Hello All,
Please may I have your help? I'm not sure if this is
possible....anyway here goes..I 1st define my top servers. Then I need
to rank this set on a per date basis. For the example below I've
created ordered sets per calender month. Then I create calculate
members that rank on a per calender month basis. Is there a better way
of doing this? that is generic for a date range and perhaps use a
cross join with time set? Any thoughts would be appreciated

with
/*
define our set 'top servers' (top 10) for the month 1/7/2004 based on
their volume (bytes)
*/
set [TOP SERVERS] as
'TopCount([Server].[Device Name].Members, 10,( [Measures].[bytes],
[time].[m].[01/07/2004]))'


/*
define our sets of 'ordered servers' (top 10) for each month based on
their volume (bytes)
*/

set [ORDER SERVERS 1/5/2004] as
'Order([Server].[Device Name].Members, ( [Measures].[bytes],
[time].[m].[01/05/2004]), BDESC)'

..
..
..
set [ORDER SERVERS 1/8/2004] as
'Order([Server].[Device Name].Members, ( [Measures].[bytes],
[time].[m].[01/08/2004]), BDESC)'

/*
define a calculated members to return the rank of the above servers
*/
member [measures].[serverRank 1/5/2004] as
'Rank ([Server].CurrentMember, [ORDER SERVERS 1/5/2004])'
..
..

..
member [measures].[serverRank 1/8/2004] as
'Rank ([Server].CurrentMember, [ORDER SERVERS 1/8/2004])'
..

select.....

Not sure if I understand exactly what your query results should look
like, but here's an MDX ranking example from Foodmart (adapted from my
earlier post). It lists stores at the country level along rows (you
could replace with a set of servers). On the columns are cross-joined
months of [1997] with the Unit Sales and Store Rank (by Unit Sales):

'Rank([Store].CurrentMember,
Order(StrToSet("Axis(1)"),
[Measures].[Unit Sales], BDESC))'

Select CrossJoin(Descendants([Time].[1997],[Time].[Month]),
{[Measures].[Unit Sales], [Measures].[SalesRank]}) on columns,
[Store].[All Stores].[USA].Children on rows
from Sales

- Deepak

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Hello Deepak
Many thanks for your reply. Let me attempt to explain what I'm trying
to acheive. The return format is as follows


1/5/2004 1/6/2004 ..... 1/10/2004
bytes util rank bytes util rank ..... bytes util rank

Servera 34 .1 3 90 .9 1 ..... 11 .05 1
Serverb 48 .7 2 12 .05 3 ..... 5 .02 2
Serverc 75 .8 1 60 .7 2 ..... 2 .01 3
..
..
hence I need to calculate a rank on a per date basis

I made the change based on your help and it worked. Please have a look
at the full script below.
Unfortunately it takes too long. The issue I have is the need to
return a rank for a server based on all the servers for a particular
time.
The number of servers in this case is 300000+ in some cases can be up
to 1 million. I was considering adding the rank as a measure in the
cube however I have another dimension based on device groups and the
rank will vary based on this. Do u have any suggestions on how I can
improve performance?

Thanks

John


with
/*
define our set 'top servers' (top 10) for the month 1/7/2004 based on
their volume (bytes)
*/
set [TOP SERVERS] as
'TopCount([Server].[Device Name].Members, 10,( [Measures].[bytes],
[time].[m].[01/07/2004]))'

/*
define our reporting time period
*/
set [REPORT TIME] as
'[time].[m].[01/05/2004]:[time].[m].[01/10/2004]'

/*
define a calculated member to return the utilisation over all the
servers for the above date.
*/
member [measures].[utilisation] as
'([Measures].[bytes])/([Measures].[bytes],[Server].[All Server])'

/*
define a calculated member to return the rank of the above servers
*/
member [measures].[serverRank] as
'Rank ([Server].CurrentMember, ORDER([Server].[Device Name].Members,
[Measures].[bytes], BDESC) )'


select
CROSSJOIN ([REPORT TIME],
{[measures].[bytes],[measures].[utilisation],[measures].[serverRank]})
ON COLUMNS,
{[TOP SERVERS]} ON ROWS
FROM NVServer
where ([device Group].[All device Group])



Deepak Puri <deepak_puri (AT) progressive (DOT) com> wrote

Hi John,

Performance is definitely a problem when you're ordering 300K+ members
on the fly - I don't have a good answer offhand, but maybe others in the
group have some ideas?

Some initial questions/thoughts are:

- The data is probably not sparse, ie. most servers would have data on
any given day. Otherwise, excluding the empty server members first might
have helped.

- Does the fact table have a single daily row per server?

- What does the Server hierarchy look like; and are there some
branches/subtrees that are highly unlikely to contain the top servers on
any given day (for example, by class or model of server)? If so, maybe a
MaxBytes measure could be used to eliminate many subtrees prior to
ordering - any node with MaxBytes < Min(TopServers) could be ignored.


- Deepak

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Hello Deepak
many thanks for the points u mentioned.
I have one enormous fact table and perhaps splitting it will help.
Anyway allow me to describe my OLAP db design...
I have a single fact table which has the following columns

timeId applId deviceId deviceGroupId bytes extConversation

the timeId is a foreign key to my time dimension table with the
following columns

timeId starttime endtime the_hour_day the_day_week the_day_month
the_month_year the_year parttion

This time dimension table would be based over a six month period and
will have entries for each hour, day, week and month
example rows

1 01/05/2004 01/06/2004 null null null 5 2004 m
2 01/05/2004 08/05/2004 null null null 5 2004 w
3 01/05/2004 02/05/2004 null 7 1 5 2004 d
4 '01/05/2004 00:00:00' '01/05/2004 01:00:00' 0 7 1 5 2004 h
5 '01/05/2004 01:00:00' '01/05/2004 02:00:00' 1 7 1 5 2004 h
etc

hence not only has a single daily row per server it also has an hourly
row per server!!

The dimensions in the cube NVServer are Server, Time and DeviceGroup.

Server dimension is based on the devices table where the column
isServer=1
(Source table filter) and DeviceGroup so that we can filter by a
location.

The devices table is extremely large hence I had to create a sub level
device Group Name for the server dimension with 'grouping' set to
automatic

Cause I'm new to analysis services I'm not sure of it's limitations
and what is the best design?

Eliminating those servers with bytes that are low sounds like a good
idea.
However perhaps I need to create more fact tables and split the cube
....
Your points and opinions are most welcome

Thanks

John
Deepak Puri <deepak_puri (AT) progressive (DOT) com> wrote

John,

For this application, sounds like you need to report at tne day level
rather than hourly. Does the daily record per server represent an
aggregation of the hourly data - in which case, a cube could be built
with daily fact data? Of course, the time dimension would accordingly be
defined down to the day level. SQL views could be applied to the fact
and time dimension tables as necessary to do this.

A MaxBytes measure would be more useful in reducing the number of
servers to be sorted if there is some correlation between the Server
hierachy and Bytes measure (which may not exist with an automatic
grouping). Is there any server attribute available (like model or type)
that could be used for such grouping? Alternatively, if there is a
correlation between the DeviceGroup hierarchy and bytes (such as the
busiest servers being at limited locations), maybe that could be
exploited in reducing the server set to sort.


- Deepak

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!