How to bring BI and analytics to modern nested data structures
Over the past few years, there has been a subtle but significant shift in the way that data is structured in databases. Whereas yesterday’s databases were typically limited to storing data in rows and tables, today’s modern databases often make use of nested data structures.
In this article, we will take a deeper dive into the nature of nested data structures, how they are represented in different databases, and the benefits and challenges of using nested data structures. Finally, we’ll propose an approach that addresses the challenge of marrying the traditional world of business intelligence with the modern world of nested data.
Let’s start with a little introduction to dimensional modeling, using a website visit as an example. There are measures of the visit that exist at the visit level, such as the number of visits and the length of the visit. There are also attributes of the visit that only exist at the visit level, such as the user’s IP address, browser type, and OS. There are also page views that occur as part of each visit, each with their own measures, for example the number of page views and the time on page. And there are page view specific attributes, such as page name, page category, and page URL.
In the traditional world of data mart or data warehouse design, a common approach to creating a model to support the analysis of this web data might be to create something that looks like the following (simplified) data model.
This type of “dimensional” modeling addresses a few challenges that occur when building models for business intelligence. First, it reduces the number of rows containing “duplicate” data: The visit fact doesn’t need to contain all of the page level details, meaning queries against the visit fact will perform better. Second, a dimensional model allows the use of a single key column (for example Browser_OS_Key) in the fact instead of multiple columns for each detail around browser and OS (versions, device, etc). This reduces the storage cost associated with the model. Third, this type of model can also improve query performance, especially for commonly used values (list of browsers, page categories, etc).
However, there are also challenges with this type of model. First, this approach is at odds with the “natural” form of web log data (and with machine and log data in general). Typically this type of data may be written to disk in the manner at which it occurs. For example, website logs may contain a record that looks like:
The process of turning this data into the dimensional model shown above can be expensive and time consuming. Additionally, the traditional dimensional data approach can lead to expensive queries, especially when those queries require joins in a distributed computing environment. Large joins—for example, joining billions of page view records with millions of user records—can be time consuming, and will perform worse than simple filter, scan, and aggregation operations on a single table.
The past decade has seen several market shifts that have resulted in the emergence of a new approach to dealing with the data types and challenges described above. Specifically:
As a result of the factors listed above, a new approach to storing and querying log data and machine-generated data in relational databases has emerged. While different databases have different implementations of this new approach, the general concept is the same, which is to support a single table where information is “nested” within records. Using the same example above, a nested model for a web session might look like the following conceptual diagram.
