Denormalizing data

Denormalization of data is needed when a statistical procedure requires that the information to be analyzed must be on the same observation. Procedures in SAS that perform data modeling are often the ones that require denormalized data, as they require that the dependent variable be present on the same observation as the independent variables. For example, imagine that you are trying to determine a mathematical model that predicts under what conditions a therapy is successful. That model might look like this ... 

Here we see that we have a success variable that is dependent on a series of other variables. All of those data need to be present on a given observation in order for a statistical modeling procedure such as PROC LOGISTIC to be useful. The denormalized data set might look something like the following ... 

Do not denormalize data unless required by statistical procedures or if SAS BY processing of the data will meet your needs. Sometimes you can see just by looking at the reporting desired that denormalization is not required. For example, look at the following table requirement ... 

Typically, clinical data come to you in a shape that is dictated by the underlying CRF design and the clinical data management system. Most clinical data management systems use a relational data structure that is normalized and optimized for data management. Much of the time these normalized data are in a structure that is perfectly acceptable for analysis in SAS. However, sometimes the data need to be denormalized for proper analysis in SAS. 

A problem occurs when end users of the data cannot conceptualize how to handle normalized data. These users go out of their way to denormalize any normalized data that they see. I have seen entire databases denormalized so that a user could work with the data, and in some cases the user unknowingly renormalizes the data so that he or she can then analyze it properly. This type of user needs to be coached as to when denormalization is needed. 

In this table you can see that clinical success is summarized for each treatment by visit. The key here is by visit. If the data set to be summarized is simply sorted by visit, then PROC FREQ, PROC TABULATE, or some other procedure can be executed with a BY VISIT statement. If the data set were denormalized, then the task of producing the required summary would be more difficult. 

So for every clinical event of concern there is an event binomial flag and a time-to-event variable. Time-to-event data sets are typically represented in a flat denormalized single observation per subject data set. 

The data model is highly normalised, which suits data integrity and manipulation via the SQL language, but creates difficulties for the end-user in terms of accessing the data. Straightforward queries may involve numerous tables which have to be joined together. As a practical work-around, the database includes several very large denormalized tables. 

Fact Table. A central table in a data warehouse whose rows each represent one unit of primary importance in the warehouse. In a chemical warehouse, the rows of the fact table might correspond to unique structures in the database. In a biology data warehouse, each row might correspond to a single experiment. The fields in the fact table are mainly pointers to information stored in other tables, or they contain data that may be repeated in other tables but is stored in the fact table (i.e., denormal ized) for rapid access. The fact table connects to other "dimension" tables in the warehouse that contain specific information that is not duplicated. 

An object model-based query interface to ArrayExpress is being developed. The Web interfaces for predefined types of queries will be provided on top of the general query mechanism. For the database to be used for efficient data mining and interactive visualization, extensive optimization may be required, e.g., by tuning of table indexing or producing a denormalized database schema for some parts of the database. 

The BioMart data model is a simple modular schema composed of a central table, linked to its satellite tables by primary/foreign key relations. The schema can be normalized but typically includes denormalizations to achieve maximum query response optimizations. All the BioMart metadata is stored in XML configuration files on the database servers. The metadata files can be readily created and modified using MartEditor, a Java-based configuration editor. 

The tools for developing data warehouses can be grouped into three categories, based on their activities acquisition tools (for inflow), storage tools (for upflow and downflow), and access products (for outflow) (Mattison 1996). Acquisition tools are necessary to perform tasks such as modeling, designing, and populating data warehouses. These tools extract data from various sources (operational databases and external sources) and transform it (i.e., condition it, clean it up, and denormalize it) to make the data usable in the data warehouse. They also establish the metadata, where information about the data in the warehouse is stored (Francett 1994). 

---