OEM 13c BI (Business Intelligence) reporting

 

13c cloud control has enable reporting feature to represent data in graphical manner. We can use oem repo database to extract required information to perform proactive dba administration. Representing data using graphs , pie charts etc.. help dba's to get the complete picture of the environment in short period of time. These health check reports helps to address issues in proactive manner.

Managing 100+ databases needs these kind of BI reporting.   As dbas we need to make sure to address issues proactively.

This article will cover BI report creation. To create these reports oem BI component should be up and running.

Use below mention command to verify the component status.

[oracle@em13clnx bin]$ ./emctl status oms
Oracle Enterprise Manager Cloud Control 13c Release 3
Copyright (c) 1996, 2018 Oracle Corporation.  All rights reserved.
WebTier is Up
Oracle Management Server is Up
JVMD Engine is Up
BI Publisher Server is Up

As the component is up and running , we can login to BI reporting console.

Creating reports consists with four steps.

1. Create data model
2. Create report using data model
3. Schedule report
4. Publish the report using email

Data Model

Initial step is to create a data model , Login to BI console and select the data model .

Best option is to start with sql query because we can extract required data from oem repo database views and tables.

This figure illustrates selecting the sql query as data model set.

Use this as a sample query to get ASM space usage and  create a sample data model.
Make sure to change the target information as per your environment.

select TARGET_NAME,TABLESPACE_NAME,TABLESPACE_SIZE,TABLESPACE_USED_SIZE,COLLECTION_TIMESTAMP
from MGMT$DB_TABLESPACES 
where TARGET_NAME ='pimstst'

Even you can create few more data models to get db growth and tablespace usage etc..

For newly created report and data models , best practice is to create separate folder and save the reports and data models.

Once the data model creation is complete select report icon and associate the correct data model.

Also make sure to save the sample data , because we are going to use this sample data to create the report.

This figure illustrates saving the sample data .

Same window top right there is icon to create the report. select the create report icon will move to report creation console.

Report creation 

This figures shows to report creation , There are two options

1. Guided method to create report
2. Use report editor ( I preferred this method , because this gives you full control over the report)

First create a lay out . In this figure we created 1 column with 10 rows .

Next steps is to select the appropriate table , as we need to generate graph use pivot table.

Field can be dragged and drop to the pivot table.

Copy the created pivot tables and copy to next row. One table we use to give data information . Other one we can convert to chat.

Pivot tab there is option to covert table to graph , This figure shows converting table to graph.

Save the repot and go backup to main windows and view the report

Congratulation !!!! You are first report is complete.

Publish report

Setup Email

 

It’s not that hard to setting up email delivery , Navigate to administration tab and select email and provide required email username , password and port number.

Schedule report

Next step is to schedule a report , to schedule report click more icon on the created report . This will open a other window feed information such as email address and mail subject etc .. 

This figure illustrates the scheduling report using email address.

Schedule the frequency of the report generation as per your requirement. This can be once a month , weekly or daily.

Job status can be verified after the report generation.