Question

Subtracting Measures from Dimensions and vice versa

  • 15 May 2015
  • 1 reply
  • 651 views

Userlevel 3

First of all, this is not a valid operation in the SQL/LookML world. Just as it is NOT possible to perform


SELECT 
(SUM(order_items.amount) - product.cost) AS order_profit
FROM order_items
LEFT JOIN order_items.product_id = product.id


But the end result is possible. Let’s walk through a few examples:


###1) Measure::Measure operation


Let’s assume we have the following views and dimensions/measures defined:


- view: orders
fields:

- dimension: id
primary_key: true

- dimension: order_price
type: number

- view: product
fields:
- dimension: product_id
primary_key: true

- dimension: product_cost

- view: order_items
fields:
- dimension: order_items_id
primary_key: true

- dimension: order_id

- measure: total_product_cost
type: sum
sql: ${product.product_cost}

- dimension: product_id

- explore: order_items
joins:
- join: orders
foreign_key: order_items.order_id

- join: product
foreign_key: order_items.product_id

And our objective is to calculate the profit on each order. Normally, we would have total_item_price measure in the order_items view from which we would subtract the total_product_cost measure.


However, frequently we have other order-specific fees associated with every order that force us to make use of the order_price dimension value that is captured within the orders table - not within an order items table.


Since we cannot subtract a measure (order_items.total_product_cost) from a dimension (orders.order_price), we create two new measures:


- view: orders
fields:
- measure: total_order_price
type: sum
sql: ${order_price}

- measure: total_order_profit
type: number
sql: ${total_order_price} - ${order_items.total_product_cost}

In this case, both total_order_price and order_items.total_product_cost are measures that are calculated over unique orders and corresponding unique items. Even though any query that pulls total_order_profit will fan-out both orders and product tables, we have ensured that product_cost is correctly duplicated across all items and is aggregated at the item level, while orders are aggregated only at the order level (e.g. total_order_price = order_price for every unique order_id record).


###2) Dimension::Dimension operation


Another solution is to convert a measure to a dimension by creating a fact table (aka derived table).


- view: order_item_facts
derived_table:
sql: |
SELECT
order_items.order_id
, SUM(product_cost) AS order_cost
FROM order_items
LEFT JOIN product ON order_items.product_id = product.product_id
GROUP BY 1
fields:
- dimension: order_id
primary_key: true

- measure: order_cost
type: number

And the associated change in the explore:


- explore: order_items
joins:
- join: orders
foreign_key: order_items.order_id

- join: product
foreign_key: order_items.product_id

- join: order_item_facts
view_label: 'Orders' #syntax available starting 3.20
foreign_key: order_items.order_id

Now the operation we are looking for happens at order level as expected:


- view: orders
fields:
- dimension: order_profit
type: number
sql: ${order_price} - ${order_item_facts.order_cost}

1 reply

Userlevel 6
Badge

Nice explanation.


A note:



So this pattern can get you into trouble. You need to be very careful when computing sums from dimensions that aren’t in the same view. It this case, each order_item is joined through a foreign key to products so the sum computes correctly, but as a rule, I avoid this type of computation. If dimension varies on the primary key of the view the computation will overflow and report an error.


For example, the following sum won’t work because for a given order_id, there can different order_items.amount.


- view: orders
fields;
- measure: total_order_amount
type: sum
sql: ${order_items.amount}

Sometimes its hard to figure out when you can and can’t sum across views, so as a rule, I just don’t do it.

Reply