SSAS First And Last Time

Hi

I have a cube built in SSIS to analyse call center agent activity. One of the things the management would like to see against the date of the activity is the First and Last activity time. I do not know how to write the MDX in SSAS to achieve this as i am not that experienced with either SSAS or MDX.

Many activities are shown against a date and the start and end time of these activities as you drill through the cube.

Any advice would be greatly appreciated.

Kind Regards
Paul

I'm propably completely wrong here, but isn't it possible to use the agreggation function (in the cube structure) on the fact table and use Max, Min for this?

Hi

I have tried using the Min function as a calculated member like this

MIN([Activity Time].[Start Time])

But that shows nothing, I am sure the answer is simple but i am a SSAS newbie. I am not sure how to do a min or max via the aggregations tab.

Kind Regards
Paul

Well you won't need any calculated member if you do like this, again there are so many gurus here that propably have better solutions but I think this will work.

Open the cube, go to Cube Structure tab, right click the fact date (ex FK_ActivityTime) and choose New Measure. Go to the Measure box (upper left) and right click edit measure and put Usage on the top to Maximum for the correct source column (might need to tick "Show all columns" on the bottom) and repeat for Minimum. Reprocess and deploy to see if it works, I hope it does

Hi

Many thanks for your idea, i gave it a go but all i got was an integer so i changed the format to hh:mm:ss and changed the data type to inherited but all i get now is 00:00:00.

Kind regards
Paul

Hi

I had another look and the column had changed so i have now selected the correct column (time) and i get this error:


Error 38 Errors in the metadata manager. The data type of the 'FirstTime' measure is not valid because the data type of the measure is a string type. 0 0


Kind Regards
Paul

Hi Paul,

I've achieved a requirement similar to this by feeding in the time measure as a number representing the fraction of a day, e.g. 3AM = 3 / 24. If you are starting from a MS SQL DATETIME column you could use something like:

Code:

CONVERT ( NUMERIC ( 38 , 37 ) , CONVERT ( DATETIME , CONVERT ( TIME , FirstTime ) ) )

Leave the measure format as hh:mm:ss. Leave Usage as Maximum/Minimum.

Good luck!

Hi Mike

Thanks for this, I need to do some further testing but it seems to do the trick.

Kind Regards
Paul

Just confirming this does indeed work very well, Thanks Mike.

I also have another requirement where i need to convert seconds (Caller Wait Time and Call duration) to hh:mm:ss but i also need to show the average, minimum and maximum values in the cube.