Documentation

Creation With Multiple Data Sources

Note: Your data sources need to be stored in our data warehouse and have to be joined in the settings section using foreign keys to be able to cross-query multiple data sources at once in datapine.

 

Creating a chart using multiple data sources is as simple as creating a chart from just one data source in datapine. You just need to make sure that all data sources are located in our data warehouse and that you’ve joined the data sources in the settings section using foreign keys before you start creating a chart. For more information please click here to learn how to join your data sources or click here to learn how to switch the storage location of your database to our data warehouse.

 

Creating a SQL Chart with multiple data sources

 

If you wish to use SQL Codes to cross-query your data, the addressing fields from different data sources need to be identified using curly brackets in front of each table name.

 

In the following example we will join a table from a sales database with a spreadsheet containing the exchange rates for foreign currencies to calculate our revenue per product in our domestic currency.

 

 

SELECT a.`Product` as ‘Product’, a.`Currency` as ‘Currency’, sum(a.`Revenue`)*b.`Rate` as ‘Revenue’

FROM {SalesDB}`Sales` a

JOIN {Currencies}`ExchangeRates` b

ON a.`Currency = b.`Currency`

GROUP BY a.`Product`, a.`Currency`

 

 

In the query above, we’ve included the data sources names in curly brackets in front of the tables that we are addressing and then gave them the aliases a and b. If you don’t wish to use aliases you may also use a format like this:

 

 

SELECT {SalesDB}`Sales` .`Product` as ‘Product’, {SalesDB}`Sales` .`Currency` as ‘Currency’, sum({SalesDB}`Sales` .`Revenue`)*{Currencies}`ExchangeRates`.`Rate` as ‘Revenue’

FROM {SalesDB}`Sales` a

JOIN {Currencies}`ExchangeRates` b

ON {SalesDB}`Sales` .`Currency = {Currencies}`ExchangeRates`.`Currency`

GROUP BY {SalesDB}`Sales` .`Product`, {SalesDB}`Sales` .`Currency`

 

 

Creating a SQL Chart from two or more static CSV Files

 

If you wish to cross-query two or more uploaded CSV files you’ll need to keep in mind that each field in the SELECT command will need to have an own alias. Otherwise datapine will not be able to identify the columns of your chart.

 

SELECT a.`Product` as ‘Product’, a.`Currency` as ‘Currency’, sum(a.`Revenue`)*b.`Rate` as ‘Revenue’

FROM {SalesDB}`Sales` a JOIN {Currencies}`ExchangeRates` b

ON a.`Currency = b.`Currency`

GROUP BY a.`Product`, a.`Currency`