Connecting to OneLake vs SQL Endpoint in Desktop

[GlassShark1]

Our org is in considering a move to Fabric and i've been experimenting with a few things.

Up until recently - i could have sworn i had two options for connecting to a Lakehouse - I could go something like:

Get Data | MS Fabric | Lakehouse and it would give me options for OneLake or the SQL Endpoint.

It seems like now if i go the same route i only have a button to 'Connect', and this always goes the SQL path (i'm admin for the workspace)

Others have noticed that if they do options for connecting (it seems temperamental?) if they select OneLake it seems to revert to SQL Endpoint anyway, as you can see the Endpoint string as it's loading. Others have said they can choose to connect via OneLake but then get an error message that they don't have permissions for XLMA Endpoints (which i understand OneLake uses in the background)?

Any advice appreciated - i'm getting a little lost with it.

For team level workspaces - should we be encouraging connection through one route or another?

[GlassShark1 · Accepted solution]

So after investigating, i've come to the conclusion this is due to a PBI update and a change to how MS wants OneLake connections to be handled. I'm not sure it's the answer, so will wait for confirmation. I noticed that different people in our org were using different versions of PBI, and the new behaviour seems to be that OneLake connections want you to write a semantic model and connect through that. We also seemed to have an issue where some users didn't have permission to read/write XLMA endpoints, which was giving an error. I think it was essentially a combination of those 2 things.

Before, i think you could connect through OneLake and bring in whatever tables you wanted and tables from other sources too. Now:

1) Get Data | LH | Connect (via OneLake) prompts you to create a model with a live connection to service (you give it a name). You select your tables from the LH and create. You then work in Power Query almost as usual. There is no Report or Data sections anymore at all though - just the modelling view

2) Your PBIX file though is essentially gone once you create the model. You can't save the pbix file itself or bring in data from other sources, do reports or anything like that - you have your model with a live connection and that's it - you're just dealing with the SM alone. It basically creates the SM straight in service instead of in desktop. 

3) It seems pretty simple from that point onwards - it's just essentially uses Desktop as an extension to write to service instead of a pbix file.

4) Once it's in service, you can pull the model into desktop and choose to connect or edit the model from there. Once you've pulled it into PBIX, you're then able to add other sources again but you'll be prompted to change the connection to Direct Query mode (perm change).

Anyway i think that's it - the new behaviour needs XLMA read/write permissions, and users using older versions of desktop will experience different behaviour. Feel free to correct me if that's not quite right.

Thanks

[v-nmadadi-msft]

Hi @GlassShark1 ,

Yes, you are correct. Connecting via OneLake allows to live edit a semantic model, where you are effectively creating and live-editing a semantic model directly in the Service rather than building a traditional standalone PBIX dataset. In this workflow, Power BI Desktop acts as a client for authoring the model, and any changes you make are written back to the Service.

I hope this information helps. Please do let us know if you have any further queries.
Thank you

[v-nmadadi-msft]

Hi @GlassShark1 

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions.

Thank you.

[v-nmadadi-msft]

Hi  @GlassShark1 ,
Thanks for reaching out to the Microsoft Fabric Community forum.

The option to toggle between OneLake and SQL endpoint is still available, make sure you are using the latest version of the Power BI Desktop.

Additionally make sure these Prerequisites are being met, OneLake integration for semantic models is supported on Power BI Premium P and Microsoft Fabric F SKUs only. It's not supported on Power BI Pro, Premium Per User, or Power BI Embedded A/EM SKUs.

Also, before enabling OneLake integration, you must have one or more import semantic models in a workspace on a Power BI Premium or Fabric capacity. Import semantic model is a type of data model where data is fully imported into Power BI's in-memory storage, allowing fast and efficient querying.

Finally, In your semantic model settings, expand OneLake integration, click the slider to On, and then select Apply. And in Admin portal, make sure this setting is turned on, Semantic models can export data to OneLake - enables semantic models configured for OneLake integration to send import tables to OneLake. Applies to the entire organization. 

I hope this information helps. Please do let us know if you have any further queries.
Thank you

[DanieleUgoCopp]

Hello,

I would standardise on the SQL Endpoint for team workspaces and make that the recommended path for Power BI Desktop development and avoids unnecessary permission complications, and consider OneLake access where there is a clear technical need for file level or Spark based work.

Best regards,
Daniele