Hello All,

Please may I have your assistance on the following::

set [TOP SERVERS2] as
'TopCount(Filter([DeviceA].[Device Name].Members,Val(DeviceA.CurrentMember.Properties("is Server"))=-1), 10,( [Measures].[bytes],
[Time_month].[01/01/2005]))'

I'm trying to return the top 10 DeviceA members based on the measure bytes and date 01/01/2005 where the "is Server" property is equal to 1.
However I would like to be able to retrieve the above over a time range which will be dynamic ie

[Time_month].[01/11/2004]:[Time_month].[01/12/2004]
This will be based on the sum of the bytes over this range
How can I acheive this?

Thanks

John

--
Message posted via http://www.sqlmonster.com

If there is a single date range applicable to the query:


'TopCount(Filter([DeviceA].[Device Name].Members,
Val(DeviceA.CurrentMember.Properties("is Server"))=-1), 10,
Sum([Time_month].[01/11/2004]:[Time_month].[01/12/2004],
[Measures].[bytes]))'

- Deepak

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

Hello Deepak

Many thanks...I have another challenge now !
You will notice that this was on DeviceA.
The cube has the following dimensions

DeviceA
DeviceB
Time_month
LocationA
LocationB

Measures::
Bytes
+others..

Where Bytes is the total volume of network traffic between deviceA and deviceB. deviceA and deviceB have the same members however u would not have any data when deviceA and deviceB are exactly the same
LocationA/B is there so that we can filter by geographical Location.
Hence I need to be able to return the top servers for both deviceA and deviceB combined

ie
deviceA deviceB Bytes
Server1 Server2 10
Server1 Server3 30
Server3 Server2 20
Server1 Workstation1 10
Workstation2 Server2 10

Top Servers::

Server Bytes
Server1 50
Server3 50
Server2 40

Any tips on how I acheive the above?

--
Message posted via http://www.sqlmonster.com

Hi John,

Assuming that the DeviceA and DeviceB dimensions are identical in
structure, LinkMember() can be used to relate them (discussed in point#2
of Chris's suggestions). Thus the DeviceB ByteCount is also added in for
each server:

'TopCount(Filter([DeviceA].[Device Name].Members,
Val(DeviceA.CurrentMember.Properties("is Server"))=-1), 10,
Sum([Time_month].[01/11/2004]:[Time_month].[01/12/2004],
[Measures].[Bytes] + ([Measures].[Bytes],
LINKMEMBER(DeviceA.CurrentMember, DeviceB)))'

- Deepak

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

Hello Deepak

I tried linkmember method however it's taking a long time to process. In fact it has not returned the data yet !...:-(
deviceA and deviceB each have 318183 members, I'm using device names , perhaps if I change these to integer deviceId this might help performance?. Also I'm using member properties isServer and isWorkstation which also may effect performance. I cannot imagine this would significantly increase performance I hope I'm wrong....what do u think?
Within T-SQL I would query both deviceA and deviceB and then do a union as my sub query with a sum.
Perhaps it will be quicker to do 2 seperate queries in MDX and union them somehow and summing the duplicates, can this be done?

Any thoughts?
Do u think I need to redesign my cube

Thanks

John

--
Message posted via http://www.sqlmonster.com