Separate iDempiere Reporting Platform or Data Warehouse

The purpose of this page is to offer a solution for creating a separate iDempiere reporting platform or Data Warehouse. The target audience is someone seeking great performance for 200+ concurrent users. If your iDempiere user counts are growing high and your reporting needs are becoming more taxing on the system, the below solution might help you provide a more rich and better performing reporting solution.

Summary

Create a separate iDempiere instance dedicated to reporting. It would have data that is up to X hours old or behind the primary instance.

Process

Use a cron job to automate the backup of iDempiere on the primary or production instance.
Use a cron job to automate the restore of iDempiere into a secondary or reporting instance.
Update the restore script to perform the following. This will transform the environment into a reporting engine.
1. Disable the normal iDempiere operational roles
2. Enable (re-activate) hidden reporting roles
3. create/update materialized views to support high-speed reporting
Things that would not change
1. All the transactional data from the last backup
2. The current list of users

User Experience

User has two systems. One is for operations and the second is for reporting.
The two systems look different (different color schemes)
The two systems present different roles during login.
The following reports would be near instantaneous
- Financial Reporting
- Aging
- Inventory Valuation
Period reports can be created from the reporting database with no load on the production server. User would see them delivered via email.

Technical Considerations

PostgreSQL would be optimized for data import to make the loading of data very quick.
The production export would exclude high-volume tables that are not needed for reporting (ad_changelog, etc...)
All attachments and files should be stored in a file system (not the db) to make export and import much faster
pgbouncer could be used to toggle between two different databases so that a switch would not happen until the next restore is already complete. Down time between restores would be seconds - not minutes.
Since jasper reporting is integrated into iDempiere, there is little that could not be reported on.
Statistical analysis could be executed in the reporting database and the results could be ported back to the production server.

Wishlist not yet Developed

Integrate ZK's pivot table into iDempiere