DataMart Configuration Version 7.0.55 and Earlier

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

DataMart supports versions of SQL Server newer than 2008, and specifying SQLServer2008 will enable that support. Think of the SQLServer2008 setting as "2008 and newer".

To configure DataMart to use the HTTP proxy server:

1. Right-click your text editor software—for example, Notepad or WordPad—and select Run as Administrator from the field.

2. Save a text file in your C:\Program Files\Lucernex\DataMart folder titled datamart.properties .

3. Enter the appropriate text in the file.

   The table below lists the properties you might add to your datamart.properties file and their attributes.

   Minimum Version	Property	Purpose	Default	Permitted Values
   	datamart.error.limit	Number of errors permitted for a given error type	250	1 - 1000
   	datamart.retry.limit	Number of IWMS communication retry attempts	360	1 - 1000
   	datamart.database.dialect	Version of target SQL Server database	SQLServer2008	SQLServer2005, SQLServer2008, or SQLServer 2012
   5.0.29	datamart.hbm.days	Number of days DataMart stores XML schema mapping files after a run has been processed	2	No more than 7*
   6.0	datamart.parallel.queries	Sets the limit on the number of concurrent IWMS queries DataMart can perform	1	1 - 10
   6.0	datamart.parallel.persistence	Sets the limit on the number of concurrent persistence threads that read the XML files provided by the IWMS and persist to the target database	1	1 - 4
   6.0	datamart.connection.timeout	SQL Server connection timeout (hours)	10	1 - 32767
   6.0	datamart.connection.url.properties	Additional JDBC connection URL parameters	MultiSubnetFailover=Yes	Comma-separated list of SQL Server connection parameters.

   *While technically there is no limit to how long you can store the XML schema mapping files, 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.

4. Save the file.

   The above settings will enable the DataMart to access Lucernex Internet assets. You don't need to restart DataMart after saving this file, as DataMart will load it when the mapping run starts.

Some clients require the use of an HTTP proxy server to control access to the Internet. DataMart can be configured to use the 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. If you specify the SSL variant you will need to also create the ssl.properties file detailed below.

To configure DataMart to use the HTTP proxy server:

1. Right-click your text editor software—for example, Notepad or WordPad—and select Run as Administrator from the field.

2. Save a text file in your C:\Program Files\Lucernex\DataMart folder titled proxy.properties .

3. Enter the appropriate text in the file.

   The text in your file will vary depending on whether you are using SSL or non-SSL. You will use one or the other, but not both. The table below lists the properties you might add to your proxy.properties file and their associated proxy types.

   Property	Purpose	Proxy Type
   http.proxy.host	The Proxy host	non-SSL
   http.proxy.port	The Proxy port	non-SSL
   https.proxy.host	The SSL Proxy host	SSL
   https.proxy.port	The SSL Proxy port	SSL

   A properly configured example is provided below:

   Copy

   1
   2
   3
   proxy.properties
   http.proxy.host=proxy.company.com
   http.proxy.port=8080

   Note:

   Please note that the port number will vary for you. You will also need to adjust the hostname and port specified in the above two lines to reflect your company's HTTP proxy server. You will also need to change the port number in the datamart.bat file.

4. Save the file.

   The above settings will enable the DataMart to access Lucernex Internet assets. You don't need to restart DataMart after saving this file, as DataMart will load it when the mapping run starts.

This optional properties file should be provided in the event you are using a SSL HTTP Proxy—such as https.proxy.host and https.proxy.port in the proxy.properties file. SSL is a complex topic but a few key concepts if understood can guide you on which of these properties are needed for your use case.

The concept of a certificate authority—also known as CA—is important. If your SSL Proxy server is using a SSL certificate signed by a certificate authority such as Verisign, you will not need the trust.store or trust.store.password properties as DataMart will accept your SSL Proxy server's certificate due to it being signed by a well-known CA. If on the other hand your SSL Proxy server is using a self-signed certificate, you will need to create a Java trust-store and import your self signed certificate into that trust-store. Assuming you can acquire your proxy's CA certificate as a .pem file you can follow this guide to produce a Java keystore: Convert pem file to Java keystore.

To configure the ssl.properties file:

1. Right-click your text editor software—for example, Notepad or WordPad—and select Run as Administrator from the field.

2. Save a text file in your C:\Program Files\Lucernex\DataMart folder titled ssl.properties .

3. Enter the appropriate text in the file.

   The text in your file will vary depending on whether you are using a certificate authority or a self-signed certificate. The table below lists the properties you might add to your ssl.properties file and their default values.

   Property	Purpose	Default
   verify.host	Verify host as defined by certificate	false
   trust.store	Path to Java trust store file	null
   trust.store.password	Trust store password	null
   key.store	Path to Java key store file	null
   key.store.password	Key store password	null

   A properly configured example is provided below:

   Copy

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

4. Save the file.

   The above settings will enable the DataMart to access Lucernex Internet assets. You don't need to restart DataMart after saving this file, as DataMart will load it when the mapping run starts.

The datamart.bat file gives instructions for DataMart to use when the DataMart service is started. It controls settings such as the port number, where your mapping information is stored, and the maximum heap that DataMart is permitted to use.

To configure the datamart.bat file:

1. Close the DataMart browser.

2. Open your Start menu.

3. Navigate to All Programs > Administrative Tools > Services.

   Leave this window open. You will need it again for step 10.

4. Stop the Lx DataMart service.

5. Navigate to C: > Program Files > Lucernex > Datamart > bat .

6. Open the datamart.bat file in a text editor software—for example, Notepad or WordPad.

   The file will have the following content.

   Copy

   1
   2
   3
   4
   5
   6
   7
   8
   9
   10
   11
   12
   13
   14
   15
   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
   .
   .
   .

7. Enter the appropriate values, as indicated in the table below.

   Minimum Version	Line Number	Setting	Acceptable Values
   6.0	6	spring_profile	hsqldb: Default Java-based in-memory database sqlServer: Configure Database properties section
   	9	datamart_port	the port where you want to access DataMart. The default value is 8080
   	12	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.

8. Save your file.

9. If you use an HTTP proxy server:

   1. Navigate to This PC > C > Program Files > Lucernex > Datamart.

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

   3. Change your port number.

   4. Save and close the file.

10. Re-start the Lx DataMart service.

11. Re-open DataMart.

    DataMart may take a few minutes to reload.

You have the option to preserve your DataMart mapping information in a SQL Server database rather than the default Java-based hsqldb in-memory database. This mapping information includes things such as information displayed in DataMart’s user interface, your execution events, and your mapping configurations.

There are three main benefits to enabling this feature:

1. Your mapping records will be preserved when upgrading to new DataMart versions,

2. You will see a reduction in memory use, and

3. SQL Server databases are more robust.

To configure your database properties:

1. Follow the Configure datamart.bat file procedures above, and set the spring_profile setting to sqlServer.

2. Navigate to C:\Program Files\Lucernex\Datamart .

3. Open the db.properties file in a text editor software—for example, Notepad or WordPad.

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

   Property	Purpose
   db.host	SQL Server Host
   db.port	SQL Server Port
   db.name	SQL Server database
   db.username	SQL Server username
   db.password	SQL Server password

5. Save your file.

6. Recreate your mappings.

DataMart clients may elect to receive email notifications on both successful and failed executions of DataMart.

It is not necessary to shut down DataMart before editing the smtp.properties file. DataMart checks the modified timestamp on the smtp.properties file and reloads it if changed since the last time DataMart read the file. This function can be performed even if DataMart is currently running.

To configure email notifications for DataMart:

1. Navigate to C:\Program Files\Lucernex\DataMart.

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

3. Enter the appropriate FIRM specific email and user data values, replacing the data elements that are enclosed with the <> symbols.

   Property	Purpose	Default
   host	SMTP server host	null
   port	SMTP server port	25
   ssl	SMTP using SSL	false
   username	SMTP username	null
   password	SMTP password	null
   from	From email address	[email protected]
   to	To email address	null

   Important!

   Be sure to delete the <> symbols.

   Note:

   The To property specifies who is to receive DataMart emails.

   Please contact your FIRM's IT department if you need assistance with configuring this file.

4. Run a full brush.

   To run a full brush, follow the Run a Full Brush / Rebuild Your Target Database procedures.

An Accruent employee will need to help you with this configuration. Please contact Support if you have questions.

To add scripts to generate custom tables in DataMart:

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 link in the Data / PS Tools section of the page.

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

   The DatamartScripts page opens.

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

   A window opens.

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

   Important!

   DataMart will run scripts by alphabetical order of the Key name.

6. Enter the script in the Value (HTML) field.

7. Click Add.

   The new script record appears in the DatamartScripts table.

Prior to the 4.0.24 release, 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. The DataMart 5.0.9 release does a better job of estimating the row size than previous versions, preventing SQL errors.

Behavior as of 5.0.9

Behavior as of 7.0.55

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.

This feature must be enabled by a Lucernex Admin. If you would like to enable foreign key constraints in Lucernex, please contact Accruent Support.

As of DataMart 6.0, you can change the number of DataMart queries allowed to be run simultaneously. This enhancement improves performance for DataMart customers with a large dataset. There are three components to this feature: one that can only be modified by Support in your Lucernex IWMS environment, and two DataMart properties that you can modify yourself.

There are three scenarios that would be impacted by this setting:

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

2. 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, say you had 3 instances of DataMart running against your firm and the value of the datamart.parallel.queries setting was equal to 2. In this example, one of the three runs would fail as the aggregated number of concurrent queries exceeds 2.

3. Some mixture of scenarios 1 and 2.

If you want to take advantage of this feature, please contact your Accruent representative.

DataMart’s functionality is provided via a set of RESTful web services. As of the 7.0.55 release, you can use these services to capture your partition mappings by performing a RESTful query to DataMart. Doing this will allow you to 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 the Swagger API Documentation Tool, 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.

Access Swagger

Export Partition Mapping File

Import Partition Mapping File