Shaping a report¶

Your first query returned every country in the view, in whatever order the warehouse felt like. A report needs less than that, and needs it in a particular order. In this tutorial you will add filters, sorting, and a row cap to the query you already have, checking the result after each change.

Prerequisites: Your first query, and the tutorial.db DuckDB database its setup script builds. You will reuse the same Sales model.

The data underneath is three rows:

revenue  cost  country  region
   100   US       West
   200   CA       West
    50    US       East

Start from a working query¶

Create report.py with the model, the engine, and a query that selects both dimensions. Every step below changes one thing about this query:

report.py¶

from adbc_poolhouse import DuckDBConfig

from semolina import (
    SemanticView,
    Metric,
    Dimension,
    create_engine,
)


class Sales(SemanticView, view="sales"):
    revenue = Metric()
    cost = Metric()
    country = Dimension()
    region = Dimension()


create_engine(
    DuckDBConfig(database="tutorial.db"), register=True
)

cursor = (
    Sales.query()
    .metrics(Sales.revenue)
    .dimensions(Sales.country, Sales.region)
    .execute()
)

for row in cursor.fetchall_rows():
    print(row.country, row.region, row.revenue)

Run it with python report.py:

CA West 2000
US West 1000
US East 500

Adding region to .dimensions() changed the grouping. US was one row of 1500 in the last tutorial and is two rows here, because the warehouse now groups by country and region. Choosing a dimension is already a decision about what the report counts.

1. Keep only the rows you want¶

.where() takes a condition built from a field and a Python operator. Insert one into the chain, before .execute():

cursor = (
    Sales.query()
    .metrics(Sales.revenue)
    .dimensions(Sales.country, Sales.region)
    .where(Sales.region == "West")
    .execute()
)

Run it again:

US West 1000
CA West 2000

The East row is gone. Sales.region == "West" compares nothing at the moment you write it: it builds a Predicate object, which .where() compiles into the WHERE clause the warehouse runs.

Comparison operators other than == work the same way, and so do the named methods on a field. This one filters on the aggregated metric rather than on a dimension:

.where(Sales.revenue > 1000)

2. Combine conditions¶

Real reports rarely ask one question. Use & for AND, | for OR, and ~ for NOT, wrapping each condition in its own parentheses:

.where((Sales.region == "West") & (Sales.revenue > 1000))

CA West 2000

US West was in the previous result and is not in this one, because its 1000 fails the second condition. | makes two conditions alternatives:

.where((Sales.region == "East") | (Sales.country == "CA"))

CA West 2000
US East 500

Both of those conditions are on dimensions. A dimension condition and a metric condition can be joined with &, as in the first example, but not with | or under one ~. Semolina raises ValueError when you run or preview such a query; Mix dimension and metric conditions explains why.

~ negates whatever follows it:

.where(~(Sales.region == "West"))

US East 500

Warning

& binds tighter than |

Python gives these operators the precedence it gives the bitwise ones, so a | b & c reads as a | (b & c), which is rarely what a report means. Parenthesize the grouping you want whenever you mix the two.

3. Fix the order¶

Nothing so far has said which order the rows arrive in, and the warehouse is free to choose. That is why step 1 returned US before CA. .order_by() makes the choice yours. Pass a bare field for ascending, or call .desc() on it for descending:

cursor = (
    Sales.query()
    .metrics(Sales.revenue)
    .dimensions(Sales.country, Sales.region)
    .order_by(Sales.revenue.desc())
    .execute()
)

CA West 2000
US West 1000
US East 500

Drop the .desc() and the same query counts up instead:

.order_by(Sales.revenue)

US East 500
US West 1000
CA West 2000

Sorting on the metric is what makes a “biggest first” report possible, and it is the step that has to come before the next one.

4. Cut it to the top rows¶

.limit(n) caps the number of rows the warehouse returns. On its own it gives you an arbitrary handful. After an .order_by() it gives you a top N:

cursor = (
    Sales.query()
    .metrics(Sales.revenue)
    .dimensions(Sales.country, Sales.region)
    .order_by(Sales.revenue.desc())
    .limit(2)
    .execute()
)

CA West 2000
US West 1000

n has to be a positive integer. Zero or a negative number raises ValueError and a non-integer raises TypeError, both before anything reaches the warehouse.

Complete example¶

Put the three clauses together and you have the report this tutorial was heading for: the two biggest markets in the West region, largest first.

Replace report.py with this and run python report.py:

report.py¶

from adbc_poolhouse import DuckDBConfig

from semolina import (
    SemanticView,
    Metric,
    Dimension,
    create_engine,
)


class Sales(SemanticView, view="sales"):
    revenue = Metric()
    cost = Metric()
    country = Dimension()
    region = Dimension()


create_engine(
    DuckDBConfig(database="tutorial.db"), register=True
)

cursor = (
    Sales.query()
    .metrics(Sales.revenue)
    .dimensions(Sales.country, Sales.region)
    .where(Sales.region == "West")
    .order_by(Sales.revenue.desc())
    .limit(2)
    .execute()
)

for row in cursor.fetchall_rows():
    print(row.country, row.region, row.revenue)

You should see:

CA West 2000
US West 1000

Each of those methods returned a new query rather than changing the one it was called on, so a half-built query is safe to keep around and branch from. The next tutorial leans on that when the filter arrives from an HTTP request.

Next steps¶

You can now shape a result. Next, serve one over HTTP:

Serve a dashboard endpoint

Shaping a report¶

Start from a working query¶

1. Keep only the rows you want¶

2. Combine conditions¶

3. Fix the order¶

4. Cut it to the top rows¶

Complete example¶

Next steps¶

See also¶