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

[Anonymous]

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.

[jcollinson2001 · Accepted solution]

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.

[mithun]

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 

[jcollinson2001 · Accepted solution]

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.

[Anonymous · Accepted solution]

Thanks for the information.

I entered my credentials and it is working now. 

[Anonymous]

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