It's all about the answers!

Ask a question

Work Item History isn't collected by Data Collection Jobs

Igor Lemes (111812) | asked Jan 22 '13, 12:30 p.m.
edited Jan 29 '13, 1:20 p.m.
Hi there,

we're currently trying to generate some reports using RRDI v 1.0.2 and RTC v3.0.1.3 

The connection with the database is OK, and data is collected to the Data Warehouse by the data collection jobs, so that if I create a Work Item and run the all the collection jobs the WI will show up at Cognos Query Studio.

However, data from the state history of the WI is not imported.
I've checked the CCM database for the WI history and it is there, and not in the Data Warehouse.

Database is SQL Server 2008 R2
Any ideas on why this is happening?

If you guys need any more info just let me know

Thanks in advance!

I've ran the Full Data Collection Job and the state history for work itens was fetched.
However, the normal Collection Jobs still not returning this data. 

Another update:
the property for the NTP Server on the JTS and CCM admin was not set up.
I've set it and the regular collection job worked, fetching the data from the work item's state history.
Awkward thing is that it only retrieved information on the first test, and stopped after it...

Still working on it

5 answers

permanent link
Rafik Jaouani (5.0k16) | answered Jan 29 '13, 2:43 p.m.
Please check the table named RICALM.RQUEST_HISTORY.

Igor Lemes commented Jan 29 '13, 2:55 p.m.

Thanks Rafik!

The content is indeed there, and was collected by the daily collection job. However it doesn't appear on Cognos Query Studio. At least not under "Request State History". Do I have to change my reports? If so, what's the query studio's "table" that I should use?

Does this point to any kind of configuration problem?

permanent link
Rafik Jaouani (5.0k16) | answered Jan 29 '13, 3:02 p.m.

 Operational Data Store/Request Area/Request History.

I am assuming you are using the Framework Manager Model that ships part of CCM and is a superset of the RRDI/Insight model.

Igor Lemes commented Jan 29 '13, 3:13 p.m.

 Ok. It is there! Thanks.

Is this the expected behaviour? 
I mean, the RIODS.REQUEST_STATE_HISTORY table is not updated on the execution of the daily jobs, being updated only upon the execution of the full collection job? 
Should I always use the RICALM.REQUEST_HISTORY table when I need to check the work itens history? 

Thanks again, you've been very helpful.

Rafik Jaouani commented Jan 29 '13, 6:39 p.m.

If you are looking for Work Item History, you need to use the Request History table. The Request State History table does not store the request history but rather stores data about request state changes. If you need more info about that table, please let me know and I will try to dig the information.

Igor Lemes commented Jan 30 '13, 7:20 a.m.

 Actually what we need is an precise list of the states the work item has been through.

The Requests History table ends up with duplicates of those states, probably because it writes a new entry for any new chances, not only state changes.

So, by using this on a report where I want to list the number of, let's say, "build failure" states for each team, I can end up with inaccurate results.

permanent link
Rafik Jaouani (5.0k16) | answered Jan 30 '13, 1:47 p.m.

Do you need the list of states for a particular Work Item?

If you do not want duplicates, why don't you do a select distinct?

Igor Lemes commented Jan 30 '13, 2:37 p.m.

For all the Work Itens.

I guess we can use this table you suggested and a select distinct, but it looks like a workaround. 

I mean, the state history table should be updated when running the daily jobs, right? This looks like the proper way of doing this.

Isn't there any possible reason for the daily collection jobs not being collecting this info?

Thanks again

Rafik Jaouani commented Jan 30 '13, 3:05 p.m.

I never confirmed the state history table is not collecting the daily changes :) Can you please raise a defect against RTC and we will investigate the issue?

Jackie Albert commented Jan 30 '13, 3:29 p.m.

If you submit this Defect, please post the link, I'd be interested in seeing the data you have also as it sounds similar to something my team has seen.

YOGESH LOKHANDE commented May 30 '13, 2:21 a.m.

Hi Rafik,

One more Ques...

Can we get all field (costume attribute of Work  item)defect or any Work item  in RI report as just like list reports ...
and  can we get information of work item tab History where we can see the states changes (Change set or work item ) in report

through Report studio.

if yes then how it get that field ...


Rafik Jaouani commented May 30 '13, 8:29 a.m.

The custom attribute data is exposed in the Request Area using the query subjects named:

Request String Extension

Request Integer Extension


Request History String Extension

Request History Integer Extension


YOGESH LOKHANDE commented May 31 '13, 1:14 a.m.

Thank you Rafik..

showing 5 of 6 show 1 more comments

permanent link
Igor Lemes (111812) | answered Jan 31 '13, 8:59 a.m.
 Defect issued:

permanent link
YOGESH LOKHANDE (10441927) | answered May 30 '13, 5:15 a.m.
Hi All,

connectivity of Birt and database or datawarehouse in rtc

Your answer

Register or to post your answer.

Dashboards and work items are no longer publicly available, so some links may be invalid. We now provide similar information through other means. Learn more here.