I would like to connect my SSAS multi-dimensional cube to Azure Data Factory. The purpose of this would be to copy the data from the Analysis Services cube to an Azure Storage Account. The cube contains calculated measures, otherwise, I could just use the underlying views that sit on the database on the SQL server itself.
There is no direct ADF connector that allows me to connect to the SSAS multi-dimensional cube. Could I use the ODBC connector as a work around?
I have researched online and seen that there is an SSAS ODBC driver available by cdata, but this will not be a viable option for me. I can't seem to find a Microsoft one.
Try the following workaround (Note I put this together using a bunch of searches - none of it has been proven).
Create an Azure Logic App and use the "Execute a SQL query (V2)" action with an ODBC connection to SSAS. Try the MSOLAP ODBC driver (part of the SQL Server Feature Pack) on the machine running the Logic App and configure the ODBC data source using the ODBC Data Source Administrator tool
https://learn.microsoft.com/en-us/azure/data-factory/connector-overview
In the "Execute a SQL query (V2)" action, write the MDX query to fetch the data from the SSAS cube, including wanted calculated measures. Execute the MDX query to fetch data to store the query results in a variable.
In the ADF pipeline, add a "Copy data" activity and configure the source dataset to use the output of the "Execute a SQL query (V2)" action in the Logic App. Configure the destination dataset to point to your Azure Storage Account. Finally, connect the Logic App to the ADF pipeline by adding a "Web Activity" to call the Logic App and connecting the output of the Web Activity to the "Copy data" activity
https://learn.microsoft.com/en-us/analysis-services/multidimensional-models/multidimensional-model-databases-ssas?view=asallproducts-allversions
Please note using the MSOLAP ODBC driver should work for your SSAS multi-dimensional cube - but as with all the steps above, I cannot guarantee it.