LIMSzine
The following page contains information on:
Relational Databases and LIMS
Evaluating Third-Party Query and Report Software
Optimize Data Access and Use
Relational databases are becoming extremely popular as systems of centralized
data storage that enable LIMS to better manage laboratory information. Conventional
relational databases provide software tools to build an information system
configured to individual needs. A relational database that is tailored to
the lab enables the user to input data, define tables and records, retrieve
information, and further customize the system to meet specific requirements.
Popular relational databases include ORACLE, Ingres, Informix, Sybase, Supra,
and Rdb/VMS.
The relational database extends the LIMS' usefulness by providing the ability
to integrate laboratory information into corporate database strategies,
allowing easy search and retrieval of information from different areas of
the organization using a single query language, the industry standard SQL.
LIMS data is stored in these databases in native form, negating the need
for pipelines from proprietary filing systems. Use of SQL enables more robust
information interrogation.
Most LIMS nowadays are comprised of a number of layered and integrated software
modules with specific capabilities such as audit trails, statistical analysis,
and advanced query and reporting. The relational database is one of the
most significant of those modules and can determine the overall effectiveness
of the entire system.
Evaluating Third-Party Query and Report
Software
To get more out of their LIMS, many companies turn to specialized data tools
such as third-party query and report software. However there's often an
assumption that users understand Structured Query Language (SQL) in order
to use these tools, resulting in a need to understand syntactic knowledge
that chemists shouldn't have to know. To avoid this roadblock, there are
a number of key questions you should ask when evaluating query and reporting
tools in order to ensure that the tool you select can be implemented quickly
and used without much fuss.
The first step is to determine whether a feature is mandatory for your application
or not, then ask questions. The following questions zero in on capabilities
that focus on query and report functionality as well as the user-friendliness
quotient.
· Does the tool require maintaining a separate dictionary? Each database
has its own dictionary, and that adds a level of complexity. This is a major
factor if your data changes often and new tables are added or relationships
change. Redundant data dictionaries offer a means of tightly controlling
what queries can be created but they can also require a great deal of time
and effort to maintain.
· How does the tool perform joins? Find out whether joins can be
done automatically. Determine whether the tool allows contexts to be defined
when multiple join paths exist between tables (where there are several possible
paths to join two tables). Ask whether the user can pre-define the path
to be used for a specific query.
· Find out whether the tool allows backward and forward scrolling
within locally buffered rows or for the entire result set without re-executing
the request and reading the result set.
· Once the data is extracted what analysis tools can it be used with?
Lotus 1-2-3? dBASE? The SAS system or the RS series? What do you need it
to work with, and are the tools compatible? Develop a list of other applications,
including the type of LIMS systems, with which the tool is compatible.
· What kind of reports can be created and how many? Determine what
calculations can be performed, such as sine, cosine, and tangent, and conditional
expressions, such as if, then, and else. Can the tool perform aggregate
statistical calculations, command line execution, or matrix reporting? Many
relational databases store data in a row fashion but you may also want to
present information in a more compact or dense format involving values in
a matrix.
Once you've reviewed the specifications and features, ask to see lab tests.
Look for information in a format you might use. Ask to see typical three-column
tests and results, particularly in different ways than the data was entered.
Take a look at the presentation and display capabilities. How easy is it
to format data and customize selections? Find out what limits exist and
where, such as table/file size and record lengths, and whether query formulation
is done iteratively without reconstructing the initial query. If you're
interested in graphical user interface (GUI) functions, find out if the
tool has the ability to transfer data through dynamic data exchange (DDE)
and what software it works with. Finally, ask yourself if you think you'll
feel comfortable using this particular tool.
Other areas to focus on are administration; installation complexity; performance,
such as speed; and the query model, such as how broadly the SQL "SELECT"
statement is supported. While cost is always an issue, functionality should
be the focus of your review. Ease of use and database access should top
your list of requirements. If the tool doesn't do what you need, a great
price is meaningless.
Optimize Data Access and Use
One of the biggest problems with using a relational database is database
access and ease of use. A third-party product that addresses this issue
is SQLASSIST from Software Interfaces (Houston, TX). This software tool
provides an intuitive interface which enables users to build SQL queries,
view the query results, generate reports, and convert data to other formats
for analysis.
The query function shields users from SQL syntax and the need to know any
of the details about database design. Users need only select items of interest,
and the software builds the syntactically correct query. The query output
can be viewed before report generation or output conversion to confirm query
accuracy. In addition, a query analyzer function notifies users of inefficient
conditions before the query is executed.
Once the data is extracted, it can be converted for use with a range of
analysis tools such as Lotus 1-2-3, dBASE, the SAS system, and the RS series.
Conversions are direct; no intermediate files are created.
Besides building queries and viewing results, SQLASSIST creates graphs and
exports data to several PC file formats. Features include advanced scientific
calculations such as sine, cosine, and tangent; run-time search conditions;
conditional expressions such as if, then, and else; matrix reporting; aggregate
statistical calculations including mean, standard deviation and variance;
and command line execution. Users have complete control over report layout.
SQLASSIST can be integrated with other applications, including PE Nelson's
SQL*LIMS, BBN's Clintrial, and DLB Systems' RECORDER, MONITOR, and ALERT
applications. Novices can install the product and be running queries and
retrieving the information in minutes without any prior knowledge of SQL.
A client-server solution, SQLASSIST can access databases on any platform.
SQLASSIST is not particularly geared to the scientific market but to queries
and reports. This means that it can operate enterprise-wide, enabling one
department to send data to another department for seamless integration.