DataMart Configuration

This optional properties file controls error handling thresholds and the version of SQL Server being targeted.

1. Run your text editor as an administrator and save a text file datamart.properties to your DataMart program files folder. For example, C:\Program Files\DataMart.

2. Enter the properties that follow in the format:

   # Description of the property

   datamart.propertyName=propertyValue

   Property	Description	Default	Permitted Values
   datamart.error.limit	Maximum number of permitted errors for a given error type before the mapping run fails.	250	1 - 1000
   datamart.retry.limit	Number of IWMS Integrated Workplace Management System. This is the Lucernex app url that your organization uses. For example, lxretail.lucernex.com. communication retry attempts.	360	1 - 1000
   datamart.database.dialect	Version of target SQL Server database. DataMart supports SQL Server 2014 or later, and specifying SQLServer2014 enables support for that version or later.	SQLServer	SQLServer2014 SQLServer2016 SQLServer2017 SQLServer2018 SQLServer2022
   datamart.hbm.days	Number of days DataMart stores XML schema mapping files after a run has been processed. Technically there is no limit to how long you can store the XML schema mapping files, however we strongly recommend that you do not set this value to a large number. The reason we make this recommendation is because setting a large value could use up a lot of storage space on your machine.	2	No more than 7
   datamart.parallel.queries	Maximum number of concurrent IWMS queries DataMart can perform.	1	1 - 10
   datamart.parallel.persistence	Maximum number of concurrent persistence threads that read the XML files provided by the IWMS and persist to the target database.	1	1 - 4
   datamart.connection.timeout	SQL Server connection timeout (hours)	10	1 - 32767
   datamart.connection.url.properties	Additional JDBC connection URL parameters	MultiSubnetFailover=Yes	Comma-separated list of SQL Server connection parameters.
   datamart.db.metrics	Enable Javamelody capturing of database metrics	false	true or false
   datamart.unicode	Support unicode field values.	true	true or false
   datamart.auth.method	Authentication mechanism.	COOKIE	COOKIE or JWT
   datamart.use.audit.trail.api	This property is deprecated. Remove the entry from the file or set to false.	false	false
   datamart.batch.size	Batch size for database operations. Size of database persistence in records for a batch for inserts. Available in DataMart 7.0.176 or later.		50 - 100
   datamart.hibernate.naming.strategy	Enables DataMart to use a physical naming strategy that converts camel case to underscores. This ensures database table and column names follow a consistent naming convention. Available in DataMart 7.0.176 or later.	PhysicalNamingStrategyStandardImpl	PhysicalNamingStrategyStandardImpl CamelCaseToUnderscoresNamingStrategy

   These settings enable DataMart to access Lucernex Internet assets. You don't need to restart DataMart after saving this file. DataMart loads these settings when the mapping run starts.

3. Save the file.

If required, your organization can configure DataMart to use your organization's HTTP proxy server so it can access the Lucernex dm.lucernex.com host.

This optional properties file enables the use of a HTTP Proxy by the DataMart when performing IWMS RESTful queries. There are two types of proxy servers supported: non-SSL and SSL.

1. Run your text editor as an administrator and save a text file proxy.properties to your DataMart program files folder. For example, C:\Program Files\DataMart.

2. In the text file enter the proxy host and proxy port in one of the following formats depending on whether your organization uses SSL::

   • SSL:

     Copy

     proxy.properties
     https.proxy.host=proxy.company.com
     https.proxy.port=8080

   • non-SSL:

     Copy

     proxy.properties
     http.proxy.host=proxy.company.com
     http.proxy.port=8080

   The port number and host is different for your organization. Obtain the correct values from your IT department. You also need to change the port number in datamart.bat to this value.

3. Save the file.

   These settings enable DataMart to access Lucernex Internet assets. You don't need to restart DataMart after saving this file. DataMart loads these settings when the mapping run starts.

4. If you specified ab https proxy, create an ssl.properties file.

Create this file if you specified an https proxy in your proxy.properties file. The ssl.properties file configures DataMart to use your organization's SSL proxy server certificate. The options you configure depend on whether your organization's SSL proxy server uses a certificate signed by a trusted certificate authority (CA), such as Verisign. DataMart accepts proxy server SSL certificates that are signed by a CA.

If your SSL Proxy server uses a self-signed certificate, you must create a Java trust store and import your self signed certificate into that trust store.

The properties you need to enter in your file vary depending on whether you are using a certificate authority or a self-signed certificate. These are the properties you might add to your ssl.properties file:

1. Run your text editor as an administrator and save a text file ssl.properties to your DataMart program files folder. For example, C:\Program Files\DataMart.

2. If your organization uses a self-signed certificate:

   1. Create a Java trust store and import your self signed certificate into that trust store.

   2. If you can acquire your proxy's CA certificate as a .pem file, produce a Java keystore by following a third-party online guides such as, Converting PEM-format keys to JKS format.

   3. Copy the generated key store file in the same folder as ssl.properties.

3. Configure the text file in one of the following ways depending on whether your certificate is self-signed:

   • Trusted CA:

     Copy

     # Edit these properties to configure the use of SSL.
     verify.host=true
     self.signed=false

   • Self-signed:

     Copy

     # Edit these properties to configure the use of SSL.
     verify.host=false
     self.signed=true
     trust.store=/Program Files/DataMart/haproxy.jks
     trust.store.password=GenericPassword123
     unlimited.strength=false

     Or:

     Copy

     # Edit these properties to configure the use of SSL.
     verify.host=false
     self.signed=true
     key.store=/Program Files/DataMart/haproxy.jks
     key.store.password=GenericPassword123
     unlimited.strength=false

   The trust/key values shown here are different for your organization.

4. Save the file.

   These settings enable DataMart to access Lucernex Internet assets. You don't need to restart DataMart after saving this file. DataMart loads these settings when the mapping run starts.

datamart.bat contains a number of settings such as the port number, where your mapping information is stored, and the maximum heap that DataMart is permitted to use.

1. Close DataMart in your browser.

2. On the DataMart server, click Start > Windows Administrative Tools > Services.

   Leave this window open. You need it later.

3. Stop the LxDatamart service.

4. Navigate to the folder where DataMart is installed. For example, C:\Program Files\DataMart\bat.

5. Open datamart.bat in a text editor running as an administrator.

   The file looks like this:

   Copy

   set java_home=%JavaHome%
   set java_exe=%java_home%/bin/javaw
   set datamart_home=%~dp0/..
    
   rem Specifies where the mapping info is to be persisted (choices 'hsqldb' or 'sqlServer').
   set spring_profile=hsqldb
    
   rem The port where the datamart web-ui will exist.
   set datamart_port=8080
    
   rem The maximum heap memory for datamart's use (do not specify more that your machine's available memory).
   set datamart_memory=15g
   .
   .
   .

6. Enter values for these settings after =:

   • spring_profile: Enter hsql, sqlServer, mysql, or postgres. If you choose sqlServer, mysql, or postgres, you must configure database properties.

   • datamart_port: The port where you want to access DataMart. The default value is 8080.

   • datamart_memory: The maximum heap that DataMart is permitted to use. The default value is 15g, or 15 GB of memory. You can increase this value; however, be careful not to exceed your machine's available memory. If you do, you run the risk of a crash of the Java Virtual Machine (JVM) which will interrupt your mapping runs.

7. Save your file.

8. If you use an HTTP proxy server:

   1. Navigate to the folder where DataMart is installed. For example, C: > Program Files > Datamart.

   2. Right-click your proxy.properties file and open it in a text editor.

   3. Change the port number to match the one in datamart.bat.

   4. Save and close the file.

9. Re-start the LxDatamart service.

10. Re-open DataMart.

    DataMart may take a few minutes to reload.

You can preserve your DataMart mapping information in a SQL Server database rather than the default Java-based hsqldb in-memory database. This includes information displayed in DataMart such as your execution events, and your mapping configurations.

Use an SQL Server database to:

• Preserve your mapping records when you upgrade to a new version of DataMart.

• Reduce memory use.

• Provide a more robust database.

If you configure DataMart to use SQL Server to store your mapping records, the mapping information that was stored in the hsqldb in-memory database is lost. You must recreate the mapping records in the new database.

1. Configure datamart.bat, and set the spring_profile setting to sqlserver.

2. Navigate to the folder where DataMart is installed. For example, C: > Program Files > Datamart.

3. Open db.properties in a text editor running as an administrator.

4. Enter the connection parameters for your SQL Server database:

   • db.host: SQL Server Host.

   • db.port: SQL Server Port.

   • db.name: SQL Server database.

   • db.instance: SQL Server instance. Leave blank if you do not have one.

   • db.username: SQL Server username.

   • db.password: SQL Server password.

5. Save your file.

6. Recreate your mappings.

You can configure DataMart to send email notifications when significant events occur such as a successful or failed mapping run. If you do not change the defaults in the smtp.properties file, email notifications are not sent.

You can edit the smtp.properties file without stopping DataMart. Changes are detected and applied when you save the file.

1. Navigate to the folder where DataMart is installed. For example, C: > Program Files > Datamart.

2. Edit the SMTP file, smtp.properties, with a text editor such as Notepad.

3. Enter the email and values for your organization:

   • host: SMTP server host.

   • port: SMTP server port. The default is 25.

   • ssl: true or false depending on whether SMTP uses SSL.

   • username: SMTP username.

   • password: SMTP password.

   • from: Email address that notifications are sent from. The default is [email protected].

   • to: Email address to send notifications.

   You must replace the data elements enclosed in <> symbols and delete the <> symbols. Contact your organization's IT department to obtain the values for this file.

4. Rebuild the reporting database (full brush rebuild of the target database from scratch).

Contact your Accruent representative to help you with this configuration.

1. Click Admin in the toolbar in the upper-right corner of the window.

   The System Administrator Dashboard page opens.

2. Click Manage Firm Properties under Data / PS Tools.

3. Select DatamartScripts from the field above the Key (LOGIN1, etc..) column.

   The DatamartScripts page opens.

4. Click Add in the bottom-right corner of the table.

   A window opens.

5. Enter the script name in Key (LOGIN1, etc..).

   DataMart runs scripts in alphabetical order of the Key name.

6. Enter the script in Value (HTML).

7. Click Add.

   The new script record displays in the DatamartScripts table.

Prior to version 4.0.24, only a certain number of fields could be contained on each database table; any overages were added to a subsequent table (for example, Contract1, Contract2, Contract3). Fields on tables are listed in alpha-numeric order. When user-defined fields were added to DataMart, DataMart was trying to add the new fields to tables whose capacity had already been met.

To resolve this, DataMart checks the number of fields which exist on a database table. If the maximum number has been exceeded, new fields are added to a subsequent table. This functionality is called table partitioning. Table partitioning is driven by the standard column quantity and row size limitations imposed by SQL server. The standard column quantity and row size limitations imposed by SQL server are 8,060 Bytes per row in memory-optimized tables and 1,024 columns per table. Review Maximum capacity specifications for SQL Server on Microsoft's website.

By default, a rebuild of the reporting database (full brush rebuild of the target database from scratch) or a delta incremental update of target database run does not reorder the fields in your partitioned tables. If a new field is added to DataMart, the field is added to the end of the applicable partitioned table.

DataMart supports foreign key constraints, which are helpful in cross-table reporting. These foreign keys allow you to use data from your DataMart instance across multiple tables, giving you more flexibility in data analysis.

Contact your Accruent representative to enable this feature. Once enabled, run a full brush.

You can change the number of DataMart queries allowed to be run simultaneously. This improves performance for DataMart if you have a large data set. Parallel processing contains a component that must be modified by your Accruent representative in your Lucernex IWMS environment, and DataMart.parallel properties that you can modify yourself.

These scenarios are impacted by this setting:

• You have multiple queries running in the same instance of DataMart.

• You have multiple instances of DataMart running and each instance of DataMart is attempting to run a query at the same time. In this scenario, the total number of queries allowed cannot exceed the number specified by the setting.

  For example, if there are three instances of DataMart running for your firm and the value of the datamart.parallel.queries setting is 2, one of the three runs waits for a free thread as the aggregated number of concurrent queries exceeds 2.

• A mixture of scenarios 1 and 2.

Contact your Accruent representative to enable this feature. Once enabled, rebuild the reporting database (full brush rebuild of the target database from scratch) to experience the performance improvements.

DataMart’s functionality is provided via a set of RESTful web services. You can use these services to capture your partition mappings by performing a RESTful query to DataMart. You can then create a backup of your partition mappings to use in an emergency, which reduces the risk of breaking any integrations you may have with DataMart. You can then use a RESTful POST call to reapply the partition mappings you exported from DataMart.

DataMart integrates with Swagger, which provides documentation of the DataMart RESTful API. This tool provides you with an easy way to create the GET and POST calls you need to import and export the JSON files that contain your partition mappings.

If you import your table partition mappings into a different instance of DataMart than the one they originated from, you will need to run a full brush in that instance of DataMart. The reason you need to do this is that a delta run compares table mappings each time a run is queued, and if the table mapping didn't already exist, DataMart will have nothing to compare the mapping to.

To view Swagger, add /swagger.html to the end of your DataMart URL.

Export Partition Map

1. In GET /mapping, click GET to expand the query you want.

2. Click Try it out.

3. Click Execute to run the query.

   This generates a list of your DataMart mapping records.

4. If you have more than one mapping:

   1. Find the name of the mapping you want to back up.

      The name is stored in the name parameter of the response body.

   2. Copy the value of the id parameter in the response body.

      This is the primary key of that mapping record.

5. In GET /partition-map/json, click GET to expand the query you want.

6. Click Try it out.

7. In mappingID within Parameters, enter the id you copied earlier.

8. Click Execute to run the query.

   This may take a while, for large partition maps.

9. In the lower-right corner of the response body of the query click Download.

   This downloads the JSON partition mapping file to your machine.

10. Store the partition mapping file in a secure location so that you have it as a backup in case of emergency.

Import partition Mapping File

If you import your table partition mappings into a different instance of DataMart than the one they originated from, you must run a full brush in that instance of DataMart. The reason you need to do this is that a delta run compares table mappings each time a run is queued, and if the table mapping didn't already exist, DataMart will have nothing to compare the mapping to.

1. In the POST /partition-map/json query section, click POST to expand the query you want.

2. Click Try it out.

3. Enter the ID of the mapping record you want to import in the mappingID field of the Parameters section.

   If you enter the ID of a different mapping record, the record you import overwrites the existing record.

4. Click Browse.

5. Find and select the partition mapping file.

6. Click Execute to run the query.

   If the query is successful, it returns a response body with a success message.