question

AP-9104 avatar image
0 Votes"
AP-9104 asked NandanHegde-7720 commented

Copy Data from vendor server SSAS cubes through Azure Data Factory

Hi, I need to copy the SSAS cubes data from our vendors server to our server through SQL server or Azure data factory.

Please suggest me the best way to do it.

Will there be any way to create the linked server for Azure Data Factory pipeline to use?


Thanks

azure-data-factoryazure-sql-databasesql-server-analysis-servicesazure-analysis-services
· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

There are some important points missing. What access do you have to the source cubes and the source server? What kinds and volumes of data do you need to get from there? Is it one-time or regular operation? What is the type of your server, how do you need the data stored there? And, since you ask for 'the best' way, what are your specific criteria for ranking the ways?

1 Vote 1 ·
AP-9104 avatar image AP-9104 AlexeiStoyanovsky ·

Hi AlexeiStoyanovsky, Thank you for the quick reply.

my answers are as below,
What access do you have to the source cubes and the source server? Read Access
What kinds and volumes of data do you need to get from there? Its a time series data with heaps of rows which are created per every 2 seconds but i believe we can summarise it before bringing.

Is it one-time or regular operation? need to retrieve every hour
What is the type of your server, how do you need the data stored there? We have SQL server on Prem also on Azure where we can store data and in tables.
what are your specific criteria for ranking the ways? Priority is to be able to make some sort of connection to automate the data pull from their server and secondly if it can be done through Azure data factory using linked server ?







0 Votes 0 ·

1 Answer

NandanHegde-7720 avatar image
0 Votes"
NandanHegde-7720 answered NandanHegde-7720 commented

Hey,
the below blog:
https://datasharkx.wordpress.com/2021/03/16/copy-data-from-ssas-aas-through-azure-data-factory/
explains how to copy data from SSAS.
Note : there is no direct connector for SSAS in ADF.

· 5
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi NandanHegde-7720,

Thank you for your reply.

Can you please help me understand if your article is suggesting to create a linked server on SSAS databases and if yes is it possible to do that on the client server and still access that linked server from our server ?

Thank you

0 Votes 0 ·

Hey,
You need a database server instance (On Prem or IaaS server) to create the linked server between the SSAS and Database instance because you would be querying the cube from the database via open row query.

Now it depends whether the client team creates the linked server in their database instance or you all create it in your instance.
Everything boils down to the network and security configuration of the servers

Note : The Linked service in ADF would be a SQL server database as there is no native connector to SSAS in ADF. So you are leveraging the database to query SSAS

0 Votes 0 ·

TThank you NandanHegde,

I will check with the clients side if they create one for us.

At the moment we are just RDP into their server to access cubes from excel file. I am not sure if we would be able to create a connection from our side.

Thanks for your help.






0 Votes 0 ·

Hi NandanHegde , If the client if offering to give us the domain login to their SQL on prem database views, would that still work to access it through ADF link service using those credentials ?

0 Votes 0 ·
Show more comments