This chapter describes how Infostore, the logging component of VoiceObjects Server, is used to store information on the behavior of your services and the behavior of the callers using them.

Overview

Infostore provides a mechanism to store specific information about the usage of your services such as call timestamp, dialog duration, etc. into a relational database management system (RDBMS) for analytic purposes. Through this functionality you gain valuable insight into the usage and acceptance of your services.

Specifically, Infostore is designed to provide the different departments of your enterprise with answers to the following questions:

Administrators and system operators:

·          What is the (average/minimum/maximum) workload of your servers?

·          What are the peak workloads and when do they occur?

·          At what time will maintenance system downtime have minimum impact?

·          Is the current number of ports sufficient? Are the servers properly sized?

·          Is the load on your cluster evenly balanced?

·          What is the percentage of aborted and rejected sessions?

Dialog designers and developers:

·          Are there daily/monthly trends in the use of your application?

·          How many of your sessions result in an error?

·          What are your most actively used services?

·          Which prompts lead to an unusually high rate of No Input/No Match events?

Business analysts and marketing specialists:

·          How many unique callers do you have?

·          How many callers call repeatedly? How often do they call?

·          How much time (on average/minimum/maximum) do callers spend in your application?

·          What are the callers’ major navigation patterns?

The session statistics are held in server memory during the course of each dialog and are written to the log tables after the session has ended. This way the execution time of the dialog is not affected and the impact on the performance of the server is negligible.

The session data is collected and stored inside a denormalized Snowflake Schema and thus is immediately ready for analysis by means of a third party BI (Business Intelligence) or reporting solution vendor. You can even access the data by executing manual SQL statements against the database tables.

As there is no ETL (Extract-Transform-Load) process involved and all statistics data are written directly to the analytic data model, all analyses are based on the most current data level.

i8  Note: Usage statistics can only be monitored if System DB logging for the specific service is enabled, which is indicated by a green light in the System DB logging column on the Server Management tab in the Control Center (in Desktop for Web on the Server Manager tab). For further information on enabling/disabling the System DB logging refer to Chapter 2 – Configuring Servers and Services in the Deployment Guide.

Scope of Data

Infostore contains Custom DB logging and System DB logging.

Custom DB logging can be used to store application-specific data in custom tables. You can activate Custom DB logging by using the Log object and setting the Destination to Custom DB (see section on Log object below).

System DB logging automatically stores session statistics in the Infostore Repository.

The following section provides a detailed overview of the scope of data that is logged by System DB logging.

Dialog information

Infostore stores information about the dialogs on different levels of granularity. On the uppermost level information about the dialog is logged. This includes information about date and time of the session, the caller’s ANI, the number of events and so forth. If service chaining is used, this information also contains references to the master session so that the complete dialog chain can be tracked. For further information on service chaining, refer to Exit in the Object Reference.

In addition, all Layer objects and their selected states are stored in the Infostore Repository. If a layer state has been changed multiple times during the dialog, only the state that was active at the end of the dialog is stored.

@8  Tip: You can exclude certain layers from being logged to Infostore by disabling the Enable Layer State logging option in the editor of the Layer object (see Layer in the Object Reference).

On a more detailed level, Infostore stores data about which objects were processed in the course of one dialog. This data includes information about the Module objects that the caller accessed during the dialog. This information is separated into module sets, module sequences and module subsequences.

A module set stores a list of all modules that were processed in one dialog. Within a module set the modules are sorted alphabetically by their reference IDs and thus do not contain information about order and frequency. Module sets may be important for your analysis if you want to know how often a specific combination of modules was accessed.

The module sequences on the other hand contain the same information as module sets but with additional information about order and frequency. You can use module sequences to find out how often one specific module was processed during one dialog or to identify sequence patterns.

The module subsequences contain information about sequences that are contained inside the module sequences. With the subsequences, you can analyze the paths that the caller took to reach a specific module.

@8  Tip: You can exclude certain modules from sets, sequences and subsequences by disabling the Enable history tracking option in the editor of the Module object. This might be applicable if you want to exclude the start module of your application from module sets and module sequences as all dialogs inevitably use the start object. It could also be helpful if you have an application with a large number of modules that do not all represent a complete sub-application.

i8  Note: Module related data is stored by referencing each module by its reference ID. Consequently, if a Module object has changing reference IDs throughout redeploys, it is not possible to treat it as the same object when creating reports.
To assure a consistent reporting on module level, it is recommended to assign custom reference IDs for modules.

On the lowest level of granularity Infostore provides logging on input state level. One input state represents a point in the dialog where the server expects input from the caller. On this level, information about the recognition, such as utterance, input mode, and confidence is stored.

i8  Note: You can disable logging on input state level by disabling the Enable Input State logging option in the Service editor (see Chapter 2 – Configure Servers and Services in the Deployment Guide.

For a more detailed list of all information logged by Infostore refer to Chapter 2 – The Logical Data Model.

Server statistics

Apart from statistics about the dialog itself, Infostore also logs information about workload of servers, server instances and services. This data includes:

·          Number of active sessions

·          Number of finished sessions

·          Number of aborted sessions

·          Number of rejected sessions

These server statistics are stored as snapshot data. At a configurable interval Infostore retrieves the current workload from the server and stores the current values of the above-mentioned counters in the database. In addition to the current values, also the difference between the values of the current snapshot and the values of the last snapshot are written to the database.

These difference counters are also logged for the following data:

·          Number of requests and volume bytes

·          Call and processing duration

·          Number of script and connector executions

·          Minimum, maximum and total connector and script execution times

In addition to the number of sessions, information about the concurrent session limits is stored. By license restrictions or by setting this value manually in the Service and Server editor you can specify the maximum number of concurrent sessions that can be processed by VoiceObjects Server. If this limit is exceeded, the particular service or server does not accept any additional sessions.

This information helps you to find out if you need to adjust the assignments of the available sessions to your servers and services. You can also identify how many incoming calls, if any, were rejected because there were insufficient concurrent sessions left.

By default the logging interval is set to one minute, but you can change this interval by modifying the following entry in the components.xml file:

<statisticsUpdateInterval>60000</statisticsUpdateInterval>

The interval must be specified in milliseconds.

While a smaller interval increases accuracy of the snapshot data it also leads to a higher amount of data written and thus disk space needed. However, setting the interval to a higher value will also impact the size of the difference counters. Depending on your environment, the interval should be chosen carefully.

Recordings

In addition to data about the dialog and each input state, Infostore stores information about all recordings that occurred in a session. This includes data about Recording objects as well as utterance recordings.

This data can be used to get an overview on the number of recordings and the disk space occupied for each service. In addition path and filename information for each individual recording is stored, which can then be used to retrieve the files.

Business tasks

Business Task objects can structure applications in terms of specific business operations such as e.g. logging into an account or performing a transaction.

Infostore stores detailed information for each business task, such as start and end point, duration, completion status and recognition related information (e.g. No Match and No Input rates). Logging of business task related information is automatically enabled when activating System DB Logging for a service.

With this information you can follow up on completion and success rates of specific tasks, you can identify problems while executing a task or the impact of marketing announcements.

For further information about how to use business tasks, refer to Chapter 11 – How to Define Business Tasks in the Design Guide or to Business Task in the Object Reference for details on the Business Task object.

Service and deployment history

For each deployment command (restore and redeploy), Infostore stores name and description of the deployed start object and its project. Along with information about the service state, e.g. configuration and filter settings of Infostore, this information is automatically logged, when System DB Logging is activated for a service.

Using this information, you can find out how often new versions of your applications are deployed and how long they are active. By combining this data with information about caller behavior, you can analyze the impact of application changes on the caller.

Extensible Data Model

One major benefit of the VoiceObjects logging architecture is the possibility to extend the system logging information with custom data. This custom data can be application-specific information that is not gathered by the System DB logging. It can also be data from CRM or legacy systems. This way you can use existing data about your callers or customers and combine it with the session statistics of Infostore. You can also map log information from the media platform to the system statistics. The following section provides a description of how to connect these different data sets.

Log object

Using the Log object with Custom DB logging is useful if you want to store application-specific data that is not in the scope of System DB logging. You might for example want to store certain choices that the caller made during a session or the responses he got from an external system.

To do so, follow these steps:

1.     Create a table containing the required columns to store your custom data inside the custom log repository. For configuration of the location of the custom log repository refer to Chapter 1 – Advanced Configuration of VoiceObjects in the Administration Guide.

1.     In the Log editor, set the Destination to Custom DB.

1.     In the Log Statement field, enter the SQL statement. This statement can include expressions or variables. To be able to map your custom data to the dialog, you need to include the VoiceObjects internal ID of the dialog (through the function Session(DIALOGID)) in your SQL statement.

To simplify the mapping to the tables of the Infostore Repository you can use the following wildcards inside the SQL statement that represent key values from tables:

Wildcard	Description
@MD_REP_SID@	Metadata Repository ID
@SRV_SID@	Server surrogate ID
@SRV_HOST_IP@	Server IP address
@SRV_INST_PORT@	Server instance port
@SRV_INST_NAME@	Server instance name
@VSC_SID@	Service surrogate ID
@SITE_SID@	Site surrogate ID
@SITE_GUID@	Site GUID
@DAY_ID@	Day ID for start of dialog – YYYYMMDD
@MINUTE_ID@	Minute ID for start of dialog – HHMM
@SECOND_ID@	Second ID for start of dialog – SS
@MASTER_DIALOG_ID@	ID of master dialog (for use with service chaining)
@DLG_ID@	Unique dialog ID
@DLG_CRMID@	Value that was assigned to the CRM ID using Session(CRMID, [arg])
@DLG_GCID@	Value that was assigned to the global call ID using Session(GCID, [arg])
@TASK_SID@	Surrogate ID of the business task that is currently active
@TASK_COUNTER@	Counter of the business task instance that is currently active

These wildcards will be resolved at call time to their respective values. The wildcard names correspond to the column names in the Infostore Repository.

i8  Note: The wildcards @MD_REP_SID@, @SITE_SID@, and @TASK_SID@ will only be replaced when Custom DB is selected as destination.

Media platform session ID

Another way of enhancing the system logging information is to extend the Infostore Repository with log information from the media platform. As the dialog fact table provides a column for the session ID of the media platform, this data can easily be merged. To set the session ID on VoiceObjects Server, you can either add the parameter MPSID=<value> to the initial connection URL from the media platform or you can set the ID manually by using the expression Session(MPSID, [<arg>]). The value that was assigned to the MPSID is automatically written to the Infostore Repository after the session has ended.

CRM ID

The CRM ID can be used as an alternative to uniquely identify a caller. By default callers are identified by their phone number (ANI). If you have an application that uses an authentication mechanism to identify the caller, you can store this value. By assigning the CRM ID a value that is also available in your corporate data warehouse, as e.g. a customer or account number, you can connect your session statistics with your customer data in your corporate CRM data warehouse.

The CRM ID can be set by using the Expression Session(CRMID, [<arg>]) or by adding the parameter CRMID=<value> to the initial URL from the media platform. This value will be stored in a dedicated column in the Infostore Repository at the end of the dialog.

Global call ID

The global call ID can be used to store a global session ID from your CTI or call control environment. By using the global call ID you are able to track a given call during its different stages within your environment. To set the global call ID on VoiceObjects Server you can either add the parameter GCID=<value> to the initial connection URL from the media platform or you can set the ID manually by using the expression Session(GCID, [<arg>]). If you don’t set the GCID explicitly, a unique call ID is automatically created and assigned to this variable. By using the expression Session(GCID) you can retrieve this call ID and pass it along when the call is transferred to an operator or a different CTI system. If the dialog is returned to the server you need to pass this call ID back via the initial URL.

The value that was assigned to the global call ID is automatically written to the Infostore Repository after the session has ended.

Encryption Features

To ensure data protection and privacy Infostore provides the possibility to store sensitive information of your sessions in an encrypted format. The following paragraph describes the different areas of encryption.

ANI encryption

By default, Infostore stores the caller’s ANI in clear text along with other call level information in the Infostore Repository. When ANI encryption is enabled, the ANI is encrypted with a one-way encryption algorithm before it is written to the Infostore Repository. Once encrypted and stored in the database it is not possible to revert to the original caller’s telephone number from the encryption string. Since any given telephone number always generates the same unique encryption string, it can still be determined if two sessions were initiated from the same or different subscribers.

To enable ANI encryption, modify the following tag in the components.xml file:

<encryptANI>true</encryptANI>

You need to restart the server for the changes to take effect.

Utterance and slot values

With detailed statistics about each input state Infostore also stores the original input of the caller and the associated slot names and values in the Infostore Repository. To suppress storing this information in a clear-text, human-readable format, each Input object contains a property Mask Caller Input. When this option is enabled, all utterances and the slot values for this input are encrypted when written to the Infostore Repository.

As the same algorithm is used as for the ANI encryption, the encrypted string is also unique and one utterance always generates the same encryption string.

Sizing Estimates

Infostore retains a complete history of detailed statistics about each dialog. Especially in environments with high caller concurrency the data stored inside the Infostore Repository can grow rapidly. Therefore it is recommended to schedule a deletion process on the database that removes all data older than a specified timeframe. To estimate sizing for the Infostore Repository database and the deletion process timeframe, this section provides data storage estimates for these tables.

i8 Note: The following sizing estimates do not include size of indexes and statistics. They only provide a guideline of expected table sizes. As storage size is dependent on the RDBMS and configuration parameters used, the actual values in a specific environment might differ.

i8 Note: To simplify the calculation of sizing estimates for an Infostore database in your environment an Excel sheet Infostore_SizingEstimates_[VersionNumber].xls is provided. The sheet is located in the folder <VOICEOBJECTS_HOME>\Platform\Docs.

Lookup tables

The Infostore Repository consists of twenty-five lookup tables, nine fact tables and four aggregate tables. Data in the lookup tables is either static (e.g. for the date and time lookup tables) or is refreshed at every server start or service redeploy. As these tables do not contain information on dialog level, the amount of data stored here is manageable. The total storage of the lookup tables consumes approximately 6 MB.

Dialog and input states

In the dialog fact table VOLDDLGSTS, one row is written for each session on the voice system. One row approximately consumes 700 Bytes of disk space.

In the input states fact table VOLDDSSEQ, one row is written for each input state that occurred in a dialog. One row consumes approximately 500 Bytes of disk space.

Example 1:

Running an application with 1,000 sessions per day and an average of 15 input states per sessions:

Dialog fact table: 700 Byte * 1,000 = 700KB per day

Input state fact table: 500 Byte * 15 * 1,000 = 7.5MB per day

Running the site 24/7: (700 KB + 7.5 MB) * 365 days = 2993 MB + 6 MB static lookup information = 2999 MB occupied disk space per year.

Example 2:

Running an application with 100,000 sessions per day:

Dialog fact table: 700 Byte * 100,000 = 70 MB per day

Input state fact table: 500 Byte * 15 * 100,000= 750 MB per day

Running the site 24/7: (70 MB + 750 MB) * 365 days = 299 GB + 6 MB static lookup information = 299 GB occupied disk space per year.

Module sets and sequences

In the module sets and module sequences fact tables VOLDMODSEQ and VOLDMODSET, one row is written for each distinct module set or sequence. One row in each of these tables consumes approximately 5 KB of disk space. The additional table VOLDRELMSQ stores one row for each module of the module sequences. One row in this table consumes approximately 100 Bytes of disk space. The table VOLDSUBSEQ stores information about each subsequence. One row in this table consumes approximately 500 Bytes of disk space.

Example:

500 module sequences and 300 module sets with an average length of five modules per sequence:

VOLDMODSEQ: 500 module sequences * 5 KB = 2.5 MB

VOLDMODSET: 300 module sets * 5 KB = 1.5 MB

VOLDRELMSQ: 500 module sequences * 5 modules * 100 Bytes = 250 KB

VOLDSUBSEQ: 500 module sequences * 5 modules * 500 Bytes = 1.25 MB

2.5MB + 1.5 MB + 250 KB + 1.25 MB = 5.5 MB

Server statistics

There are five tables, which hold information about server statistics:

In the table VOLDLOGSRV one row for each service and server instance is written per interval. One row in this table consumes approximately 300 Bytes of disk space.

In the table VOLDAGGRSS one row for each server instance is written per interval. One row in this table consumes approximately 300 Bytes of disk space.

In the table VOLDAGGRSV one row for each server and service is written per interval. One row in this table consumes approximately 150 Bytes of disk space.

In the table VOLDAGGRES one row for each server is written per interval. One row in this table consumes approximately 150 Bytes of disk space.

In the table VOLDAGGSIT one row for each site is written per interval. One row in this table consumes approximately 150 Bytes of disk space.

Example:

Having four server instances with five services, three sites and the interval configured to 1 minute:

60 min * 24 hours * 300 Byte * 4 instances * 5 services = 8640 KB per day

60 min * 24 hours * 300 Byte * 4 instances = 1728 KB per day

60 min * 24 hours *150 Byte * 5 services = 1080 KB per day

60 min * 24 hours * 150 Bytes * 1 server = 216 KB per day

60 min * 24 hours * 150 Bytes * 3 sites = 648 KB per day

(8640 KB +1728 KB + 1080 KB + 216 KB + 648 KB)* 365 days = 4494 MB per year

Recordings

For each recording one row is written to the table VOLDRECLOG. Each row in this table approximately consumes 600 Bytes of disk space.

Example:

Running an application with 1,000 sessions per day and an average of 10 recordings per sessions:

600 Byte * 1,000 * 10 = 6 MB per day

6 MB * 365 days = 2190 MB per year

Business tasks

For each Business Task object that was started one row is written to table VOLDTASKSTATS. One row in this table approximately consumes 500 Bytes of disk space.
For each parameter of each finished business task, one row is written to table VOLDTASKDATA. One row in this table approximately consumes 300 Bytes of disk space.

Example:

Running an application with 1,000 sessions per day and an average of 5 business tasks, with each business task having an average of 3 parameters:

500 Bytes * 5 * 1,000 = 2 MB per day

300 Bytes * 3 * 5 * 1,000 = 4.5 MB per day

(2 MB + 4.5 MB) * 365 = 2372 MB per year

Service and deployment history

For each deployment command (redeploy and restore) one row is written to table VOLDVSCOBJ. One row in this table approximately consumes 300 Bytes of disk space.

Example:

Running an environment with 5 services and an average of 4 redeploys per month:

300 Bytes * 4 * 5 = 6 KB per month

6KB * 12 = 72 KB per year

Maintaining the Infostore Repository

8     Caution: Infostore does not perform database administrative and maintenance activities. As these tasks are mandatory to maintain a good reporting environment, it is strongly recommended to involve the database administration team into the planning and maintenance of the Infostore database.

Depending on the number of sessions and thus the amount of data that is stored there are a number of tasks that should be taken. These tasks could include, but are not limited to the following:

·          The table statistics should be updated regularly to ensure a most efficient execution plan of the SQL statements. This can be done by running the LDStatistics.sql scripts for your database system. Depending on your requirements and the update frequency, a nightly or weekly schedule should be used.

·          Old data that is no longer used for reporting purposes should be deleted from the Infostore Repository in a regular interval. This might be done e.g. on a monthly schedule, deleting all data older than 3 months.
Find below a strategy to delete Infostore data that is older than a specific day.

·          As with VoiceObjects 7, you can configure Infostore to only store statistics for a subset of your sessions. By using Infostore filtering on a per service level you can limit the overall amount of data that is stored in your Infostore Repository. However, you can still report on the session usage based on these sample data.

·          Input State logging generates detailed log information for each session. Typically this information is only needed in specific situations, e.g. when deploying a new version of your application. It is therefore recommended to disable Input State logging for your services when detailed data is not needed.

·          Depending on your reporting requirements and the reporting tool you are using additional database indices may be required. Consult your database administration team to identify slow running queries and to identify and set according indices.

·          There are several database settings that can drastically improve performance, such as the block size of tables, the optimizer mode etc. As these settings are very specific to the database system used and your environment, you should consult your database administration team to tune these settings.

Infostore data deletion strategy

This paragraph provides some best practices on how to remove data from the Infostore Repository. The described strategy shows how to delete server and session data that is older than a specific day. Additional steps are provided to optionally remove also related dimensional data.

Before adapting the script and executing delete commands on the Infostore Repository, ensure that your reporting and analysis requirements are defined and accordingly matched by the script. The requirements must at least define the period of time data is preserved in Infostore and the recurrence of the delete job. Furthermore you might backup, archive or aggregate the data for later use before finally deleting the data.

8     Caution: The following deletion strategy and the shown SQL examples will delete data from the Infostore Repository. Since this data deletion might be irrevocable make sure that future reporting requirements can be fulfilled by the remaining data and ensure that the SQL that is executed meets your intention.
VoiceObjects doesn’t warrant that the following scripts are error free, support your database system or meet your functional requirements. VoiceObjects is not liable for any loss of data and not liable for any data recovery.

Depending on the data volume to be deleted the execution of DELETE commands can take time, may slowdown the database performance or may lock tables. In order to prevent the data write operations of VoiceObjects Server from failures it is recommended to perform this task during maintenance windows, or at least not during peak-loads.

Redo logs are typically written during delete operations. Before executing delete scripts ensure that the redo logs are of sufficient size. You might consult the database administration team to setup the according configuration.

Data deletion concept

All fact data stored contains a day key, the DAY_ID. This DAY_ID is of numerical value in format YYYYMMDD, like 20080408 or 20081231.

The deletion conditions stated below use a [day] placeholder that should be replaced by the DAY_ID for the oldest day that will remain in the database. The format of the [day] parameter must be YYYYMMDD like 20080430.

Example:

If you want to delete all data before April 1^st, 2008, replace the [day] with 20080401.

 

For Infostore configurations using partitioned fact tables data deletion can alternatively be done by month. Dropping the month partitions with an SQL statement like ALTER TABLE DROP PARTITION for the according fact tables in step 1 and 2 will be of better performance and will not fill the redo logs.

 

The following steps and the sequence of commands given ensure the consistency of the Infostore Repository across all fact and dimensional data. Therefore obey the sequence of commands given when deleting data. Other tables than those listed need not and should not be touched.

Step 1: Remove data from server log tables

The following commands remove all data from the server log tables that are older than the specified day.

delete from VOLDAGGSIT where DAY_ID < [day];

delete from VOLDAGGRSV where DAY_ID < [day];

delete from VOLDAGGRSS where DAY_ID < [day];

delete from VOLDAGGRES where DAY_ID < [day];

delete from VOLDLOGSRV where DAY_ID < [day];

 

Step 2: Remove the service specific session data

The next commands first remove all session related data of sessions that where logged before the specified day. After that all session entries are removed.

delete from VOLDTASKDATA where DLG_ID in (select DLG_ID from VOLDDLGSTS where DAY_ID < [day]);

delete from VOLDTASKSTATS where DLG_ID in (select DLG_ID from VOLDDLGSTS where DAY_ID < [day]);

delete from VOLDRECLOG where DLG_ID in (select DLG_ID from VOLDDLGSTS where DAY_ID < [day]);

delete from VOLDDSSEQ where DLG_ID in (select DLG_ID from VOLDDLGSTS where DAY_ID < [day]);

delete from VOLDDLGSTS where DAY_ID < [day];

 

Step 3: Optionally delete corresponding module sequence and layer data

In some deployments the storage of module sequences can also result in large data volumes. The next commands show how to remove this data and the data of layers and layer states. If the service is still deployed, deleted data may get recreated by the server if needed.

delete from VOLDMODSEQ where MOD_SEQ_SID NOT IN (select MOD_SEQ_SID from VOLDDLGSTS);

delete from VOLDMODSET where MOD_SET_SID NOT IN (select MOD_SET_SID from VOLDDLGSTS);

delete from VOLDSUBSEQ where MOD_SEQ_SID NOT IN (select MOD_SEQ_SID from VOLDDLGSTS);

delete from VOLDRELMSQ where MOD_SEQ_SID NOT IN (select MOD_SEQ_SID from VOLDDLGSTS);

delete from VOLDLYRSET where LYR_SET_SID NOT IN (select LYR_SET_SID from VOLDDLGSTS);

delete from VOLDLYRREL where LYR_SET_SID NOT IN (select LYR_SET_SID from VOLDDLGSTS);

 

Step 4: Optionally remove data of services not deployed anymore

This optional step shows how to remove all dimensional data of services that are not deployed anymore and that are not having any fact data stored in Infostore.

8     Caution: The execution of the following commands for services that are still deployed will result in data inconsistency and can result in failures during service operation by VoiceObjects Server.

The following commands require a parameter [list_of_notdeployed_services] to identify all services that are not deployed anymore. The parameter can be a comma-separated list of service IDs like [3, 2, 6] or [14].

To determine the corresponding service IDs open the table VOLDVSCOBJ.

select * from VOLDVSCOBJ;

The table contains a row for each service deployment and provides detailed information about the service and the deployment. The column VSC_SID uniquely identifies a service and can be used in the [list_of_notdeployed_services] to identify the services for which the dimensional data should be deleted.

delete from VOLDMODULE where VSC_SID in [list_of_notdeployed_services] and MOD_SID not in (select MOD_SID from VOLDRELMSQ);

delete from VOLDLYRSTATE where VSC_SID in [list_of_notdeployed_services] and LYRS_SID not in (select LYRS_SID from VOLLYRREL);

delete from VOLDLAYER where VSC_SID in [list_of_notdeployed_services] and LYR_SID not in (select LYR_SID from VOLDLYRSTATE);

delete from VOLDTASK where VSC_SID in [list_of_notdeployed_services] and TASK_SID not in (select TASK_SID from VOLDTASKSTATS);

delete from VOLDVSCOB where VSC_SID in [list_of_notdeployed_services] and VSC_SID not in (select VSC_SID from VOLDDLGSTS) and VSC_SID not in (select VSC_SID from VOLDAGGRSV);

Similar to the deletion of service entries not deployed server data can be deleted. To identify not deployed servers open the table VOLDSRVOBJ.

select * from VOLDSRVOBJ;

The SRV_SID uniquely identifies a server.

delete from VOLDSRVOBJ where SRV_SID in [list_of_notdeployed_servers]
and SRV_SID NOT IN (select SRV_SID from VOLDLOGSRV)
and SRV_SID NOT IN (select SRV_SID from VOLDDLGSTS);