Connecting Azure Analysis Services to an Azure SQL Database in real-time.

Question

"Hello, I'd like to inquire about connecting Azure Analysis Services to an Azure SQL Database in real-time.

Could you provide guidance on establishing a live connection between Azure Analysis Services and Azure SQL Database? Additionally, I'm interested in understanding the steps to create an API for Azure Analysis Services."

Answer 1

First, navigate to the Azure portal where you need to reate or select an existing Azure Analysis Services (AAS) instance.In your AAS model, when defining your data source, select Azure SQL Database as the data source.

Then, input the necessary connection details: the server name, database name, authentication method, and credentials.

Don't forget to verify that the connection setting is set to "DirectQuery" since this allows for real-time querying of the data, rather than importing it.

When it comes to configuring the network, you need to verify that the AAS instance and Azure SQL Database can communicate. You may need also to verify your firewalls and VNet configurations to allow traffic between the services.

Now comes the part of creating the API. First, create an instance of Azure API Management Service from the Azure portal. Within Azure API Management, define a new API. Set the Web service URL to your Azure Analysis Services endpoint. This will be something like https://<Your-AAS-name>.asazure.windows.net

Then, set up authentication to your AAS using the required method. You can define various operations like POST for sending DAX queries to AAS.

How to consume the API ? It's easy : you can define for example a POST for sending DAX queries to AAS and use the Azure portal Test feature to test the API operations you defined.

Please test and get back to us.