Running SQL Queries on PPM Operational Reporting Database

Anyone with advanced reporting requirements should consider using the PPM Operational Reporting DB Schema, especially since the release of Content Pack 2.0. It is not only faster and lighten the load on PPM production database, but also results in much simpler SQL because the data is already aggregated in facts and dimensions tables.

For example, the SQL query listing all resources used earlier in that document hits two tables and one very complex view (KNTA_USERS_V, leveraging 12 database tables). However, running the same SQL query on the PPM Reporting database only needs to get data from one single table (RPT_DIM_RM_RESOURCES) that includes all resources information.

In order to run a SQL query on the reporting database, replace ppmdb by reportingdb in the placeholder where SQL query is executed. For example: ${reportingdb.execQuery('SELECT * FROM RPT_DIM_RM_RESOURCES')}. This requires that PPM Reporting Content Pack 2.0 or later be installed and properly configured.