[Feature Request]: MS Fabric Data Warehouse / Azure SQL Connection

[asjones987]

Description

Add the ability for JASP to connect to an MS Fabric Warehouse / Lakehouse SQL Endpoint

Purpose

No response

Use-case

Connectnig to a company warehouse would allow for keeing data in one main spot.

Is your feature request related to a problem?

No response

Is your feature request related to a JASP module?

No response

Describe the solution you would like

No response

Describe alternatives that you have considered

No response

Additional context

I saw back in JASP 0.16.4 there was an option added to connect directly to a SQL Database. However that looked to be something on the same network (on-premises SQL Server). With the growing use of things like Azure or really in my case Microsoft Fabric has there been any discussion about connecting directly to MS Fabric SQL Endpoint? I can see connecting to an online Data Warehouse, Lakehouse, or SQL Server via SQL Server Endpoint would be very helpful.

We use MS Fabric Dataflows Gen2 to land survey stuff into an MS Fabric Warehouse. The MS Fabric Warehouse has a SQL endpoint. SQL View or other tools could transform the data as needed and JASP could connect for statistical needs in a nice GUI environment.

Tools like Asure Data Studio (ADS) and the MS SQL Extension for VS Code are all open source and might provide a cross platform framework for how to connect to the online SQL endpoints.

You all already support connecting to the Open Science Framework so I know connecting to Online Sources is not out of the question. Hopefully it is something you all could implement as more systems move online.

[boutinb]

Hi @asjones987, I see here https://learn.microsoft.com/en-us/fabric/data-warehouse/connectivity that it is possible to connect via ODBC. We support ODBC, did you try it?

[JorisGoosen]

@boutinb there are some issues before with connecting via SQL.

Im hoping to get to those issues too before the upcoming release...

@asjones987 Im assuming you can already connect to the azure sql endpoint from a local client somehow?
Given that you mention using visual studio code.

The ODBC client should indeed already support connecting to an online database.
So would indeed be good to know if you tried.

Testing this on our end would be a bit hard though, given that we dont have this azure stuff running to test with.

[asjones987]

Thanks for the follow up.

@boutinb I did not realize that the newest OBDC driver supported connecting to an online source. That is good to know.
Interesting as all the Fabric training and videos show direct connection using using SSMS, Azure Data Studio, or VS Code with the MS SQL Extension (as Azure Data Studio is going away in a year). These all connect directly so with Azure Data Studio/ VS Code with MS SQL Extension you can use it on Mac/Linux the same way no drivers. Was trying to easily support my Mac people.

The OBDC driver is for Windows only and version 18 or higher.

Currently i use Azure Data Studio https://github.com/microsoft/azuredatastudio and connect directly to my warehouse. Then connect to a view I have built. Get the data, save as CSV then import into JASP. Screenshot below of an Azure Data Studio Connection to Fabric type setup.

I am wondering if some of the developers of JASP work for a University or other group that already has a MS Fabric License. It would no be hard to add an extra testing workspace. For something like this.

I truly appreciate you all looking into this.

[JorisGoosen]

Well we do indeed work for a university that runs some microsoft stuff, at least for the forseeable future.
I figure I could ask someone at IT if they have something like this up and running.

Im not sure when I can get to this though, because there are some other issues with connecting to different databases as well.
But the problem is the connections are ll made through qt, and depend heavily on thirdparty libraries. Which makes it a bit of a pain to test and run. (IE I have to set up a lot of databases)

And we are also working hard on upgrading some datawrangling within JASP for the upcoming release and we do not have the biggest programming team Im afraid...

But when I get to this it would definitely be a good idea to talk to IT and see if they have something like this somewhere.
Im on a mac as well so if I get it working it will probably work for your mac people.

[JorisGoosen]

What might be good to know is that our database interface is basically a very thin wrapper around: https://doc.qt.io/qt-6/qtsql-index.html so any documentation at qt on the various possible plugins needed or arguments are basically valid for JASP