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:
-
Locate, or create, the
user.configfile in the same directory asVersionOne.DatamartLoader.exe.config:- Typical path:
C:\Program Files\VersionOne\[instance name]\user.config
Do not add this setting to
VersionOne.DatamartLoader.exe.configitself. Setup overwrites that file on every install, so any value you add there does not survive an upgrade. Settings inuser.configare preserved across upgrades. - Typical path:
-
Open
user.configwith a text editor (run as Administrator). -
Add or update the
DataRetentionDayskey in the<appSettings>section:
<appSettings>
<add key="DataRetentionDays" value="90" />
</appSettings>
-
Set the value (in days) based on your data retention policy. For example,
90retains 90 days of data. -
Save the configuration file.
-
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.
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:DryRunfirst 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
DataRetentionDaysin theuser.configfile.VersionOne.DatamartLoader.exe.configgets 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:
- Run
/purge:DryRunto see the number of records eligible for deletion. - Check the Data Mart ETL logs for data purge execution details.
- Query the Data Mart database to confirm the oldest available data matches your retention period.
- 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.
Related tasks
Installing and Configuring Analytics to use an Existing SQL Server User