HP ALM Database metrics

By XeroCube | August 14, 2012

The past week has seen me and my team attempting to create several SQL Queries to pull specific information out of our HP ALM Project databases. Basically, we are looking to pull test case execution and status updates per day and report on our local Confluence installation.

The TESTCYCL table stores data pertaining to each specific test case instance in a project. These records are associated with the CYCLE table, which catalogs each Test Set in the Test Lab. There are several other details, which I will explore at another time.

Since we are looking for status updates, it is very important that we get “No Run” counts. The RUN table captures the data for each test run for each test instance. Each record is timestamped, allowing us to determine the resulting Test Execution Status by date. Once we get that data, we can pivot the information and report the counts by date. However, this doesn’t provide the full picture. Remember: the RUN table captures each test execution.

Since no test execution has happened when you add a test case instance to a test set, there is no record in the RUN table. This means our status counts will not accurate reflect “No Runs”. To account for these “No Runs”, the database assumes that TESTCYCL records with no accompanying RUN records is a ‘No Run’. Simple enough… except…

What we have discovered is that the HP ALM database structure does not allow you to determine when a test case instance has been added to a test set. This means that we cannot easily determine this portion of the “No Run” counts by date. If HP (or perhaps Mercury Interactive) would have included a simple set of audit fields on this table, then we would have been able to finish our reports.

I will provide an update if we determine how to solve this issue fully.

Topics: Quality Assurance, Quality Center / ALM, SQL, Tech, Tools | 5 Comments »

5 Responses to “HP ALM Database metrics”

Michael Deveaux Says:
August 14th, 2012 at 8:27 pm

You could probably add a custom field to the TESTCYCL table and do some workflow that will automatically populate the timestamp when A test case instance is added.

XeroCube Says:
August 14th, 2012 at 8:32 pm

I had thought about that, but we were hoping to accomplish this without any unnecessary customizations. We have way too many projects to contend with customizing each one.

However, should we go that route, I had thought about using the workflow to automatically add a “No Run” record to the RUN table. It is semi-kludgy, but it could work.

I’ll tell you, the SQL queries right now are crazy complicated.

Geoffrey Says:
August 15th, 2012 at 7:44 am

Not only that, but adding the workflow option to populate a new field in the database would not do anything for the existing test instances, and we’re trying to create a SQL statement that mimics the test case status progress (day-to-day) report available through the ALM Analysis and Live Analysis features. The fact that HP is able to produce the report makes me think they’ve just placed the information in an unusual spot in the data structure.

XeroCube Says:
August 16th, 2012 at 8:04 pm

Turns out that we discovered that the AUDIT_LOG table holds the key as to when a Test Case Instance (TESTCYCL) is added to a Test Set (CYCLE). The earliest timestamped record in the AUDIT_LOG table for the Test_Case_ID where AU_ENTITY_TYPE is ‘TESTCYCL’ will get you the date.

Select then Min(Date_Field) grouping by the ID_Field shoul dnet you the results you are looking for. I will attempt to post the actual SQL statement soon.