How to install the OpenLM Reporting Hub

Currently, the installation and configuration of the OpenLM Reporting Hub and reporting system require the presence of an OpenLM support person.

It is recommended to contact our support team at support@openlm.com, and our representatives will be glad to assist and guide you online.

Download and Unzip #

1. Download the OpenLM Reporting Hub and unzip it to the server which will house the Reporting Hub.
2. Unzip the file to the server which will host the Reporting Hub. You can locate it on the same server as OpenLM Server or a different one, depending on your preference.

License File #

1. Go to the unzipped Reporting Hub folder
2. Navigate to ETL folder → Jobs folder → License folder
3. Paste the license file into the license folder

Reporting database #

1. Navigate to the unzipped Reporting Hub folder → Postgres folder →Install “Postgres Editor.msi” →Install “Postgres Server.exe“.
2. Navigate to the Postgres “data” folder (C:\…\PostgreSQL\14\data) → Edit the file “pg_hba.conf” → Add the following line “host all all 0.0.0.0/0 md5“
3. Navigate to the Postgres “data” folder (C:\…\PostgreSQL\9.5\data) →Edit the file “postgresql.conf”
4. Make sure work_mem is not commented out and has 4MB – 12MB of memory, depending on your system
5. Make sure shared_buffers is not commented out and has 2048MB – 8192MB of memory, depending on your system
6. Save the edited file and restart the PostgreSQL service via the Windows Services.
7. Activate PGAdmin→ Connect to the Postgres server → right click on the server → Create database → name the new DB “ReportingHub” then save it.

Power BI #

• Navigate to the unzipped Reporting Hub folder →Power BI folder → Install “Power BI Desktop.msi” → Install “Power BI Connector.msi” (All components on the entire machine including GAC component).

Connection properties #

1. Navigate to the unzipped Reporting Hub folder → ETL folder → Run the “Edit_connection.bat” file (if you do not see a prompt to open, open the kettle.properties file in the kettle\.kettle folder in a text editor like Notepad)
2. Input source database details. (server, port, username, password, database name).
3. Input destination Postgres database details (server, port, username, password, database name).
4. (Optional) Input destination MSSQL or MySQL database details (server, port, username, password, database name).
5. Input the server hostname and MAC address in the designated fields.
6. Input SMTP server details (server, port, username, password, sender email, and destination email)

Available properties:

ETL_TIMEZONE=int value 0..24 Default 0: TimeZone Offset
ETL_LIVE=true/false Default false : (Not yet implemented)
ETL_DATA_AGGREGATION_BY_HOUR= true/false (Minimal data aggregation per hour if ‘true’ or per day if ‘false’).’
ETL_RUN_ON_INCREMENTS= true/false (Increment the data each run if ‘true’, or sync the entire dataset each time if ‘false’)
ETL_COMPILE_RESERVED_LICENSES= true/false (Default true: Consider reserved licenses as used licenses if ‘true’, disregard reservations if ‘false’)
ETL_SHOW_ONLY_TRUE_DENIALS= true/false Default false: true/false Extract only true denials if ‘true’, extract false denials as well if set to ‘false’
ETL_DENIALS_AGGREGATION_PERIOD= integer value. Default 0 (Time interval in minutes to consider for denials aggregation)
ETL_EXPORT_DENIALS_INTERVAL= integer value. (Default 7 periods in days for each transfer iteration. Values: 7-30 (7 for bigger DB’s 30 For smaller)
ETL_ANONYMIZE=true/false (Default false: If set to “true” it will obfuscate usernames, hostnames mails, and other sensitive fields in the target database)
ETL_FILTER_BY_VENDOR=accepts a CSV string as input. Default empty. (If specified – it will process data only for vendors from this list)

ETL scheduling #

1. Activate “Windows Task Scheduler” and choose the “Task Scheduler Library”.
2. Under “Actions”, click on “Create Task”.
3. Under the tab “General”, Name the task “OpenLM ETL”.
4. Check the checkbox “Run whether the user is logged on or not”.
5. Check the checkbox “Run with highest privileges”.
6. Navigate to the tab “Triggers” and click on “New”.
7. Set the schedule to be once a day at 12:00 AM and click ok.
8. Navigate to the tab “Actions” and click on “New”.
9. Choose the action “Start a program”,
10. Select the file “Run ETL.bat”.
11. Click OK, and now the scheduled task of the ETL is set.
12. 7. Run ETL
13. Navigate to the unzipped Reporting Hub folder → ETL folder → activate the “Run ETL.bat” file → Might take a while to finish.
14. 8. Sample Reports
15. Navigate to the “Reports” folder, found in the “OpenLM Reporting Hub” folder.
16. Activate a report by double-clicking it.
17. In the Power BI → “Edit Queries” → “Data Source Settings” → “Change source”
18. Input the correct Postgres host and database name then press “OK”.
19. Click on “Edit Permissions” → “Edit”
20. Input the correct Postgres username and password then press “OK”.
21. Refresh data or “Apply Changes”