The Data Model diagram helps you to quickly and easily define data sets, break groups, and totals for a report based on multiple data sets.
If you do not link the data sets (or queries), the data engine produces a multipart unrelated query data set.
For example, in the data model, image shown below, one query selects products and another selects customers. There is no relationship between the products and customers.
The result is shown in the data structure as depicted in the following image.
It is possible that the data fetched for one part of the data set or query is determined by the data fetched for another part. The result is often called a master/detail, or parent/child relationship that is defined with a data link between two data sets or queries.
When you run a master/detail data model, each row of the master (or parent) query executes a query against the detail (or child) to retrieve only matching rows.
In the example, image below, two data sets are linked by the element Customer ID. The Orders data set a child of the Customers data set.
The example produces the data structure shown in the following image.
Certain guidelines are recommended for building data models.
Reduce the number of data sets or queries in your data model as much as possible. In general, the fewer data sets and queries you have, the faster your data model will run. While multi-query data models are often easier to understand, single-query data models tend to execute more quickly. It is important to understand that in parent-child queries, for every parent, the child query is executed.
You should only use multi-query data models in the following scenarios:
To perform functions that the query type, such as a SQL query, does not support directly.
To support complex views, for example, distributed queries or GROUP BY queries.
To simulate a view when you do not have or want to use a view.