Data warehouse

Large chemical databases, combinatorial libraries, and data warehouses have become indispensable tools in modem chemical research. Accordingly, stmctural information must be stored in these databases and searched in an appropriate manner. 

Pharmaceutical companies are making significant investment to mine data, transfer old, nonstructured, often report-based data into central data warehouses for common access. The rapid change in IT infrastructure and technologies for data storage has created a very difficult environment where access to data can be cumbersome and requires enormous efforts and investment. 

Online analysis processing mainly comprises the interactive exploration of multidimensional data sets, or data cubes, which are manipulated by operations from matrix algebra, for example, slice-and-dice, roll-up, and drill-down. Computing performance is related to data warehouse size and also data quality, for example, missing data, unsharpness, and redundancy. The multidimensionality issue is critical for extracting pertinent information and selecting the results to be stored and visualized. 

Data from external and internal sources is integrated, aggregated, or associated in time series. Data items may contain errors or the data may be missing, unsharp, redundant, or contradictory. A language with operators and variables is required to establish models. Validity levels also have to be defined using suitable optimization and validation criteria. In addition, a search method is required to extract the data from the data warehouse and prepare it for analysis. 

Both the nucleic acid sequences and the protein sequences derived from the biological information are collected in most such databases. Large amounts of data in these databases need to be sorted, stored, retrieved, and analyzed. Selection of subsets of data for particular analysis should also be done. IT providers designed such a data warehouse and developed an interface that provides an important benefit to researchers by making it easy to access the existing information and also to submit new entries (i.e., datamining) (Table 5.6). Middlewares and structured query language (SQL) softwares were developed for this purposes. The former one is used... 

Sagynaliev E, Steinert R, Nestler G, Lippert H, Knoch M, Reymond MA. Web-based data warehouse on gene expression in human colorectal cancer. Proteomics 2005 5(12) 3066-3078. 

The hardware and software IQ processes may be reasonably straightforward if the physical system is intended to be located on the same site and only used by that site. However, the introduction of centrally managed systems, shared service operations, and data warehouses as a means of lowering costs and providing a more streamlined infrastructure within an organization may make the overall validation activity for the site more complex. 

Physicochemical properties that are based solely on the structure are stored in the data warehouse, but properties that are data-source dependent, such as citation or biological activity, are only referenced. A typical use of a chemical warehouse is to search for a set of... 

Figure 9.16. Star schema design of a chemical data warehouse. The central source table allows access to the Extemal-IDof every molecule, arranged by source database. These External-ID values can be used to build multidimensional views of the data. For example, to see all the reactions with products that can be found in source database ACD, one would combine data from the source dictionary table (Source ID for database ACD), the reactions table (StructJD, and Role), and moltable (Struct ID) table, using identifiers (Extemal-ID)from the central source table.

For certain purposes, like reagent selection, a data warehouse is too large and comprehen-... 

Cleaning and Transforming Data. When importing data from diverse data sources (files, databases, spreadsheets, LIMS systems, etc.) into a database or data warehouse, the 4 ta usually needs to be standardized, checked, and sometimes transformed to some common format and content. This allows faster search and retrieval, and serves as a check of data integrity. The rules that define the cleaning/trans-formation process are often termed "business rules," and in the case of chemical data, they may include checking and modification of chemical structures. 

Concordance. A data warehouse architecture used in MDL relational chemical and reaction databases. The central "fact" table of a. concordance has a record for each unique structure in the database, with pointers to the instances of the structure in various "source" databases. 

Daemon. From Unix, a program that runs continually as a background process to perform routine functions on demand or on a schedule. In the context of a chemical data warehouse, an example would be a registration program that periodically checks input databases to see if there are any new structures that need to be added to the warehouse. If there are, the daemon extracts the structures from the source databases, transforms and "cleans" them if needed, and registers them to the warehouse. 

---