Thanks for the information on the delta_ts field. A custom attribute is created on the work item named Last change (Bugzilla) that contains the value in the delta_ts field. Modified date does not contain this information; it still contains the date the work item was imported.

It could be that before you migrated you CQ data you already had some work items. Then somehow the import of the CQ data inserted items whose modification dates are prior to the already existing work items. If that is case, then the data warehouse collection task, will not see those work items.
One way to fix this is to:
1) import the CQ data into an empty repository that has no work items.
2) build the data warehouse.
3) export it to a file.
4) go back to your real repository.
5) empty the data warehouse.
6) import the file you exported in step 3.

Steps 3 and 6 are not documented in the RTC online help. So please raise a work item about this issue and we will give you better instructions.
The CQ team is also welcome to suggest a better solution to this.

I would like to understand what the implications of this procedure are in the following situation:

1. We have a 1.0.1 repository that has been migrated to 2.0
2. This repository has some projects which have existing work items in them
3. Some of the CQ records we are importing through bugzilla are going into these existing projects (again which already have non-imported work items).
4. Other CQ records are going into newly created projects.

If we do this process as outlined above will the data warehouse be out of sync with the work items that already existed? Will there be any other issues?

Also - if you wouldn't mind - could you create the work item for the necessary instructions in #3 and #6 as you will probably be able to better explain what is needed. If you could post the details here if they won't be going immediately into the work item it would also be helpful.

From the information we got from the CQ team, the data should have been imported with the correct modification dates; which should have triggered the data collection task to update the data warehouse. Why do you think the data is missing?

Actually the steps to fix this can be reduced to one step:

Login to your database and empty all the tables under the WORKITEMS_SNAPSHOT schema. The next time the data collection task runs, it will refill all the tables. For this to work you really need to empty all the tables under the WORKITEMS_SNAPSHOT. The list of tables is (for DB2):

WORKITEM_CHNGS, WORKITEM_STATES, WC_INT_EXT, WC_BOOL_EXT, WC_LONG_EXT, WC_DOUBLE_EXT, WC_FLOAT_EXT, WC_TIMESTAMP_EXT, WC_STRING_EXT, WC_BIGDECIMAL_EXT, WC_MEDIUM_STRING_EXT, WC_LARGE_STRING_EXT.

I do not see the same set of tables under WORKITEMS_SNAPSHOT schema:

db2 => LIST TABLES FOR SCHEMA WORKITEMS_SNAPSHOT



Table/View                      Schema          Type  Creation time

------------------------------- --------------- ----- --------------------------

CATEGORY                        WORKITEMS_SNAP> T     2009-04-13-15.03.04.800283

NEW_WI_COUNT                    WORKITEMS_SNAP> V     2009-04-13-15.03.05.127067

PRIORITY                        WORKITEMS_SNAP> T     2009-04-13-15.03.04.911841

SEVERITY                        WORKITEMS_SNAP> T     2009-04-13-15.03.04.931222

STATE_TBL                       WORKITEMS_SNAP> T     2009-04-13-15.03.04.872477

TYPE_TBL                        WORKITEMS_SNAP> T     2009-04-13-15.03.04.892682

WORKITEM_CHNGS                  WORKITEMS_SNAP> T     2009-04-13-15.03.04.950971

WORKITEM_CHNGS_FROM             WORKITEMS_SNAP> V     2009-04-13-15.03.05.482653

WORKITEM_CHNGS_TO               WORKITEMS_SNAP> V     2009-04-13-15.03.05.450310

WORKITEM_STATES                 WORKITEMS_SNAP> T     2009-04-13-15.03.05.052977



  10 record(s) selected.

BTW this is a RTC 1.0.1 db. Will it suffice if I delete records from the tables listed above for RTC 1.0.1?

From the information we got from the CQ team, the data should have been imported with the correct modification dates; which should have triggered the data collection task to update the data warehouse. Why do you think the data is missing?

Actually the steps to fix this can be reduced to one step:

Login to your database and empty all the tables under the WORKITEMS_SNAPSHOT schema. The next time the data collection task runs, it will refill all the tables. For this to work you really need to empty all the tables under the WORKITEMS_SNAPSHOT. The list of tables is (for DB2):

WORKITEM_CHNGS, WORKITEM_STATES, WC_INT_EXT, WC_BOOL_EXT, WC_LONG_EXT, WC_DOUBLE_EXT, WC_FLOAT_EXT, WC_TIMESTAMP_EXT, WC_STRING_EXT, WC_BIGDECIMAL_EXT, WC_MEDIUM_STRING_EXT, WC_LARGE_STRING_EXT.

Thanks
Sunil

For a 1.0.1 data warehouse, remove all the rows from the following tables:
WORKITEM_CHNGS, WORKITEM_STATES

For a 1.0.1 data warehouse, remove all the rows from the following tables:
WORKITEM_CHNGS, WORKITEM_STATES

Ok - we've now tested this on a 2.0 server as well. In a project which had only 4 automatic task work items from project initialization in it we imported 1282 defect and enhancements through bugzilla import. After this and several nightly runs of the snapshot updates we had only 4 tasks, 0 defects and 0 enhancements being shown by the "Open Work Items by Type" default query.

After this I went in and deleted all rows from the tables in your post. I did not delete the rows from WORKITEMS_SNAPSHOT.CATEGORY, PRIORITY, SEVERITY, STATE_TBL or TYPE_TBL tables.

I went and ran the same report again against this project after this - and saw that I was getting no values to select in the parameters and zero results so the data was definitely clean.

After rerunning the snapshot update the report still shows only the 4 initial tasks (with all items selected in parameters).

Doing some spelunking in the WORKITEM_CNHGS table shows that there is one record in it for every work item in the project. The category ID in CATEGORY_ID exists in the WORKITEM_SNAPSHOT.CATEGORY table. However I find that when selecting the report parameters the only category listed for selection is the base team default category. All of the imported defects/enhancements have a different category that is a sibling of the default team category. These categories were auto-created during the import process.

I also see that there are 6430 records in WORKITEM_STATES where WC_FACTID matches the FACT_ID from the rows in WORKITEM_CHNGS for the project.

Thoughts?

(FYI - Sunil, Bharath and I work together)

On 8/11/2009 10:38 AM, xdcx11 wrote:

rjaouaniwrote:
For a 1.0.1 data warehouse, remove all the rows from the following
tables:
WORKITEM_CHNGS, WORKITEM_STATES

Ok - we've now tested this on a 2.0 server as well. In a project which
had only 4 automatic task work items from project initialization in it
we imported 1282 defect and enhancements through bugzilla import.
After this and several nightly runs of the snapshot updates we had
only 4 tasks, 0 defects and 0 enhancements being shown by the
"Open Work Items by Type" default query.

After this I went in and deleted all rows from the tables in your
post. I did not delete the rows from WORKITEMS_SNAPSHOT.CATEGORY,
PRIORITY, SEVERITY, STATE_TBL or TYPE_TBL tables.

I went and ran the same report again against this project after this -
and saw that I was getting no values to select in the parameters and
zero results so the data was definitely clean.

After rerunning the snapshot update the report still shows only the 4
initial tasks (with all items selected in parameters).

Doing some spelunking in the WORKITEM_CNHGS table shows that there is
one record in it for every work item in the project. The category ID
in CATEGORY_ID exists in the WORKITEM_SNAPSHOT.CATEGORY table.
However I find that when selecting the report parameters the only
category listed for selection is the base team default category. All
of the imported defects/enhancements have a different category that
is a sibling of the default team category. These categories were
auto-created during the import process.

I also see that there are 6430 records in WORKITEM_STATES where
WC_FACTID matches the FACT_ID from the rows in WORKITEM_CHNGS for the
project.

Thoughts?

(FYI - Sunil, Bharath and I work together):

When running the report, which parameters are you specifying? Keep in
mind the "Open Work Items by Type" has, by default, "My Team Areas"
specified for the Team Area parameter, and "Current Iteration" for the
Iteration parameter. If you clear these selections, so that no values
are selected for any parameters, do you still see the same thing?

james
RTC Reports Team Lead

We debugged this issue with the customer, and found out this was caused by user error:
- The import and the data collection tasks worked as expected.
- The imported work items were assigned to a category that had no deliverable (the "a release is scheduled for this iteration" checkbox is unchecked). Categories with no deliverables are not collected in the data warehouse. So work items assigned to those will not show up in the charts.
- To fix this, check the "a release is scheduled for this iteration" checkbox and wait for the data warehouse collection tasks to run at the end of the day.

This particular customer used a bugzilla importer. I am not sure if the CQ importer has the same problem. Work items should be assigned to Iterations that have deliverables.

Can someone from the Work Items team, please confirm the above statement?