Connect to Analysis services in Synapse

Question

Hi, in PowerBI i have connected to a OLAP cube database with the following query:
Source = AnalysisServices.Database(<server-url>, <database-name>)

Is there anyway to connect to the same data source in Synapse? I've tried a couple of the linked services in Synapse without luck.

Mathias

Answer 1

Hey,

unfortunately there is no direct connector to AAS as linked service within synapse/ADF but you can use a below workaround :

https://datasharkx.wordpress.com/2021/03/16/copy-data-from-ssas-aas-through-azure-data-factory/

But in case if there is an MFA issue, you would need to use MSFT fabric :

https://datasharkx.wordpress.com/2023/10/31/copy-data-from-azure-analysis-service-aas-through-microsoft-fabric/

Answer 2

Connecting to Azure Analysis Services from Azure Synapse Analytics is not directly supported in the same manner as connecting from Power BI. In Azure Synapse, you can connect to various data sources including Azure SQL Database and Synapse Analytics itself, but Azure Analysis Services is not listed among the supported data sources for direct querying or data import within Synapse Analytics.

https://learn.microsoft.com/en-us/azure/analysis-services/analysis-services-datasource

https://learn.microsoft.com/en-us/azure/analysis-services/analysis-services-datasource#:~:text=,Yes%20Yes%20%E3%80%905%E2%80%A02%E3%80%91

https://learn.microsoft.com/en-us/azure/analysis-services/analysis-services-datasource#:~:text=Analysis%20Services%20Yes%20No

However, you could potentially use Azure Analysis Services as a source for SQL Server, which in turn could be accessed by Synapse Analytics.

https://learn.microsoft.com/en-us/azure/analysis-services/analysis-services-datasource#:~:text=SQL%20Server%20Yes%20Yes%20,17%E2%80%A08%E3%80%91

To establish such a connection, you would typically use the server name in the format <protocol>://<region>/<servername>​ and you would need the proper client libraries updated.

https://learn.microsoft.com/en-us/azure/analysis-services/analysis-services-connect

For more advanced scenarios, consider using a linked server approach from SQL Server to connect to Azure Analysis Services, but note that this method requires the use of the MSOLAP client library and is not the same as connecting directly from Synapse Analytics.