Unable to Fold Query using MariaDB Direct Query Adapter for Power BI

[alish_b]

I installed the mariadb-connector-ODBC-3.1.20-win64 ODBC connector and used the MariaDB connector in Power BI.

Then I used the Direct Query mode to connect to the database. I was able to pull some of the data from column (containing text) in the database's table but while trying to get the count of another column (containing numbers) I ran into 'ole db or odbc error: [Expression.Error] We couldn't fold the expression to the data source. Please try a simpler expression..'. Now, getting count should be a simple expression and the number of rows is also shy of a few hundred thousand. I even tried extraction of that particular column only in Power Query but the same error.

Then, I tried the same with Import mode and it worked flawlessly. But I need to implement Direct Query mode to leverage near real time reporting.

Additionally, with DirectQuery some relationships would show a cardinality of Many to Many while they were in fact they were observed to be Many to One using Import mode.

Also, the 'View Native Query' option is greyed out from the very first 'Source' step in both DirectQuery and Import mode.

I am not sure if I am doing something wrong or some configuration need to be done on the database side. I would greatly appreciate any help on troubleshooting this.

Thanks.

[v-jingzhan-msft]

Hello @alish_b 

Thank you for reaching out with your issue regarding DirectQuery and Query Folding. Let's address your concerns step by step.

1. Query Folding Error: The error message you're seeing indicates that Power BI is unable to fold the expression back to the data source. Query folding is a process where the steps you define in Power Query are translated into a single query statement that is then executed by the data source. When query folding does not occur, Power BI must process the data transformations locally, which is not ideal in DirectQuery mode.

   To troubleshoot this issue, you can try simplifying your query to see if certain transformations are preventing the folding. For more information on query folding, please refer to the Query folding guidance in Power BI Desktop. You can find the transformations that achieve folding or prevent folding from Power Query query folding. 

2. Relationship Cardinality Discrepancy: The discrepancy in relationship cardinality between DirectQuery and Import modes can be due to several factors, including how Power BI determines the uniqueness of columns and the data source constraints. For a detailed explanation, you can read the Model relationships in Power BI Desktop documentation.

   To resolve this, you may manually adjust the cardinality type in Power BI Desktop if you are certain of the data characteristics. Ensure that the data types of the related columns are consistent and that the data is clean and free of integrity issues before establishing relationships.

3. 'View Native Query' Option Greyed Out: The 'View Native Query' option being greyed out indicates that the step you're trying to view cannot be translated into a native query that the data source can execute. This is often related to the lack of query folding. In DirectQuery mode, all transformations need to be supported by the underlying data source. If a transformation is not supported, Power BI cannot generate a native query for that step.

   To understand more about why this might be happening, you can refer to the DirectQuery in Power BI documentation which explains the limitations and considerations when using DirectQuery. 
   To have an alternative, sometimes you may refer to Query folding on native queries.

I hope this would be helpful. 

Best Regards,
Jing
If this post helps, please Accept it as Solution to help other members find it. Appreciate your Kudos!

[alish_b]

Hello @v-jingzhan-msft ,

Thank you for the response and the useful links. I believe the cardinality issue should be solved by the approach you referred to.

However, the most crucial issue at the moment is the 'View Native Query' being grayed out from the very first Source step when connecting to the MariaDB where I am basically pulling all the columns in the table which should be a SELECT statement that can be folded back to the database. I tried similar steps with a replica database in SQL Server and it works fine and the 'View Native Query' is active in the 'Source' step and even through other transformations. I am not sure what is blocking similar functionality in MariaDB and it gives me a warning with 'This step results in a query that is not supported in DirectQuery mode' prompting to Switch all tables to Import mode in the first step itself (where it is only pulling contents of the DB with =MariaDB.Contents("Server_Name", null, null).

I am not sure if there is a problem with the connector itself or there is a need to perform any additional setup in database end specifically in case of MariaDB connections.

I would really appreciate your help on this.

Thanks,

Alish

[lbendlin]

 the most crucial issue at the moment is the 'View Native Query' being grayed out from the very first Source step when connecting to the MariaDB 

That is largely meaningless.  You will find that queries fold even if that link is greyed out.  The only sure way is to run query diagnostics or network traces.

Note the presence of a Value.NativeQuery Power Query function that includes a folding hint.