locked
unable to connect through direct query mode? RRS feed

  • Question

  • I have two questions.

    1.When i am trying to change my ssas tabular model with  direct query mode its not working.Its throwing an error like this?

    Failed to save modifications to the server. Error returned: 'This edition of SQL Server Analysis Services does not support DirectQuery mode.

    What should i do?

    2.Can we connect to Azure SQL Datawarehouse as a data source with DirectQuery mode in SSAS tabular ?

    Thanks in Advance,

    Avinash 

    Friday, July 7, 2017 5:26 PM

All replies

  • Direct Query is only supported by the Enterprise Edition (see https://docs.microsoft.com/en-us/sql/analysis-services/analysis-services-features-supported-by-the-editions-of-sql-server-2016 ) this error would suggest that you are most likely running the standard edition. If you want to use Direct Query you will need to upgrade to Enterprise.

    In answer to your section question - yes Azure SQL DW is listed as a supported data source - https://docs.microsoft.com/en-us/sql/analysis-services/tabular-models/directquery-mode-ssas-tabular


    http://darren.gosbell.com - please mark correct answers

    Saturday, July 8, 2017 9:57 AM
  • Thanks for the reply Darren.

    I found that I was using Basic pricing bucket(B2) for my Azure Analysis services. And it does not support Direct Query mode. So, I created another instance in Azure Analysis services of standard edition S1 type which supports Direct query mode.

    Now we are getting this error.

    You cannot set this model to DirectQuery mode. The deployment server is running a SQL server edition that doesn't support DirectQuery.

    We are using Azure SQL Data warehouse as our Data source which supports Direct Query mode in all pricing buckets.we should not be getting this error.

    Any idea about this.I am missing out here. Or can you suggest something for using Direct query mode with Azure Datawarehouse as a source and Azure analysis service as a deployment server?

    I am creating the tabular model in ssdt in my local machine and then trying to deploy it over Azure analysis server.

    Sunday, July 9, 2017 4:38 AM
  • Hi Avinash,

    Thanks for your question.

    According to the error messages "The deployment server is running a SQL server edition that doesn't support DirectQuery" , it is the deployment server that doesn't support DirectQuery.

    You can find the deployment server in your SSDT project.In SSDT > Solution Explorer, right-click the project > Properties. Then in Deployment > Server paste the server name. See below image:

    You may need to make sure that deployment sever support DirectQuery. If you are using SSAS 2014 instance for the deployment sever, then it may not support Azure Datawarehouse as data source, only support On-premises SQL Server relational database.

    Tabular models in DirectQuery mode have some restrictions. Before switching modes, it's important to determine whether the advantages of query execution on the backend server outweigh any reduction in functionality.If you change the mode of an existing model in SQL Server Data Tools, the model designer will notify you of any features in your model that are incompatible with DirectQuery mode.For more information about this, please refer to Restrictions part in below link:
    https://docs.microsoft.com/en-us/sql/analysis-services/tabular-models/directquery-mode-ssas-tabular

    If you want Azure Datawarehouse as data source and create a SSAS Tabular in DirectQuery model, you may try to create a SSAS Tabular 1400 models. As Kay stated in below blog, it will meet all of your requirements.
    https://azure.microsoft.com/en-gb/blog/using-modern-data-sources-in-azure-analysis-services/


    Best Regards
    Willson Yuan
    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Monday, July 10, 2017 6:31 AM
  • Thank you Darren.

    I changed the Deployment server. But when I am trying to connect through Azure SQL DataWarehouse as a source with DirectQuery,It is throwing me this error.

    We’re sorry – This data provider can only be used if Microsoft .NET Full Framework 4.0 or higher is installed. You can download the Microsoft .NET Full Framework 4.0 here:

    http://go.microsoft.com/fwlink/?LinkId=243132

     

    But I am already using the latest version of Microsoft .NET Framework.Can you please suggest me what to do?

    Thanks,

    Avinash

    Monday, July 10, 2017 3:45 PM
  • The .net framework comes in multiple profiles, typically client machines will have the "client" framework installed and servers will have the "full" framework. This message probably means you only have the latest version of the client profile and you need to have the full profile installed.

    http://darren.gosbell.com - please mark correct answers

    Tuesday, July 11, 2017 1:24 AM
  • Hi Avinash,

    Thanks for your response.

    According to your description, it seems that your original issue "the deployment server is running a SQL server edition that doesn't support DirectQuery" has been resolved. Please kindly mark the helpful replies as answers. By doing so, it will benefit all community members who are facing similar issues. Your contribution is highly appreciated.

    This error means you need to install Microsoft .NET Full Framework 4.0 or higher in your local machine with the SSDT installed. Please follow the error message, go ahead, download and install it.


    Best Regards
    Willson Yuan
    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com



    Tuesday, July 11, 2017 2:09 AM
  • Hi Darren,

    I am trying to build a Tabular Model but when I try to connect Azure SQL DW in DirectQuery mode "Failed to save modifications to the server. Datasource has authentication kind OAuth2. This is not supported in DirectQuery 1400 mode"

    As per MS Docs - Direct Query is not supported with OAuth credentials.

    Need help.

    Thanks

    Ajit

    Friday, August 21, 2020 12:29 PM