OLE DB or ODBC error with Direct query connection to snowflake

[Deepanshu_46]

Hello Experts,
I am facing an issue in which relationship between table is not working in direct query connection to  snowflake.I have defined relationship between two tables using a mapper table . I am getting this issue when try to cross filtering one table based on selection in another.I have aslo attached the data model of relationship. It started working when I get the mapper table in import mode instead of direct query.Your help will be appreciated. Thanks in advance

[Anonymous]

Hi @Deepanshu_46 

Can you tell me if your problem is solved? If yes, please accept it as solution.

Regards,

Nono Chen

[Anonymous]

Hi @Deepanshu_46

I understand that you have encountered the problem of creating relationships between tables in direct query mode but it does not work as expected.

When you encounter OLE DB or ODBC errors when connecting to Snowflake using DirectQuery, it could be for several reasons:

Driver issues: Make sure you are using the latest ODBC or OLE DB driver for Snowflake that is compatible with Power BI. Outdated or incompatible drivers can cause connectivity issues.

Query complexity: DirectQuery generates SQL queries that are executed by the data source. This may result in errors if the query is too complex or not supported by Snowflake. Check the complexity of relationships and generated queries.

Error tracing: Leverage Power BI Desktop’s tracing capabilities to determine the cause of errors. To enable tracing, go to File > Options & Settings > Options, select the Diagnostics tab, and then select Enable Tracing. After reproducing the error, check the trace log for details.

As for the cross-filtering issue, when using DirectQuery mode, Power BI relies on the capabilities of the data source to perform operations such as joins and filtering. If the underlying data source is not optimized for these operations, it may result in performance issues or unsupported scenarios.

In import mode, data is loaded into Power BI's in-memory engine, which is optimized for operations such as cross-filtering. This is why you may see improvements when switching the mapper table into import mode.

To resolve this issue, consider the following:

Model Optimization: Simplify the data model and use bidirectional relational filtering with caution as it may generate complex queries that may not perform well in DirectQuery mode.

Composite Model: If feasible, use a composite model to combine the advantages of import and DirectQuery modes by configuring the storage mode of the model table accordingly. This helps utilize the in-memory engine for some tables while maintaining DirectQuery connections for other tables.

You can view the link below for more details:

DirectQuery model guidance in Power BI Desktop - Power BI | Microsoft Learn

Regards,

Nono Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.