
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.
Query
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.
SQL Databases
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.
SELECT [UserGUID]
,[Login]
,[Name]
,[Number]
FROM [_VWU].[dbo].[LrUser] order by Created desc
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.
Directory database – It includes all the information about every BCM directory and directory template.
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.
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.
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.
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.
SQL jobs
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).
Agur Koort
Covington Creative - Contact Centers Done Right!
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.