SQL Questions (New to Cisco)

Unanswered Question
Aug 18th, 2010

Hello. I work for Clarian Health in Indianapolis and am trying to learn as much as possible about the SQL databases, both AWDB and HDS so that I can handle the reporting for our Revenue Cycle Customer Service.

I am currently working my way through the Database Schema Handbook for Cisco Unified ICM /Contact Center Enterprise & Hosted. I’m also reviewing the explanation pages that are available for the reports on WebView. During my reviews, I have noticed a few things that confuse me.

My questions are:

  1. 1. Why do a majority of the tables on our SQL Server start with “t_”?
  2. 2. Why do some of the tables have data on the AWDB server but not on the HDS server, and vice versa? (Examples: t_Agent and t_Agent_Team and t_Agent_Team_Member and t_Person are blank on the HDS database but not blank on the AWDB database; but the t_Agent_Logout is blank on the AWDB database and not blank on the HDS database)
  3. 3. When data is moved to the HDS server every 30 minutes, is it also removed from the corresponding AWDB table?
  4. 4. In review of the agent26: Agent Consolidated Daily Report syntax info located on the WebView, 1 of the calculations uses the LoggedOnTimeToHalf from the Agent_Half_Hour table while the remaining calculations uses the same field from the Agent_Skill_Group_Half_Hour table. Can you please tell me why this is? Why would all of the percent calculations not use the data from the same table? (The % of time Agent paused and/or put a task on hold uses the Agent_Half_Hour Table. All other % calculations uses the same field from the Agent_Skill_Group_Half_Hour Table.)
  5. 5. Also in reviewing the agent26: Agent Consolidated Daily Report syntax info, I noticed that it contains the Skill_Group table, the Agent_Half_Hour table and the Media_Routing_Domain table. Both the Skill Group table and the Agen_Half_Hour table contain links to the Media_Routing_Domain table. Which relationship/join is actually utilized for this report?
  6. 6. Why doesn't the LoggedOnTimeToHalf field on both the Agent_Half_Hour table and the Agent_Skill_Group_Half_Hour table have the same value in them?
  7. 7. On the agent_26: Agent Consolidated Daily Report syntax explanation page, the Agent State Times: Log on Duration says that it is derived using the Agent_Half_Hour.LoggedOnTimeToHalf field, but when i convert this field to HH:MM:SS, it does not match the actual WebView report. But, when I use the Agent_Skill_Group_Half_Hour.LoggedOnTimeToHalfHour, it does match. Which one is correct?
  8. 8. On the agent_26: Agent Consolidated Daily Report, why does the Completed Tasks: Transfer Out contain both the TransferredOutCallsToHalf and the NetTransferredOutCallsToHalf fields? What's the difference between the two? What Transfer out data writes to each field?

Thank you.

Angie Combest

Clarian Health

[email protected]

I have this problem too.
0 votes
  • 1
  • 2
  • 3
  • 4
  • 5
Overall Rating: 0 (0 ratings)
Loading.

You need to be careful when looking at the three databases - Logger, AW, HDS - which use the same schema. But many of what appear to be tables in the AW are really views into the t_ tables in the HDS - the data is not there in the AW DB. You are right to look at the schema - but check with SQL Enterprise to understand a bit more.

In essence, the AW DB is for configuration data and real-time data. The HDS is for historical data. You can query the AW DB for (say) Call_Type_Half_Hour data and join with the Call_Type table to resolve the call type ID into its name - but the data is really in the HDS through the view.

The DB design is quite complex and sophisticated - many things are not obvious.

Keep up your research.

Regards,

Geoff

acombest19 Wed, 08/18/2010 - 09:30

Geoff -

Yes, I understand about data being populated on the different servers. What I don't understand is why? Also, why do some of the tables start with "t_" when none of the documentation I've read has ever mentioned this????

Also, A LOT of the "help" documentation on WebView is completely wrong. If you build the calculations using the tables they give you, it won't match their WebView report output. It really doesn't instill trust in any documentation when they one's I've used have been inaccurate......

Thanks for all the help!!!!

Thx - A

Actions

This Discussion