Migrating Stability Studies from a legacy LIMS to a new LIMS....
20 views
Skip to first unread message
cnovak
May 30, 2008, 7:41:11 PM5/30/08
to LIMS & Lab Informatics
When implementing a new LIMS, often a topic of intense discussion is
the strategy for accessing data from a legacy system once the new
system is in use. Of particular concern is how to best address the
open Stability Studies.
Does anyone have experience or opinions they are willing to share
about how to best determine if an open Stability Study should be moved
to a new system? What criteria are used to make the determination?
Length of the study? Time left on the study?
Your insights are greatly appreciated.
Cindy
the strategy for accessing data from a legacy system once the new
system is in use. Of particular concern is how to best address the
open Stability Studies.
Does anyone have experience or opinions they are willing to share
about how to best determine if an open Stability Study should be moved
to a new system? What criteria are used to make the determination?
Length of the study? Time left on the study?
Your insights are greatly appreciated.
Cindy
list_...@geometrick.com
May 30, 2008, 9:42:13 PM5/30/08
to lims-l...@googlegroups.com
Cindy,
Issues to consider:
- If a study is short, a few months or so, it's probably best left on the system it's started on. However, the other thing to consider is whether it's part of a larger project. If an entire project has just started, its studies are all probably best moved to the new system.
- Doing an automated conversion is a problem, as you know. However, doing a study partially in one system, partially in another, then makes the study more difficult to report and analyze.
- So, a long study, something a few years long, you might want to entirely restart in the new system. Let's suppose it's a 5-year study. If it's got 4 years left to go, it's probably worth rescheduling in the new system. If it's got 2 years left to go, it seems somewhat less worthwhile to start in the new system. Then, you have to ask yourself whether it's worth keeping the old system around for 2 more years or whether you should entirely put the whole study in the new system or whether you can find a way to report it between two systems.
- If your old system is automated, you might be able to read from it to merely create the ongoing studies. Then, you can hire temps to manually add/remove tests and enter the final results. Cindy, I know your current system is a LabWare system, and I know you can automate to that point, with some programming, of course. But, it's much less drastic than what you'd have to do if you tried to automated the whole thing.
- The other thing you can do is to create a program that will dump data from both systems into a reporting database. Then, use that database to report and trend the studies.
Gloria Metrick
GeoMetrick Enterprises
Helping companies understand and manage their laboratory data.
1.781.365.0180 (Voice Only)
http://www.GeoMetrick.com/
LinkedIn
Profile:
http://www.linkedin.com/in/geometrick
GeoMetrick Enterprises is the home of "Out
on a LIMS: The Newsletter for People Who Risk Life and LIMS on a Daily
Basis" which is internationally distributed monthly to
subscribers across the industry, probably including your co-workers and
competitors, including every major LIMS vendor.
To subscribe:
http://www.geometrick.com/newsltr_signup.htm
Eddie
May 31, 2008, 9:51:45 AM5/31/08
to LIMS & Lab Informatics
Gloria gave a thorough reply for any day of the week. I just wanted
to share a piece of personal experience. I had the same issue moving
from an old DOS based LIMS to Oracle and had little choice but to
migrate the on-going stability studies. It amounted to printing out
reports of old data and having temps enter the data into the new
system. It was labor intensive and the old data often did not fit
into the new format, as it had changed much over the years. My first
choice (from admin perspective) would be to leave the old system
intact, if you are willing to use it for the next x years, which most
end users are not. Second choice would be to use something like
Crystal and create reports with sub reports that have ODBC connect to
each system. You can have part of the study in the old system, part
in the new, and combine them on the report. I don't know how this
would work for statistical analysis, as Gloria pointed out. In the
end, I think that Gloria's suggestion about exporting the data from
both into a reporting database is probably the most practical
solution. That is also a common practice for archiving purposes of a
single system and should not present too much difficulty. Good luck
and let us know how it turns out.
Ed
On May 30, 9:42 pm, list_mem...@geometrick.com wrote:
> Cindy,
>
> At 07:41 PM 5/30/2008, you wrote:
>
> >When implementing a new LIMS, often a topic of intense discussion is
> >the strategy for accessing data from a legacy system once the new
> >system is in use. Of particular concern is how to best address the
> >open Stability Studies.
> >Does anyone have experience or opinions they are willing to share
> >about how to best determine if an open Stability Study should be moved
> >to a new system? What criteria are used to make the determination?
> >Length of the study? Time left on the study?
> >Your insights are greatly appreciated.
>
> Issues to consider:
> * If a study is short, a few months or so, it's probably best
to share a piece of personal experience. I had the same issue moving
from an old DOS based LIMS to Oracle and had little choice but to
migrate the on-going stability studies. It amounted to printing out
reports of old data and having temps enter the data into the new
system. It was labor intensive and the old data often did not fit
into the new format, as it had changed much over the years. My first
choice (from admin perspective) would be to leave the old system
intact, if you are willing to use it for the next x years, which most
end users are not. Second choice would be to use something like
Crystal and create reports with sub reports that have ODBC connect to
each system. You can have part of the study in the old system, part
in the new, and combine them on the report. I don't know how this
would work for statistical analysis, as Gloria pointed out. In the
end, I think that Gloria's suggestion about exporting the data from
both into a reporting database is probably the most practical
solution. That is also a common practice for archiving purposes of a
single system and should not present too much difficulty. Good luck
and let us know how it turns out.
Ed
On May 30, 9:42 pm, list_mem...@geometrick.com wrote:
> Cindy,
>
> At 07:41 PM 5/30/2008, you wrote:
>
> >When implementing a new LIMS, often a topic of intense discussion is
> >the strategy for accessing data from a legacy system once the new
> >system is in use. Of particular concern is how to best address the
> >open Stability Studies.
> >Does anyone have experience or opinions they are willing to share
> >about how to best determine if an open Stability Study should be moved
> >to a new system? What criteria are used to make the determination?
> >Length of the study? Time left on the study?
> >Your insights are greatly appreciated.
>
> Issues to consider:
> left on the system it's started on. However, the other thing to
> consider is whether it's part of a larger project. If an entire
> project has just started, its studies are all probably best moved to
> the new system.
> * Doing an automated conversion is a problem, as you know.
> consider is whether it's part of a larger project. If an entire
> project has just started, its studies are all probably best moved to
> the new system.
> However, doing a study partially in one system, partially in another,
> then makes the study more difficult to report and analyze.
> * So, a long study, something a few years long, you might want to
> then makes the study more difficult to report and analyze.
> entirely restart in the new system. Let's suppose it's a 5-year
> study. If it's got 4 years left to go, it's probably worth
> rescheduling in the new system. If it's got 2 years left to go, it
> seems somewhat less worthwhile to start in the new system. Then, you
> have to ask yourself whether it's worth keeping the old system around
> for 2 more years or whether you should entirely put the whole study
> in the new system or whether you can find a way to report it between
> two systems.
> * If your old system is automated, you might be able to read from
> study. If it's got 4 years left to go, it's probably worth
> rescheduling in the new system. If it's got 2 years left to go, it
> seems somewhat less worthwhile to start in the new system. Then, you
> have to ask yourself whether it's worth keeping the old system around
> for 2 more years or whether you should entirely put the whole study
> in the new system or whether you can find a way to report it between
> two systems.
> it to merely create the ongoing studies. Then, you can hire temps to
> manually add/remove tests and enter the final results. Cindy, I know
> your current system is a LabWare system, and I know you can automate
> to that point, with some programming, of course. But, it's much less
> drastic than what you'd have to do if you tried to automated the whole thing.
> * The other thing you can do is to create a program that will
> manually add/remove tests and enter the final results. Cindy, I know
> your current system is a LabWare system, and I know you can automate
> to that point, with some programming, of course. But, it's much less
> drastic than what you'd have to do if you tried to automated the whole thing.
> dump data from both systems into a reporting database. Then, use that
> database to report and trend the studies.
> Those are the main thoughts I have on this. But it's Friday night and
> starting to get late, so I hope I haven't left anything major out of
> them. Good luck.
>
> Gloria Metrick
> GeoMetrick Enterprises
> Helping companies understand and manage their laboratory data.
>
> 1.781.365.0180 (Voice Only)http://www.GeoMetrick.com/
> database to report and trend the studies.
> Those are the main thoughts I have on this. But it's Friday night and
> starting to get late, so I hope I haven't left anything major out of
> them. Good luck.
>
> Gloria Metrick
> GeoMetrick Enterprises
> Helping companies understand and manage their laboratory data.
>
Stu Miller (moderator)
May 31, 2008, 10:08:56 AM5/31/08
to LIMS & Lab Informatics
Hi Cindy,
Gloria makes some very good points on what could be considered in
order to filter which studies will be migrated to the new LIMS. I
would agree that the biggest factor is size / maturity of the
studies.
- Long term studies just started in legacy system, migrate them.
- Studies that are near completion, complete them in the legacy
system.
- If studies are part of a larger stability program and need to be
reported together from one database (the new one), move them.
Where it gets tricky is deciding how to move the data. Your decisions
on which studies (and therefore the volume of data) to migrate will
have a large impact on the cost - benefit scenarios that will
determine if it's economically viable. See below.
The other part of this decision is how to migrate them. Gloria gave us
some ideas there as well. Although it sounds like you've decided to
migrate some studies (just need to figure which ones "need" to be
migrated) there are also a couple other things to consider for others
who may also be trying to decide if they should migrate data and how:
1) Cost vs. benefit - assuming legacy system stores stability studies
and data in a proper database. If the cost of migrating studies in an
automated way (programs, ETL tools and validation) will be far more
than the added labor for the manual workarounds if you leave it in the
legacy system it may not be worth it. However, make sure to include
the costs of additional QC/QA procedures necessary to ensure there are
no errors generated by manual workarounds. If large volumes of
studies / data are involved, that will usually tip this decision
towards doing automated migration. Trick is figuring out how much of a
premium the company is willing to pay to get it done if the volumes
are low or relative costs are close.
2) Benefits and risks - part of the justification analysis in the
point above. How many people's jobs will be impacted by manual
workarounds? Could it cause more errors due to complicated procedures
and confusion due to parallel work processes - old process or new
process, old system or new system? What is the cost of added training
to ensure this doesn't occur? How many hours will be "lost" compared
to using a single system? How will overall productivity of the
business be impacted (not much if stability is a small part of what
you do)? How will critical but periodic events or tasks (annual
stability reporting delays etc.) be impacted by manual workarounds?
What are the risks to quality? For how long will the manual
workarounds be required?
Finally, you need to remember as well that data migration will
normally be a one time event so unless you have multiple legacy
databases to migrate to a single new system, there is no re-use
benefit (of ETL tools and programs) from automating migration. On the
other hand, if you have multiple legacy databases and they are of a
similar database structure, automated ETL migration tools re-use could
be a significant factor.
Hope this helps.
Regards,
Stu
Gloria makes some very good points on what could be considered in
order to filter which studies will be migrated to the new LIMS. I
would agree that the biggest factor is size / maturity of the
studies.
- Long term studies just started in legacy system, migrate them.
- Studies that are near completion, complete them in the legacy
system.
- If studies are part of a larger stability program and need to be
reported together from one database (the new one), move them.
Where it gets tricky is deciding how to move the data. Your decisions
on which studies (and therefore the volume of data) to migrate will
have a large impact on the cost - benefit scenarios that will
determine if it's economically viable. See below.
The other part of this decision is how to migrate them. Gloria gave us
some ideas there as well. Although it sounds like you've decided to
migrate some studies (just need to figure which ones "need" to be
migrated) there are also a couple other things to consider for others
who may also be trying to decide if they should migrate data and how:
1) Cost vs. benefit - assuming legacy system stores stability studies
and data in a proper database. If the cost of migrating studies in an
automated way (programs, ETL tools and validation) will be far more
than the added labor for the manual workarounds if you leave it in the
legacy system it may not be worth it. However, make sure to include
the costs of additional QC/QA procedures necessary to ensure there are
no errors generated by manual workarounds. If large volumes of
studies / data are involved, that will usually tip this decision
towards doing automated migration. Trick is figuring out how much of a
premium the company is willing to pay to get it done if the volumes
are low or relative costs are close.
2) Benefits and risks - part of the justification analysis in the
point above. How many people's jobs will be impacted by manual
workarounds? Could it cause more errors due to complicated procedures
and confusion due to parallel work processes - old process or new
process, old system or new system? What is the cost of added training
to ensure this doesn't occur? How many hours will be "lost" compared
to using a single system? How will overall productivity of the
business be impacted (not much if stability is a small part of what
you do)? How will critical but periodic events or tasks (annual
stability reporting delays etc.) be impacted by manual workarounds?
What are the risks to quality? For how long will the manual
workarounds be required?
Finally, you need to remember as well that data migration will
normally be a one time event so unless you have multiple legacy
databases to migrate to a single new system, there is no re-use
benefit (of ETL tools and programs) from automating migration. On the
other hand, if you have multiple legacy databases and they are of a
similar database structure, automated ETL migration tools re-use could
be a significant factor.
Hope this helps.
Regards,
Stu
> Cindy,
>
> At 07:41 PM 5/30/2008, you wrote:
>
> >When implementing a new LIMS, often a topic of intense discussion is
> >the strategy for accessing data from a legacy system once the new
> >system is in use. Of particular concern is how to best address the
> >open Stability Studies.
> >Does anyone have experience or opinions they are willing to share
> >about how to best determine if an open Stability Study should be moved
> >to a new system? What criteria are used to make the determination?
> >Length of the study? Time left on the study?
> >Your insights are greatly appreciated.
>
> Issues to consider:
> * If a study is short, a few months or so, it's probably best
>
> At 07:41 PM 5/30/2008, you wrote:
>
> >When implementing a new LIMS, often a topic of intense discussion is
> >the strategy for accessing data from a legacy system once the new
> >system is in use. Of particular concern is how to best address the
> >open Stability Studies.
> >Does anyone have experience or opinions they are willing to share
> >about how to best determine if an open Stability Study should be moved
> >to a new system? What criteria are used to make the determination?
> >Length of the study? Time left on the study?
> >Your insights are greatly appreciated.
>
> Issues to consider:
> left on the system it's started on. However, the other thing to
> consider is whether it's part of a larger project. If an entire
> project has just started, its studies are all probably best moved to
> the new system.
> * Doing an automated conversion is a problem, as you know.
> consider is whether it's part of a larger project. If an entire
> project has just started, its studies are all probably best moved to
> the new system.
> However, doing a study partially in one system, partially in another,
> then makes the study more difficult to report and analyze.
> * So, a long study, something a few years long, you might want to
> then makes the study more difficult to report and analyze.
> entirely restart in the new system. Let's suppose it's a 5-year
> study. If it's got 4 years left to go, it's probably worth
> rescheduling in the new system. If it's got 2 years left to go, it
> seems somewhat less worthwhile to start in the new system. Then, you
> have to ask yourself whether it's worth keeping the old system around
> for 2 more years or whether you should entirely put the whole study
> in the new system or whether you can find a way to report it between
> two systems.
> * If your old system is automated, you might be able to read from
> study. If it's got 4 years left to go, it's probably worth
> rescheduling in the new system. If it's got 2 years left to go, it
> seems somewhat less worthwhile to start in the new system. Then, you
> have to ask yourself whether it's worth keeping the old system around
> for 2 more years or whether you should entirely put the whole study
> in the new system or whether you can find a way to report it between
> two systems.
> it to merely create the ongoing studies. Then, you can hire temps to
> manually add/remove tests and enter the final results. Cindy, I know
> your current system is a LabWare system, and I know you can automate
> to that point, with some programming, of course. But, it's much less
> drastic than what you'd have to do if you tried to automated the whole thing.
> * The other thing you can do is to create a program that will
> manually add/remove tests and enter the final results. Cindy, I know
> your current system is a LabWare system, and I know you can automate
> to that point, with some programming, of course. But, it's much less
> drastic than what you'd have to do if you tried to automated the whole thing.
> dump data from both systems into a reporting database. Then, use that
> database to report and trend the studies.
> Those are the main thoughts I have on this. But it's Friday night and
> starting to get late, so I hope I haven't left anything major out of
> them. Good luck.
>
> Gloria Metrick
> GeoMetrick Enterprises
> Helping companies understand and manage their laboratory data.
>
> 1.781.365.0180 (Voice Only)http://www.GeoMetrick.com/
> database to report and trend the studies.
> Those are the main thoughts I have on this. But it's Friday night and
> starting to get late, so I hope I haven't left anything major out of
> them. Good luck.
>
> Gloria Metrick
> GeoMetrick Enterprises
> Helping companies understand and manage their laboratory data.
>
list_...@geometrick.com
May 31, 2008, 10:25:31 AM5/31/08
to lims-l...@googlegroups.com
At 09:51 AM 5/31/2008, you wrote:
You can have part of the study in the old system, part
in the new, and combine them on the report. I don't know how this
would work for statistical analysis, as Gloria pointed out. In the
end, I think that Gloria's suggestion about exporting the data from
both into a reporting database is probably the most practical
solution. That is also a common practice for archiving purposes of a
single system and should not present too much difficulty. Good luck
and let us know how it turns out.
Ed's pointed to the issue of reporting between two systems, so let's go
into a little more depth on that:
You could setup some views, but it's not quite that easy. That brings the fields together but many fields will have different values. Tests will probably be setup entirely differently, for example.
If you're doing single-layered studies (just an x and y axis of condition and time point) and if the time points and conditions have exactly the same name in the two systems and if the final result name is similar or the same, there's some possibility of doing this. Notice all the "ifs" in what I just said, though.
It goes back to cost/benefit, too, which I think Stu mentioned in more detail. So, the question is: Why do we need this study in one place? Just for trending?
The other possibility, rather than create a permanent reporting database, is to transport data, as needed, to the tool using it. What I mean is that some people will send their data to SAS and do some adjustments once it's put into SAS or have a program to "massage" the file before it goes into SAS.
I guess the biggest issue here is to make sure you're asking these questions as you're setting up your studies in your new system. If you do that along the way, it can make reporting and trending somewhat less painful than if you create your new study model without keeping some of this in mind.
You could setup some views, but it's not quite that easy. That brings the fields together but many fields will have different values. Tests will probably be setup entirely differently, for example.
If you're doing single-layered studies (just an x and y axis of condition and time point) and if the time points and conditions have exactly the same name in the two systems and if the final result name is similar or the same, there's some possibility of doing this. Notice all the "ifs" in what I just said, though.
It goes back to cost/benefit, too, which I think Stu mentioned in more detail. So, the question is: Why do we need this study in one place? Just for trending?
The other possibility, rather than create a permanent reporting database, is to transport data, as needed, to the tool using it. What I mean is that some people will send their data to SAS and do some adjustments once it's put into SAS or have a program to "massage" the file before it goes into SAS.
I guess the biggest issue here is to make sure you're asking these questions as you're setting up your studies in your new system. If you do that along the way, it can make reporting and trending somewhat less painful than if you create your new study model without keeping some of this in mind.
Anthony Romeo
May 31, 2008, 2:17:29 PM5/31/08
to lims-l...@googlegroups.com
There are multiple solutions to this problem, and they all have their pluses and minuses. It depends on multiple factors, from how far along studies are, to the possible costs, time and resources involved. In some ways, it is similar to the challenges experienced when moving from one LIMS to another.
Tony Romeo
E-mail for the greater good. Join the i’m Initiative from Microsoft.
Tony Romeo
E-mail for the greater good. Join the i’m Initiative from Microsoft.
Lynn Schwartz
Jul 17, 2008, 4:40:56 PM7/17/08
to LIMS & Lab Informatics
I've just rejoined this list (again), so apologies if this post is
perhaps a bit dated. I wanted to respond because I've been involved
in this decision 3 different times. The first time the solution was
to merge into a data warehouse and report from there (project
failed). The second time the solution was to use parallel systems -
start the new studies on the new LIMS and finish the old studies on
the old LIMS. The last time the decision was made to was to migrate
and we just completed this exercise this spring and sucessfully
migrated close to 1 million result records from 1 LIMS to another for
approximately 20 different Stability Studies.
The decision to migrate was made primarily based on
- the on-going nature of the project (22 on-going studies, future
studies anticipated and a desire to track data across all studies)
- the size of the dataset involved
- the chance to improve the way the tests had been defined in the
original system - multiple variations could be 'cleansed' into a
smaller set of tests to make reporting and trending easier
- the fact that we had the ability to upgrade and convert the old
(Oracle) database to read-only and use Crystal Reports to view related
metadata and audit trail records that would be too difficult to
migrate
The approach we took was to manually re-create the studies in the new
LIMS and go through the required actions to log the samples (which
also created the test and result records). Then all we had to deal
with were mapping and updating the result records from one system to
the other. A translation table to map definitions between the old and
new LIMS (ie test names, component names, units, etc) was created so
the SQL script could figure out where to upload the result values and
metadata. The scripts produced a log file to capture error messages
and we had the ability to run one study at a a time and could run it
repeatedly until the log file contained no errors - very important to
find the incorrect or missing items in the translation table.
One advantage of taking this approach - validate the script
functionality once rather than needing to manually validate 100% of
results entered in the new system.
Along with all the other great points brought up in the other
messages, I hope this helps you make your decision. Good luck and let
us know the progress!
Lynn Schwartz
3M Drug Delivery Systems Division
Reply all
Reply to author
Forward
0 new messages