We would like to move the call records from the ACD to an offline database for reporting purposes. We have two ACDs and would like to 'merge' the data for some global reporting. We do not have DataMart and most likely would not get the money approved to pay for it. My question is do we need all of the tables replicated on the offline DB to have successful reporting or can we stick with the call detail table for apps and the necessary tables for the agent reporting/event table, etc... And if you have attempted to move data to an offline database, what obstacles did you have to overcome. Thanks!
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Offline Database for Reporting
- Thread starter jgaskins
- Start date
Sirgordo
IS-IT--Management
- Sep 16, 2004
- 22
I might have a solution for you, we are much less expensive than DataMart and actually provide you everything you need to do it yourself should you wish to go that route. We provide the ETL and the Schema at a very low cost. We do not charge per seat, we charge a flat rate for the solution. It is a complete handover solution in which we maintain only the extract portion for platform changes but for the most part your internal dB team can maintain the rest. Let me know if you are interested. You can reach me here or directly at sjbogani@secamer.com
HardCoal99
Technical User
We've done exactly what you are asking about for a single ACD instance, but utilimately our reporting demands got so complicated we went ahead and purchased Aspect's Datamart. Since buying the Datamart, we've installed a second ACD. I wouldn't have a good grasp of how difficult it would be to merge data from the two ACDs in an offline database.
Some of the tables we exported to an offline database include the following: AAC_EVENT, AAC_REASON, AGENTGRP, AGENT_SUPGRP, APPLIC, CALLDETAIL, DAGENT, DAPPLIC, EVENTDTL, PARMS, TEAM and USERS. Your indexes will vary depeding on the queries you are running. Since the data sets can be large, good queries will result in much shorter processing times.
The work to create the offline database was significant. You need to correctly understand the relationship between the tables you need. You should have a soid understanding of Aspect's "tables and fields" guide. I recommend creating alerts and logs to monitor the data extraction.
Some of the tables we exported to an offline database include the following: AAC_EVENT, AAC_REASON, AGENTGRP, AGENT_SUPGRP, APPLIC, CALLDETAIL, DAGENT, DAPPLIC, EVENTDTL, PARMS, TEAM and USERS. Your indexes will vary depeding on the queries you are running. Since the data sets can be large, good queries will result in much shorter processing times.
The work to create the offline database was significant. You need to correctly understand the relationship between the tables you need. You should have a soid understanding of Aspect's "tables and fields" guide. I recommend creating alerts and logs to monitor the data extraction.
We currently have 2 ACDs and backup the data to SQL servers for reporting. We keep seperate tables for each ACD, but they could be merged if you like.
The biggest problem we had was getting the sproc to successfully run that pulled the Oracle data into the SQL data. But then before that we have always been using the Oracle tables directly to develop reports so we were very familiar with the table structures.
The biggest problem we had was getting the sproc to successfully run that pulled the Oracle data into the SQL data. But then before that we have always been using the Oracle tables directly to develop reports so we were very familiar with the table structures.
Beeing familiar with the table structures is essential.
What should your global reports show and what do you need to get the necessary data?
I think it's possible to merge data from multiple ACDs. Calldetail and call_today are fine because they contain the node-id to distinguish the ACDs. You may even combine the data to a single table, if that's all what you want.
Most of the other event-, daily- and configuration-tables don't contain a node-id - you'll have to design your "personal datamart" or use apropriate references in your reports. You'll have to decide the layer at which you want your data to become suitable for global reporting.
At the moment I daily feed a system with reports (summarized, 1/2 hour) from 2 ACDs, it receives additional data from IVR, carrier-systems, different business-systems and external partners. This system has its own reference-tables containing the teams, users, groups, DDIs, dialed numbers (and more) used by the different locations. This way you may get the global picture at some clicks, the cost is a lot of time or money to implement something like that.
What should your global reports show and what do you need to get the necessary data?
I think it's possible to merge data from multiple ACDs. Calldetail and call_today are fine because they contain the node-id to distinguish the ACDs. You may even combine the data to a single table, if that's all what you want.
Most of the other event-, daily- and configuration-tables don't contain a node-id - you'll have to design your "personal datamart" or use apropriate references in your reports. You'll have to decide the layer at which you want your data to become suitable for global reporting.
At the moment I daily feed a system with reports (summarized, 1/2 hour) from 2 ACDs, it receives additional data from IVR, carrier-systems, different business-systems and external partners. This system has its own reference-tables containing the teams, users, groups, DDIs, dialed numbers (and more) used by the different locations. This way you may get the global picture at some clicks, the cost is a lot of time or money to implement something like that.
This was a while ago so I don't remember all the details anymore but I did have a system setup to merge data from 2 ACD's using a VB program and a MySQL database.
It's easy enough to get the data out from both ACD's, I think I grabbed the Apps and Groups tables along with the Call Details and Daily Call Details tables.
Where you may have problems, and have to excercise caution is where you have the same App or Group numbers referenced on both ACD's but referring to different Apps or groups. To combat this I had to create combined App & Group tables, renumber any clashing numbers and then alter the references in the Call Detail & Daily Detail to reference the correct Apps or Groups in the new table.
Once I had worked that out properly the system worked perfectly and it made our reporting an awful lot more straight forward.
It's easy enough to get the data out from both ACD's, I think I grabbed the Apps and Groups tables along with the Call Details and Daily Call Details tables.
Where you may have problems, and have to excercise caution is where you have the same App or Group numbers referenced on both ACD's but referring to different Apps or groups. To combat this I had to create combined App & Group tables, renumber any clashing numbers and then alter the references in the Call Detail & Daily Detail to reference the correct Apps or Groups in the new table.
Once I had worked that out properly the system worked perfectly and it made our reporting an awful lot more straight forward.
- Thread starter
- #7
- Status
Similar threads
- Locked
- Question
- Locked
- Question
- Locked
- Question