Finance forum

PowerBI - Issue connecting with SQL database in UAT environment

(0) Share

Report

Posted on by YOG

414

Can anyone help resolve this issue please? I am trying to open available content packs from LCS.

Categories:

Implementation Lifecycle

I have the same question (0)

All responses (7)

Answers (1)

jac_rod 39 Moderator on at

Like (0)

Report

Where are you seeing this? Is this on the UAT server in Power BI desktop, or are you trying to connect from a different machine?

Was this reply helpful? Yes No
YOG 414 on at

Like (0)

Report

Hi Jacob.

I am getting same error on both UAT and my PC.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Are your hourly invoices past due for payment?

Was this reply helpful? Yes No
YOG 414 on at

Like (0)

Report

Hi Rahul.

I am getting problem connecting to SQL database as a start. Please advise

Was this reply helpful? Yes No
Suggested answer

nmaenpaa 101,162 Moderator on at

Like (0)

Report

From where are you trying to connect? What content packs are these? Is this Power BI desktop or Powerbi.com? Could you describe your scenario in more detail please. Screenshots would be nice too.

You can't connect directly to the SQL server in the D365FO cloud environments. Only embedded Power BI reports in D365FO workspaces can dot that.

Was this reply helpful? Yes No
YOG 414 on at

Like (0)

Report
Hi Nilolaos,

Please help me understand,

There are 3 ways to connect with D356FO through PowerBI

SQL Server (Currently UAT environment)

Odata feed

BYOD

Scenario - I want to sales report and found that 'CustInvoiceTrans' table gives me data I need. so I have started digging into all 3 options above to get this table

1) SQL Server - I can see this table in SQL database and trying to connect but no luck. I have been informed that Microsoft control this database

Steps I took -

Download 'Local Administrator Accounts' from LCS and Login

Open SQL Server management studio and login

AXDB database with all table is there

Open PowerBI Desktop > Get Data > SQL Server > put database name (error on this stage)

Questions :

1. can I actually connect this db?

2. also am I gone a have access of db after we go live?

Content packs - I see, we have 19 content packs in my lcs shared library and deployed file (refresh data entity) but reports are not showing any data even we have so many data in UAT. l looked all slicers and they are with correct date setup.

I have tried downloading this file and open them through BI desktop but cannot connect.

2) Odata feed - is not showing this table at all

Steps I took -

PowerBI desktop > Get data > Odata feed > '<URL>/data

3) BYOD - this is only for data entities not for table

Steps I took -

Set azure db

Config D365 for export

I hope, above make sense.

Please advise

Was this reply helpful? Yes No
Verified answer

nmaenpaa 101,162 Moderator on at

Like (0)

Report

1) You can't use direct SQL. In UAT you can access the SQL but only from the UAT VM, not outside. In production you can't access SQL at all.

2) OData exposes entities as well, not tables. The tables are for internal use only in D365FO, all external data exchange happens through the data entities.

So you have to connect using the entity name. Unfortunately it can sometimes be difficult to find out what name should be used. In Visual Studio, see what name is used in the Public collection name for the entity. That is the one that is used for OData.

About the content pack (and other) issues, I suggest that you try to search for Microsoft's documentation on how to use Power BI with D365FO.

Was this reply helpful? Yes No