Verification Martial Arts: A Verification Methodology Blog

Analyzing results of the Performance Analyzer with Excel

Posted by Janick Bergeron on April 13th, 2009

jb_blog

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).

sqlite1

Click the “Add…” button to add a new data source. Scroll down to select the “SQLite3 OBDC Driver” entry.

sqlite2

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.

sqlite3

Click “OK” to complete the creation of the new data source on your computer.

sqlite4

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.

sqlite11
This will bring up a dialog box to select a data source. Select the data source corresponding to the desired SQLite database.

sqlite6

Click “OK”. The following error pops up. Don’t worry! The next step is to correct that error.

sqlite7

Click “OK” to close the error pop-up. This will bring up the Microsoft Query Wizard.

sqlite8

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.

sqlite92

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.

sqlite10

Click “Next >” three times then return to Microsoft Excel by clicking “Finish”. The Import Data dialog box will appear.

sqlite13Click “OK” to insert the data as a table and voila! You now have a table populated from the data dynamically extracted from the SQLite database.

sqlite111

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.

sqlite12

I hope you’ll find this step-by-step guide useful.

What cool thing have you done with the Performance Analyzer?

5 Responses to “Analyzing results of the Performance Analyzer with Excel”

  1. Dennis Brophy Says:

    Janick, I believe SQL is an ANSI/ISO standard, not an IEEE standard. Not that it changes your blog entry, but if someone goes looking for the standard, they would have a better change to find it at webstore.ansi.org than at the IEEE. -Dennis

  2. SQL Tutorials Says:

    Does anyone know if there is another language or set of commands beside SQL for talking with databases?

    I’m working on a project and am doing some research thanks

  3. SQL Tutorials Says:

    You know, the thing about SQL is, that there is virtually nothing that can replace it.

    Does anyone know if a substitute exists for sql? I mean besides MS SQL and Oracle and all that jazz. Thanks.

  4. Noufal Ibrahim Says:

    I don’t know a replacement for SQL but you should consider using a strong ORM like Python’s SQLAlchemy would alleviate *most* (if not all) the problems with using raw SQL (backend agnostic queries, optmisations etc.).

    The other alternative is to move from relational databases to object or document databases. CouchDB is a popular document database and I’ve had some good experiences with it.

  5. Verification Martial Arts » Blog Archive » Auto-Generation of Performance Charts with the VMM Performance Analyzer Says:

    [...] from one of Janick’s earlier blog on the VMM Performance Analyzer Analyzing results of the Performance Analyzer with Excel, ”The VMM Performance Analyzer stores its performance-related data in a SQL database.SQL was [...]

d401fab5ec0904ea9c1a5c06fe11d8ee\\\\\\\\\\\