What kind of data mining can I perform on my ASTRA data?
Introduction
After collecting many SEC-MALS or FFF-MALS experiments in
ASTRA®, whether it be over several days, weeks, or even years, you may find yourself needing to locate a specific experiment without searching exhaustively by date, by folder, or by trying to remember the file name. You may want to view trends or troubleshoot. Maybe you want to determine if your MALS calibration constant has been drifting over time. You might also decide to compare the radius and molar mass of every collection in the last month, to review run-to-run variation.
ASTRA provides a way to mine data through the Security Pack option which uses a Microsoft SQL Server database to store data and to keep it secure. By taking advantage of the powerful features provided by a database, you can write very specific, complex queries to answer virtually any question about a large collection of data. ASTRA experiments previously collected outside of Security Pack can be imported in bulk so that your entire data history is available for mining.
The results here using this data mining feature to monitor MALS calibration constants, sorted by instrument and collection time. Specifically, it shows the timestamp, file name, and operator of all calibrations run. The actual query is shown in the next section.
Quick start instructions to connect to and query the ASTRA database
Step 1: To query the SQL Server database, launch SQL Server Management Studio (SSMS). This should be installed on the computer hosting the database. Note, it’s possible that ASTRA is installed on a separate computer from the one hosting the database. If this is the case, you will need to request assistance from your IT department or ask them to run a query of interest for you. If SQL Server Management Studio is installed, you should see the icon in the Windows Start menu.
Note: The user that launches SQL Server Management Studio should be the same user that installed it, or have administrator permissions on the instance, in order to connect to the database.
Caution: An administrator has write permission to the database and may potentially modify the data. If any data are modified outside of ASTRA, the database will be marked as ‘corrupted’, per ASTRA’s security policy. If only queries are performed, there is no risk of ‘corrupting’ the database. To prevent ‘corruption’, use only SELECT statements, i.e., commands that begin with the word SELECT, which are by nature read-only. It is strongly encouraged to make a backup copy of the database before querying. To create a backup, right-click on the database in the Object Explorer, and under Tasks, choose Back up.
Step 2: After opening SQL Server Management Studio, connect to the SQL Server instance. This is usually named < computer >\< Server Instance >. ASTRA installations typically use Windows authentication so no user name or password should be required, as long as the logged-in Windows user is an administrator for this SQL Server instance.
Step 3: Click the New Query button at the top of the window to begin typing the query. Some examples of queries are detailed in the next section. Then click Execute, or hit F5, to run the query.
Example SQL queries: Example 1
Following are some common examples that demonstrate the structure and utility of SQL queries. To execute any of these queries, or others described in Querying the SQL Database in ASTRA Security Pack, replace “Astra data” with the name of your database in the line use [Astra data].
Example 1: Find all calibrations performedIf you want to track how often the instrument has been calibrated, or how much the calibration constant drifts, or who was the user that performed the calibration, here is a starting point to answer any of those questions. This query can be copied directly into SSMS.
use [Astra data]
SELECT
fe.timeCollected, fe.directory, fe.fileName
, c.m_sCollectionOperatorName
, m_dValue
, i.m_sComputerName
FROM WResultData rd
join WFileEntry fe on rd.groupID = fe.m_gEntryGroupID
join WScriptCollectionProcedure c on c.groupID = fe.m_gEntryGroupID
join WInstrumentDescriptor i on i.groupID = fe.m_gEntryGroupID
join WExternalQuerySupport eqs on eqs.id = rd.m_nDataName
where
eqs.label = 'calibration constant'
order by
i.m_sComputerName, fe.timeCollected
Example 2
The WResultData table contains all results that you see in EASI table. You can report any combination of those results in a single query. Here, the query asks which experiments have a z-average radius between 10 and 100 nm, with a polydispersity < 1.2. This query can be copied directly into SSMS.
use [Astra data]
SELECT
fe.directory, fe.fileName
, rd1.m_nPeak as "peak"
, e1.label as "rz"
, rd1.m_dValue
, e2.label as "Pd"
, rd2.m_dValue
FROM
WFileEntry fe
, WResultData rd1, WResultData rd2
, WExternalQuerySupport e1, WExternalQuerySupport e2
where fe.m_gEntryGroupID = rd1.groupID
and fe.m_gEntryGroupID = rd2.groupID
and e1.id = rd1.m_nDataName
and e2.id = rd2.m_nDataName
and rd1.m_nPeak = rd2.m_nPeak
and (e1.label = 'rz' AND rd1.m_dValue > 10 AND rd1.m_dValue < 100)
and (e2.label = 'Mw/Mn' and rd2.m_dValue < 1.2)
AND rd1.m_bUnableToCalculate = 0
and rd2.m_bUnableToCalculate = 0
order by fe.fileName, rd2.m_nPeak
Writing your own queries
Here are two primary resources to help you get started taking advantage of this feature and writing your own queries:
- The ASTRA User’s Guide (M1006) – A summary of the database structure, and the most important tables and columns, is in Appendix D: Querying an Experiment Database. The User Guide installs with ASTRA, in the Program Files folder. For example, if you are using ASTRA version 7.3.2, you will find it in "C:\Program Files\WTC\ASTRA 7.3.2\ASTRA 7.3 User's Guide (M1006 Rev I).pdf".
- Querying the SQL Database in ASTRA Security Pack (TN1020) – This technical note offers a comprehensive collection of queries and it explains the basics of how to search columns and join tables. It can be found on the Wyatt Support Center.
Do you have a question? Contact our experts here in Customer Support. We’re happy to help! Call +1 (805) 681-9009 option 4 or email support@wyatt.com.