Boring Semantic Layer (BSL)

Use the following comparison to determine which semantic layer tool fits your workflow.

Key Differences

• BSL (Boring Semantic Layer): Best for Python-first analytics and AI agents. It uses a pure Python API with Ibis expressions, making it highly portable across 20+ databases. It is easy to set up via pip and provides native MCP protocol support for AI/LLM integration.
• Malloy: Best for complex analytical queries and users wanting a specialized DSL. It features a custom query language with optimized SQL generation and excellent VSCode support. It is Google-backed and focuses on preventing join fan-out through graph-based automatic safety.
• dbt Semantic Layer: Best for enterprise metrics governance. It relies on YAML configuration and is part of the larger dbt ecosystem. It is designed for analytics engineers and integrates extensively with BI tools like Tableau and Power BI, though it often requires a paid dbt Cloud subscription for full features.

Comparison Summary

To aggregate time-based dimensions, use the time_grain or time_grains parameters instead of using date functions (like .month()) in filters.

Global Time Grain

Use time_grain to apply the same grain to all time dimensions in the query. Requirement: You MUST include the time dimension in the dimensions list.

Available Grains (short or long form):

• year / TIME_GRAIN_YEAR
• quarter / TIME_GRAIN_QUARTER
• month / TIME_GRAIN_MONTH
• week / TIME_GRAIN_WEEK
• day / TIME_GRAIN_DAY
• hour / TIME_GRAIN_HOUR
• minute / TIME_GRAIN_MINUTE
• second / TIME_GRAIN_SECOND

Per-Dimension Time Grains

Use the time_grains dictionary when different dimensions require different aggregation levels.

Time Range Filtering

Use the time_range parameter for preferred time-based filtering. It handles ISO 8601 formats and time zones automatically:

{
    "start": "2024-01-01T00:00:00Z",
    "end": "2024-12-31T23:59:59Z"
}

query_model(
    model_name="orders",
    dimensions=["orders.order_date", "orders.ship_date"],
    measures=["orders.total_sales"],
    time_grains={"orders.order_date": "month", "orders.ship_date": "quarter"}
)

The sessionization pattern groups sequential time-series events into logical sessions based on inactivity timeouts. The workflow follows these steps:

1. Identify Boundaries: Use the lag() window function to find the time difference between the current event and the previous event for a specific user. A session start is marked if the gap exceeds a threshold (e.g., 30 minutes) or if it is the user's first event.
2. Assign Session IDs: Use a cumulative sum (.sum().over(...)) of the session start markers (cast to integers) to generate unique, incrementing IDs for each session per user.
3. Calculate Metrics: Group by the newly created session_id to calculate session-level metrics like duration, event count, or conversion status.
4. Aggregate to User Level: Group by user_id to summarize behavior across all sessions (e.g., total sessions, average events per session, conversion rate).

from ibis import _

# 1. Identify Boundaries & 2. Assign Session IDs
result = (
    activity_st
    .group_by("user_id", "minute_offset", "page_url", "action")
    .aggregate()
    .mutate(
        prev_minute=lambda t: t.minute_offset.lag().over(
            group_by="user_id",
            order_by=t.minute_offset
        ),
        minutes_since_last=lambda t: t.minute_offset - t.prev_minute,
        is_session_start=lambda t: (t.minutes_since_last > 30) | t.prev_minute.isnull(),
        session_id=lambda t: t.is_session_start.cast("int32").sum().over(
            group_by="user_id",
            order_by=t.minute_offset,
            rows=(None, 0)  # Cumulative sum
        )
    )
)

To enable LLMs to perform time-based aggregations and range queries via MCP, you must explicitly define time dimensions in your semantic table. This is done by setting is_time_dimension=True and specifying the smallest_time_grain in the dimension configuration.

Available time grains:

• second / TIME_GRAIN_SECOND
• minute / TIME_GRAIN_MINUTE
• hour / TIME_GRAIN_HOUR
• day / TIME_GRAIN_DAY
• week / TIME_GRAIN_WEEK
• month / TIME_GRAIN_MONTH
• quarter / TIME_GRAIN_QUARTER
• year / TIME_GRAIN_YEAR

from boring_semantic_layer import to_semantic_table

flights = (
    to_semantic_table(flights_data, name="flights")
    .with_dimensions(
        arr_time={
            "expr": lambda t: t.arr_time,
            "description": "Arrival time of the flight",
            "is_time_dimension": True,
            "smallest_time_grain": "TIME_GRAIN_SECOND",
        },
        origin={
            "expr": lambda t: t.origin,
            "description": "Origin airport code"
        },
    )
    .with_measures(
        flight_count={
            "expr": lambda t: t.count(),
            "description": "Total number of flights"
        }
    )
)