Cisco Support Community
cancel
Showing results for 
Search instead for 
Did you mean: 
Announcements

Welcome to Cisco Support Community. We would love to have your feedback.

For an introduction to the new site, click here. If you'd prefer to explore, try our test area to get started. And see here for current known issues.

New Member

ICM SQL Questions (New to Cisco)

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: Agen 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?

Thank you.

Angie Combest

Clarian Health

acombest@clarian.org

6 REPLIES
Hall of Fame Super Red

Re: ICM SQL Questions (New to Cisco)

Hi Angie,

I think you may get a better response within this (Contact Center) section

of the Cisco Support Community

https://supportforums.cisco.com/community/netpro/collaboration-voice-video/contact-center?view=discussions

Cheers!

Rob

New Member

Re: ICM SQL Questions (New to Cisco)

Done. Thanks much!!!!

Thx -  A

New Member

Re: ICM SQL Questions (New to Cisco)

  1. 1. Why do a majority of the tables on our SQL Server start with “t_”?
  2. This may not be an exact answer but here are my thoughts - Cisco setup the tables as t_ for table.  By not giving them their direct name, i.e. Termination_Call_Detail is t_Termination_Call_Detail, you will be more inclined to query the view of the table which does contain it's exact name.  It's a general best practice not to ever build a query against the tables directly and always use the views.  This prevents the possiblity of corrupting the database.  So in short, they skewed the names a bit to try to force people to use the views instead of the tables.
  3. 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)
  4. Somehwat in relation to the first question - all of your real-time and configuration tables will be in the AWDB and all of your historical tables will be in the HDS.  The simple thing to remember is that while the historical tables may be blank in the AWDB, if you look at the view, it will actually pull the information from the HDS table.  So if you are building queries and put the information from 1 & 2 together, you basically get two things.  Always query the views and always query the AWDB.  There should be no reason to ever query a table directly or the HDS DB.
  5. 3. When data is moved to the HDS server every 30 minutes, is it also removed from the corresponding AWDB table?
  6. Only your half-hour tables are moved every 30 minutes.  The rest is replicated from the logger as soon as it has a chance.  Your half-hour tables should always be blank in the AWDB, as they are stored in the HDS DB.
  7. 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.)
  8. I'm not a huge webview user, but I would guess the reason LoggedOnTimeToHalf is pulled from Agent_Half_Hour is because agents can be continously reskilled throughout the day, thus their total login time is actually not reflective of how long they are logged into a skill group.  It is possible to determine how long an agent is logged in throughout the day through the Agent_Skill_Group_Half_Hour table by looking at the duration of logged on time for the outbound skill group that Cisco automatically assigns to the agent for a given Peripheral.  Cisco could've done it this way, but probably was all in who was writing the query.
  9. 5. Also in reviewing the agent26: Agen 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?
  10. Looking at the query, they have several subqueries that join the MRDomainID in the Agent, Skill_Group and Agent_Half_Hour tables to the MRDomainID in the Media_Routing_Domain table.  Most enviornments only use the default MRD, so this is pretty much inconsequential in the template query.

Hope this helps,

Nathan

Green

Re: ICM SQL Questions (New to Cisco)

Good stuff Nathan.

One of the reasons for the t_xx tables and the Views is ICM Partitioning. This has nothing to do with disc partitioning.

ICM Partitioning is not used much any more, although I dare say Cisco do have some very big long-standing customers still using it.

This used Security through the user name and the View to control what you could see. It's more trouble than it's worth.

Regarding 5 - MRDs make more sense when you go add in alternate channels like EIM/WIM.

Regards,

Geoff

New Member

Re: ICM SQL Questions (New to Cisco)

Thanks for the additional info Geoff!!! I really appreciate it!!!!

Thx - A

New Member

Re: ICM SQL Questions (New to Cisco)

This was a MAJOR help!!! Thank you so much!!!!!!

Thx - A

2077
Views
4
Helpful
6
Replies