Creating Custom reports with BI Publisher

BI Publisher offers you the possibility to create your own custom reports, I will describe you how to create a report displaying the tablespace size and used size in a simply way.

It is advised to create a specific folder in which we will create our reports, because in case of upgrade you won’t lose your BI reports. In the catalog window, you select create folder:

We have to grant permissions to this folder to Enterprise Manager Users who will have access to this report; we give Read, Write, Delete access to the user with the EMBIPAuthor role, and only Read access to the EMBIPViewer, we also select Apply permissions to items within this folder:

We also create a data model folder in the psi_report folder and we also grant the adequate permissions to the users:

Now we can create the data model:

What do we need? We need a list of values (for the list of database instance), a parameter (the name of the database):

We select list of value to define the database list and we select Query Builder:

You have access to all the mgmt$ sysman’s view of the Enterprise Manager repository. You select for example the mgmt$dbninstanceview and the fields target_guid and instance_name:

The query is automatically generated:

Now we add parameters :

Once we have created a list of values and parameters, we can create a data set:

You select SQL Query, you give a name for the data set and you select Query Builder:

In the query builder, you select the mgmt$dbninstance and mgmt$db_tablespaces. By selecting in the right case of the field TARGET_GUID, we select the column for join, the we select the other columns we need, such as tablespace_size and tablespace_used_size:

You select the condition field in the Query Builder windows in order to add a condition to the target_guid with the list_of_database parameter:

We save the data model in the psi_report/data model folder:

You also have to save the data model as Sample Data:

Finally we can create a new report:

We select an existing Data Model:

We can select different options:

You build your report by selecting the different fields, and pushing them in the right window:

Finally you save the report file:

Your report is ready to be visualized; selecting the instance name from the menu allows you to visualize the report for each database instance:

As we have access to the repository views,there are a lot of possibilities to create reports about CPU, SGA, AVAILIBILITY_HISTORY, ALERT_HISTORY, COMPLIANCE, aso…:

For example, it is quite easy to display the SGA details of ou database instances in a few minutes:

The BI Publisher environment allows the report scheduling, there is also the possibilty to receive those reports by mail. BI Publisher reporting is a very powerful tool allowing Oracle DBAs to generate reports displaying the overall architecture of their environment.