CDR Tickets

BZDATETIME::2010-01-05 16:18:21
BZCOMMENTOR::Bob Kline
BZCOMMENT::1

(In reply to comment #0)

> ... Or are we going about this process using the wrong approach?

Yes. First of all, what you're calling "forced import" is actually a mechanism to ensure that NLM will send us a trial which they otherwise wouldn't, because it isn't indexed in a way that matches our query. So it's really a "forced download," not a "forced import." Second, removing the 'blocked' disposition for the trials (which you did in preparation for the "force") clears out the CDR ID from the cdr_id column, so it's no surprise that the import software will create new CTGovProtocol documents. Third (and this is the critical piece, and the reason I've bumped up the priority to P2), unblocking the old CT.gov documents was a big mistake, because it caused those out-of-date documents to be re-published.

Whenever you're about to do something you haven't ever tried before, it's always prudent to try it on one document and make sure that your action will have the effect that you think it will. And if you're not absolutely certain what's going to happen with this new situation, I would strongly urge CIAT to consult with OCCM before you pull the trigger.

I think the following things need to happen (in this order):

1. Re-block the old CT.gov protocols. Do this immediately.
2. Have Lakshmi review this comment and let us know if anything in it is
wrong.
3. Delete the rows in the ctgov_import table for these trials.
4. Delete the new CT.gov protocol documents.

I will need to perform step 3 myself (after Lakshmi has taken care of step 2), and I can also handle step 4. Then the next import job should see the transfer block for the InScopeProtocol docs and handle these the way it would any newly transferred trial. You would then check to make sure all of the trials came in and got imported, and only if one or more trials didn't come in (because their indexing didn't match our query) would you force download of those trials.

BZDATETIME::2010-01-05 17:14:03
BZCOMMENTOR::William Osei-Poku
BZCOMMENT::2

(In reply to comment #1)
> (In reply to comment #0)
>
> > ... Or are we going about this process using the wrong approach?
>
> Yes. First of all, what you're calling "forced import" is actually a mechanism
> to ensure that NLM will send us a trial which they otherwise wouldn't, because
> it isn't indexed in a way that matches our query. So it's really a "forced
> download," not a "forced import."

Thanks for the correction. I really need to get into the habit of calling this the right and new name instead what it used to be called :-).

>Second, removing the 'blocked' disposition
> for the trials (which you did in preparation for the "force") clears out the
> CDR ID from the cdr_id column, so it's no surprise that the import software
> will create new CTGovProtocol documents.
>Third (and this is the critical
> piece, and the reason I've bumped up the priority to P2), unblocking the old
> CT.gov documents was a big mistake, because it caused those out-of-date
> documents to be re-published.
>

Should we implement something that prevents this from happening (One NCT IDs creating two records for CTGov documents? I was under the impression that for CTGov documents, the NCT ID alone was unique and that it was NOT possible to have the same NCT ID in the table two or more times and that was why we force download to get updated versions of a trial. Some of the situations we encounter with the duplicate protocols can be confusing sometimes and it is probably better to have the software prevent the duplicates from happening in situations like this.

> Whenever you're about to do something you haven't ever tried before, it's
> always prudent to try it on one document and make sure that your action will
> have the effect that you think it will. And if you're not absolutely certain
> what's going to happen with this new situation, I would strongly urge CIAT to
> consult with OCCM before you pull the trigger.
>
> I think the following things need to happen (in this order):
>
> 1. Re-block the old CT.gov protocols. Do this immediately.

This is completed.

> 2. Have Lakshmi review this comment and let us know if anything in it is
> wrong.
> 3. Delete the rows in the ctgov_import table for these trials.
> 4. Delete the new CT.gov protocol documents.
>
> I will need to perform step 3 myself (after Lakshmi has taken care of step 2),
> and I can also handle step 4. Then the next import job should see the transfer
> block for the InScopeProtocol docs and handle these the way it would any newly
> transferred trial. You would then check to make sure all of the trials came in
> and got imported, and only if one or more trials didn't come in (because
> their indexing didn't match our query) would you force download *of those
> trials*.

BZDATETIME::2010-01-06 09:35:58
BZCOMMENTOR::Lakshmi Grama
BZCOMMENT::3

I have reviewed the comment and the solution Bob writes up is what we discussed yesterday. Is there a way you can test on Mahler?

BZDATETIME::2010-01-06 10:18:00
BZCOMMENTOR::Bob Kline
BZCOMMENT::4

(In reply to comment #3)
> Is there a way you can test on Mahler?

I believe so. I propose that the test be done in two halves. Let's have CIAT repeat the original steps #5, #6, and #7 as described in the initial comment for this issue on the first half of the trials listed in that comment. Then I will run a download/import job. At this point, we'll have created the conditions which existed on Bach for those trials yesterday at the point in time when the problem was reported (except we won't have published the old CT.gov protocols). Then we'll perform steps #1, #3, and #4 from comment #1 above and I'll run another download/import job.

For the remaining trials listed in the original comment we'll do things the way they should have been done to start with. CIAT will add the transfer blocks and I will drop the rows from the ctgov_import table for those trials and run another download/import job (which I'll probably combine with the second job above).

Then you and CIAT can review the results and confirm that "all is well" for both sets. :-)

Sound like a reasonable approach?

BZDATETIME::2010-01-06 10:55:26
BZCOMMENTOR::Bob Kline
BZCOMMENT::5

(In reply to comment #2)

> Should we implement something that prevents this from happening (One NCT IDs
> creating two records for CTGov documents?

The reason a second CTGovProtocol document is created is that the table's record of the CDR ID for the first CTGovProtocol document was removed. This happened when CIAT decided that we no longer wanted to import NLM's version of the trial information, and replaced the CTGovProtocol CDR ID with the ID for the InScopeProtocol document of which NLM's document was now to be considered a duplicate. When you went in with the "force download" step even that ID was wiped out, at which point there was no connection between the NCT ID and any CDR document. So the import software had no choice but to create a new CTGovProtocol document. In fact, you could have the software create an unlimited number of new CTGovProtocol documents by setting the cdr_id column to NULL after every download. It would have been possible to undo the first decision by having the original CTGovProtocol's CDR ID manually restored in the cdr_id column (there's no user interface for doing this), but that's not what you would really want to do, because it would miss the opportunity to transfer the most recent information from the InScopeProtocol document and have the trial continue to be published with that document's CDR ID. As for preventing users from doing what you have done, we could remove the interface for marking documents as duplicates, and prevent users from forcing download of trials we've received before. Would you really want us to do that?

> I was under the impression that for CTGov documents, the NCT ID alone was
> unique and that it was NOT possible to have the same NCT ID in the table
> two or more times ...

Yes, that's absolutely the case: the nlm_id column, where the NCT id is stored, is the primary key for the table, so yes, only one to a customer. However, I hope my explanation above makes it clear why, when you remove the CDR ID telling the software where to store updates for the imported document, the software has no choice but to create a new one. It might be possible to go find the original CTGovProtocol document by looking for the NCT ID if there were only one such document per NCT ID, but that's not true, and even if it were true how do we know that CIAT wouldn't sometimes want a new CTGovProtocol document created in some situations?

> ... and that was why we force download to get updated versions of a trial.

Once again, the reason for the mechanism to force NLM to give us a trial document is because NLM wouldn't give us the document as part of the result set we get back from our base query. That's the only purpose of this mechanism. It has always been the only purpose of this mechanism.

BZDATETIME::2010-01-06 14:21:36
BZCOMMENTOR::William Osei-Poku
BZCOMMENT::6

(In reply to comment #4)
> (In reply to comment #3)
> > Is there a way you can test on Mahler?
>
> I believe so. I propose that the test be done in two halves. Let's have CIAT
> repeat the original steps #5, #6, and #7 as described in the initial comment
> for this issue on the first half of the trials listed in that comment. Then I
> will run a download/import job. At this point, we'll have created the
> conditions which existed on Bach for those trials yesterday at the point in
> time when the problem was reported (except we won't have published the old
> CT.gov protocols). Then we'll perform steps #1, #3, and #4 from comment #1
> above and I'll run another download/import job.
>

This is done for the first 5 trials on the list in Mahler.

(In reply to comment #5)

> As for preventing
> users from doing what you have done, we could remove the interface for marking
> documents as duplicates, and prevent users from forcing download of trials
> we've received before. Would you really want us to do that?
>
How about implementing a warning email or report or alert, even if it is after the fact? I am concerned that if nothing is done to either prevent or alert, there is a possibility that some of these situations can go unnoticed for a long time. Some of the trials that fall into this category will come up during the transfer process and while we will be careful to follow the proper approach, I think putting in place a 'system' solution even if it is not perfect will be helpful.

BZDATETIME::2010-01-06 14:43:28
BZCOMMENTOR::Bob Kline
BZCOMMENT::7

(In reply to comment #6)

> This is done for the first 5 trials on the list in Mahler.

My intention was that we not do anything until Lakshmi has responded to my question about whether my proposed approach to testing meets her expectations.

BZDATETIME::2010-01-06 15:13:03
BZCOMMENTOR::William Osei-Poku
BZCOMMENT::8

(In reply to comment #7)
> (In reply to comment #6)
>
> > This is done for the first 5 trials on the list in Mahler.
>
> My intention was that we not do anything until Lakshmi has responded to my
> question about whether my proposed approach to testing meets her expectations.

I did not realize that, Sorry. I suppose since this is Mahler, it won't be a problem to undo changes or to leave the changes alone if Lakshmi disagrees with the approach or suggests a different one.

BZDATETIME::2010-01-07 10:22:13
BZCOMMENTOR::Bob Kline
BZCOMMENT::9

(In reply to comment #6)

> How about implementing a warning email or report or alert, even if it is after
> the fact?

Please work with Volker (via a separate issue) to have him put some additional documentation on the page used for marking/unmarking documents as duplicates, explaining more fully the implications of the actions. The extra documentation should make it clear that when you tell the system you want to stop getting a trial document from NLM and instead use an InScopeProtocol document of our own to represent the trial, the connection between the NCT ID and the old CTGovProtocol document is removed from the table used to track CT.gov imports, and when you change your mind and tell the system that you no longer want to treat NLM's document as a duplicate, you are removing the connection between the NCT ID and any CDR document, which will cause the import software to create a new CTGovProtocol document. If you're afraid the users won't read the documentation, you can have Volker use a blinking bold red font. If you're still afraid they won't read it, you can have him send the user an email message containing the same explanation.

You could also have him enhance the interface to allow the users to wipe out the row from the ctgov_import table, so that instead of creating a CTGovProtocol document under a new CDR ID (or finding the ID of the old CTGovProtocol document so we can resume importing to new versions of that document) the import will create a new CTGovProtocol version using the CDR ID of the InScopeProtocol document, preserving the information from that document. But if the basic problem is that the users are performing actions on these duplicate documents without understanding what they're doing, I'm afraid this would only make the problem worse.

You might also want to review the online documentation for these processes to make sure that it's accurate and adequately explains what the users should understand when they're working with protocol imports. If not, I would strongly urge you to correct any gaps or inaccuracies (and have Volker or me review the results).

BZDATETIME::2010-01-07 11:08:53
BZCOMMENTOR::Bob Kline
BZCOMMENT::10

(In reply to comment #4)

> Sound like a reasonable approach?

Lakshmi gave verbal approval for proceeding with the testing. I have run the first download/import job for the first half of the test set. CIAT can now re-block the InScopeProtocol documents for those trials. CIAT can also go ahead and add the transfer blocks to the InScopeProtocol documents in the second half of the test set. Let me know when these steps are done, and I'll proceed with my next tasks.

BZDATETIME::2010-01-07 14:48:04
BZCOMMENTOR::Bob Kline
BZCOMMENT::11

(In reply to comment #10)

> CIAT can now re-block the InScopeProtocol documents for those trials.

That should have said "... re-block the CTGovProtocol documents for those trials." Also, CIAT needs to unblock the InScopeProtocol documents for those trials, and also unblock all eleven of the InScopeProtocol documents on the production system.

BZDATETIME::2010-01-07 16:33:07
BZCOMMENTOR::William Osei-Poku
BZCOMMENT::12

(In reply to comment #11)
> (In reply to comment #10)
>
> > CIAT can now re-block the InScopeProtocol documents for those trials.
>
> That should have said "... re-block the CTGovProtocol documents for those
> trials." Also, CIAT needs to unblock the InScopeProtocol documents for those
> trials, and also unblock all eleven of the InScopeProtocol documents on the
> production system.

MAHLER:
1. The five test InScope trials on Mahler which were blocked have now been unblocked.
2. The corresponding CTGov documents are all blocked
3. The five test Inscope trials on Mahler (in step 1 above) now have transfer blocks
4. The other 6 remaining Inscope trials on Mahler also have the transfer blocks.

Are all of the above steps correct?

BACH:
1. All the blocked InScope trials on Bach have been unblocked. (All of them do have the transfer blocks also and the old CTGov documents remain blocked).

On Bach, we will also need to block the duplicate records that were created, right? They are currently not publishable but it looks like we still need to block them?

BZDATETIME::2010-01-07 17:06:54
BZCOMMENTOR::Bob Kline
BZCOMMENT::13

(In reply to comment #12)

> 3. The five test Inscope trials on Mahler (in step 1 above) now have
> transfer blocks

Are you saying that they didn't have transfer blocks at the point when you posted comment #6, where you said "This is done for the first 5 trials on the list in Mahler"?

> Are all of the above steps correct?

Depends on your answer to the question I just asked.

> On Bach, we will also need to block the duplicate records that were created,
> right? They are currently not publishable but it looks like we still need to
> block them?

Technically no; if you never create publishable versions they'll never be published. I'd block (or delete) them anyway.

BZDATETIME::2010-01-07 17:14:04
BZCOMMENTOR::William Osei-Poku
BZCOMMENT::14

(In reply to comment #13)
> (In reply to comment #12)
>
> > 3. The five test Inscope trials on Mahler (in step 1 above) now have
> > transfer blocks
>
> Are you saying that they didn't have transfer blocks at the point when you
> posted comment #6, where you said "This is done for the first 5 trials on the
> list in Mahler"?
>

No they did have transfer blocks from yesterday. The remaining 6 got their transfer blocks today. Sorry about the confusion.

BZDATETIME::2010-01-08 11:34:40
BZCOMMENTOR::Bob Kline
BZCOMMENT::15

(In reply to comment #4)

> Then you and CIAT can review the results and confirm that "all is well" for
> both sets. :-)

We're ready for this step, Lakshmi. Here are the IDs in question for the documents on Mahler to be reviewed (where A means first half of test, in which the incorrect processing steps were applied to the documents and then backed out, and B identifies the second half of the test, in which the documents were processed correctly to start with):

NCT00237627 368767 (A)
NCT00280176 549844 (A)
NCT00405366 550127 (A)
NCT00334438 550130 (A)
NCT00307255 550136 (A)
NCT00320073 550148 (B)
NCT00246753 550151 (B)
NCT00227032 550155 (B)
NCT00280748 551069 (B)
NCT00257426 561597 (B)
NCT00280735 561625 (B)

BZDATETIME::2010-01-11 11:23:21
BZCOMMENTOR::Bob Kline
BZCOMMENT::16

(In reply to comment #15)
> (In reply to comment #4)
>
> > Then you and CIAT can review the results and confirm that "all is well" for
> > both sets. :-)
>
> We're ready for this step, Lakshmi. Here are the IDs in question for the
> documents on Mahler to be reviewed (where A means first half of test, in which
> the incorrect processing steps were applied to the documents and then backed
> out, and B identifies the second half of the test, in which the documents were
> processed correctly to start with):
>
> NCT00237627 368767 (A)
> NCT00280176 549844 (A)
> NCT00405366 550127 (A)
> NCT00334438 550130 (A)
> NCT00307255 550136 (A)
> NCT00320073 550148 (B)
> NCT00246753 550151 (B)
> NCT00227032 550155 (B)
> NCT00280748 551069 (B)
> NCT00257426 561597 (B)
> NCT00280735 561625 (B)

This is a high-priority issue, so I want to make sure it doesn't fall through the cracks.

BZDATETIME::2010-01-11 11:39:48
BZCOMMENTOR::Bob Kline
BZCOMMENT::17

Might it be a good idea to revisit the rule that we always skip over trials marked as duplicates when we get the set from NLM during the download job? There might be a way to handle those which have a corresponding InScopeProtocol document to which a transfer block has been added. Would there be any drawbacks to overriding the rule for such documents? Are there cases involving these documents where we'd still want to apply the rule?

BZDATETIME::2010-01-11 12:18:25
BZCOMMENTOR::William Osei-Poku
BZCOMMENT::18

(In reply to comment #16)
> (In reply to comment #15)
> > (In reply to comment #4)
> >
> > > Then you and CIAT can review the results and confirm that "all is well" for
> > > both sets. :-)
> >
> > We're ready for this step, Lakshmi. Here are the IDs in question for the
> > documents on Mahler to be reviewed (where A means first half of test, in which
> > the incorrect processing steps were applied to the documents and then backed
> > out, and B identifies the second half of the test, in which the documents were
> > processed correctly to start with):
> >
> > NCT00237627 368767 (A)
> > NCT00280176 549844 (A)
> > NCT00405366 550127 (A)
> > NCT00334438 550130 (A)
> > NCT00307255 550136 (A)
> > NCT00320073 550148 (B)
> > NCT00246753 550151 (B)
> > NCT00227032 550155 (B)
> > NCT00280748 551069 (B)
> > NCT00257426 561597 (B)
> > NCT00280735 561625 (B)
>
> This is a high-priority issue, so I want to make sure it doesn't fall through
> the cracks.
They all look good.

However, one additional trial - 579861 NCT00574145 (which does not appear to be in the set of trials we are currently testing) is also on the Newly imported Transferred Trials list. But this trial is still in the InScope version instead of the converted version.

BZDATETIME::2010-01-11 12:38:00
BZCOMMENTOR::Bob Kline
BZCOMMENT::19

(In reply to comment #18)

> However, one additional trial - 579861 NCT00574145 (which does not appear to be
> in the set of trials we are currently testing) is also on the Newly imported
> Transferred Trials list. But this trial is still in the InScope version instead
> of the converted version.

Sorry, what's the relevance of this to the duplicate CTGovProtocol cleanup issue?

BZDATETIME::2010-01-11 12:40:43
BZCOMMENTOR::William Osei-Poku
BZCOMMENT::20

(In reply to comment #17)
> Might it be a good idea to revisit the rule that we always skip over trials
> marked as duplicates when we get the set from NLM during the download job?
> There might be a way to handle those which have a corresponding InScopeProtocol
> document to which a transfer block has been added. Would there be any
> drawbacks to overriding the rule for such documents? Are there cases involving
> these documents where we'd still want to apply the rule?

It is not in all cases that we own the corresponding CTGov trial (in clinicaltrials.gov). For example, 389159 was correctly skipped by the software. We did not register the CTGov version of the trial (which we have obviously not imported yet). Technically, trials that fall into the above category (and there may be quite a few) cannot be transferred. So bypassing the rule completely may not be the solution in all situations. We may be able to provide you with some scenarios to see if you can provide a programmatic solution but also considering the fact that there are not too many trials that fall into this category for which we need to transfer, I think reporting such skipped trials should suffice for now.

BZDATETIME::2010-01-11 12:44:15
BZCOMMENTOR::William Osei-Poku
BZCOMMENT::21

(In reply to comment #19)
> (In reply to comment #18)
>
> > However, one additional trial - 579861 NCT00574145 (which does not appear to be
> > in the set of trials we are currently testing) is also on the Newly imported
> > Transferred Trials list. But this trial is still in the InScope version instead
> > of the converted version.
>
> Sorry, what's the relevance of this to the duplicate CTGovProtocol cleanup
> issue?

Please ignore since it has nothing to do with the review I am doing. I was just reporting this because it was included in the list. I expected to see only 11 trials on the list but I saw 12 and I thought I should let you know. I am sorry about this confusion.

BZDATETIME::2010-01-14 13:07:42
BZCOMMENTOR::Bob Kline
BZCOMMENT::22

Lakshmi said she doesn't need to review the test results. Bob needs to apply the fix on Bach, and CIAT will have Bob remove the ctgov_import rows for similar cases which come up in the future.

BZDATETIME::2010-01-14 17:01:30
BZCOMMENTOR::Bob Kline
BZCOMMENT::23

I have removed the 11 rows for the trials affected from the ctgov_import table (along with the dependent rows in the ctgov_import_event table). I made copies in xctgov_import_event_task4734 and xctgov_import_task4734 so we don't lose the information. We need to monitor what happens with tomorrow morning's download and import jobs very carefully. I will delete the 11 unwanted duplicate CTGovProtocol documents (CDR662993 etc.) after we confirm that the transfers were handled correctly.

BZDATETIME::2010-01-15 11:34:38
BZCOMMENTOR::William Osei-Poku
BZCOMMENT::24

(In reply to comment #23)
> I have removed the 11 rows for the trials affected from the ctgov_import table
> (along with the dependent rows in the ctgov_import_event table). I made copies
> in xctgov_import_event_task4734 and xctgov_import_task4734 so we don't lose the
> information. We need to monitor what happens with tomorrow morning's download
> and import jobs very carefully. I will delete the 11 unwanted duplicate
> CTGovProtocol documents (CDR662993 etc.) after we confirm that the transfers
> were handled correctly.

Confirmed. The transfers came through correctly. Thank you!

BZDATETIME::2010-01-22 15:11:13
BZCOMMENTOR::Bob Kline
BZCOMMENT::25

The following documents were deleted (extra CTGovProtocol docs):

CDR0000662993
CDR0000662996
CDR0000663002
CDR0000663001
CDR0000662999
CDR0000663000
CDR0000662994
CDR0000662992
CDR0000662998
CDR0000662995
CDR0000662997

BZDATETIME::2010-01-22 15:47:53
BZCOMMENTOR::William Osei-Poku
BZCOMMENT::26

(In reply to comment #25)
> The following documents were deleted (extra CTGovProtocol docs):
>
> CDR0000662993
> CDR0000662996
> CDR0000663002
> CDR0000663001
> CDR0000662999
> CDR0000663000
> CDR0000662994
> CDR0000662992
> CDR0000662998
> CDR0000662995
> CDR0000662997

Verified!

I have marked this issue as resolved.

BZDATETIME::2010-01-22 15:48:37
BZCOMMENTOR::William Osei-Poku
BZCOMMENT::27

(In reply to comment #26)

> Verified!
>
> I have marked this issue as resolved.

Issue Closed. Thank you!