Modifying BI Publisher reports in Oracle Enterprise Manager 13c, e.g. ‘Database Usage Tracking Summary’

1

Recently I bumped into an annoying bug in the code of a report, wrote about it a while ago, notified Oracle. And they has confirmed that a new version of this specific report is in development phase.

But.. a report can be modified, so if it’s not that hard, why wait for a patch when you want to use a report you need in the meantime? It is is not advisable to modify a standard report, because undoubtedly there will be other changes in a patch to come (e.g. the datamodel), but as long you are aware of this…..

So I chose to modify my favourite report ‘Database Usage Tracking Summary’ for this blog.  And it turned out to be surprisingly simple.

My goal is to modify the query of the report, and for fun the header-default. Just for those who are interested in this specific query I will first give a short description of the change.

The query is using two important views:

– SYSMAN.MGMT$EM_LMS_DBA_FEA_INFO_USAGE

– SYSMAN.MGMT$EM_LMS_DBA_FEA_USAGE_STAT

By the way: the old view SYSMAN.MGMT$DB_FEATUREUSAGE has NOT been deprecated.

Description of the problem: in a specific record, the content of the  column ‘feature_info’ in the view SYSMAN.MGMT$EM_LMS_DBA_FEA_INFO_USAGE does not contain the string ‘compression used’.

That’s why the following code in the report gives a false result :

trim(substr (to_char(feature_info), instr(to_char(feature_info), 'compression used: ',1,1) + 18, 2)) != '0'

 

this code gives the result ‘ta’ , and that’s indeed not equal to ‘0’.

Solution I want to implement (not beautiful but effective for the moment):

decode(
 instr(to_char(feature_info), 'compression used: ',1,1),
 0,0,
 trim(substr(to_char(feature_info), instr(to_char(feature_info), 'compression used: ',1,1) + 18, 2))
 ) != '0'

 

 

Navigate to the BI Publisher Enterprise Reports:

image

 

There are (at least I think) two ways to navigate to the editing part of a report :

– Just run the report and then edit the report. This is what I did.

– Go to BI Publisher and choose the ‘Catalog’  option – I’ll use this later on.

 

image

Choose the report you want and edit this at the upper right corner:

image

When in the editor screen, just save the report under another name and in a map of your choice.

image

There’s also a possibility to copy reports while in the Catalog screen (lower left corner), but I chose not to use this:

image

When choosing  ‘Catalog’ you’ll find the report where you saved it. I named the report DB_Usage_Tracking_Summary_JO1.

image

When opening this report through the catalog you will be directed to the editing screen. Don’t edit, but choose the title above the report as shown below.

image

Modify the data set as shown below.

image

Now you’re able to modify the code. But..  you are editing a data-set which belongs to the original report, so be aware of saving your modifications under another dataset!!

image

After you’re completely done, don’t forget to save the report.

Just for fun, I modified the lay-out of the reports. This has been well documented at youtube:

https://www.youtube.com/watch?v=-_bSWNbQ2XU

The result of this code and lay-out modification:

 

image

 

Resources:

OEM13c as SAM-tool: https://technology.amis.nl/2016/01/31/usage-tracking-reports-using-oracle-enterprise-manager-13c-sam-tool/

How to customize an existing BI report: https://www.youtube.com/watch?v=-_bSWNbQ2XU

About Author

Until February 2019, Job worked as a solution architect at AMIS Services with a special interest in Oracle licensing, High Availability architectures and managing complex (Oracle) environments, which includes Cloud environments.With a background as Oracle developer, DBA, team-manager and license-consultant he is able to utilize the Oracle technologies to a cost-efficient architecture for his customers.He is regularly involved in consultancy regarding: - Unlimited License Agreements (ULA). - License compliancy-checks and advice regarding optimizing the environment. - Second opinions. - Education / presentations about licensing and managing your infrastructure in the most cost-efficient manner.Twitter: @jobaclenl

1 Comment

  1. Correct, you can modify an existing supplied BI Publisher report. I just wanted to make it a little clearer that it’s always best to do that on your own copy of the report (which you can get by doing a create like and saving) because new releases MAY overwrite the changes that you’ve made. If you have your own copy, that won’t happen.