The Reporting API supports querying multiple cubes in a single request by including measures from different cubes in the measures array. The API automatically handles the join server-side.
When you include measures from multiple cubes (e.g., Orders.net_sales and PaymentAndRefunds.total_amount), the API:
- Identifies the cubes involved in your query
- Determines the join relationships based on common dimensions between cubes
- Executes the join server-side and returns combined results
- Aggregates the measures according to your specified dimensions and time granularity
Note
Key benefit: You don't need to specify join conditions—the API handles this automatically based on the cube schema relationships.
Before querying multiple cubes, it's important to understand how they relate:
- One order can have multiple payments (split payments, partial payments, etc.)
- Each payment has an
order_idlinking it to an order - Each order has
tenders[].payment_idreferences to its payments
When you join Orders and PaymentAndRefunds on order_id, payment measures are aggregated (summed) for all payments belonging to that order.
| Dimension | Links | Use For |
|---|---|---|
order_id | Orders ↔ Payments | Order-level analysis |
location_id | All cubes ↔ Locations | Location performance |
merchant_id | All cubes ↔ Merchant | Merchant-level aggregation |
customer_id | Orders ↔ Customers | Customer analysis |
Warning
Join Validation: Not all cubes can be joined together. If you try to query measures from cubes that don't have a join path, the API will return an error:
{"error": "Can't find join path to join 'Orders', 'CatalogModifierList'"}
The API automatically determines if cubes can be joined based on their schema relationships. Always test your multi-cube queries to ensure the cubes have a valid join path.
The behavior depends on whether you include a common dimension that links the cubes:
When you query multiple cubes without specifying a linking dimension, the API aggregates both cubes at the same level:
{ "measures": ["Orders.count", "PaymentAndRefunds.count"], "timeDimensions": [{ "dimension": "Orders.sale_timestamp", "dateRange": ["2024-01-01", "2024-01-31"], "granularity": "day" }] }
Response: Each row shows aggregated totals for both cubes at the same time granularity:
{ "data": [ { "Orders.sale_timestamp.day": "2024-01-01T00:00:00.000", "Orders.count": 42, "PaymentAndRefunds.count": 45 } ] }
Use case: Compare overall metrics (e.g., "Did we have more payments than orders today?")
When you include a dimension that exists in both cubes (like order_id or location_id), the API joins row-by-row:
{ "measures": ["Orders.net_sales", "PaymentAndRefunds.total_amount"], "dimensions": ["Orders.order_id"], "timeDimensions": [{ "dimension": "Orders.sale_timestamp", "dateRange": ["2024-01-01", "2024-01-31"] }], "limit": 10 }
Response: Each row shows an order with its associated payment data:
{ "data": [ { "Orders.order_id": "ORDER123", "Orders.net_sales": "125.50", "PaymentAndRefunds.total_amount": "135.50" }, { "Orders.order_id": "ORDER456", "Orders.net_sales": "89.25", "PaymentAndRefunds.total_amount": "96.30" } ] }
Use case: Analyze individual orders and their payment details (e.g., "Which orders have payment amounts that differ from net sales?")
Type: Aggregated join (no common dimension)
Compare order counts with payment counts by day to identify discrepancies:
{ "measures": [ "Orders.count", "Orders.net_sales", "PaymentAndRefunds.count", "PaymentAndRefunds.total_amount" ], "timeDimensions": [{ "dimension": "Orders.sale_timestamp", "dateRange": ["2024-01-01", "2024-01-31"], "granularity": "day" }], "segments": ["Orders.closed_checks"] }
Response:
{ "data": [ { "Orders.sale_timestamp.day": "2024-01-01T00:00:00.000", "Orders.count": 42, "Orders.net_sales": "4250.50", "PaymentAndRefunds.count": 45, "PaymentAndRefunds.total_amount": "4590.04" }, { "Orders.sale_timestamp.day": "2024-01-02T00:00:00.000", "Orders.count": 38, "Orders.net_sales": "3980.25", "PaymentAndRefunds.count": 40, "PaymentAndRefunds.total_amount": "4298.67" } ] }
Why this is useful: Payment count may be higher than order count when orders have multiple payments (e.g., split payments, partial payments). This aggregated view helps identify such patterns over time.
Type: Row-level join (common dimension: location_id)
Build a comprehensive location performance report combining sales, items, and customer data:
{ "measures": [ "Orders.net_sales", "Orders.count", "ItemTransactions.net_quantity", "CustomerSnapshots.count" ], "dimensions": ["Orders.location_id"], "timeDimensions": [{ "dimension": "Orders.sale_timestamp", "dateRange": "last 30 days" }], "segments": ["Orders.closed_checks"] }
Response:
{ "data": [ { "Orders.location_id": "L1234567890ABC", "Orders.net_sales": "125000.50", "Orders.count": 1250, "ItemTransactions.net_quantity": 3500, "CustomerSnapshots.count": 450 }, { "Orders.location_id": "L9876543210XYZ", "Orders.net_sales": "98000.25", "Orders.count": 980, "ItemTransactions.net_quantity": 2800, "CustomerSnapshots.count": 380 } ] }
Why this is useful: The cubes are joined on location_id, so each row shows all metrics for a specific location. This enables location-level KPI calculations:
- Average items per order:
ItemTransactions.net_quantity / Orders.count(e.g., 3500 / 1250 = 2.8 items per order) - Average orders per customer:
Orders.count / CustomerSnapshots.count(e.g., 1250 / 450 = 2.78 orders per customer)
Type: Row-level join (common dimension: order_id)
Analyze individual orders with their total payment amounts:
{ "measures": ["Orders.net_sales", "PaymentAndRefunds.total_amount"], "dimensions": ["Orders.order_id"], "timeDimensions": [{ "dimension": "Orders.sale_timestamp", "dateRange": "last 7 days" }], "segments": ["Orders.closed_checks"], "limit": 10 }
Response:
{ "data": [ { "Orders.order_id": "ORDER123", "Orders.net_sales": "125.50", "PaymentAndRefunds.total_amount": "135.50" }, { "Orders.order_id": "ORDER456", "Orders.net_sales": "89.25", "PaymentAndRefunds.total_amount": "96.30" } ] }
Note
Important: Since one order can have multiple payments, PaymentAndRefunds.total_amount is the sum of all payments for that order. If an order has 2 payments of $50 each, the total_amount will be $100.
Why this is useful: Each row represents a specific order with its aggregated payment data. This helps identify:
- Orders where payment amount differs from net sales (due to tax, tips, rounding)
- Orders with missing payment records (payment amount = 0)
- Total payment amounts for orders with split/partial payments
Type: Row-level join (common dimension: payment_id)
To see individual payments (not aggregated by order), use payment_id as the dimension:
{ "measures": [ "PaymentAndRefunds.total_amount", "PaymentAndRefunds.tip_amount" ], "dimensions": [ "PaymentAndRefunds.payment_id", "PaymentAndRefunds.order_id", "PaymentAndRefunds.type" ], "timeDimensions": [{ "dimension": "PaymentAndRefunds.reporting_timestamp", "dateRange": "last 7 days" }], "limit": 10 }
Response:
{ "data": [ { "PaymentAndRefunds.payment_id": "PAYMENT001", "PaymentAndRefunds.order_id": "ORDER123", "PaymentAndRefunds.type": "CARD", "PaymentAndRefunds.total_amount": "75.50", "PaymentAndRefunds.tip_amount": "10.00" }, { "PaymentAndRefunds.payment_id": "PAYMENT002", "PaymentAndRefunds.order_id": "ORDER123", "PaymentAndRefunds.type": "CASH", "PaymentAndRefunds.total_amount": "60.00", "PaymentAndRefunds.tip_amount": "0.00" } ] }
Why this is useful: This shows individual payments, including multiple payments for the same order (note both payments have order_id: "ORDER123"). Use this when you need to:
- Analyze payment method mix at the payment level
- Identify split payment patterns
- Track individual payment amounts and tips
For more complex analysis, you can also query cubes separately and join in your application:
{ "measures": ["Orders.net_sales"], "dimensions": ["Orders.order_id"], "timeDimensions": [{ "dimension": "Orders.sale_timestamp", "dateRange": "last 7 days" }] }
{ "measures": ["PaymentAndRefunds.total_amount"], "dimensions": ["PaymentAndRefunds.order_id", "PaymentAndRefunds.type"], "timeDimensions": [{ "dimension": "PaymentAndRefunds.reporting_timestamp", "dateRange": "last 7 days" }] }
Join the results using order_id as the key.
Note
When to use client-side joins: Use this approach when you need fine-grained control over the join logic or when querying at different granularities.
{ "measures": ["Orders.net_sales", "PaymentAndRefunds.total_amount"], "dimensions": ["Orders.location_id"], "timeDimensions": [{ "dimension": "Orders.sale_timestamp", "dateRange": "last 30 days" }], "segments": ["Orders.closed_checks"] }
{ "measures": ["Orders.net_sales", "ItemTransactions.net_quantity"], "dimensions": ["Orders.location_id"], "timeDimensions": [{ "dimension": "Orders.sale_timestamp", "dateRange": "last 30 days" }], "segments": ["Orders.closed_checks"] }
{ "measures": ["Orders.count", "CustomerSnapshots.count"], "dimensions": ["Orders.location_id"], "timeDimensions": [{ "dimension": "Orders.sale_timestamp", "dateRange": "last 30 days" }], "segments": ["Orders.closed_checks"] }
{"error": "Can't find join path to join 'Orders', 'CatalogModifierList'"}
What this means: The cubes don't have a direct or indirect join relationship in the schema.
Solutions:
- Query cubes separately and join in your application
- Use a different cube combination that has a join path
- Check the Schema Explorer to understand cube relationships
- Test joins before deploying to production
Note
Join paths are defined in the server-side Cube.js schema and may change over time. Test multi-cube queries in development before relying on them in production. If a join path doesn't exist, the API returns an error like "Can't find join path to join 'Cube1', 'Cube2'".
| Cube 1 | Cube 2 | Join Key | Works? |
|---|---|---|---|
| Orders | PaymentAndRefunds | order_id, location_id | ✅ Yes |
| Orders | ItemTransactions | order_id, location_id | ✅ Yes |
| Orders | CustomerSnapshots | customer_id, location_id | ✅ Yes |
| PaymentAndRefunds | ItemTransactions | order_id, location_id | ✅ Yes |
| Orders | Location | location_id | ✅ Yes |
| Orders | CatalogModifierList | None | ❌ No |
Always test multi-cube combinations to ensure a join path exists:
{ "measures": ["Cube1.measure", "Cube2.measure"], "limit": 1 }
If you get a "Can't find join path" error, use client-side joins instead.
Know the relationship between cubes:
- One-to-many: Orders → Payments (one order, multiple payments)
- Many-to-one: Orders → Customers (multiple orders, one customer)
This affects how measures are aggregated.
Include dimensions like order_id or location_id to get row-level detail:
{ "measures": ["Orders.net_sales", "PaymentAndRefunds.total_amount"], "dimensions": ["Orders.order_id"] }
Multi-cube queries can return many rows:
{ "measures": ["Orders.net_sales", "PaymentAndRefunds.count"], "dimensions": ["Orders.location_id"], "limit": 1000 }