Analyzing results of the Performance Analyzer with Excel
Posted by Janick Bergeron on April 13th, 2009
Janick Bergeron, Synopsys Fellow
The VMM Performance Analyzer stores its performance-related data in a SQL database. SQL was chosen because it is an IEEEANSI/ISO standard with a multiplicity of implementation, from powerful enterprise systems like Oracle, to open source versions like MySQL to simple file-based like SQLite. SQL offers the power of a rich query and analysis language to generate the reports that are relevant to your application.
But not everyone knows SQL. You need an SQL-aware application to do fancier stuff. And guess what! Excel can import SQL data! And everyone knows Excel!
In this post, I will show how you can get VMM Performance Analyzer data from a SQLite database into an Excel spreadsheet. A similar mechanism can be used if you are using MySQL or any other SQL implementation that offers an ODBC (Open Database Conduit).
First, download and install the SQLite OBDC from http://www.ch-werner.de/sqliteodbc/sqliteodbc.exe on your PC and install.
Next, create a new OBDC Data source by opening the OBDC Data Source Administrator by navigating to Start -> Settings -> Control Panel -> Administrative Tools -> Data Sources (OBDC).
Click the “Add…” button to add a new data source. Scroll down to select the “SQLite3 OBDC Driver” entry.
C lick the “Finish” button. This will then open the OBDC data source configuration window. Specify a name for the data source and browse to the SQLite database file. A SQLite ODBC data source connects to a single database file. It is therefore a good idea to name this new data source according to the performance data it contains. Leave all other options to their default value.
Click “OK” to complete the creation of the new data source on your computer.
Repeat these steps for each SQLite database that will need to be analyzed using Microsoft Excel on your computer. Click “OK” when all data connections have been defined.
Start Excel with a blank workbook, then Select Data -> From Other Sources -> From Microsoft Query.
This will bring up a dialog box to select a data source. Select the data source corresponding to the desired SQLite database.
Click “OK”. The following error pops up. Don’t worry! The next step is to correct that error.
Click “OK” to close the error pop-up. This will bring up the Microsoft Query Wizard.
Click on “Options”. Modify any of the check boxes but make sure the “Tables” option is (or remains) checked. You must make a modification to at least one check box.
Click OK. The Microsoft Query Wizard should now be populated with the list of tables found in the SQLite database file. From this point on, which table and which columns you select depend on the analysis you wish to perform.
For example, to perform an arbiter fairness analysis, you would select the “InitiatorID” and “active” columns of the “Arb” table.
Click “Next >” three times then return to Microsoft Excel by clicking “Finish”. The Import Data dialog box will appear.
From there, a PivotChart can be used to display the average and maximum arbiter response time for each initiator. As you can see, the arbiter appears to be fair, given the small number of samples collected.
I hope you’ll find this step-by-step guide useful.
What cool thing have you done with the Performance Analyzer?