It's all about the answers!

Ask a question

How to query out Coutinious Request History

Zhaolong Li (325) | asked Aug 06 '14, 5:51 p.m.
I need to find a way to show all the Request History Day by Day. What I can get now from Data Warehouse is a the history table which records the changes. However, I can't find a table recording the status for each Request in the past days.

Could anyone please help?

The package I'm referring to is Data Warehouse/Operational Data Store/Request Area. I can provide any detail if I'm not stating clear in my question.  Thanks in advance.

Ralph Schoon commented Aug 07 '14, 3:43 a.m.

I would try to provide more information on what you try to do with which product and what you want to show. I think this is about reporting and the history of work item (workflow states).

I am not a data warehouse/reporting expert, however a data warehouse typically does some kind of data compression and does not store all the history of all data.  

Zhaolong Li commented Aug 07 '14, 9:34 a.m.

Thank you Ralph for the clarification and your warm heart as always.

Accepted answer

permanent link
Francesco Chiossi (5.7k11119) | answered Aug 07 '14, 4:59 a.m.
Hello Zhaolong,

Request History only stores the changes, like you correctly noticed, not the daily status of the work items.
If you need that kind of information you will need to built it yourself in a report, for example by comparing the daily date with the date of the request history entry and pick the matching one, however this might not be trivial to accomplish.

Another option is to use the request metrics, the advantage is that you get an entry for each day, the disadvantage is that the data is aggregated meaning that you cannot see the status of a single request but you will get totals for all the requests sharing the same dimensions; also not all the request fields are available in the metrics.

You can find more information on metrics in this article:

Improve the value of your CLM reports by using metrics

Best Regards,

Francesco Chiossi
Zhaolong Li selected this answer as the correct answer

Zhaolong Li commented Aug 07 '14, 9:41 a.m.

Hi Francesco,

You are absolutely right. Actually, your conclusion is exactly the same as what I found out by myself.

Dimensional metrics has the total Request number for each day. However, it doesn't contain user attribute like APAR which I need it to be a filter. Relational Data , as you said, it seems to me impossible to accomplish since I need to join every history table (like status , State , etc) and work out a way to fill in the no change record days. From the computation point of view, it is too expensive to achieve.

I wish this is not the final conclusion.

Francesco Chiossi commented Aug 07 '14, 10:11 a.m. | edited Aug 07 '14, 10:11 a.m.

Hello Zhaolong,

an alternative I can think of, could be the customization of the Insight ETL to get the data you need.
For example either adding the APAR field as a dimension on the metrics, or builing a new table with the information you need.
The problem with that approach is that it's far from being trivial to develop and requires quite advanced Cognos knowledge; so it's a considerable investment.

Best Regards,

Francesco Chiossi

One other answer

permanent link
Brian Fleming (1.6k11928) | answered Aug 07 '14, 8:17 a.m.
Instead of Request History, have you looked at Request State History?

Zhaolong Li commented Aug 07 '14, 9:42 a.m.

I did. It only stores Request State History change record. I can't come up with a idea to get those pieces together

Ralph Schoon commented Aug 07 '14, 10:02 a.m.

The data is in RTC, at least the one that can be seen on the history of RTC work items. It would be possible to traverse all the historical states of a work item for example using the API. Not sure about REST/OSLC. Not sure how much load that would add, if you ran some tool over night.

Your answer

Register or to post your answer.