Forum Discussion

VanThuan's avatar
VanThuan
Frequent Visitor
5 months ago
Solved

SSAS Tabular fails to process table with filter when using ODBC: Incorrect number of parameters

Hi Community, I'm experiencing an issue with SSAS Tabular (Visual Studio) when connecting to a data source via ODBC driver (Simba Trino). Environment - Visual Studio with SSAS Tabular project - O...
Need Help
  • v-kpoloju-msft's avatar
    v-kpoloju-msft
    5 months ago

    Hi VanThuan, 

    Thank you for reaching out to the Microsoft Fabric Community Forum. Also, thanks to ibarrau, for those inputs on this thread.

    This behaviour is related to how SSAS Tabular handles query folding with ODBC sources. When you add a filter step in Power Query, the mashup engine tries to push that filter down to the data source by generating a parameterized SQL query (using ? placeholders). In your case, the Simba Trino ODBC driver expects parameter values but does not receive them correctly, which results in the “Incorrect number of parameters” error. Currently, there isn’t any setting in SSAS Tabular or Visual Studio to control or fix this parameter binding behaviour.

    As a workaround, you can avoid this issue by either preventing query folding or by bypassing parameterization completely. The most reliable options are add Table.Buffer before applying the filter so the filtering happens locally instead of being pushed to Trino, or use a native SQL query via Odbc.Query with the filter written directly in SQL (e.g., WHERE client_id IN (4,5,6)), which avoids parameter placeholders entirely and usually provides better performance.

    Refer these links:
    1. https://learn.microsoft.com/en-us/power-query/odbc-parameters 
    2. https://learn.microsoft.com/en-us/sql/odbc/microsoft/limitations?view=sql-server-ver17

    Hope that clarifies. Let us know if you have any doubts regarding this. We will be happy to help.

    Thank you for using the Microsoft Fabric Community Forum.

ibarrau's avatar
ibarrau
Super User
5 months ago

Hi. SSAS doesn't have the same mashup engine as excel or power bi, it's kind of older. I'm wondering if it might be trying to fold the query but can't do it. Maybe if we force prevent that filter buffering the table.

Try this:

let
Source = #"Odbc/dsn=My_Trino_PPR",
Test_Database = Source{[Name="Test",Kind="Database"]}[Data],
gold_Schema = Test_Database{[Name="gold",Kind="Schema"]}[Data],
client_Table = gold_Schema{[Name="client",Kind="Table"]}[Data],
NoFold = Table.Buffer(client_Table),
Filtered = Table.SelectRows(NoFold, each ([client_id] = 4 or [client_id] = 5 or [client_id] = 6))
in
Filtered

Otherwise the only alternative I can think about is running a native query, can you write a native query to that souce? something like:

Odbc.Query("dsn=....", "Select * .... where client_id IN (4,5,6) ")

I hope that helps,