Skip to main content

Data Mart and ETL

The content in this article applies to On-premise Digital.ai Agility instances only.

Please note that Datamart for Enterprise Edition is ONLY available for local installs, not on hosted instances.

The Data Mart database is a denormalized representation of the highly normalized Digital.ai Agility runtime database. The ETL program is responsible for reading data from the Digital.ai Agility runtime database, making data transformations, and loading the Data Mart database. Because the ETL must understand the Digital.ai Agility runtime database schema, you must upgrade the ETL with each release of the Digital.ai Agility application.

Installing, or managing, a Data Mart instance involves creating, or updating, the Data Mart database schema and the ETL executable (VersionOne.DatamartLoader.exe). In order to accomplish these task Setup needs to be executed as Administrator. On newer versions of Microsoft Windows, this involves more than just logging in as Administrator, or a user with Administrative privileges. The best way to ensure that you run setup with the appropriate privileges is to right-click on the Setup executable and select "Run As Administrator".

If your Agility license includes Analytics, the Data Mart ETL is responsible for updating the metadata used to create Custom Reports. In that case, the Setup process includes a few additional panels that specify the Analytics metadata database connection.

Data Retention Configuration​

Data Mart can delete historical data based on a configured retention period. Deletion is not automatic—it happens only when you run the purge process manually (see Data Purge Process). This feature helps manage storage costs and maintain system performance by removing unnecessary historical data while ensuring compliance with data retention policies.

How Data Retention Works​

Starting with version 25.3, Data Mart includes a data purge capability that removes historical data based on the DataRetentionDays configuration setting. The DataRetentionDays setting defines your retention policy on a rolling basis, but the retention window is only enforced when you run the purge process. Data older than the configured retention period remains in the database until you execute the purge.

Data purge is not executed as part of the regular ETL run. It must be triggered as a separate standalone process using specific command-line options. When DataRetentionDays is configured, the ETL behavior also changes during full loads to process only the retained data instead of the entire dataset.

Configure Data Retention Period​

To configure the data retention period:

  1. Locate, or create, the user.config file in the same directory as VersionOne.DatamartLoader.exe.config:

    • Typical path: C:\Program Files\VersionOne\[instance name]\user.config

    Do not add this setting to VersionOne.DatamartLoader.exe.config itself. Setup overwrites that file on every install, so any value you add there does not survive an upgrade. Settings in user.config are preserved across upgrades.

  2. Open user.config with a text editor (run as Administrator).

  3. Add or update the DataRetentionDays key in the <appSettings> section:

<appSettings>
<add key="DataRetentionDays" value="90" />
</appSettings>
  1. Set the value (in days) based on your data retention policy. For example, 90 retains 90 days of data.

  2. Save the configuration file.

  3. Run the data purge process to delete data older than the retention period. Full ETL loads also use this setting to limit which data they process.

note

Configuring DataRetentionDays does not automatically delete data. You must execute the data purge process separately to remove historical data. See Data Purge Process for details.

Data Purge Process​

The data purge process deletes historical data older than the configured retention period. This process must be triggered manually as a standalone operation, separate from regular ETL runs.

Execute Data Purge​

You can run the purge process using the following command-line options:

Dry Run (Simulation)

VersionOne.DatamartLoader.exe /purge:DryRun
  • Simulates the purge process without deleting any data
  • Displays the number of records that would be deleted
  • Use this option to preview the impact before executing the actual purge

Force (Actual Deletion)

VersionOne.DatamartLoader.exe /purge:Force
  • Executes the actual data purge
  • Permanently deletes records older than the configured retention period
  • Deletion is irreversible. Ensure you have backups before you run this option.

ETL Full Load Behavior​

When DataRetentionDays is configured and you execute a full ETL load:

VersionOne.DatamartLoader.exe /mode:Full

The ETL process loads only the required (retained) data instead of loading the entire dataset. This optimization improves performance and ensures consistency with your data retention policy.

Best Practices​

  • Always run /purge:DryRun first to verify the number of records that will be deleted
  • Schedule the purge process separately from regular ETL runs using Windows Task Scheduler
  • Monitor purge execution through the Data Mart ETL logs
  • Ensure adequate database backups exist before running /purge:Force
  • Consider running purge operations during maintenance windows to minimize system impact

For detailed instructions on running Data Mart commands, see Run the Datamart ETL/Dataloader.

Important Notes​

  • Configuration file: Configure DataRetentionDays in the user.config file. VersionOne.DatamartLoader.exe.config gets overwritten on every install, so the setting does not survive an upgrade if added there.
  • Report impact: Historical reports and trend analysis are limited to the data available within the retention window. Adjust report date ranges accordingly.

Verify Data Retention Configuration​

To verify the data retention setting is working:

  1. Run /purge:DryRun to see the number of records eligible for deletion.
  2. Check the Data Mart ETL logs for data purge execution details.
  3. Query the Data Mart database to confirm the oldest available data matches your retention period.
  4. Monitor storage usage to observe reduction in Data Mart database size after purge operations.

For assistance with data retention configuration, contact Digital.ai Agility support.

Error​

When setup fails, you are presented with a window that indicates the failure and provides an option to open the log file. In most cases the error message clearly indicate what failed (i.e. Insufficient rights to create database), however there are situations when Windows returns a cryptic error code. In these situations, please email Digital.ai Agility support and include the setup log file.

Installing and Configuring Analytics to use an Existing SQL Server User

System Maintenance

On-Demand (V1 Hosted) Maintenance