BCM SQL databases guide for beginners
With default installation you will have eight BCM databases and two databases for Microsoft Reporting Services. It is good to know what you have in every database to help you with troubleshooting and administration. BCM databases have pretty straight forward databases and table names, so if you look at them, you probably understand what data is in there. However, I hope my examples will help you to start learning about BCM databases.
The easiest way to start a query from SQL Server Managment Studio is to click that big button on the left.
Keep in mind that for every query presented in this article you need to adjust the database name in the query. For example in this line you should change _VWU with Yourdatabasname_VWU.
FROM [_VWU].[dbo].[LrUser] order by Created desc
All the timestamps in the databases are in GMT timezone (same timezone is used in BCM application logs).
You can easily create a query which queries information from every table column for top 1000 rows. Right click on the table and choose „Select Top 1000 Rows“. This is a good way to understand what every table includes.
VWU database – It includes all the historical reporting data and is the easiest place to query user/queue GUID-s and names. These are useful for understanding information from all the other databases.
- LrUser is the best table to get user GUID, name and number in one place. I use order by Created, because this table includes all the agents which have ever been in the system and I want to see the newer information first. So you can also get information about deleted/modified agents.
SELECT [UserGUID] ,[Login] ,[Name] ,[Number] FROM [_VWU].[dbo].[LrUser] order by Created desc
- DimApplicationLevel is the best table to get queue/IVR GUID, name and number from one place. I use order by Creationtime, because this table includes all the queues which have ever been in this system and I want to see the newest ones first. So you can also get information about deleted or modified queues.
SELECT [ApplicationGUID] ,[ApplicationType] ,[ApplicationName] ,[QueueGUID] ,[QueueType] ,[QueueName] ,[QueueDescription] ,[QueueNumber] FROM [_VWU].[dbo].[DimApplicationLevel] order by Creationtime desc
DSArea database – BCM is constantly writing new contacts and new system data (like new users/queues/IVR-s) to this database. It is a temporary database for the SAP BCM historical data creation process (Reporting Data Transformation Process SQL job).
Configuration database – Basically it includes all the configuration information you will insert through System Configurator.
- From AuthLogin table you can reset BCM.Admin password (http://scn.sap.com/docs/DOC-45853).
- From VirtualUnit table you will find all the Virtual Unit names and IP addresses for documentation.
Directory database – It includes all the information about every BCM directory and directory template.
- If you are tracking some missing entries after directory import, you can start with DirectoryEntryAttr table. Search only for first or last name, because every entry has its own datarow in the table. The following query will show if a person with the name „Flanders“ is included in the table.
SELECT [DirectoryEntryGUID] ,[DirectoryMasterListGUID] ,[MultiLingual] ,[Language] ,[TextValue] ,[BinaryValue] ,[TextValueIndex] FROM [_Directory].[dbo].[DirectoryEntryAttr] where [TextValue] = 'Flanders'
Monitoring database – It includes all the information for Online Monitoring for the current day.
- TAMCurrentContactList table includes all the current contacts.
- TAMOperatorStats table includes information about agent current status.
- TAMQueueStatus table includes information about when and who was attached to exact queue.
Monitoring_History database – It includes all the information for Online Monitoring for previous days. Online Monitoring will also show historical information for previous days in this database.
- From Database Virtual Unit you will configure how long historical data will be saved in this database (by default it is 1 month).
- TAOperatorLogDetail table includes different agent events. For example when a user logged in and logged out. From the Timestamp table column you will find the exact time when event happened. Value 1 column includes UserGUID. Value 2 column includes QueueGUID or user number or user terminal type.
SELECT [TimeStamp] ,[Event] ,[Value1] ,[Value2] FROM [_Monitoring_History].[dbo].[TAOperatorLogDetail] order by TimeStamp desc
Outbound database – It includes all the data about outbound campaigns, dialers and classifiers.
Operative database – It includes data connected with messaging like Chat messages, BCM instant messages, e-mails. Also call recording information, script results, voicemail messages and callbacks.
- InstantMessage table includes information about internal instant messages which CDT agents can send to each other.
- ScriptResultDetail table includes information about every script questions/answer.
- ActivityChatData table includes information about every client chat conversation.
- ActivityEmailData table includes information about every e-mail. Data column includes entire e-mail source information, so you will find sender e-mail server, receiver e-mail server, subject, text etc from there.
- VoicemailMessage table includes information about every voicemail. Filename and Filepath column helps to find this voicemail file in the server.
- CallRecording table includes information about every call recording.
- CallBackQueue table includes information about calls in the callback queue.
ReportServer and ReportServer TempDB is used by Microsoft Reporting Services which is used by the BCM Reporting website. It holds information about every report and datasource you have in your reporting website.
Reporting Data Transformation Process – This job runs by default every hour. It will take gathered data from DSArea and move it to the VWU database. Calculated statistics are then generated based on VWU data to Microsoft Analaysis Services OLAP database. Most of BCM standard reports are querying statistical data from this OLAP database.
Reporting OLAP database management – It will empty the OLAP database and calculate all the statistics again based on VWU data. This is first thing to check if you think that your statistics in standard reports are wrong.
Directory: Full-text catalog rebuild and Directory: Full-text catalog update – These are more database specific processes. Basically these jobs update and manage information in Directory database. Learn more about full text indexing: https://www.simple-talk.com/sql/learn-sql-server/understanding-full-text-indexing-in-sql-server/
Monitoring summary data collection – This job constantly updates the monitoring database to show realtime data about calls, agents etc from Online Monitoring. The job must be running all the time. If your Online Monitoring information looks messed up, this is the first thing to check.
Monitoring monitoring daily maintenance – Clears Monitoring tables at the end of the day and removes old data from Monitoring_History database. This is based on the Monitoring Data History Retention Time setting (Database Virtual Unit).
Covington Creative – Contact Centers Done Right!