Multi-Source Queries

Ultorg perspectives can combine data from multiple data sources. To add a table from a different data source to the current perspective, use the Custom Join action in the usual manner. You can select a table in any data source as the Child Table.

Example: Joining Database Tables and a CSV File

For example, we might start with the following perspective, which shows Courses and Sections/Meetings in an academic course catalog:

A perspective showing Courses, Sections, and Meetings from a single course catalog database, with the Folders sidebar visible. In the Folders sidebar, a second data source from a CSV file has been added, but is not yet used in the open perspective.

Suppose we have, separately, a CSV file containing additional information about individual rooms. To include this information in the perspective, we first add the CSV file as a separate data source, as can be seen in the Folders sidebar above. Then, we invoke Custom Join on the “Place” field, and select the table in the CSV file, and the “Room Number” field in the latter:

In the perspective from the previous figure, we invoke the Custom Join action on the Place field from the Meetings table. The Custom Join dialog appears, where we select the Room Data table from the CSV data source as the child table and the Room Number field from the latter as the join to constrain against.

Clicking OK, a new “Room Data” subquery appears with data from the CSV file:

The perspective from the previous figure, after OK is clicked in the Custom Join dialog. A Room Data subquery appears under Meetings. The Folders sidebar is visible, with a blue arrow indication active on the Room Data table, to indicate that the cell cursor is currently on a value sourced from the latter data table.

The blue arrow in the Folders sidebar () shows which table and data source the data under the cell cursor comes from.

We can make additional fields from the CSV file visible with the field selector, and then proceed with other actions. For example, we can make the “Stage” field visible, and then filter to find only courses that are taught in rooms with a stage:

The perspective from the previous figure, with the new fields Stage and Capacity now made visible in the Room Data subquery. A filter has been added on the Stage field, to show only Courses, Sections, and Meetings taught in a room with a stage.

How it Works

Behind the scenes, Ultorg handles multi-source queries by creating extracts of the required data tables and running queries on your local computer. You can click Refresh Data () to refresh extracted data if necessary.

Annotating External Data

It is often useful to combine data in an external data source with supplemental data that you prepare yourself. Such data can be provided, for instance, in an Excel file or in Google Sheets.

You can also edit the supplemental data directly from Ultorg, and write changes back to the original spreadsheet.

Example: What-If Forecasting

Suppose we have a read-only database of products and orders, and want to simulate the effects of future price changes.

An initial perspective might join tables such as Customers, Orders, Line Items, and Products, and calculate the total revenue per customer:

A perspective showing Customers and Orders, with Line Items and Products subqueries further nested under the latter. At the Customers level, a formula calculates the total revenue from the customers' orders.

To store our pricing adjustments, we create a separate table in Excel, with columns named Product ID, Adjustment, and Notes. We fill in a few rows to start with, so that Ultorg can detect the data type of each column:

A screenshot of Microsoft Excel with a Price Changes table containing three columns Product ID, Adjustment, and Notes, with four rows of populated data.

In Ultorg, we connect to the Excel file by clicking Add Data Source→Connect to File.

Then, in our existing perspective, we select the “ID” column under Products, click Custom Join in the context menu, and join on the “Product ID” column in our spreadsheet table.

The Custom Join dialog opened to join in the Price Changes table from the Excel file with the Products subquery in our existing database perspective.

Data from the Price Changes table will appear as a new subquery under Products:

The perspective from the previous figure after OK has been pressed in the Custom Join dialog.

A single row from the Price Changes table can appear in multiple positions, if the related product is referenced from multiple orders (e.g. “Alice Mutton”).

Now we can insert a formula that estimates total revenue per customer with the proposed pricing adjustments. We use the ifnull function to set a default multiplier of 1 for products that do not have an entry in our Excel sheet.

The perspective from the previous figure, with a new formula added to compute the revenue per customer with simulated price changes, as well as a new formula containing the difference in revenues before and after the price changes are applied.

We have also added a formula showing the difference in revenue before and after the price changes, for each customer, with an inline bar chart ().

Further price adjustment entries can now be made from Ultorg, in the context of all the other data that that is visible in the perspective.

The perspective from the previous figure, now in Data Editing mode and with a single row pending insertion in the Price Changes table.

When Apply Edits is pressed, the edits will be written to the Excel file, and formulas will recalculate. See Data Editing Basics for more information.

Demo Video

The example above is featured in the following demo video.