Simplify/hide workaround aggregation - Explaining to stakeholders

1

We have a bigQuery view over an order_items table and a few metabase questions/reports over this view. I think the stakeholders are going to struggle with concepts, in terms of creating their own questions, for example:

I want to show product sku, name, base cost, qty sold

To create this question, we group on product sku and name. We add to view the max(base_cost) because we use a CTE to join this onto the projection - so we could easily use min() or avg() here for the same data - its same for all rows of a particular sku. for qty sold we use sum(qty_invoiced)

The stakeholders get the last part. But confused why am I grouping by name, I thought product sku only would work? - I have to explain to include name in the report in needs to be included. Then why are we max(base_cost) instead of just base_cost. I feel like I am venturing into SQL land to explain these concepts.

Part of this problem is the product we use currently masks all the aggregation (and non aggregation) because it has deep knowledge of the data.

Is there a way to simplify this or how do you explain the whole aggregation works this way… .

2

You need to create a view on the database that includes all the summary information.

3

Yes, but I still want to filter by order_date so stakeholder can look at whatever date range they like. The only way to achieve that with an aggregated view would be using a parameterized SQL query but then that would lose the other niceties I think (I will give it a try)

4

I sometimes have the same problem and usually I create new column Key (name key, not database key) with the combined value of {SKU - Name}, it avoid false positive and simplify the querying by having them to select one column instead of two but the underlying problem of having to show the name won’t be solved.

1 Like