Nested Queries
Query Nesting turns complex data relationships into easily reportable structures and will undoubtedly be one of the most useful Reporting v3 features for. So what is a Nested Data Source? The simple definition is that a Nested Query uses the results of a previously executed query to collect data. The general structure of a Nested Data Source is one in which you have a Parent queries and child queries. Those well-versed in SQL are probably thinking that this sounds like a JOIN and in fact, there are some similarities. There are also some major differences which allow Nested Data to be both easier and more powerful to accomplish:
-
Nesting relationships are not restricted to data in a single schema, database or even source! Nesting is easy to configure across tables, between different databases, or even with sources like the Tag Historian!
-
Writing queries for nested query sources can be far simpler and easier to maintain than writing complex JOIN operations
-
Nested structures allow more control in how data is collected, allowing data structures and relationships that are more expressive
How Nesting Works
Let's use a simple data relationship to help illustrate how nesting occurs.
Imagine we have data collected from two unrelated sources that look the ones seen in this table.
We want to create a data source connecting all these things for reporting using nesting. The process to accomplish this is:
|
Nest Query Configuration
|
Example of what these queries could look like:
Special Considerations
Nested Queries are powerful and easy to use, but users should be aware of runtime implications. Imagine the scenario above, where we have two sets of data, each with 5000+ rows. When our child query executes, each row of its query is going to require a lookup from the parent. For most common sets of data and database sizes, this won't be an issue, but it's possible to imagine that instead of just one child query, we have a dozen. In addition, some of those children also have many children. It's very easy to see in this scenario how exponential growth occurs and our system performance may suffer. Most report designers will limit query sizing as oversized data structures are simply not as easy to work with. However, if you feel an urge to generate massive complex trees of million line queries, you may be waiting a while.