OpenLM Reporting Hub installation guide

Before you start, make sure the server meets the Reporting Hub system requirements. The Data Integration component requires Java JDK 11.

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 hosting the Reporting Hub. Depending on your preference, you can locate it on the same server as the OpenLM SLM or a different one.

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 → ReportingHub folder → Postgres folder → Install postgresql-18.6-3-windows-x64.exe (PostgreSQL 18.6). During the installation, ensure the Stack Builder is left unchecked and remember the password setup for PostgreSQL.

2. Navigate to the Postgres "data" folder (C:\...\PostgreSQL\18\data).

3. Using a text editor with Administrator privileges, open the "pg_hba.conf" file.

4. Look for the IPv4 local connections section.

5. Copy and paste the existing line underneath, then change the ADDRESS from 127.0.0.1/32 to 0.0.0.0/0. Save the changes.
   

   caution

   0.0.0.0/0 accepts connections from any IPv4 address. To limit access, use the address range of your network instead, for example 192.168.1.0/24.

6. Navigate to the Postgres "data" folder (C:\...\PostgreSQL\18\data).

7. Using a text editor with Administrator privileges, open the "postgresql.conf" file.

   • Make sure work_mem is not commented and has 4MB - 12MB of memory, depending on your system. This setting is the maximum amount of memory that a query operation, such as a sort or hash table, uses before it writes to temporary disk files. The default is 4MB (recommended).
   • Make sure shared_buffers is not commented and has 2048 - 8192MB of memory, depending on your system. The default is 128MB.

8. Save the edited file and restart the PostgreSQL service through the Windows Services.

9. Navigate to Windows Start → PGAdmin 4 and launch this app. Otherwise, use the Windows Search bar to look for the app.

10. To connect to the server, select the PostgreSQL 18 server and input the password (Step 1 above). Select OK.

11. Open the context menu of the PostgreSQL 18 server → Create → Database → name the new DB "ReportingHub" then save it.

Power BI

• Navigate to the unzipped Reporting Hub folder → ReportingHub folder → PowerBI 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 ETL\kettle\.kettle folder in a text editor like Notepad).
2. Input source database details. (server, port, username, password, database name&schema).
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, MAC address, and License Name in the designated fields in the License params section.
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 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, emails, 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 select the "Task Scheduler Library."
2. Under "Actions," select "Create Task."
3. Under the "General" tab, 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 "Triggers" tab and select "New."
7. Set the schedule once daily at midnight and select OK.
8. Navigate to the "Actions" tab and select "New."
9. Select the action "Start a program".
10. Select the file "Run ETL.bat".
11. Select OK, and now the scheduled task of the ETL is set.

Run ETL

1. Navigate to the unzipped Reporting Hub folder → ETL folder → activate the "Run ETL.bat" file→Might take a while to finish.

Sample reports

1. Navigate to the "Reports" folder in the "OpenLM Reporting Hub" folder.
2. Activate a report by double-clicking it.
3. In the Power BI → "Edit Queries" → "Data Source Settings" → "Change source"
4. Input the correct PostgreSQL host and database name, then select "OK."
5. Select "Edit Permissions" → "Edit".
6. Input the correct Postgres username and password, then select "OK."
7. Refresh data or "Apply Changes."