Reply
Regular Visitor
Posts: 27
Registered: ‎03-08-2017
Accepted Solution

Direct Query not working in the October version of Power BI Server

Hi All,

 

When I try to use Direct Query mode and run a SQL query from the data base to create a report, I am getting the below error, My question is ' Is it mandatory for me to connect to the Analysis Services to connect to the database tables if I want to use Direct Query mode ?' If I use the import mode, the reprot runs fine.

 

 Please help.

DQ.jpg


Accepted Solutions
Member
Posts: 89
Registered: ‎02-26-2018

Re: Direct Query not working in the October version of Power BI Server

This looks like you haven't setup the data sources properly. If you click the ellipsis on the report tile in Report Server, and select Manage, then go to data sources. Scroll down the credentials section, you then have 2 options...

 

  • Authenticate as the user viewing the report, which requires Kerberos authentication to be setup properly - see the following https://docs.microsoft.com/en-us/power-bi/report-server/configure-kerberos-powerbi-reports
  • Manually key in credentials of an account that has permissions on your data source - this can either be a windows account (select Windows Authentication from the drop down) or a SQL server account (select Basic Authentication from the drop down)

Hope this helps.

View solution in original post

Regular Visitor
Posts: 27
Registered: ‎03-08-2017

Re: Direct Query not working in the October version of Power BI Server

Thanks for the information.

 

I entered my credentials and it is working now. Smiley Happy

View solution in original post


All Replies
Member
Posts: 89
Registered: ‎02-26-2018

Re: Direct Query not working in the October version of Power BI Server

This looks like you haven't setup the data sources properly. If you click the ellipsis on the report tile in Report Server, and select Manage, then go to data sources. Scroll down the credentials section, you then have 2 options...

 

  • Authenticate as the user viewing the report, which requires Kerberos authentication to be setup properly - see the following https://docs.microsoft.com/en-us/power-bi/report-server/configure-kerberos-powerbi-reports
  • Manually key in credentials of an account that has permissions on your data source - this can either be a windows account (select Windows Authentication from the drop down) or a SQL server account (select Basic Authentication from the drop down)

Hope this helps.

Regular Visitor
Posts: 27
Registered: ‎03-08-2017

Re: Direct Query not working in the October version of Power BI Server

Thanks for the information.

 

I entered my credentials and it is working now. Smiley Happy

Regular Visitor
Posts: 18
Registered: ‎07-28-2015

Re: Direct Query not working in the October version of Power BI Server

Well I have the exact same problem when using DirectQuery and my datasource is SQL Server. Typing in credentials does not solve it! It works fine with SSAS as a datasource when i type in my credentials.. 

 

Looks like a bug because every error message I get referes to Analysis Services!

 

Anyone got a clue?

 

TIA

 

Bobby

New Member
Posts: 1
Registered: ‎01-04-2019

Re: Direct Query not working in the October version of Power BI Server

While connecting to AS cube via direct query i face issue in dataflow... when i try to do the same with Database , i dont get any kind of error... can anyone help... following is the query...

 

let
Source = AnalysisServices.Databases("abc.de.xyz.net\xyz_CPG", [TypedMeasureColumns=true, Implementation="2.0"]),
CO_LEAN = Source{[Name="CO_LEAN"]}[Data],
#"COLOR LEAN CPG1" = CO_LEAN{[Id="COLOR LEAN CPG"]}[Data],
#"COLOR LEAN CPG2" = #"COLOR LEAN CPG1"{[Id="COLOR LEAN CPG"]}[Data],
#"Added Items" = Cube.Transform(#"COLOR LEAN CPG2",
{
{Cube.AddAndExpandDimensionColumn, "[Article]", {"[Article].[Pack Size].[Pack Size]", "[Article].[Article Material].[Article Material]", "[Article].[Article Material Number].[Article Material Number]", "[Article].[Main Article Material].[Main Article Material]", "[Article].[Main Article Material Number].[Main Article Material Number]", "[Article].[Material].[Material]", "[Article].[Material Number].[Material Number]", "[Article].[Reporting Hierarchy].[Overall Market]", "[Article].[Reporting Hierarchy].[Brand]", "[Article].[Reporting Hierarchy].[Sub Brand]", "[Article].[Reporting Hierarchy].[Market]", "[Article].[Reporting Hierarchy].[Market Segment]", "[Article].[Reporting Hierarchy].[Product]"}, {"Article.SMU Article\Attributes\Keys", "Article.Article Material", "Article.Article Material Number", "Article.Main Article Material", "Article.Main Article Material Number", "Article.Material", "Article.Material Number", "Article.Overall Market", "Article.Brand", "Article.Sub Brand", "Article.Market", "Article.Market Segment", "Article.Product"}}
})
in
#"Added Items"

 

 

error message is "Please specify how to connect"

 

Doesnt work and doesnt populate the gateway server as well