Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
itchyeyeballs
Impactful Individual
Impactful Individual

Datamart ODBC DSN Timeout

Hi all,

 

Does anyone know if it's possible to set up an ODBC DSN to connect to a Datamart? Our tests keep timing out.

 

[Microsoft][ODBC Driver 17 for SQL Server]TCP Provider: Timeout error [258].
[Microsoft][ODBC Driver 17 for SQL Server]Login timeout expired
[Microsoft][ODBC Driver 17 for SQL Server]Unable to complete login process due to delay in login response

 

Our organisation just upgraded to Gen2 so we are testing functionality. We have successfully connected Excel (awesome, huge step forward) and SSMS but have hit timeout issues using other tools. Specifically, we want to enable an ODBC connection to enable Alteryx to consume the datamart tables. 

Any help appreciated 

1 ACCEPTED SOLUTION
otravers
Community Champion
Community Champion

This now works, make sure to have the latest driver:

https://www.oliviertravers.com/power-bi-datamarts-sql-server-desktop-tools-connection/

------------------------------------------------
1. How to get your question answered quickly - good questions get good answers!
2. Learning how to fish > being spoon-fed without active thinking.
3. Please accept as a solution posts that resolve your questions.
------------------------------------------------
BI Blog: Datamarts | RLS/OLS | Dev Tools | Languages | Aggregations | XMLA/APIs | Field Parameters | Custom Visuals

View solution in original post

9 REPLIES 9
otravers
Community Champion
Community Champion

This now works, make sure to have the latest driver:

https://www.oliviertravers.com/power-bi-datamarts-sql-server-desktop-tools-connection/

------------------------------------------------
1. How to get your question answered quickly - good questions get good answers!
2. Learning how to fish > being spoon-fed without active thinking.
3. Please accept as a solution posts that resolve your questions.
------------------------------------------------
BI Blog: Datamarts | RLS/OLS | Dev Tools | Languages | Aggregations | XMLA/APIs | Field Parameters | Custom Visuals

👍

Any idea if they have fixed the other issues? I stopped trying to use them as they were super slow and gave me loads of problems with things like dataflows,

I haven't tried to load a lot of data recently and my early experience was similar to yours. It's probably worth a second look, anecdotally it feels faster but I have yet to try any serious data load this year.

------------------------------------------------
1. How to get your question answered quickly - good questions get good answers!
2. Learning how to fish > being spoon-fed without active thinking.
3. Please accept as a solution posts that resolve your questions.
------------------------------------------------
BI Blog: Datamarts | RLS/OLS | Dev Tools | Languages | Aggregations | XMLA/APIs | Field Parameters | Custom Visuals

I'll see if I can dig out the workspaces I set up to compare. 

As this functionality seems so Beta (compared to the other preview functionality IMO) it feels like it needs a much more detailed and up-to-date communications page from Microsoft. It's being actively pushed to users but I never knew if it was broken or if I was doing something wrong. I'm reluctant to sink much more time into it even though it seems like it has a lot of potential.

v-henryk-mstf
Community Support
Community Support

Hi @itchyeyeballs ,

 

For this type of problem, try the following statement Use "ConnectionTimeout" instead:

Source = Odbc.DataSource("dsn=Production", [value=true, ConnectionTimeout=#duration(0, 0, 15, 0)])

 

vhenrykmstf_0-1658220655416.png

Refer to related document:

Odbc.DataSource - PowerQuery M | Microsoft Docs


If the problem is still not resolved, please provide detailed error information and let me know immediately. Looking forward to your reply.


Best Regards,
Henry


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

Thank you for the response, still no luck though, does seem like it's just not supported.

 

Error message is the same in the original post.

otravers
Community Champion
Community Champion

It seems the way they handle authentication is particular as I haven't been able to connect with many tools aside of the officially supported SSMS and ADS. How are you setting up authentication in your DSN? I tried the various AAD authentication options offered by the v18 ODBC SQL driver but like you I'm always getting timeouts when I'm testing the connection.

------------------------------------------------
1. How to get your question answered quickly - good questions get good answers!
2. Learning how to fish > being spoon-fed without active thinking.
3. Please accept as a solution posts that resolve your questions.
------------------------------------------------
BI Blog: Datamarts | RLS/OLS | Dev Tools | Languages | Aggregations | XMLA/APIs | Field Parameters | Custom Visuals

This is why I get confused.

Last month, I was able to access datamart in Python Notebook and there was running log to prove it.

 

yet, it doesn't work anymore when I tried to re-run the notebook this week.

 

I wonder whether it has been blocked somehow. I don't know who could confirm it tho.

We have tried all the possible DSN configurations, suspect it's just not supported. Hopefully will come in time.

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.