Tag Archives: custom graph

How to import Enterprise Manager Grid Control Data into Excel and Create Attractive Looking Graphs and Charts

It is really not difficult to import performance data from your Enterprise Manager Grid Control setup and produce some very nice looking graphs from the data stored within.

Here’s one I made earlier.

Custom Oracle Enterprise Manager Graph / Report produced using Excel
A nice 3D graph of Enterprise Manager repository data demonstrating how much nicer Excel’s graphs are than Oracle OEM’s

 

Here’s how to do it:

  1. The first step is to install ODAC. You must do this because in my experience, on a Windows XP PC, Microsoft’s ODBC for Oracle driver doesn’t appear to handle Oracle’s sysdate at all well. You can download ODAC (11.2 Release 4 (11.2.0.3.0)  in my case) from Oracle’s rather swish download pages.
  2. The next thing to do is create an entry for your repository database. Make sure you edit the tnsnames.ora that is part of the ODAC installation or this won’t work.
  3. Create an ODBC data source using the tnsnames entry you created in step 2.
  4. Fire up Excel.
  5. From the menu bar select Data / Import External Data / New Database Query
  6. Select the ODBC data source you created in step 3.
  7. Enter your username and password. Username will be sysman, see your admin people if you don’t know the password.
  8. The MS Query query wizard shop pop up. Expand the the first table that appears in the Available Tables and Columns list and add the first row only to Columns in your query (for me the table is AQ$_MGMT_ADMINMSG_BUS_G and the first column is MSGID).
  9. Keep clicking next until you reach Query Wizard – Finish and you’re asked what you want to do next.
  10. Select the middle option – View Data or Edit data in Microsoft Query and click finish.
  11. If all goes well you should a Microsoft Query window containing another a visual representation of the query you just created
  12. Click on the AQ$_MGMT_ADMINMSG_BUS_G table ( or which ever table you used in step 8 ) and press delete. The table should disappear from the screen.
  13. Click the SQL button (at the very top of the window) and paste your query into the the SQL box that opens up then click OK. (You can use the query builder features of Microsoft Query but,  to be honest, that can take a while. What i usually do is take my base query (click here to see it or download it here: sample_oemrep_query.sql) and paste that in). NOTE: this query returns daily summaries up until the day before today – the query won’t return any data for today.
  14. Change your parameters to suit and Save the query if you wish (Irecommend this).
  15. Then click the Return Data button (its at the top of the window to the immediate left of the SQL button). You should find yourself back in Excel territory.
  16.  Choose where you want to put the data and click ok. You should have a spreadsheet full of Enterprise Repository data.
  17. Create charts and graphs and pivot tables and pivot charts to your heart’s content.

Beware! There is a caveat:

Your date must be formatted as YYYY-MON-DD or MS Query will have a fit. Your query, if you’re going to query on dates must contain something like:

MGMT_METRICS_1DAY.ROLLUP_TIMESTAMP>TO_DATE('2012-JAN-01','YYYY-MON-DD')

or for the last 90 days worth of metrics data..

MGMT_METRICS_1DAY.ROLLUP_TIMESTAMP >= SYSDATE - 90

 

Leave a comment if this is any use to you.

Video (and amendments coming soon!).
Seamus.