Statistical analysis of relational database: is it possible and how?

Question

I have been struggling with flat file databases and corresponding statistical packages for almost 20 years now (from Excel to SPSS, then Stata, and currently R).

However, I have always had to convert complex and multidimensional relational databases (eg in Access or MySQL) to often overly simplified flat sheet databases, which is at best time consuming (but often means reducing the amount of information available for each analysis).

Indeed, the approach I have always followed is the typical one of converting a relational database through specific queries into one or more flat file databases. While this is simple enough for most analyses, especially univariate and bivariate, it may become more confusing for multivariable and multivariate analyses, as it requires taking multiple and complex queries, and most importantly often oversimplifying the data themselves.

Now that I try to get more acquainted with big data and data science, I wonder whether the shift to big data will require also a shift to data analysis encompassing multiple tables and relations, without diluting the efficiency and power of a relational database when it is converted into multiple flat file databases.

So, my question is, simply: is it possible to directly perform complex (eg multivariable) analyses of relational databases? And if yes, how?

This is not a philosophical question (only). For instance, I am now working on a relatively large (reaching 2000 patients) observational study on transcatheter aortic valve implantation for severe aortic stenosis (RISPEVA). It is based on a MySQL electronic case report form which corresponds to 12 separate tables with complex relations and often multiple entries per each patient. My approach so far to try to identify predictors of long-term death (eg if looking for a score) has been, as usual, to create multiple tables through queries, and then distill the key features capable of predicting death. This means going through multiple stages of analysis and, at best, it is time consuming.

My fear is however that it might overlook one or more of the relational features of the data, and thus loosing precision or accuracy. Could it be done in a different fashion, directly analyzing the relational database as it stands?

Answer 1

My understanding of your question is that you are interested in methods to uncover multidimensional relationships in data yet are reluctant to take low-dimensional slices of the data for analysis. This is, in a sense, the basis of many machine learning algorithms that use data in high dimensions to make predictions or classifications with often very complex rules that are learned directly from the data.

There are classes of relational methods which perhaps fit more neatly into what you are thinking of, however. For example, the infinite relational model is a Bayesian nonparametric framework for identifying hidden structure across many dimensions in a way that appears to conceptually match what you want. For a sample problem that this might be used for, consider a relational database which contains 3 tables with 3 different primary keys and containing information on a set of cases $S$, a set of patients $P$ and a set of doctors $D$ that performed procedures during these cases. I offer this as a low-dimensional example but all of this can be scaled up to include more data.

Then, suppose that you have an indicator variable denoting whether or not the patient had a good outcome. As shown in the paper I linked, you could simultaneously find partitionings of each of $S$, $D$ and $P$ such that each partition cell contained similar outcomes. This learning is done by performing optimization of the likelihood of the data under a Bayesian model. This might inform you as to which doctors are good or bad, or whether certain patients are particularly troublesome for a given procedure. Again, this framework is flexible and affords a range of generative models for the underlying process.

This may be more complex than what you desired - it's a bit of a jump from Excel or SPSS to writing custom inference code in another programming language. Still, it's how I would approach this problem.

Answer 2

So, my question is, simply: is it possible to directly perform complex (eg multivariable) analyses of relational databases? And if yes, how?

I have a problem with this question. First, the role of a database is mainly efficient storage and retrieval for ETL (extract, transform, load), not statistical analysis. To ask what statistical method can be applied on the format of a database (or format of data) ignores the statistical question (see @Glen_b's comments).

If you received an Access file, or relational database extract, it's format was probably put in place by a software engineer who cared mostly about the efficiency storage and retrieval, not the analysis types of analysis. Now, depending on your statistical question/hypothesis this data will need to be transformed in one way or another. It should be noted that statistical models weren't constructed to be applied to data formats (e.g. text files, hdfs, relational databases, graph databases); that is not the concern of most statistical models.

My fear is however that it might overlook one or more of the relational features of the data, and thus loosing precision or accuracy. Could it be done in a different fashion, directly analyzing the relational database as it stands?

This is vague. What is meant by "relational features"? Are you referring to relational tables?

Let's assume you have a demographics table with patient id and other stuff. Now, let's assume you have your repeated measures for your patients by patient id. Then if you know enough SQL, these "relation features" can be combined using a simple JOIN (left, right, inner, ).

This is how you would combine your relational tables (which I assume is what you mean by "relational features"). Often, this can be challenging especially if you have a database with 100+ tables/views. But even then, you shouldn't need all those tables; if you have a well defined question, you should be able to narrow the problem down to 2-5 tables.

Now after merging your data, it may occur that the nature of your data is clustered by, say, patient id. For a scenario like this, a hierarchical model or mixed effects would be appropriate. This approach will then capture more of that context that certain observations occur for specific individuals. Reference: http://www.ats.ucla.edu/stat/mult_pkg/glmm.htm

In summary, a physical format shouldn't sway the type of analysis you conduct. As an analyst, it is your responsibility to account that the format of the data is appropriate for the analysis. For this reason there are tools for transforming your data so that it can capture the necessary context for your statistical analysis. Reference: http://vita.had.co.nz/papers/tidy-data.pdf