HDS Replication for custom reporting?

[allsup-jwagner]

We are currently running UCCE 7.5(9) and looking to build some custom reporting, to include information regarding specific calls.  I know you are not supposed to develop against the TCD tables directly, so we tried setting up replication of the awdb and hds databases to one of our SQL reporting servers.  However, we ran into issues with the weekend maintenance packages on the HDS as it looks like Cisco truncates the awdb tables and repopulates them.  This fails though when the tables are published for replication and then creates havoc on the HDS with the synchronization. 

My question, is what are the recommended best practices for setting up replication to a reporting server for UCCE?  Should we be configuring the replication to come from a PG?  I guess log shipping would probably be an option as well, but some of the data we are looking for would be from real time tables, so log shipping causes issues with stale data in those instances, so I'd prefer to not do that.  Our DBA has suggested we could set up a maintenance job to tear down the replication on the HDS each evening before the Cisco maintenance jobs run, and then rebuild them again after they finish, but we're not sure if this is the best course of action and would prefer to find something about best practices. 

Any assistance would be much appreciated.

Thanks,

John

[geoff · Accepted solution]

Interesting problem. I'm not sure you can set up replication like that. Cisco say you are supposed to make your offline copy of the HDS on another DB so you can query those TCD tables, but they don't mention replicate

It's odd that you want to also make queries of the realtime tables - that would not be the intention of the offline copy. If I was running some extensive historical queries of the RCD/TCD overnight, I would be happy with getting a backup copy at close of business and coming out with my reports the next morning.

But if you want to do real-time reports, this would not be the right way to go.

Can't you separate the reports and run those real-time reports against the live HDS?

Regards,

Geoff

[allsup-jwagner]

Geoff, thanks for the response. 

Part of what we are looking to do is to provide a comparison of some of the real time data with some historical data within our custom supervisor desktop dashboard.  The real time data we are currently pulling from the HDS and it is working fine.  However, we are looking to add in some additional analytical data for them to allow them to see their averages for things like daily and hourly call counts, answer rates, etc, which we can do a lot of from the HDS, and could even do this through some simple log shipping as the data does not need to be very current. 

However, we are also looking to provide agents with detailed call data throught the day, along with their own average stats for a selected timeframe.  While the agent stats can be pulled from the HDS, individual call details like who took which calls and what wrap up data is being used most often probably should not.  We also have several custom report requests for reporting on number of calls to a specific toll free number or to/from a particular client.

While I only have about 500 agents right now, we are running a custom desktop and a lot of custom data reporting within it and I am not too keen on adding anything from the TCD tables without offloading at least those tables elsewhere.  My thought was that offloading that data to another server would be the best way to handle it all in one place and to avoid needing to set up any kind of linked servers which we would need if we only offloaded the TCD tables only.  Log shipping would work, except the agents already have issues with the frequency at which the default stats are updated, so I was hoping to be able to give them more of a real-time call history. 

One thing I have looked at doing was to offload a copy of the TCD table to a database we run on the HDS box for the custom desktop, but I'm not sure how far Cisco wants you to move that TCD table.  Is it enough of a best practice to move it to a separate database on the same server, or do you need it on a different server completely?  It looks like the server we are running on would never notice if we ran the reports from a separate DB that had a copy of the TCD tables, but I'm not sure if that's a good idea or not.

Thanks,

John

[Gergely Szabo · Accepted solution]

Hi,

what you might want to do is to set up a separate database server and have a job for copying data from TCD and RCD (and other, like Dialer_Detail, Agent_Event_Detail; or _Half_Hour/_Interval tables may be useful too) tables based on the DbDateTime column value, like every five minutes. I would not really do that on the same server, just to be on the safe side, even though at this moment you don't see any performance concerns. One never knows when the time comes for complicated queries, like multiple JOIN's and after all, there's only one CPU time.

Real time tables are not really real time, this is what I keep telling our customers too. Real time information never belongs to a database table. What you might want to do instead is to open a monitor-type session to CTI/CTI OS and listen for all events, then filter out what you need and then do some calculations, and write down the results. Same applies to wallboards: if you are okay with some delay, use those tables/views from the database. If you want real time like real time, then listen for events coming from the CTI server / CTI OS server.

G.

[allsup-jwagner]

What we ended up doing for right now is to enable the replication for the HDS and also a nightly snapshot of the AWDB.  Both of these are residing on a separate SQL server we use for SSRS reporting.  We decided to include the AWDB snapshots so we did not need any kind of linked servers or anything like that when developing complex historical reporting outside of Webview.  For the real time data within the custom desktop, we are using the real time reporting tables to provide the agents with more comprehensive stats than the defaults, and will be adding some historical info from the reporting server, along with call history.

Since we opted to do the nightly AWDB snapshots, so far it is not conflicxting with any of the Cisco maintenance packages.  Should we run into any issues, I'll update this thread.

Thanks Geoff and Gergely!