Microsoft Fabric Lakehouse & Qlik Cloud Analytics

[Jamie_T]

Has anyone managed to connect a MS Fabric Lakehouse to Qlik Cloud Analytics as a data source without going through the Data Movement Gateway and QDI (not an option at the moment for us)?

There isn't a native connector for Fabric as there is for Synapse, and there doesn't seem to be anyway to force a connection through any other way at the moment.

The closest we've got is by using ODBC (via Direct Access Gateway) as the ODBC on one of our servers can connect to the Lakehouse, but we then get an error stating invalid near 'Field Name' which stops the data preview from loading correctly and just trying to connect direct form the Load Editor doesn't even recognise the database\schema\field name as being valid

[celindho]

I managed to connect to a Fabric SQL endpoint using Microsoft ODBC driver. The test was done using Qlik Sense on Windows, not Qlik Cloud Analytics, but the result should be identical in both cases. If I remember correctly I used a service principal with the AppId as the username and a Client Secret (=Secret Value) as the password. This however requires a quite recent version of the Microsoft SQL DB ODBC driver to work. I did this just for testing purposes and have not yet had the need to make a production deployment, so i can't say anything about performance.

None the less, I'm really disappointed in Qlik not yet releasing an update to the Synapse connector to also allow for Fabric SQL endpoints. It should be a quite simple thing to remove the artificial built-in blocker of the said connector.

[zkazimov · Accepted solution]

Hello community members,

I have found the solution using ODBC Driver for SQL Server. This applies to Qlik Cloud. I use service principal account to connect to fabric. 

Steps

1. Setup VM for Qlik Direct Access Gateway: Setting up Qlik Data Gateway - Direct Access | Qlik Cloud Help

2. Install latest ODBC Driver for SQL Server:  Download ODBC Driver for SQL Server - ODBC Driver for SQL Server | Microsoft Learn

3. Setup account in Azure to read data from Fabric workspace. Any of the options mentioned in here should work: Microsoft Entra authentication in Synapse Data Warehouse - Microsoft Fabric | Microsoft Learn

4. Copy SQL endpoint from Fabric Workspace. Lakehouse or Warehouse endpoint

5. Setup ODBC connection in Qlik Cloud

5.1. Choose Direct Access Gateway you setup in step 1

5.2. Choose ODBC Driver 18 for SQL Server in Database Properties

5.3. Connection string: SERVER: <fabric endpoint from step 4>; DATABASE: <Lakehouse or Warehouse Name>; Authentication: <Method you decided in step 3. Refer to documentation for values>

5.4. Encrypted Properties: Add UID for username. Add PWD for password you created in step 3 

5.5 Database Specific Properties: Choose Custom SQL Syntax. Select Detect

5.6 Test Connection and Save

6. DO NOT ATTEMPT TO PREVIEW DATA. You will get errors

7. Insert Connection String. Example, LIB CONNECT TO 'Fabric Bronze';

8. Run queries you need.  Format [Lakehouse or Warehouse Name].[Schema Name].[Table Name]. Example. 

SELECT *
FROM [LH_Bronze].[dbo].[TMS_Load] ;

See attached screenshot for more details. 

[Jamie_T]

Thanks for this - I've now got this working against my lakehouse!

It seems a bit odd that this is the only way to do this so far as we're pulling data from the cloud, to an on-premise server before pushing it back to a cloud platform.

Qlik need to provide a connector that work natively and removes the requirement for the direct access gateway