Using Alteryx with Fabric

[cpshort7186]

Is anyone successfully using Alteryx in combination with Fabric?  I have struggled to get alteryx to connect to a lakehouse or data warehouse and am finding that there does not seem to be much/any support from the vendor (Alteryx) in getting this to work.  If you have decided to use alteryx in conjunction with Fabric I'd love to know:

• Are there any resources that helped you get connected?
• What were the primary reasons you choose to use alteryx over native MS tools (notebook, dataflows, etc)?
• Are there any "lessons learned" or "gotchas" you would share based on your experience that you wish you would have known ahead of implementing?

[spencer_sa]

My experiences with Alteryx and Fabric

• I have successfully read and written csv files to the Files area of a lakehouse using the Alteryx Python tool and the ADLS DFS API.  (I had initial issues using the Blob storage tool so wrote my own in python)
• I have successfully read from the SQL Endpoint using the Python tool - I've not tried just using the SQL Server connection yet - something for the future. (but as Anonymous shows, it is possible using the ODBC tools)

All of these work with both AZCLI authentication (bleh) and Service Principals (the One True way).  The SQL Endpoint connection requires the SQL Server Driver version 18? or above. (I think 17 was preview only)

• I've not yet tried reading/writing Parquet (mostly a library thing - Arrow), but that is a next step.  I wouldn't try to write a Delta table, simply because I'm pretty sure Fabric does a better job of doing V-Ordering than any python library would - I'd probably just use a Copy Data activity to move to tables.

Going the other way, I've not yet tried triggering an Alteryx Server job from with Fabric using the API, but I have got it working from the command line.  I suspect I'm going to have networking/security issues to make sure that Fabric can connect to the internal network-connected Alteryx Server.

[Fragmaticx]

@cpshort7186  i dont think here is a solution, not if you want to write to a lakehouse our dwh. BUt with the new fabric sql you can write as to any sql - with high speed 🙂 

[cpshort7186]

Yes, the new T-SQL notebooks are a step in the right direction.  Unfortunately (as far as I can tell) you can not combine Python or Spark SQL steps in a notebook with T-SQL.  The whole notebook has to be T-SQL.  This limits what I can do with it.  I view Alteryx as a prototyping tool, and will eventually move any code that needs to be scheduled into Fabric Notebooks anyways, but it would be nice if Fabric SQL endpoints worked well enough and performant enough to support prototyping.  As of now it's taking orders of magnitude longer than expected. 

[Anonymous]

Here is the process to connect using Alteryx to a my Lakehouse in MS Fabric:

Choose: MS SQL Server | OLE db

Select:  MS OLE DB Driver for SQL Server:

1. Enter your SQL data endpoint connection from Fabric as the server name.

2.  Select authenitcation type:  'Active Directory - Integrated'.

3.  Test connection, then select the database (Lakehouse name) from the dropdown.

If this works for you please accept this as a solution.  Thanks!

[lbendlin]

What part are you trying to connect to?  The SQL endpoints should be no problem for Alteryx.  The Delta Tables might require Alteryx to understand Parquet.

[cpshort7186]

I've tried connecting via "Microsoft Azure Data Lake Store" and "Microsoft Azure Synapse Analytics" and have been unsuccesful with both.  My understanding is that Data Lake would be read-only, but that in theory I should be able to read/write to a Data Warehouse artifact via Alteryx.  

[Joshrodgers123]

My first suggestion would be to give up now 🙂 I tried going down this path. Alteryx support said, "We don't support Fabric". Makes no sense as it is just SQL Server.

You can get it to work by creating an ODBC connection using the SQL Server 18 driver.

If you are using an AD login with MFA, Alteryx will prompt you at least twice on every run for your credentials and MFA.

You will also receive this error when running a query any query: 

Input Data (1) Error SQLExecute: [Microsoft][ODBC Driver 18 for SQL Server][SQL Server]DECLARE CURSOR is not supported.

According to these docs: Error: "Cursors are not supported" or "Cursor support is not an implemented feature" when running a ...

You need to add SET NOCOUNT ON to the end of all your queries.

It works, but not really to a point where it is very usable. My suggestion would be to use Fabric items to do your Alteryx work.

[cpshort7186]

Thank you!  This is the suspicion I had and it's good to hear from another practitioner about the experience.  

[lbendlin]

Now try the actual option - Microsoft Azure SQL Database.

[cpshort7186]

Unfortunately I have not been able to get this option to work either.

[Fragmaticx]

I got it working, but its slow as he......

And I dont know why.

But if you create a service principal and give it write rights on your lakehouse table - you can connect using oledb driver in alteryx with SP.

[cpshort7186]

After much back and forth with Alteryx and our IT group I was able to do the same... had to create a service principal with all rights needed to read and write to the environment, had to make sure correct version of ODBC driver was installed, had to open a port on the firewall.  But after all that I can select from lakehouse and data warehouse objects, and I can write to the warehouse.  However, I've also found writing to the warehouse extremely slow.  Workflow runs in 15sec when running without the final write action or when writing to CSV, but takes 20min to run writing to a warehouse table.  Not a huge data volume (CSV is only ~75kb).  Would love to hear if anyone has resolved this.  I'm not sure if it's an alteryx issue, an ODBC driving issue, or a fabric issue.