[BUG]: Power BI report issue

[melmgcoe]

Describe the bug Unable to setup the Power BI report included in the latest CoE starter kit

Component (please tell us which flow or app you are experiencing issues with):

• Solution - N/A
• Version - N/A
• App or Flow - Power BI report

To Reproduce Steps to reproduce the behavior:

1. Launch Power BI desktop and sign in using a service account with systems admin access to our dedicated CoE Environment.
2. Browse to the PBIT file and enter the CDS URL for our CoE environment.
3. An error appears relating to SQL connectivity.

Expected behavior The report should load as expected. I need assistance to confirm if the error relates to a firewall connectivity issue on our side.

Screenshots Refer to the screenshot below:

[rkarthi37]

Even i am also facing the same issue. I have tried both Import and Direct Query method and both of them are throwing the same error, Could anyone please help us.?

[manuelap-msft]

Hello,

if you are using the latest version of the dashboard with the Dataverse connector, the URL has to be input in this format org24234.crm.dynamics.com without the leading https:// and trailing /.

Additionally, some customers saw this issue when TDS ports were blocked: https://docs.microsoft.com/en-us/powerapps/developer/data-platform/dataverse-sql-query#blocked-ports

Please confirm you have entered the URL in the correct format, and the relevant ports are not blocked.

Thank you Manuela

[melmgcoe]

Thanks Manuela, I’ve tried entering the URL without the https:// and trailing / like the documentation stated.

The telnet test is failing from my laptop device but passes on my VDI.

Let me try getting the report up and running on the VDI and come back to you with an update.

Regards,

@.D741BA.94B1C600] [M&G Plc Logo] @.D741BA.94B1C600] Melvin Uy Collaboration Technical Specialist Workspace Collaboration O365 @. @.> t. 02039770030 (Internal: 60030)

10 Fenchurch Avenue London EC3M 5AG @.***D741BA.94B1C600]

@.D741BA.94B1C600] @.D741BA.94B1C600] @.***D741BA.94B1C600]

From: Manuela Pichler @.> Sent: 05 May 2021 12:46 To: microsoft/powerapps-tools @.> Cc: Uy, Melvin @.>; Author @.> Subject: Re: [microsoft/powerapps-tools] [BUG]: Power BI report issue (#996)

Hello,

if you are using the latest version of the dashboard with DirectQuery, the URL has to be input in this format org24234.crm.dynamics.com without the leading https:// and trailing /.

Additionally, some customers saw this issue when TDS ports were blocked: https://docs.microsoft.com/en-us/powerapps/developer/data-platform/dataverse-sql-query#blocked-ports

Please confirm you have entered the URL in the correct format, and the relevant ports are not blocked.

Thank you Manuela

— You are receiving this because you authored the thread. Reply to this email directly, view it on GitHubhttps://github.com/microsoft/powerapps-tools/issues/996#issuecomment-832623265, or unsubscribehttps://github.com/notifications/unsubscribe-auth/AO4ITOSGGHDFUJFOJYZAEZ3TMEVWFANCNFSM44C6KTGA. This email is confidential and should not be used by anyone who is not the original intended recipient. PGDS cannot accept liability for statements made which are clearly the sender's own and not made on behalf of the company. In addition no statement should be construed as giving investment advice within or outside the United Kingdom.

PGDS (UK ONE) LIMITED, 10 Fenchurch Avenue, London EC3M 5AG. Incorporated and registered in England and Wales. Registered Office as above. Registered number 1967719.

"PGDS" is the trading name of certain subsidiaries of M&G; plc (registered in England, number 11444019), whose registered office is at 10 Fenchurch Avenue, London EC3M 5AG, some of whose subsidiaries are authorised and regulated, as applicable, by the Prudential Regulation Authority and the Financial Conduct Authority. PGDS (UK One) Limited is part of the same corporate group as the Prudential Assurance Company. The Prudential Assurance Company is a direct/indirect subsidiary of M&G; plc, a company incorporated in the United Kingdom. The Prudential Assurance Company is not affiliated in any manner with Prudential Financial, Inc, a company whose principal place of business is in the United States of America or Prudential plc, an international group incorporated in the United Kingdom.

An e-mail reply to this address may be subject to interception or monitoring for operational reasons or for lawful business practices.

[melmgcoe]

Hello, I see a different error when I run the report on my VDI:

@.***D741CF.13BAE490]

I’ve found the following article that suggests the issue is still network related:

Unable to connect Power BI Desktop to Azure SQL Da... - Microsoft Power BI Communityhttps://community.powerbi.com/t5/Desktop/Unable-to-connect-Power-BI-Desktop-to-Azure-SQL-Data-Warehouse/m-p/711654

I don’t believe this is a firewall issue though as the telnet test works to CDS URL from my VDI. Is there an authentication issue of sorts that needs to be resoved?

Regards,

@.D741CF.13BAE490] [M&G Plc Logo] @.D741CF.13BAE490] Melvin Uy Collaboration Technical Specialist Workspace Collaboration O365 @. @.> t. 02039770030 (Internal: 60030)

10 Fenchurch Avenue London EC3M 5AG @.***D741CF.13BAE490]

@.D741CF.13BAE490] @.D741CF.13BAE490] @.***D741CF.13BAE490]

From: Manuela Pichler @.> Sent: 05 May 2021 12:46 To: microsoft/powerapps-tools @.> Cc: Uy, Melvin @.>; Author @.> Subject: Re: [microsoft/powerapps-tools] [BUG]: Power BI report issue (#996)

Hello,

if you are using the latest version of the dashboard with DirectQuery, the URL has to be input in this format org24234.crm.dynamics.com without the leading https:// and trailing /.

Additionally, some customers saw this issue when TDS ports were blocked: https://docs.microsoft.com/en-us/powerapps/developer/data-platform/dataverse-sql-query#blocked-ports

Please confirm you have entered the URL in the correct format, and the relevant ports are not blocked.

Thank you Manuela

— You are receiving this because you authored the thread. Reply to this email directly, view it on GitHubhttps://github.com/microsoft/powerapps-tools/issues/996#issuecomment-832623265, or unsubscribehttps://github.com/notifications/unsubscribe-auth/AO4ITOSGGHDFUJFOJYZAEZ3TMEVWFANCNFSM44C6KTGA. This email is confidential and should not be used by anyone who is not the original intended recipient. PGDS cannot accept liability for statements made which are clearly the sender's own and not made on behalf of the company. In addition no statement should be construed as giving investment advice within or outside the United Kingdom.

PGDS (UK ONE) LIMITED, 10 Fenchurch Avenue, London EC3M 5AG. Incorporated and registered in England and Wales. Registered Office as above. Registered number 1967719.

"PGDS" is the trading name of certain subsidiaries of M&G; plc (registered in England, number 11444019), whose registered office is at 10 Fenchurch Avenue, London EC3M 5AG, some of whose subsidiaries are authorised and regulated, as applicable, by the Prudential Regulation Authority and the Financial Conduct Authority. PGDS (UK One) Limited is part of the same corporate group as the Prudential Assurance Company. The Prudential Assurance Company is a direct/indirect subsidiary of M&G; plc, a company incorporated in the United Kingdom. The Prudential Assurance Company is not affiliated in any manner with Prudential Financial, Inc, a company whose principal place of business is in the United States of America or Prudential plc, an international group incorporated in the United Kingdom.

An e-mail reply to this address may be subject to interception or monitoring for operational reasons or for lawful business practices.

[manuelap-msft]

Hello,

unfortunately screenshots that you have pasted in are not coming through via email, you would need to go to Github directly https://github.com/microsoft/powerapps-tools/issues/996 and upload them via here!

Thank you Manuela

[melmgcoe]

No worries, here's the screenshot via Github:

[melmgcoe]

Hi Manuela, can you please get back to me ASAP re the SQL error. To be honest, it looks like a generic connectivity issue so I can raise a call for the Power Platform support team's assistance if that's what you advise.

[manuelap-msft]

Hello, to me it still looks like a network / connectivity issue so yes, I would suggest raising this through regular support channels and see what they say! Thank you!

[melmgcoe]

Thanks, I've gone ahead and done that now. Is it possible to place this issue on hold until I hear back from the Power BI support team? Just in case they need any info that may need the CoE Starter Kit team's specific knowledge.

[manuelap-msft]

Of course! We'll keep the ticket open here until you hear back!

[Johannes-Vink]

I remember vaguely that if Power BI is running on Windows 7 and when TLS1.2 is not enabled, you can run vague errors connection errors. Maybe that is the case here?

[melmgcoe]

Hi Johannes, thanks for the advice. I'm running a Windows 10 machine and enabled TLS v1.2 but still see the handshake error. I've logged a call with our internal Networks team for assistance.

[hsteweri]

Having the same issue. I can rule out network problems.

[NizarFIL]

I'm having the same issue as well.

[manuelap-msft]

Hello,

we're in the process of moving the CoE Starter Kit from this repo (powerapps-tools) to it's own dedicated repo (coe-starter-kit). We are transferring issues to the new repo today, and you will receive responses from the new repo. The new repo URL is: https://github.com/microsoft/coe-starter-kit/issues

Sorry for any inconvenience this may cause Manuela

[JeneferM-MSFT]

Can you please try using this old version of the PBI while we work through the issues with the new Dataverse connector version? Feb Version of PBI

Note that you will need to enter the org url with the prefix included as shown here: https://yourcoe.crm.dynamics.com

[hsteweri]

I'm getting "Access to ressource denied" when using the old version of the PBI

[JeneferM-MSFT]

Hello you need to be an admin on the envt in order to view he tables. Please be sure you are logged in to Power BI with the identity which setup the coe (or which has access to the envt)

[melmgcoe]

Hello, I'm able to get the PBI dashboard report working using the February release. So the issue appears to be with the April release and the change to direct query as Manuela mentioned. Please let me know once the issue has been resolved and a new PBI available to download.

[manuelap-msft]

Hello,

there won't be a bug fix, as this is not an issue with the CoE Starter Kit Power BI Dashboard and we have many customers using the Dataverse connector version.

There are two different ways to connect to Dataverse from Power BI: Dataverse: This connector is the most recent version and uses the tabular data stream (TDS) protocol. Common Data Service (Legacy): This is the earlier version of the connector.

As you can see, these are fundamentally different ways of connecting to the data - it's not that the April version of our report has a bug and the February one does not, they just use different ways of connecting to the data.

Dataverse / TDS endpoint has certain pre-requisites which are listed here: https://docs.microsoft.com/en-us/powerapps/maker/data-platform/data-platform-powerbi-connector

Going forward because many customers are struggling with the pre-requisites, we will make two version of the Power BI dashboard available.

[melmgcoe]

HI Manuela, thanks that's very useful info to know. I'll mention the security aspect to my team and see if it's worth looking into getting the pre-reqs in place to get the direct query version working.

[rkarthi37]

Thanks for your tips Manuela. On my side when i pasted the URL without https:// and / that connectivity error got resolved. But now I have few more errors. I have recently deployed COE Core 2.0 and ran all the sync flows and tried to refresh the dashboard. Error details are below RPA Sessions Cannot convert value '' of type Text to type Date. Audit Log OLE DB or ODBC error: [DataSource.Error] Microsoft SQL: Return records size cannot exceed 83886080. Make sure to filter result set to tailor it to your report. RequestId: TDS;582c36cf-41df-47e2-9246-f764515d857d;2 Time: 2021-05-25T01:07:48.4225420Z. App OLE DB or ODBC error: Exception from HRESULT: 0x80040E4E. Flow OLE DB or ODBC error: Exception from HRESULT: 0x80040E4E. Maker OLE DB or ODBC error: Exception from HRESULT: 0x80040E4E. Flow Connection Reference OLE DB or ODBC error: Exception from HRESULT: 0x80040E4E.

Could you please help me to fix this errors.

[manuelap-msft]

Hello @rkarthi37,

Unfortunately you are hitting a known limitation of the TDS endpoint, which only supports 80 MB maximum size limit. https://docs.microsoft.com/en-us/powerapps/developer/data-platform/dataverse-sql-query#limitations

As your table exceeds that, you will have to use the Common Data Service (Legacy) connector. Dataverse connector won't work for you. You can download the Power BI Template that uses Import here - please note, you will have to enter the Org Url in https://yourorg.crm.dynamics.com format again: https://github.com/microsoft/powerapps-tools/blob/master/Administration/CoEStarterKit/Individual%20Components/Dashboard-PowerPlatformAdminDashboard-2021-02-02%20(1).pbit

[rkarthi37]

If we use Import based Power BI Template, will there be a support in future also or are there any plan to discontinue this template and planning to support only Direct based Query based template..?

Import Power BI Template works fine without any error for us.

Regards, Karthik

From: Manuela Pichler @.> Sent: Tuesday, May 25, 2021 1:09 PM To: microsoft/coe-starter-kit @.> Cc: Rajendran Karthikeyan RajendranKarthikeyan@JohnDeere.com; Mention @.***> Subject: Re: [microsoft/coe-starter-kit] [BUG]: Power BI report issue (#341)

[EXTERNAL]

Hello @rkarthi37https://github.com/rkarthi37,

Unfortunately you are hitting a known limitation of the TDS endpoint, which only supports 80 MB maximum size limit. https://docs.microsoft.com/en-us/powerapps/developer/data-platform/dataverse-sql-query#limitations

As your table exceeds that, you will have to use the Import connectivity mode. DirectQuery won't work for you. You can download the Power BI Template that uses Import here - please note, you will have to enter the Org Url in https://yourorg.crm.dynamics.com format again: https://github.com/microsoft/powerapps-tools/blob/master/Administration/CoEStarterKit/Individual%20Components/Dashboard-PowerPlatformAdminDashboard-2021-02-02%20(1).pbit

— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHubhttps://github.com/microsoft/coe-starter-kit/issues/341#issuecomment-848056895, or unsubscribehttps://github.com/notifications/unsubscribe-auth/ARRWF55N6CZ3LXKOXNU76VLTPPKT5ANCNFSM442GE7LA. [EXTERNAL EMAIL]: This message was generated from an external source. Do not follow guidance, click links, or open attachments unless you recognize the sender and know the content is safe.

[manuelap-msft]

There are no plans to discontinue support for Common Data Service (legacy) - actually the opposite is true, see our roadmap item here https://github.com/microsoft/coe-starter-kit/issues/477

[rkarthi37]

Thanks a lot for clarifying it quickly.

Regards, Karthik

From: Manuela Pichler @.> Sent: Tuesday, May 25, 2021 2:56 PM To: microsoft/coe-starter-kit @.> Cc: Rajendran Karthikeyan RajendranKarthikeyan@JohnDeere.com; Mention @.***> Subject: Re: [microsoft/coe-starter-kit] [BUG]: Power BI report issue (#341)

[EXTERNAL]

There are no plans to discontinue support for Import - actually the opposite is true, see our roadmap item here #477https://github.com/microsoft/coe-starter-kit/issues/477

— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHubhttps://github.com/microsoft/coe-starter-kit/issues/341#issuecomment-848176603, or unsubscribehttps://github.com/notifications/unsubscribe-auth/ARRWF53UHZLLJBCHNWAM7RDTPPXD7ANCNFSM442GE7LA. [EXTERNAL EMAIL]: This message was generated from an external source. Do not follow guidance, click links, or open attachments unless you recognize the sender and know the content is safe.

[NizarFIL]

I was able to get the report to work after reaching out to our Firewall team and opening port 1433.

[manuelap-msft]

Hello,

our documentation was now updated with this troubleshooting guide, and the release from today includes the two Power BI files https://docs.microsoft.com/en-us/power-platform/guidance/coe/setup-powerbi#troubleshooting https://docs.microsoft.com/en-us/power-platform/guidance/coe/setup-powerbi#which-dashboard-to-use

Closing out this issue.

Thank you Manuela