Reporting Server/Reporting Table Development Steps
This document discusses the steps to take for creating a new reporting table in the reporting datawarehouse, defining the load script and deploying it on a reporting server.
This how to assumes that you have a fully running reporting development environment. To set up a development environment with reporting modules please checkout out this page.
Also make sure that you have a module defined for your custom work. In this howto we will use a specific module.
This howto also assumes that your Openbravo development is fully build and has a running tomcat to access its user interface.
The reporting solution consists of three artifacts which need to be developed:
- Reporting table in the reporting data warehouse
- Load script to load/transform data from the Openbravo Database into the data warehouse
- Domains/reports within the Reports Server
These artifacts are all deployed on the reporting server as indicated in the architecture:
The development steps can be divided in two parts. Work to be done on your local development system and deployment to be done on the reporting server.
- Development on your local dev system
- create reporting database (locally)
- create the reporting table (for example pgadmin user interface)
- export the reporting model
- create load script in the openbravo application dictionary
- export and commit and push to your repository
- Deployment on the Reporting Server
- pull the changed repo
- do an Openbravo smartbuild
- stop tomcat
- create reporting database
- generate data loader application
- run the data loader application
- start tomcat
- use the jasper reporting server user interface to adapt the domain
Development on local system
Create Reporting Database
Go to the following module folder: modules/org.openbravo.reporting.tools. Check that the reporting.properties file exists and has a reporting database connection information.
Note: the reporting database defined in reporting.properties is a separate database from the standard Openbravo database. Choose another database name than openbravo (e.g. reporting).
Then execute the following command:
modules/org.openbravo.reporting.tools$ ant create.reporting.database
Create Reporting Table
Then create the reporting table. Things to consider:
- the table name should use the database prefix of the module in which you want to place it
- add relevant indexes also with the dbprefix prefix
- do not use/add foreign key constraints
Then create the reporting table in the reporting database. See the example below using pgadmin4.
Export Reporting Model
Go to the following module folder: modules/org.openbravo.reporting.tools. Then execute the following command:
modules/org.openbravo.reporting.tools$ ant export.reporting.model.full
Create Load Script
The next step is to create the load script in the application dictionary.
For a standard load script you can see a good example below in the screenshot. Some note:
- Choose the correct reporting table (for which you are creating the load script)
- The script order determines the order in which scripts are executed to load data. Scripts with the same script order are executed in parallel. A guide line for the value: master data has a value of 10 or 20, transactional data load scripts normally have a sort order of 30, 40 or 50
- If the 'always do full reload' is not checked then the query should normally have filters on updated time stamp (see the screenshot in the where clause) to implement incremental loads.
For more details check out this page.
Export Database and commit and push to repo
Next step is to get the changes into your repo. First step is export database, goto the root of the Openbravo project:
Push the changes to the repo:
cd modules/com.openbravo.cart.reporting.tools git add * git commit -m "My new reporting table" git push
Deployment on the Reporting Server
Use ssh to goto the reporting server. The following folders are relevant:
- /opt/OpenbravoERP: folder with the Openbravo install
- /opt/OpenbravoReporting/DataLoaderApp: the data loader application which loads the data from Openbravo into the reporting database
Pull the changed repo
Goto to module folder and pull it:
cd /opt/OpenbravoERP/modules/com.openbravo.cart.reporting.tools git pull
cd /opt/OpenbravoERP ant smartbuild -Dlocal=no
Tomcat needs to be stopped to allow creating the reporting database: Execute:
sudo /etc/init.d/tomcat stop
Drop the current Reporting Database
To drop the database connect to psql on the openbravo database and drop and create the (empty) reporting database:
PROD_openbravo@cart-u18-prod-reporting:/opt/OpenbravoReporting/DataLoaderApp$ psql -h localhost -U tad -W openbravo Password for user tad: psql (10.14 (Ubuntu 10.14-0ubuntu0.18.04.1)) SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off) Type "help" for help. openbravo=> drop database reporting; DROP DATABASE openbravo=> create database reporting; CREATE DATABASE openbravo=>
Create Reporting Database Tables
Make sure the org.openbravo.reporting.tools has a reporting.properties file with all the relevant information set. Then goto the reporting tools module and execute the create.reporting.database ant task:
cd /opt/OpenbravoERP/modules/org.openbravo.reporting.tools ant create.reporting.database
Generate Data Loader Application
In the org.openbravo.reporting.tools module there is a script (generate_app) to generate the data loader application:
cd /opt/OpenbravoERP/modules/org.openbravo.reporting.tools ./generate_app
The data loader application is generated into /opt/OpenbravoReporting/DataLoaderApp
Run the data loader application
Goto the data loader folder, change the executable flag on the update script (is not set by default) and then run the dataloader app:
cd /opt/OpenbravoReporting/DataLoaderApp chmod +x update_script ./update_script
Note: the log of the dataload can be found here: /var/log/openbravo/dataloader.log.
sudo /etc/init.d/tomcat start
Login into Jasper and adapt the domain
For the details how to work with domains and reports with modules visit this page.