Can we copy data from Azure Analysis Services using Azure Data Factory?

Question

I cannot find any connector related to this and wanted to know if this is possible.

Answer 1

You also can use Azure Analysis Services REST API.

Answer 2

With Azure Function you can make a very easy c# script using

Microsoft.AnalysisServices.AdomdClient (to retrieve data) and System.Data.SqlClient (to write data)

I suggest you using SqlBulkCopy to persist the retrieved DataTable for efficiency. but really depend if you need an upsert or you just want an insert.

Azure Function has a limited timeout and you pay for consumption, because of that it is not ideal for data with more than 10 min of retrieving + writing time. With ADF you may find an exploit to this limitation scheduling the process in more than one call.

The best case scenario for Azure Function: you have a polling that get the data from the AS and write data on your DB.

I wanted to share this solution because it could be cheaper than a linked server, especially if you have already an Azure Function premium plan.

There is also another way to retrieve data that include Sql Server integration Service but I think that if you had SSIS you would probably already know.

Answer 3

By copying data from Azure analysis service , do you mean to pull the aggregated data from the OLAP system into a target store? If yes, then there is no direct connector but you can pull the data based on a linked server concept via ADF v2.

We need to create a linked server between an IaaS/On Prem/SQL MI data instance and AAS Server :

In ADF, create a linked service connecting to the database instance and query the Azure analysis service through database instance via OpenQuery :

select * from openquery(AZUREAS, 
'evaluate SUMMARIZECOLUMNS(
 ''Geography''[City],
 "My Measure", [My Measure]
)')

This is how you would be able to copy the AAS data from ADF into a destination :)

My Blog post for the same: https://datasharkx.wordpress.com/2021/03/16/copy-data-from-ssas-aas-through-azure-data-factory/