On-Demand Queries: DirectQuery + Dynamic M Parameters (Power BI)

1. Purpose

2. Problem Statement

3. Solution Approaches

4. Option B Implementation Guidance

5. Concrete Example: BOM Explosion

6. Security considerations

7. Approaches to explore

8. Example Implementation

1. Purpose

This page documents a pattern for building interactive, parameter-driven reports in Power BI when the underlying logic is best executed on-demand in Snowflake rather than precomputed into large tables.

What this page covers:

• On-demand vs. materialized approaches
  How the “compute only what the user asked for” pattern compares to precompute + persist results in Snowflake tables.
• Power BI DirectQuery + Dynamic M Parameters
  How to pass user selections (parameters) into Snowflake queries at runtime
• Design standards and guardrails
  Design standards and guardrails to keep the solution fast, scalable, and maintainable (parameter contracts, model patterns, query patterns, and performance controls).
• Concrete example
  A BOM explosion example demonstrating application of the approach to a recursive / high-cardinality use case.

2. Problem Statement

Many analytics problems share these characteristics:

• Users expect an interactive experience (slicers/filters change results immediately).
• The required data is not a simple fact table; it involves expensive transformations such as:
  • Recursion / graph traversal (e.g., BOM explosions)
  • Rule-based derivations (eligibility, pricing, allocations)
  • “As-of” logic and effectivity windows
  • Multi-step joins with large dimensions or high-cardinality relationships

2.1 The precompute temptation

A common approach is to precompute all possible outcomes into a single table so BI can query it quickly. However, precomputing can create a second-order problem:

• The persisted dataset becomes very large (especially when results are many-to-many, recursive, or explode combinatorially).
• Even if the logic is computed once, the BI layer still has to:
  • Scan/aggregate a massive table
  • Apply interactive filters over very large partitions
  • Handle large intermediate result sets
  • Keep refresh/storage under control

Outcome: The resulting Power BI experience can be slower and less stable than computing a small, relevant slice of results on demand.

2.2 Example: BOM explosion

Users need to view the multi-level BOM explosion for a selected item.

Complication: Not all assemblies are tied to a finished good. Therefore, to support “explode any item” via precompute, we would have to store full explosions for:

• All finished goods, and
• All assemblies (including those not referenced by any finished good)

That produces a very large explosion table. Building an interactive Power BI report on top of it tends to be slower because each visual/filter interaction may still scan and aggregate large volumes of exploded rows.

In contrast, an on-demand approach computes the explosion for the selected item only, which is often significantly smaller and faster for interactive usage.

3. Solution Approaches

This page focuses on two primary approaches, and includes one complementary approach to explore.

3.1 Option A — Materialized data results (precompute + persist in table)

Definition
Compute the result set ahead of time and store it as a table or set of curated tables in Snowflake. Power BI queries the stored results.

How it works (high level flow)

Source data (transactions/master data)
→ transformation logic (SQL, procedures, tasks, dynamic tables)
→ curated table(s)
→ Power BI (Import or DirectQuery)

What it’s good at

• Broad scans and portfolio-style analytics (“show me everything”)
• Consistent and predictable query patterns
• Reuse across many reports/tools
• Exports, scheduled reporting, downstream consumers

Primary concerns

• Storage growth (can be significant for exploded/recursive logic)
• BI performance may still degrade if the table is huge and interactions scan large partitions

Common optimization strategies (Power BI-focused)

• Partitioning/clustering around the most common filters
• Materializing only hot paths (e.g., only finished goods; or only last N revisions)
• Summary + detail split (precompute summary, compute detail on demand)

3.2 Option B — On-demand execution (Snowflake UDTF + DirectQuery + Dynamic M Parameters)

Definition
Execute the transformation logic at query time for the user’s current selection by passing Power BI slicer values into a Snowflake query (often calling a UDTF). Results are computed on demand and returned to visuals.

How it works (high level flow)

• User selects parameters in Power BI (slicers/filters)
  → Dynamic M parameters populate Power Query parameters
  → Power Query issues a native Snowflake SQL statement
  → SQL calls TABLE(<udtf>(…)) (often via LATERAL)
  → Snowflake returns the scoped result set

What it’s good at

• Interactive drill-down where each interaction is scoped (e.g., one item/customer at a time)
• Expensive logic where precomputing “all combinations” is impractical
• Minimizing data footprint while keeping logic centralized in Snowflake

Primary concerns

• Query volume: each interaction (and often each visual) can trigger execution
• Concurrency: many users interacting can increase warehouse load
• Guardrails required to prevent unbounded queries (depth/range/row limits)

Key design principle

• Use dimension/mapping tables for slicer values and relationships.
• Avoid making slicers dependent on UDTF output—otherwise the UDTF gets executed just to populate filter choices, increasing cost and latency.

4. Option B Implementation Guidance

4.1 What this implementation does

This pattern lets a user pick a value in a slicer (for example, an Item) and have Power BI pass that value into a parameterized query. Power BI then re-queries the source in DirectQuery mode and refreshes the visuals using the result for that specific selection.

Key idea:

• Slicer selection → update Model field value → set Dynamic M parameter → set Power Query parameter → Native SQL query executed in DirectQuery

4.2 Required building blocks in Power BI

You need four components:

1. DirectQuery result table

• This is the “fact-like” table that comes from a native SQL statement (the one that calls the UDTF).

2. Power Query parameter(s)

• Example parameter: p_item
• Created in Power Query and referenced inside the native SQL.

3. A field in the model to bind to the parameter

• Usually a column in a dimension table like DimItem[Item]
• This is what the user interacts with via slicers.

4. Dynamic M parameter binding

• Links the model field (e.g., DimItem[Item]) to the Power Query parameter (p_item)
• Enables runtime injection of the slicer selection into the DirectQuery statement.

4.3 Setup steps

Step 1 — Create the Power Query parameter(s)

In Power Query Editor:

• Manage Parameters → New Parameter
• Create p_item
  • Type: Text
  • Default: safe default (e.g., “common item” or “dummy that returns no rows”)

Step 2 — Create the DirectQuery table using a native SQL statement

Create a new query using the Snowflake connector in DirectQuery mode and use a native query that references the parameter.

Power Query (M) embedding approach

In Power Query (M) embed the SQL and reference p_item. The exact UI steps vary slightly by Power BI version, but the concept is consistent:

• connect to Snowflake
• choose DirectQuery
• “Advanced options” / “SQL statement” or Value.NativeQuery usage
• ensure the query uses the parameter placeholder and the PQ parameter is referenced

Practical note: keep this result table focused. Don’t use it to populate slicer lists.

SQL pattern

• Build the SQL string using the Power Query parameter p_item (with basic escaping)
• Submit it as the native SQL statement in DirectQuery

let
    ItemSafe = Text.Replace(p_item, "'", "''"),
    Sql = "SELECT * FROM TABLE(BOM_EXPLODE_LATEST('" & ItemSafe & "'))"
in
    Sql

Step C — Create/load a dimension table for slicers (Item list)

Load a simple DimItem table (Import or DirectQuery) containing available item values:

• DimItem[Item]

Use this column in a slicer.

Step D — Bind the slicer field to the Power Query parameter (Dynamic M)

In the semantic model:

• Bind:
  • Model field: DimItem[Item]
  • To parameter: p_item

Result:

• When a user selects an item in the slicer, Power BI passes that value to p_item and reruns the DirectQuery query.

4.4 Runtime behavior (what to expect)

What happens

• User selects an item
• Power BI reruns the DirectQuery statement using the selected value
• Visuals using the parameterized result table refresh

Important behavior

• If multiple visuals use the parameterized table, Power BI may issue multiple queries (often one per visual)
• Recommendation: limit how many visuals depend on the parameterized query

Trade-off

• Pros: interactive, selection-driven “on-demand” retrieval
• Cons: increased query volume when many visuals/tooltips depend on the parameterized table

4.5 Practical guardrails (Power BI-focused)

Slicer configuration

• Make the Item slicer single-select (recommended)
• Avoid Select all if it causes broad/unbounded queries

Visual design to reduce query churn

• Keep tooltips minimal on visuals backed by the parameterized table (tooltips can trigger extra queries)
• Use a dedicated “details” page if many visuals are needed
  Keep the “on-demand” dataset confined to that page

5. Concrete Example: BOM Explosion

Goal

User selects one item and sees its latest BOM explosion (multi-level), with minimal fields.

Parameters

• p_item (Text) — required, single-select

Output fields (minimal)

Return only:

• parent_item
• component_item
• level

Why this set:

• Sufficient to show hierarchy (matrix) or table
• Supports correctness checks
• Keeps the example aligned to dynamic parameter passing

Power BI setup for the example

1. Load DimItem (list of items) and place DimItem[Item] in a slicer (single-select).
2. Create PQ parameter p_item.
3. Create DirectQuery result table using the SQL below, referencing p_item.
4. Bind DimItem[Item] → p_item using Dynamic M parameters.
5. Create a table visual from the result table with:
   • parent_item
   • component_item
   • level

Simplified UDTF call

The UDTF returns the latest version automatically (no version/as-of parameters exposed to Power BI).

SQL pattern

let
    ItemSafe = Text.Replace(p_item, "'", "''"),
    Sql = "SELECT parent_item, component_item, level " &
          "FROM TABLE(BOM_EXPLODE_LATEST('" & ItemSafe & "'))"
in
    Sql

Expected user flow

• Pick Item A → visual shows exploded rows for Item A (latest BOM).
• Pick Item B → visual refreshes and shows Item B’s explosion.

Performance comparison: Option A vs Option B

6. Security considerations

Due to limitations in the Snowflake driver for this solution, parameter binding is not supported. As a result, parameter values must be concatenated into the SQL statement.

This introduces a SQL injection risk that needs to be carefully managed by doing the following:

• Avoid using user-controlled or externally managed data sources to populate parameters.
• Use trusted, curated dimension or reference tables that contain validated values.
• Prefer stable system-generated identifiers over descriptive or free-text fields.
• Escape parameter values before inserting them into the SQL string.

Safe parameter fields:

• item_id
• customer_id
• product_id
• other internal IDs managed by a system

Avoid parameter fields such as:

• customer name
• item description
• comment fields
• free-text search fields

Using internal IDs reduces the risk of unexpected characters, inconsistent values, and SQL injection attempts. It also makes the report behavior more predictable because slicer values come from controlled reference data rather than free-form user input.

Query guardrails

On-demand execution can become expensive if users are allowed to run broad requests.

Recommended guardrails:

• Use single-select slicers for parameters that drive UDTF execution.
• Avoid “Select all” behavior.
• Add depth limits for recursive logic.
• Add row limits where appropriate.
• Use a safe default parameter that returns no rows or a small known result.

7. Approaches to explore

Option C — On-demand execution with Paginated Reports (UDTF + parameters)

Useful resource: Use Dynamic M Parameters in DirectQuery mode and Paginated Reports – PBI Guy

8. Example Implementation

Prerequisites:

• An active Snowflake account
• Power BI Desktop installed
• Download the Snowflake SQL scripts attached to this post: Snowflake SQL Queries.sql
• Download the Power BI template file: SNFLK Dynamic filtering template.pbix

8.1. Snowflake: Setup

Create the data objects necessary to perform a BOM explosion per item – download and sequentially execute the queries from the attached Snowflake SQL Queries.sql file.

8.2. Power BI: Setup

1. Open the Power BI template file SNFLK Dynamic filtering template.pbix
2. Click the Transform data button, then locate the P_EXPLODE_BOM query from the left side menu
3. Click on Advanced Editor after that, and ensure to replace the text YOUR_SERVER_NAME with the actual Snowflake server name associated with your Snowflake account
   

Walkthrough of the Power BI template:

1. Configure the Snowflake tables in direct query mode
2. Create a new parameter pExplodeItemId and give it a default value of something that will return a small result set
   

   

3. Open the Power BI model view and bind the parameter to the field that will be passed to the Snowflake UDF. In this case, we use the ITEM_ID field from the ITEM table

   

4. In Power BI, set up a direct query UDF data model that will pass the Power BI parameter to Snowflake
   
   let
   Source =
     Snowflake.Databases(
          “YOUR_SERVER_NAME.snowflakecomputing.com",
            "COMPUTE_WH",
           [
                Role = "PUBLIC"
          ]
       ),
    DB =
          Source{[Name = "SANDBOX", Kind = "Database"]}[Data],
    Sql = "SELECT * FROM TABLE(PUBLIC.EXPLODE_BOM( '" & Text.Replace(pExplodeItemId,"'","''") & "')) ",
    Data = Value.NativeQuery(
        DB,
        Sql,
        null,
        [EnableFolding = true]
    )
in
    Data

At the time of writing the article, Snowflake connector for PowerBI does not support binding parameters to native queries. SQL injections risks can be mitigated when binding parameters feature is released.

5. Example output A selected finished product displays its full explosion, including sub-assemblies and lower-level components, without requiring all possible BOM explosions to be precomputed and stored in a large table.

   

Explosion of a finished product containing sub-assemblies

Explosion of an assembly