RRNetha avatar image
0 Votes"
RRNetha asked SamaraSoucy-MSFT commented

Azure Analysis services - Data source change

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

· 1
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.


I can't think of a reason why you wouldn't be able to convert to the new data source without starting over particularly since Analysis Services will treat the Synapse pool the same as a SQL Server. The error is unfortunately not specifying exactly what went wrong.

The two things I can think of offhand that could cause it to fail are an error in editing the bim file or some form of permissions issue, probably the first.

There are a couple things you can try starting with your existing model to try to track it down:

  1. Instead of creating a new data source, edit the existing one to point at Synapse and update the credentials. This does have the disadvantage of not renaming your data source but avoids having to edit every table.

  2. Use both data sources but only edit 1-2 tables and see if you still get a validation error

  3. Create a simplified model- say 1 table from your on premises server and go through the migration process with just the single table.

1 Vote 1 ·

1 Answer

RRNetha avatar image
1 Vote"
RRNetha answered SamaraSoucy-MSFT commented

Hi @SamaraSoucy-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-

I am investigating to resolve it.

Thanks for your help!

· 1
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.

I'm glad to hear you got it figured out. It looks like one of my teammates is already helping getting the account name error resolved, so hopefully you can get that worked out soon as well.

0 Votes 0 ·