Merging results from different Explores
Explores in Centricity are designed by your Centricity developers to combine the data from your database tables in the best way, using defined relationships between data fields and tables. Because of this, it is best to use a single Explore to examine your data.
However, there may be times when your Centricity developers haven’t created the relationships you need, or they faced technical limitations. In these cases, Centricity lets you combine data from different Explores (even from different models or projects) to create data tables and visualizations.
Using the Merged Results feature, you can create a query from an Explore and then add queries from other Explores to display the merged results in a single table. From there, you can examine the data, pivot fields, and create visualizations.
The Merged Results feature has a limit of 5,000 rows of data for each of the merged queries. If you include queries that return more than 5,000 rows of data, only the first 5,000 rows returned are included in the merged results.
Understanding merged results
When you merge queries, you start out by creating a single query from a single Explore, and then you combine other queries with that first query.
By default, that first query is considered the primary query. This is an important concept because when Centricity matches the data to create the merged results, it matches each added query to the primary query (not to any other added query). So, whenever you add a query, you need to include a dimension that can be matched to a dimension in the primary query.
All of the primary query’s fields are displayed in the merged results, using the primary query’s names for the fields. This means that if the primary query and an added query use different names for a matching dimension, only the primary query’s dimension name will be displayed in the results.
Merged results do not perform an actual SQL join. But, for those who are familiar with SQL joins, the Merged Results feature combines the results of multiple Explores in a similar way as would a left join. The results of the added query are combined with the results of the primary query as if they are being left joined into the primary query.
If you aren’t familiar with the idea of a left join, no worries. In practical terms, here’s why it matters which query is the primary query:
How field names appear: For matching fields, the primary query’s field names are used in the merged results, as shown above.
How merged results handle a query without a matching value: The next section below discusses how Centricity handles merging data when only some of the queries have specific value(s) in the matching dimension(s).
How merged results handle a query having multiple matching values: The last section on this page discusses how Centricity handles merging data when some of the queries have multiple rows with a specific value (or combination of values) in the matching dimension(s).
What if one query doesn’t have a matching data value?
Another reason the primary query is important is because of the way null values are handled in the matched dimensions:
If a row exists in the primary query but not in the additional query, then the added query’s fields will be NULL for that row.
For example, if the primary query has a row for Maternity, the merged results show this row. If the added query does not have a Maternity row, any of the fields from the added query will show NULL for Maternity.
If a row exists in the added query but not in the primary query, then the row will not show in the results at all.
For example, if the added query has two rows for Jeans, but the primary query does not, this row is not shown in the merged results at all.
If we switch the primary query to make the added query as the new primary query, we get different merged results. In this case, Jeans would be displayed in applicable rows, but Maternity would be excluded because it does not exist in our new primary query.
What if one query has multiple rows for the same value?
Finally, designating the desired primary query is also important because of the way multiple rows with matching values are handled. If the added query has two or more rows with values that match a row in the primary query, the primary query row will be duplicated that number of times.
For example, take an added query that has two rows for Dresses. In the merged results, the Dresses values from the primary query appear twice, once for each of the Dresses rows from the added query. This is because when the added query has multiple rows that match a row in the primary query, the primary query rows will be duplicated in the merged results.
Note that if you switch the primary query in this case, you would still have two Dresses rows, since the newly designated primary query has two rows for Dresses. The takeaway is that, when you merge queries, the results may have more rows than the primary query has — but there will be fewer rows.