Please may I have your assistance with the following

My Analysis Server Db design is as follows

Cube Name: Server_day

with a source table filter
"OLAPDeviceA"."isServer"=1 or "OLAPDeviceB"."isServer"=1

hence from the following dimensions DeviceA or DeviceB is a Server

Dimensions::

Time_day
DeviceA
DeviceB
LocationA
LocationB


Measures::

Bytes
Bytes Sent
Bytes Received
AveTime

Just for background information AveTime is the Network response time based on the conversation between A and B.
I've design the cube is such a way where I can filter based on Location(Group of devices). The reason for A and B is I also need to be able to identify conversations external to a location ie LocationA<>LocationB

Hence my data is based on a conversation between deviceA and deviceB where at least one of the devices is a server.

deviceA or deviceB can have the following member properties (isServer=1, isWorkstation=0) or (isServer=0, isWorkstation=1).

Example of my cube data for say date (Time_day) : 1/5/2004

DeviceA DeviceB LocationA LocationB Bytes Bytes Sent Bytes Rec AveTime
S1 W1 X Y 4 2 2 0.5
W2 S1 Y X 3 1 2 0.01
W3 S1 X X 10 3 7 0.1
S2 S1 Y X 8 5 3 0.03
S1 S3 X Z 5 1 4 0.007


Example for of my cube data say date (Time_day) : 2/5/2004

DeviceA DeviceB LocationA LocationB Bytes Bytes Sent Bytes Rec AveTime
S1 W3 X X 9 5 4 0.5
S3 S1 Z X 12 5 7 0.01
W2 S2 Y Y 5 3 2 0.1
S2 S3 Y Z 17 11 6 0.03
S3 S1 Z X 21 8 13 0.007



I need to be able to rank the Servers on a per date, total volume (Bytes) basis for example


1/5/2004

Rank Device Bytes Utilisation Avetime

1 S1 30 30/30 (.5+.01+.1+.03+.007)/5
2 S2 8 8/30 .03
3 S3 5 5/30 .007

2/5/2004

Rank Device Bytes Utilisation Avetime

1 S3 50 50/64 (.01+.03+.007)/3
2 S1 42 42/64 (.5+.01+.007)/3
3 S2 22 22/64 (.1+.03)/2

1/5/2004 - 2/5/2004

Rank Device Bytes Utilisation Avetime

1 S1 72 72/94 (.5+.01+.1+.03+.007+.5+.01+.007)/8
2 S3 55 55/94 (.007+.01+.03+.007)/4
3 S2 30 30/94 (.03+.1+.03)/3

Return all the above together in the following format

date range 1/5 2/5
Rk Dev Bytes Util Avetime Rk Bytes Util Avetime Rk Bytes Util AveT
1 S1 72.....................1 30............. 2 42......
2 S3 55.....................3 5.............. 1 50...
3 S2 30.............. 2 8............. 3 22...

Any ideas on how I can generate the above using MDX would be greatly appreciated. Bearing in mind that the date range (example above 1/5/2004 - 2/5/2004) will be dynamic.
Perhaps I need to re-design the cube to acheive my objective. Any advice would be great

thanks

John

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

Hi John,

I'll deal with each of the issues that this poses in turn:
1) First of all, the date range problem. You can read about my favourite way
of solving it here:
http://spaces.msn.com/members/cwebbbi/Blog/cns!1pi7ETChsJ1un_2s41jm9Iyg!113.entry

2) The problem of finding conversations where the server you are interested
in is either Device A or Device B can be solved in a similar way to the date
range. You create two device dimensions, Device and DeviceHidden, and hang
Device off the Device A column in your fact table and DeviceHidden off the
Device B column. As the name suggests, you then hide the DeviceHidden
dimension. You can then find the value for a measure where the currentmember
on Device is Device A or Device B (I'm right in assuming from your post that
DeviceA can never be the same as DeviceB, aren't I?) using the MDX
(Device.Currentmember, DeviceHidden.[All DeviceHidden], Measures.MyMeasure) +
(Device.[All Device], LINKMEMBER(DeviceHidden.Currentmember, Device),
Measures.MyMeasure).
3) Putting steps 1 and 2 together, you'd have a calculated measure for each
measure looking something like this:
SUM({
OPENINGPERIOD([START DATE].[LOWEST LEVEL], [START DATE].CURRENTMEMBER)
:
CLOSINGPERIOD([START DATE].[LOWEST LEVEL], LINKMEMBER([END
DATE].CURRENTMEMBER, [START DATE]))
}, VALIDMEASURE((Device.Currentmember, DeviceHidden.[All DeviceHidden],
Measures.MyMeasure))
+
VALIDMEASURE((Device.[All Device], LINKMEMBER(DeviceHidden.Currentmember,
Device), Measures.MyMeasure))
)

3) Getting the average time is easy now - you just need to have a measure
with aggregation type count, apply the technique above to it (as you will
have done with all your other measures) and then divide average time by this
count.

4) Finally the MDX for your final report will be something like the
following example for Foodmart 2000:
WITH
SET MYSTORES AS 'ORDER(
FILTER(
[Store].[Store Name].MEMBERS
,STORE.CURRENTMEMBER.PROPERTIES("Store Type")="Small Grocery")
, MEASURES.[UNIT SALES], BDESC)'
MEMBER MEASURES.SALESRANK AS 'RANK(STORE.CURRENTMEMBER, MYSTORES)'
SELECT {MEASURES.SALESRANK,MEASURES.MEMBERS} ON 0,
MYSTORES
ON 1
FROM SALES

....where the store dimension represents your Device dimension.

I hope you can work out how to relate all this to your cube!

Chris



"John Shiangoli via SQLMonster.com" wrote:

Chris,
Many thanks for the tips. I like your manifesto....
I'm very much a novice to MDX and I'm going through your points step by step and trying to understand them?

In answer to your question a member of dimension deviceA cannot be the same as a member of dimension deviceB for any given cell with data. However the same member will appear in both dimensions but will not have an intersection with itself ie in real life a device on a network cannot have a conversation with itself
I hope this makes sense?

The plan is this MDX will be called via T-SQL

Anyway from the points u mentioned (please correct me if i'm wrong) I need to 1st create a virtual cube such as

CREATE SESSION CUBE [MyCube]
FROM [aaiServer_day]
(
MEASURE [aaiServer_day].[Bytes],
DIMENSION [aaiServer_day].[DeviceA],
DIMENSION [aaiServer_day].[DeviceB] HIDDEN,
DIMENSION [aaiServer_day].[Time_day]
)


which will create the DeviceB as a hidden dimension

I then need to add an endtime dimension (At the moment [Time_day] is the start time).
I'm not sure how to do this from MDX any suggestions?

On the above virtual cube create the following calculated measure

member [measures].[TotalBytes] as

SUM(

<<A time set which I need to work out???>>
,
VALIDMEASURE((DeviceA.Currentmember, DeviceB.[All DeviceB], Measures.Bytes)) +
VALIDMEASURE((DeviceA.[All DeviceA], LINKMEMBER(DeviceB.Currentmember, DeviceA), Measures.Bytes)))

)


I don't quite understand the use of the function VALIDMEASURE

.....then it should then be easy ??!! :-(

then at then end drop the virtual cube

DROP CUBE [MyCube]

Please may I have some guidance on the above points

Thanks

John

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