[FR] Composite Data Source

[nextchamp-saqib]

Problem:
Users who have multiple erpnext sites with the same doctypes & fields need to have a consolidated view of data across all sites. Currently, they have to connect to each site and create individual reports. This still doesn't give them a consolidated view of data. They need to store the query results using Query Store and then create a report. This is a tedious process.

Solution:
Create a checkbox in Insights Data Source called Is Composite which when checked will allow the user to select multiple data sources (already connected) that have same database type and same table & column structure.

For eg.

1. Let's say we have 3 sites, abc.erpnxt.com, xyz.erpnext.com and pqr.erpnext.com and all of them have the same doctypes & fields.
2. We will connect all 3 sites to Insights by adding 3 data sources of type MariaDB with site credentials.
3. We will create a new data source called Consolidated Accounts and check the Is Composite checkbox.
4. We will select all 3 data sources in the Data Sources table.
5. We can optionally select the common tables from each data source. In this case, we will add only one table, GL Entry, so only one Insights Table GL Entry will be created with only the common columns from all 3 sites.
6. Now, when the Consolidated Accounts data source is selected in a new query, the generated SQL will be the same as if we had selected one of the underlying data sources.
7. While executing the query of a composite data source, the query will be parallelly executed on all the underlying data sources and the results will be merged and returned to the user. Optionally, the site name can be added as a column in the result set.