Connecting NWA Quality Analyst software to Agilent OpenLAB ECM
Contents
1. B clients hosting a copy of NWA Quality Analyst You may distribute the ODBC connection details in a file then If you want to limit the database access to specific users you may set up the data source under the User DSN tab In the Create New Data Source window select SQL Server or Microsoft ODBC for Oracle dependent on the DB management system hosting the OpenLAB ECM database Set a name and description of your choice Select or enter the OpenLAB ECM database server name Select SOL server authentication and enter ACAMLVIEW user and it s password The ACAMLVIEW user password was set during the OpenLAB ECM Intelligent Reporter installation Select a driver for which you want to set up a data source Microsoft Paradox Driver db Microsoft Paradox Treiber db Microsoft Text Driver bc csv Microsoft Text Treiber b csv Microsoft Visual FoxPro Driver Figure 1 Data source type selection This wizard will help you create an ODBC data source that you can use to connect to SQL Server What name do you want to use to refer to the data source Name VMLI7 How do you want to describe the data source Description OpenLAB ECM Which SQL Server do you want to connect to Server vMLI7 X rar ets con tee Figure 2 Enter OQpenLAB ECM database server name How should SQL Server verify the authenticity of the login ID With Windows NT authentication using the network login ID G With2. Introduction Quality assurance and quality improvement are Important elements of ISO 9000 Quality management systems Statistical Process Control SPC Process capability and performance analysis and regression analysis are important statistical techniques that help you achieve planned results and continually improve your production and lab processes Agilent OpenLAB ECM Intelligent Reporter uses a result database that stores analytical quantitation results generated by Agilent OpenLAB CDS or Waters Empower across multiple labs and across time ranges up to several years The Agilent Report Template Editor or SOL server Report Builder allows you to generate method instrument and or compound specific result charts Microsoft SOL server Reporting Services technology refreshes and publishes web based reports in an unattended mode Connecting NWA Quality Analyst software to Agilent OpenLAB ECM Intelligent Reporter Technical Note Abstract Statistical Process Control SPC is a well established element of ISO 9001 certified quality management systems Northwest Analytics NWA Quality Analyst software offers advanced Statistical functions to analyze chromatography data system CDS result data stored in Agilent OpenLAB ECM This note describes how to setup an integration of NWA Quality Analyst software with OpenLAB ECM and OpenLAB ECM Intelligent Reporter When developing new processes or improving or troubleshooting
3. SQL Server authentication using a login ID and password entered by the user To change the network library used to communicate with SQL Server click Client Configuration iv Connect to SQL Server to obtain default settings for the Login ID ACAMLVIEW Password seeeveees cone eT cet e Figure 3 Enter user name and password 2 6 If not already set change the default database to the OpenLAB ECM DB instance name IV Change the default database to Jecm341 Attach database filename ooo o F Create temporary stored procedures for prepared SOL statements and drop the stored procedures f Only when you disconnect When you disconnect and as appropriate while you are connected J Use ANSI quoted identifiers J Use ANSI nulls paddings and wamings Use the failover SOL Server if the primary SOL Server is not available Next gt Cancel Help Figure 4 Select the OpenLAB ECM database instance 7 Do not use strong data encryption Save long running queries to a log Tile for troubleshooting purposes C Change the language of SQL Server system messages to Default z Use strong encryption for data IV Perform translation for character data m Use regional settings when outputting cumency numbers dates and times J Save long running queries to the log file C Users t AppData Local Temp QUERY LC Browse Long query time milliseconds 20000 J7 Log ODBC driver sta
4. _TRIGGERS ACAML DD_VIEWS ACAML ECMLEGACYPROJECT ACAML INJECTIONCOMPOLIN ACAML INJECTIONISTDAMO ACAML INJECTIONS Policies New View Select Top 1000 Rows Edit Top 200 Rows Script View as b view Dependencies Full Text index DEAA AA AAAA AAAA AAA z f execute gt om v 39 celal e G3 63 SQLQuery11 sql olservice 78 ALTER To DROP To DROP And CREATE To CREATE To SQLQuery10 sql olservice 63 2 New Query Editor Window File E dipboard E gent Job L Script L Help Schema View schema permissions Users or roles A ACAML_VIEWER Permissions for ACAML_VIEWER Explicit Effective Permission Alter Control Delete Insert Server WMLI Grantor Connection AGILENT Solservice View connection properties References Select Take ownership Update Grant ad With Grant i i nlolajolololo ACAML View name 40_PEAK_RE SULTS NWA Database role Column Permissions OOVOOVO OO References iF International Organization for Standardization ISO 9000 2005 Quality management systems Fundamentals and vocabulary International Organization for Standardization ISO TR 10017 2003 Guidance on statistical techniques for ISO 9001 2000 Agilent Technologies Statistical Process Analysis with NWA Quality Analyst software hosted by Agilent OpenLAB ECM Intelligent Reporter 2014 P N 5991 5440EN www agilen
5. and in a meaningful order e g instrument name sequence name sample name calibration level injection time modification time data analysis method name and time stamp compound name compound amount peak area signal name file name of the result set ECM location ECM file version You may also change or translate the column name in the name column to match your lab s terminology Add a key column to define the sorting order of the result table and data items in the control chart plot The INJECTION _ ACQUIREDDATE should be used for chronological sorting Hinks Selection ee i a a 1 INSTRUMENT INSTRUMENT_NAME Connection apec 2 SEQUENCE_NA amp ME S amp MPLE_NAME Data Source vmii af SAMPLE_CALIBRATIONLEVEL Ih NJECTION_ACGUIREDDATE Table ACAML COMPOUNDSBYPEAKS Denormaiize 5 NJECTION_LASTMODIFIEDDATE C SOL NJECTION_DAMETHODNAME i Copy 8 METHOD CHANGED y INJECT ON_DAMETHODMODDATE 9 COMPOUND MPOUND_NAME 10 AMOUNT APOUND_AMOUNT Options m Database Connection and Field List Help gt Connection ODBC Data Source ymli Table ACAML AO_PEAK_RESULTS_NWA Fields SEQUENCE ID Extermal Database Fields Tables Available Fields m Key Columns INJ_TIME D External Assign Assign All SEQUENCE_ACQUIREDDATE SEQUENCE_ACQUIREDEY NAME SEQUENCE_LASTMODIFIEDDATE T Add R
6. arting commands in the Run file look in the current Table 2 shows an example of a very useful directory which has been changed to the new and simple 5 line script temporary directory contains a list of all available command and various examples of their use SetCurrentDir E Program Files QA The SetCurrentDir Run file command lets you specify a file path that the Run File Interpreter will use as the current directory with file names that do not have paths specified This local path needs to host the NWH required for the database connection If the RUN file should be accessible from multiple ECM clients this path needs to be used on all clients and it needs to host the latest versions of the NWH files CONNECT XSRMDA_Tramadol DAT The CONNECT command establishes a connection to an external database Although the database connection is stored in the NWH file you can use the connect command only with DAT file extension XRS XSRMDA_Tramadol DAT AMOUNT X R The XRS command creates a Graphics file G SBREAKDOWN SAMPLE containing an X bar X bar and Range or X bar and Standard Deviation chart For the ease of use this part of the script should be generated using the Run File Wizard CHART The CHART command displays an existing Graphics file on the screen Appendix A Creating custom views in SQL server In cases where certain data items do not exist in the built in views or where the views are too large and complicated for e
7. emove SEQUENCE_LASTMODIFIEDBY SEQUENCE_ACQUIREDBY IT No Key Columns Figure 7 Query definition in NWA Quality Analyst Figure 8 Data mapping Query conditions Definition of external database filters Switch to the External Database Filters tab shown in figure 9 to define a meaningful filter You may also filter the data at a later point in time but for optimized query performance and automated execution the external filter WHERE condition should be as specific and narrow as possible Table 1 shows recommended filter conditions After defining the external database filter press OK to execute the query The query returns a view similar to a sequence table of your CDS system including results the results of the compounds specified in the external database filter Go to Edit gt Database Connectivity Definition to do further fine tuning of the filter Once you are satisfied with result selection SAVE your result in a Quality Analyst Data File DAT file During the save process you are asked to save the Database Connectivity Definition with the file The connection details query and filter are stored along with the DAT file in a NWH file By simply double clicking this file you can open the application connect to the ECM database and load the most recent data of interest nef adla Extemal Database Field a 1 INSTRUMENT 2 DA METHOD Ma
8. emove all the carriage returns Selecting an existing table or view is much simpler however the Quality Analyst application cannot host more than 255 table columns The built in ACAML COMPOUNDSBYPEAKS view of the ECM Intelligent Reporter database contains the typical result fields to generate instrument method sample and compound specific control charts however it contains more than 255 columns All columns gt 255 are cut off and do not get imported Amongst others the following table columns are cut off and they could be considered to be interesting for SPC charts or data filtering PEAK WIDTH 5PERC PEAK PLATESPERMETER PEAK SIGNALTONOISE PEAK ASYMMETRY_1 PEAK PURITY SIGNAL_DESCRIPTION FILE_NAME FILE LOCATION FILE_VERSION FILE_ISCURRENT For these reasons the ease of use and for improved performance it is recommended to create a customized database view as described in Appendix A This view may contain only data Items required for SPC charting and data for sample method instrument file and or user identification Data mapping In the Database Connectivity Definition window shown in figure 8 you need to define the mapping between the columns of the DB view and the columns of the Quality Analyst result file You can quickly generate a 1 1 mapping by pressing the Assign All button However for better readability of the Quality Analyst result table it is recommended to assign only a subset one by one
9. existing processes you need to handle your data in a more interactive way and apply various statistical methods to find the most applicable one By integrating OpenLAB ECM Intelligent Reporter with NWA Quality Analyst you can quickly analyze data sets for meaningful insights This note describes how to connect NWA Quality Analyst with Agilent OpenLAB ECM Intelligent Reporter Some basic database administrator knowledge is required to execute these steps Examples reports are described in application note Statistical Process Analysis with NWA Quality Analyst software hosted by Agilent OpenLAB ECM Intelligent Reporter QA A 4 NWA QUALITY ANALYST Software requirements Visit http www nwasott com products nwa quality analyst to request a free trail copy of NWA Quality Analyst Data backend Agilent OpenLAB ECM 3 4 1 with Agilent OpenLAB ECM Intelligent Reporter A 02 0x Supported CDS systems Agilent OpenLAB CDS rev A 01 03 or higher Waters Empower 2 or 3 Agg Agilent Technologies Setup a ODBC data source NWA Quality Analyst can connect to the Agilent OpenLAB ECM database using an ODBC data source The following steps describe how to setup an ODBC data source on client PCs iF 4 Start the ODBC Data Source Administrator Control Panel gt All Control Panel Items gt Administrative Tools gt Data Sources ODBC Under the System DSN tab press Add Select File DSN if you work with multiple OpenLA
10. nts the name should be specified If your control samples are following a fixed name a pattern you could use it for filtering In case of multi signal methods the signal name field would filter for a single signal Since OpenLAB ECM Intelligent Reporter supports various CDS applications with different terminologies the sample type field is a numeric value where e g 1 represents calibration runs Consult the on line help of the Report Template Editor for enumerations Using the run file wizard to Table 2 Example script for automated result query and chart generation automate the chart generation Command Comment USETEMPDIR ON USETEMPDIR is intended to be used when more than one Quality Analyst user might access the Quality Analyst software comes along with a Run File Wizard helping users to write scripts for automated chart generation same Connectivity Data Set stored in a shared lt offers a full series of functions to start location This command makes sure that all such statistical analysis and chart generation users are working with their own local copies of Some basic file operations are not part of Une adausi Gi the wizard but need be added in a text When USETEMPDIR is ON the CONNECT editor The Quality Analyst User s manual command copies the Header file from the old current directory into the new temporary directory then retrieves data from the database into a Data file in the temporary directory All ch
11. t com chem openlab For research use only Not for use in diagnostic procedures This information is subject to change without notice Agilent Technologies Inc 2015 Published in USA January 23 2015 Publication Number 5991 5439EN whee Agilent Technologies
12. tch INJ TIME COMPOUND C Like INSTRUMENT New During the last Remove During the last Figure 9 Defining a filter condition Table 1 Useful filter parameters Filter expression FILE_ISCURRENT match 1 INEJCTION_ACQUIREDDATE during the last X months days during the last lt ask gt months days from to INJECTION_DAMETHODNAME like match lt method gt COMPOUND_NAME like match lt compound gt INSTRUMENT_NAME like match lt instrument gt SAMPLE_NAME like match lt sample gt SIGNAL_NAME match DAD 1B SAMPLE_TYPE match X ida INJECTION _DAMETHODNAME INJECTION_DAMETHODMODDATE month s day s Add OK INJ_TIME D Remove T No Key Columns Description This filter ensures that only results from the latest ECM file versions are retrieved This filter condition is essential for query performance and limiting the size of the result set If you choose lt ask gt as a filter condition the user is prompted to enter a time range each time the query is run The name of the data analysis method should be used as rough filter for comparing compound amounts of your interest If your method contains multiple compounds you shrink the result set down to a single compound The breakdown by categories function can be used in case you want to generate the same type of chart for all your compounds Unless you like to compare the results acquired on multiple instrume
13. tistics to the log file C Users App Data Local Temp STATS LO Browse ae a coes J o Figure 5 Set logging options 8 Review the settings and run the ODBC DB connection test Anew ODEC data source will be created with the following configuration Microsoft SQL Server ODBC Driver Version 06 01 7601 Figure 6 ODBC test result Connecting Quality Analyst software to OpenLAB ECM Intelligent Reporter The following steps require the presence of a configured ODBC data source Creation of a new data set Next steps need to be executed only one time Once you have a good example query you could use it as a template for other queries Start Quality Analyst and select Create a new Data Set e Select New External Database e Choose Generic External database In the External Database Setup window shown in figure select ODBC as connection type and select the ODBC data source you have created according to the previous section Enter the password for user ACAMLVIEW when prompted This password has been at the time of the OpenLAB ECM Intelligent Reporter installation Data query For selecting the data to be imported you can either choose an existing table or view or you could enter a free SOL query The free SOL query window is sensitive in respect to the query syntax and format If you develop a suitable query in e g SOL server management studio you cannot simply copy paste the query syntax but you need to r
14. xternal applications to connect to the existing views can be customized in the Microsoft SOL Server Management Studio 1 Start the Microsoft SOL Server Management Studio and connect to the ECM database as user SA 2 Select a view to be used as a template for the new view and script it to a file 3 Open the sql file in Microsoft SOL server Management Studio 4 Search the line CREATE VIEW ACAML lt current view name gt and replace lt current view name gt with the name of the new view Note All names of customized views should start with AO so you they can easily identified as custom views later on 5 Delete all parameters from the select Statement you are not interested in 6 Save the SQL file 7 Execute the CREATE VIEW script 8 Refresh the tree view of the Object Explorer 9 Right click the new view in the Object explorer tree view open the Properties dialog and grant SELECT permissions to the ACAML_ Viewer role B Microsoft SQL Server Management Studio File Edit View Query Project Debug Tools Window Community Help D new Query Oy D D So Ds OF id S ag fa ecm341 Object Explorer rx Comnet Ma Y dbo INst_Inj_Files E Tables E Views E System Views El acam ACAML ACAML COMPOUNDSANDPEAKS ACAML COMPOUNDSBYINJECTIONS ACAML COMPOUNDSBYINSTRUMENTS ACAML COMPOUNDSBYPEAKS ACAML DD_CONSTRAINTS ACAML DD_INDEXES Design ACAML DD_PROCEDURES ACAML DD_S NONYMS ACAML DD_TABLES ACAML DD
Download Pdf Manuals
Related Search