SSAS First And Last Time

View previous topic View next topic Go down

SSAS First And Last Time

Post  PugMaster on Wed Sep 28, 2011 10:45 am

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

PugMaster

Posts : 21
Join date : 2010-07-07

View user profile

Back to top Go down

Re: SSAS First And Last Time

Post  Lindell on Wed Sep 28, 2011 11:28 am

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?

Lindell

Posts : 6
Join date : 2011-08-02

View user profile

Back to top Go down

Re: SSAS First And Last Time

Post  PugMaster on Thu Sep 29, 2011 4:58 am

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

PugMaster

Posts : 21
Join date : 2010-07-07

View user profile

Back to top Go down

Re: SSAS First And Last Time

Post  Lindell on Thu Sep 29, 2011 5:57 am

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

Lindell

Posts : 6
Join date : 2011-08-02

View user profile

Back to top Go down

Re: SSAS First And Last Time

Post  PugMaster on Thu Sep 29, 2011 3:29 pm

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

PugMaster

Posts : 21
Join date : 2010-07-07

View user profile

Back to top Go down

Re: SSAS First And Last Time

Post  PugMaster on Thu Sep 29, 2011 3:33 pm

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

PugMaster

Posts : 21
Join date : 2010-07-07

View user profile

Back to top Go down

Re: SSAS First And Last Time

Post  Mike Honey on Mon Oct 03, 2011 1:07 am

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!
avatar
Mike Honey

Posts : 185
Join date : 2010-08-04
Location : Melbourne, Australia

View user profile http://www.mangasolutions.com

Back to top Go down

Re: SSAS First And Last Time

Post  PugMaster on Mon Oct 03, 2011 9:17 am

Hi Mike

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

Kind Regards
Paul

PugMaster

Posts : 21
Join date : 2010-07-07

View user profile

Back to top Go down

Re: SSAS First And Last Time

Post  PugMaster on Fri Oct 07, 2011 5:17 am

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.

PugMaster

Posts : 21
Join date : 2010-07-07

View user profile

Back to top Go down

Re: SSAS First And Last Time

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum