Azure Analysis services - Data source change

Question

HI - We have Azure AS tabular model which points to On premise SQL server. As part of our cloud migration i was trying to change the Datasource to point to Synapse Analytics database. We have migrated all the on premise tables to Synapse pool with the same names. No changes to remaining structure of the cube. Our expectation is to make the existing on premise AS Model to deploy to cloud and change the Data source to refer Synapse pool and everything should work. I have created the new Data source which points to Synapse pool and changed the data source reference to existing tables. I have modified the data source reference by modifying the "Model.bim" code. However, When i am trying to validate the query its throwing an error "Object reference not set to an instance of object". Is it possible to change the Data source from On premise SQL server to Azure Synapse and deploy the model to cloud. Or i need do everything from scratch. Thanks

Answer 1

Hi @Samara Soucy - MSFT - Thanks for response. Finally it got resolved and here are the steps i followed:

1. I have enabled Legacy Data sources in Visual studio which recognize plain SQL queries since my current model tables are derived from SQL queries in Tabular model.
2. Earlier i made the connection to synapse pool using compatibility level 1500 which used Power Query model
3. after creating the Datasource using Legacy datasource i switched the connection with new connection as you mentioned in #01 in your response.
4. Finally it recognized the SQL pool and started working

I am able to get and process the data in Visual studio. However one issue i am facing while processing the cube in AS server after deployment, "No mapping between account names and security IDs was done".

I created separate thread for it- https://learn.microsoft.com/en-us/answers/questions/328058/azure-as-model-deployment-error.html

I am investigating to resolve it.

Thanks for your help!