Connect to Analysis services in Synapse

Mathias Opland 140 Reputation points
2023-11-06T15:09:55.11+00:00

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

Azure Synapse Analytics
Azure Synapse Analytics
An Azure analytics service that brings together data integration, enterprise data warehousing, and big data analytics. Previously known as Azure SQL Data Warehouse.
5,005 questions
{count} votes

Accepted answer
  1. Nandan Hegde 32,421 Reputation points MVP
    2023-11-06T16:05:35.4066667+00:00

    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/

    0 comments No comments

1 additional answer

Sort by: Most helpful
  1. Sedat SALMAN 14,065 Reputation points MVP
    2023-11-06T15:28:03.22+00:00

    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.

    0 comments No comments

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.