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:

  1. 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!

  2. Writing queries for nested query sources can be far simpler and easier to maintain than writing complex JOIN operations

  3. 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.

Codes

Frequency

CodePK

Code

1

ZG

2

GB

3

DC

4

PD

5

JE

6

FO

7

QT

...

...

5436

POE

FrequencyID

CodeID

Frequency

1

6

11

2

7

41

3

5

13

4

4

26

5

3

13

6

2

32

7

1

11

...

...

...

3456

5345

34

 

We want to create a data source connecting all these things for reporting using nesting. The process to accomplish this is:

  1. Create a new data source to be the Parent query. The parent query will likely depend on what your data looks like. For this example, we'll just use the Codes data.

  2. Give your data source a meaningful name, CodeFrequency will work for us.

  3. Create a Child Query by clicking the images/download/attachments/6047588/AddButton.PNG button in right configuration pane of your data source, also giving it a name. We will call this nested data NEST.

  4. In the Child query, reference one or more column values from the parent.

Nest Query Configuration
images/download/attachments/6047588/NestedQueries.png

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.