How to query out Coutinious Request History
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.
Accepted answer
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
http://www.ibm.com/developerworks/rational/library/improve-value-clm-reports-metrics/index.html
Best Regards,
Francesco Chiossi
Comments
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.
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
Comments
I did. It only stores Request State History change record. I can't come up with a idea to get those pieces together
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.
Comments
Ralph Schoon
FORUM ADMINISTRATOR / FORUM MODERATOR / JAZZ DEVELOPER 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
Aug 07 '14, 9:34 a.m.Thank you Ralph for the clarification and your warm heart as always.