Generate Reports Using Another Database

[Douglas Menezes]

Hello Team,

We have our PPM database replicated to another database for reporting purposes. And now we need to use this new replica to gerate the reports.

Can anyone help me?

Best Regards,

Douglas 

[Etienne_Canaud]

Hi Douglas, 

It's most certainly possible, but in order to point you in the best direction I need some clarifications:

1) What PPM version are you on? Any upgrade plans in the near future?

2) Is the duplicated PPM DB also Oracle, or is it something else such as MS SQL or Postgres?

3) What type of PPM reports do you have in mind exactly? JSP Reports? PL/SQL Reports? Excel Reports? Dashboard Portlets Reports? Or something else?

4) Do you need to mix data from PPM Prod DB & Duplicated DB in your reports? For example, if your replication has too much lag and that you need some real-time data for some part of your report.

Also, purely out of curiosity, what technology are you using for PPM DB Replication, and how frequently is the replication run? Is it near-real time or more of a "once a day during the night sync" kind of replication?

Cheers,

Etienne.

[Douglas Menezes]

Hi Etienne

Thanks for answer, me reply is above in bold.

1) What PPM version are you on? Any upgrade plans in the near future?

9.52

2) Is the duplicated PPM DB also Oracle, or is it something else such as MS SQL or Postgres?

It's a Oracle Data Guard

3) What type of PPM reports do you have in mind exactly? JSP Reports? PL/SQL Reports? Excel Reports? Dashboard Portlets Reports? Or something else?

The default PPM Reports, PL/SQL. I attached a example.

4) Do you need to mix data from PPM Prod DB & Duplicated DB in your reports? For example, if your replication has too much lag and that you need some real-time data for some part of your report.

No, all my reports will be from the Duplicated DB

Also, purely out of curiosity, what technology are you using for PPM DB Replication, and how frequently is the replication run? Is it near-real time or more of a "once a day during the night sync" kind of replication?
It's near real time, The Data Guard replicate run in every transaction

BR,

Douglas 

[Etienne_Canaud · Accepted solution]

Hi,

Thanks for the info.

- For PL/SQL reports, since they will always hit the PPM DB, the only way to hit a different DB than the PPM DB is to create a DB Link in the PPM DB pointing to the reporting DB, and then use the DB Link in your query.
This will result in a perf impact to PPM DB as data still has to go through the PPM DB (and all sorting is done locally in PPM DB), but overall this should still be more lightweight than hitting directly the PPM DB. Just make sure you have a good network between both Oracle servers.

- For Excel Reports, you have two ways to hit a different DB:
* Use JDBC URL in the Excel template, which might be a security concern as DB credentials have to be included in the Excel template (but is the simplest approach)
* Create a new JNDI datasource in PPM, and use JNDI name to retrieve a connection to DB to run your SQL. This is much more secure, and provide better performance since connections are pooled, but require to change PPM configuration to add the new datasource.
Both these approaches are documented in this document (https://admhelp.microfocus.com/ppm/en/9.60/PDFs/ExcelReports_PDF.pdf ), in paragraph "Running SQL on External Databases"

- For JSP Reports, since you can write Java code in it you could technically write all the boilerplace code to get a DB connection to the reporting DB either with raw JDBC URL (which requires to include DB credentials in the code), or by first creating JNDI datasource in PPM and then writing the code to get connection from JNDI datasource directly. Or you can reuse the class leveraged by Excel Reports to have this done in a much simpler way. The class to looking for is com.mercury.itg.common.excel.exporter.data.ExternalDBJDBCLazyExecutorProvider, just make sure to call disposeAll() on it after usage to close all connections that may have been opened.

Thanks,

Etienne.