Optimizing Aspect Call Center Reports 2

[michaelgray]

My company has recently moved from CustomView ReportRunner/Writer to Seagate Info 7.5.

We have installed on the Seagate server, Aspect Call Centre Reports 2.0.2.

The problem that I have is that When running a Call Detail report (CallProfileDetail.rpt), with a filter on the Application number, This filter is not actually taking effect until all the data is on the Seagate Server.

This means that if I run a "Last Month" date range, the report is taking around 15 minutes to run (Due to the volume of call records received)

Has anyone got a good work-around for this!?!?

 

[Moebius01]

I'm not positive, but I believe you would need to create a copy of the report and edit the filter at the report level. Otherwise, Seagate is running the report as it is stored, then applying custom filters after the fact.

 

[EJW]

Use Report Writer? Nature of the beast I'm afraid. Experienced this problem with a lot of reports when moved to Info. 15 minutes still sounds like a long time though. Can you access the tables quicly in a similar Oracle Test? SQL prompt for instance?

 

[CmdrGravy]

I think the problem is caused by the way Aspect use the Access database pass through queries.

When you run a report designed as per the Aspect recommendations the main report runs a Query on the ACD between the dates specified, this is regardless of any other filters e.g. Application numbers which are present in the sub report.

The sub report then runs it's query on the Access Database dataset using the filters you have specified and this part should run pretty quickly.

I have no idea why Aspect have chosen to implement this kind of soloution but in my situation with a massive amount of applications and high call volumes Call Detail reports can take ages and ages to run and it's a real pain in the neck.

I am currently re-designing my reports to run directly on the ACD database ( by passing the Access database altogether ) where all the filters are applied immediatley. I am still using the Main Form - Sub Form format in order to keep the Aspect "Last Month", "Yesterday" parameters etc.

In some quick tests these new reports can run up much much faster, especially for long date ranges than the Aspect ones. The Aspect ones are fine for "Yesterday" reports however.

The best soloution would be to rewrite all the .civ files to report directly on the ACD rather than using the Access Database but this would be an incrediably tedious and long winded job !

 

[michaelgray]

CmdrGravy,

This was exactly the process I had started to implement, however another spanner was thrown in the works.

As you may know, the ol' Aspect ACD doesn’t like doing much work, (i.e. high CPU utilization). When I started to send a more complex SQL statement in to the ACD, yes, it processed faster (Maybe) but it caused very high CPU usage (> 95% for duration of the report). The other thing I found was that no records started being retrieved from the ACD until it had done its "Initial Processing" (high CPU%) -> but then they came through Very quickly, (2-3 thousand a sec).

Do you, or anyone else, know how the report runner/writer handled its filtering system, in regards to record processing? -> could this be similar to Seagate 7.5 where it downloads the whole date range, and then filters them on the local machine?

We love you aspect!

 

[Moebius01]

Runner/Writer was anything by efficient in its workings. Given that it's basically Crystal 5. I seem to remember that it did filter by range and app/group, but that was lost when subreports came into play.

My personal suggestion when dealing with complex reports or sub-reports is to pull the data into a database off of the switch, and report from it. Generally much faster as you can control how the query runs, it takes the load off the Aspect as it needs only 1 raw dump per day (or interval if you're doing day to date reporting), and you can also keep raw data as long as you need. Otherwise known as data warehousing, but doesn't have to be as complex as that.

We ended up doing this to allow for some tricky linked trunk reporting at one point.

 

[CmdrGravy]

to michaelgray - this is slightly off topic here but would you mind explaining how you can get info like the ACD Utilization and the speed it processes records ? I would definatley like to see the effect our report runs are having on the ACD but don't know how to get this info.

Thanks.

 

[Moebius01]

The speed you can watch as Runner executes the query. You can see the records as they return. For ACD CPU, the easiest way is a canvas. system.pct_idle will show the CPU Idle percentage in RealTime. We used this to troubleshoot queries (and certain stress tests I wrote) maxing out the CPU. On a side note, I was never able to create a query that maxed the CPU to the point of call proccesing failure, but I was able to push it hard enough to slow down the voice system.

 

[chris13]

Hello, I inspected the passtrough-queries in the access-db. they only expect start-/end-date and time as parameters for filter-conditions.

2 years ago I modified dictionaries to be able to use the same reports with CC Rel.6 and Rel.7 with ReportWriter/Runner. that was not very funny... but succesfull. now some braindead VIP ignored any arguments and bought SeagateInfo Version 8!!!
meanwhile the ACDs are all V8.x, we use IP-NIQ and an IVR (non-Aspect, no CTI). thanks to NIQ, anti-trunk-tromboning, transfers to partners etc. I sometimes have to examine 3 or more records from calldetail to know how the call is to be counted...
until now i just have coded some sql with self-joins to get what I need. it seems best to setup another DB on a separate server, use pl/sql or something else to get all the stuff I need from the different sources, and then run the against that DB.

good luck,
chris

 

[CmdrGravy]

Following the advice here I've began pulling the data of the ACD into a MySQL database and so far the results have been very promising - monthly reports can now run in under a minute rather than 2+ hours and I can add all kinds of other useful information directly into the database.

It seems to take around 20mins to pull out the entire CALLDETAIL table and around 2 mins to keep it updated every day so I'm doing this automatically every night and it works very well.

 

[michaelgray]

CmdrGravy,

Your solution sounds like it will give me everything I need - performance as well as the ability to implement additional functionality.
Without myself being a DBA, how easily is it to develop/implement this? - Do you have a "How To" guide of setting up a new DB, and "Automating" the data dump from the CallDetail - if not, any ideas on possibly where I could get assistance with this?

 

[CmdrGravy]

Hi Michael,

I am using a MySQL database which is ( pretty much ) freely available from

http://www.mysql.com

and have written a small VB program to dump the data from Aspect into this table.

I've found MySQL is pretty easy to set up - it has a nice point and click installer although you do need to read the installation documentation to get it going properly.

I also downloaded the MyODBC thing which allows you to access the MySQL database using ODBC ( this is how Crystal and my VB program access it ).


I did experience 1 or 2 problems getting Crystal to work with MySQL properly but on the web I found instructions on how to edit the registry to get it work properly - takes around 5mins - and since then it has worked just fine.

I can send you the links to where to get all this stuff, the VB code I use to dump the data ( you'd have to tailor this to your own requirements ) and the scripts to create the database on MySQL to put the data into if you let me know how I can get this over to you.

 

[michaelgray]

CmdrGravy,

Do you have some public web-space where you could post it to (in zip or the likes)?

Is it something you could post to this Forum directly - i.e. How much code is there!?

 

[CmdrGravy]

I'll put everything into a zip file and stick it on my website for you - I'll let you know where that is when I have set it up.

 

[jtomberlin]

Another possibility is to get your hands on the 2.0.1 version of the reports. This was before Aspect got heavily into using the pass-through queries, and in my experience, run much faster. It will save a lot of rewriting and will have the more-or-less standard Aspect layout. Of course, the difficulty lies in getting the 2.0.1 reports.

 

[CMoney]

We have been using Seagate for about 2 years now. The problem described is definetley because of the passthrough queries and the use of Access. As suggested before we have recreated all of the deatil reports to run straight from the switch. The summary reports will be fast enough as designed. We are a medium sized center and have not had any problems running these straight from the switch. There is a priority system on the switch which will not allow the reports to run if the CPU is taxed with calls so it should not affect customers.

 

[CmdrGravy]

michael, go to this url

http://gravychain.co.uk/club/node/view/597

and you will find my description of the process I followed and all the relevant files etc.