How to solve the error "Not Supported in DirectQuery 1400 Mode" in AAS

Katie Tran 5

I imported data into AAS, then I changed the DirectQuery Mode is ON. With the opening file, it was okay. However, when I closed and re-opened the file, it showed the error as below. I cannot open the model or change anything.

User's image

When reopening, the properties of Model.bim's DirectQuery Mode is greyed out and "Off" as default.

User's image

I tried to change the credential in the code in Model.bim but it still had the same error.

User's image

After many attempts, I tried to delete the model and created a new one. However, I cannot delete the model with the error as well.

User's image

My questions are:

How do I solve that error in Visual Studio to recover what I have already done?
How do I delete the model in SSMS with that error to release the memory?

Thanks for your help!

PRADEEPCHEEKATLA-MSFT 85,351 Reputation points Microsoft Employee

2023-07-20T07:04:01.2+00:00

@Katie Tran - Thanks for the question and using MS Q&A platform.

From the error message it's looks like you are using Azure Synapse SQL endpoint - it should be Azure Analysis service endpoint.

Azure Synapse SQL Endpoint: synapse-ondemand.sql.azuresynapse.net

Azure Analysis services Endpoint: asazure://centralus.asazure.windows.net/AAS

The compatibility level refers to release-specific behaviors in the Analysis Services engine. For example, DirectQuery and tabular object metadata have different implementations depending on the compatibility level. In-general, you should choose the latest compatibility level supported by your platform.

For more details, refer Compatibility level for tabular models and Deploy SSDT Tabular Model (1400) with Azure Analysis Services 1500.

Hope this helps. Do let us know if you any further queries.
Katie Tran 5 Reputation points

2023-07-21T06:32:22.6266667+00:00
Thanks for your answer.

As I understand, I created Azure SQL database, then I imported the data from Azure SQL database into AAS to build the data model. Thus, it should be Azure SQL endpoint instead of AAS endpoint. After creating AAS model, I will connect Power BI to AAS model by AAS endpoint.

However, the problem is related to AAS model development (not PBI). Please note that:

I successfully built the data model with the import method (connected to Azure SQL endpoint) with Microsoft account

When I switched the model (1) from the import method with Microsoft account into Direct Query mode, it leaded to the above error and I could not change or edit the data model or change the account from Microsoft to database account. Therefore, I lost everything I have done in the model. Additionally, I could not delete the model.

After that, I tried to create a new model with the Direct Query method (connected to Azure SQL endpoint) with Database account, then it does work in the new model.

The problem is still here - the model (2) after switching from Import mode to Direct Query. I could not do any changes or delete it. Please advise me to solve the problem.
PRADEEPCHEEKATLA-MSFT 85,351 Reputation points Microsoft Employee

2023-07-24T08:38:28.3666667+00:00

@Katie Tran - I agree that this issue looks strange and I wasn't able to reproduce this issue. If you have a support plan could you please file a support ticket for deeper investigation and do share the SR# with us?
Katie Tran 5 Reputation points

2023-07-24T23:44:29.55+00:00

Thanks for your suggestion. I just opened a support ticket with support id: 2307240030005689.
PRADEEPCHEEKATLA-MSFT 85,351 Reputation points Microsoft Employee

2023-07-25T05:14:34.73+00:00

@Katie Tran - Thanks for sharing the support ticket.
Yashas Krishna 0 Reputation points

2023-09-14T15:15:20.25+00:00

@PRADEEPCHEEKATLA-MSFT Any updates on the above issue ? 'Datasource has authentication kind OAuth2. This is not supported in DirectQuery 1400 mode' I'm facing same issue
Katie Tran 5 Reputation points

2023-10-04T02:56:39.1633333+00:00

@Yashas Krishna they sent an update email as below.

"We have received an update from our Product Group.

Which you are trying to connect with MFA for DirectQurryMode 'ON' for DefaultPowerBIDataSourceVersion PowerBI_v1 it's not supported by design.

If you like to connect MFA for DirectQurryMode 'ON' needs to upgrade to DefaultPowerBIDataSourceVersion PowerBI_v3."

Hope it helps.

3 answers

Katie Tran 5 Reputation points

2023-10-04T02:58:42.7033333+00:00

Update: Microsoft team sent me an update email as below.

"We have received an update from our Product Group.

Which you are trying to connect with MFA for DirectQurryMode 'ON' for DefaultPowerBIDataSourceVersion PowerBI_v1 it's not supported by design.

If you like to connect MFA for DirectQurryMode 'ON' needs to upgrade to DefaultPowerBIDataSourceVersion PowerBI_v3."

Hope it helps.
Please sign in to rate this answer.

0 comments No comments
Sign in to comment
Katie Tran 5 Reputation points

2023-10-12T23:32:31.5633333+00:00

Update from Microsoft's Product Group.

"Unfortunately, in this scenario, it is not possible to switch back to Vertipaq cache mode (Direct Query OFF) so you might have to create a new model.

Product Group team working on the process to disable ‘Data Query Mode’ property if auth is MFA (OAuth2) to avoid such issues in future."
Please sign in to rate this answer.

0 comments No comments
Sign in to comment
Katie Tran 5 Reputation points

2023-10-17T23:00:09.8533333+00:00

Update from Microsoft's Product Group as below.

"I had discussion with my manager and product group about your concern, as per Microsoft policy if more customers are asking same kind of feature, it will be released sooner or later.

At this moment we don't have any ETA, once I have any update will keep you posted."
Please sign in to rate this answer.

0 comments No comments
Sign in to comment

Share via

How to solve the error "Not Supported in DirectQuery 1400 Mode" in AAS

3 answers