Data Source object configuration for Azure Data Explorer?

[joe-smith-powerbi]

My organization is testing out the Azure SQL Database Watcher preview, which monitors and collects DB workload data into an Azure Data Explorer database (KQL). I'm trying to set up an AAS model connection to this database, and I'm honestly not sure if this is even a supported connection. I know the Azure Data Explorer connector is not explicitly supported as a data source in TE3. As such, I started by enabling the "Use Analysis Services for change detection" setting in Preferences.

In PBI Desktop, I can establish a connection using the built-in connector. It requires specifying the cluster path and database name, and authenticates using AD Integrated. Once connected, I can browse the database, or specify a KQL or SQL query. Reviewing the M expression shows it uses the AzureDataExplorer.Contents function to pull the data source.

In TE3, I tried building a Data Source object in a new model, by exporting the .PBIDS for the data source and replicating the attributes from it for the "azure-data-explorer" protocol (with and without specifying the database and authentication details). This failed to connect at all.

After some research in MS Learn, I found you can use an Azure SQL data source connector with an explicit SQL query (ADE will apparently translate the SQL into a KQL native query on its side). (link to MS Learn doc) I've tried replicating this in TE3, and while it seems to connect to the cluster and database, it does not authenticate mya ccount. I've tried two different authentication methods; AD Integrated works fine on desktop but fails in TE3; also tried username/password, which also failed in TE3 (didn't try desktop).

My question is, has anyone had success connecting Azure Analysis Services and Azure Data Explorer? Is there a way I can do this with TE3, or am I just doing it all wrong? 😆 Thank you for any assistance!

[mlonsk]

Hi @joe-smith-powerbi

I do not believe that AAS supports connecting to Azure Data Explorer using a Legacy or Power Query Explicit data source. At least it is not documented.

To see if you can make it work in AAS you could try to create a model in Visual Studio to see if there is an inbuilt connector to be used there. You can then save the .bim file and open the model in Tabular Editor to continue working on the model. As long as the AAS engine supports the data source it will also work in Tabular Editor.

The other option you have is built your model in the Power BI service. Power BI uses implicit measures (i.e. partition defined M) to connect with. You can try and open the Power BI file where you are connected to Azure Data Explorer and launch Tabular Editor as an external tool. You will then be able to inspect the partitions and see the M code. You can copy this M code into your model built in Tabular Editor and deploy it to the Power BI/Fabric service which will then be able to import data from Azure Data Explorer into your semantic model.