Reporting Server/Materialized Views
Contents |
Materialized views are an important part of the reporting solution. Materialized views are used to provide pre-aggregated views on the underlying data. For information on materialized views check out the postgresql documentation.
For example, take a table with orderlines with customer, store and product dimensions. A report which aggregates along the product dimension can be build directly on the table. However, an alternative which can perform better is to define a materialized view which aggregates on the non-used dimensions as customer and group by date (to aggregate away the time). This materialized view has much less records than the original table and is therefore faster to load for a report.
As materialized views are the basis for reports it makes sense to define all the needed indexes on a materialized view and not/less on the source table.
Materialized views are completely refreshed at each run of data loader application. This is performant, we have seen materialized views refresh rates are 2-3 million records/minute on average M5 AWS hardware with simple join/aggregations (which cover real use cases). Note actual performance may vary depending on the complexity of the view query.
How to define a materialized view
A materialized view should first be created in the database. It consists of a query and zero or more indexes. The name of the materialized view should start with the DB prefix of the module in which it is handled.
The next step is to export the materialized definition into xml of the corresponding module (see an example here). To do this execute the following ant command in the org.openbravo.reporting.tools module folder:
ant export.reporting.model
The materialized views are exported to the following folder in their modules: src-db/database/reporting-model/materializedViews.
The next step is to create the corresponding record in the application dictionary. To accomplish this execute the following ant command in the org.openbravo.reporting.tools module folder:
ant populate.reporting.tables.in.ad
You can find the materialized view in the Reporting Tables and Columns:
A materialized view also has columns, the main difference with a standard table is that the 'Is Materialized View' checkbox is checked.
Materialized view load script definition
A materialized view also needs a corresponding load script definition. The load script definition is quite simple (see screenshot below). The standard load script for a materialized view does a refresh.
