Line and Area chart fail over direct connection to SQL Server 2017 instance

[bcarver]

I'm not able to use the standard Line or Area Chart visuals over a direct connection to a SQL Server 2017 tabular instance. 

It seems to be the instance itself, I can use these visuals over a similar direct connection to a SQL Server 2016 cube on a 2016 tabular instance, but once I either migrate the 2016 version tabular cube to the 2017 instance (retaining that version, using a backup / restore) or when I recreate the same cube in as a 2017 version (Compatibility Level SQL Server 2017 / Azure Anaysis Services) these visuals work fine on the desktop but - when published to Power BI as a report (or pinned dashboard)  - they fail with the message "Can't display the visual", and technical details:

Couldn't load the data for this visual

Couldn't retrieve the data for this visual. Please try again later.
Please try again later or contact support. If you contact support, please provide these details.

Activity ID8d62accf-098c-73a3-3ce2-e8b9d728809d

Request IDf5ca77d2-8859-0a48-9ddb-c0c667523dc4

Correlation IDdcaf2139-57b8-cfc4-853c-29b25e002bb7

TimeWed Jan 03 2018 09:59:04 GMT-0500 (Eastern Standard Time)

Version13.0.3495.303

Cluster URIhttps://wabi-us-north-central-redirect.analysis.windows.net

I have the more detailed logging option set on the On Prem Gateway through which the data requests pass, and I see activity - but nothing I'd descibe as an error or warning. 

I'd like to convert my Power BI reports (and dashboards over them) to a new SQL Server 2017 instance, but I use the line and area chart visuals quite often. 

Thanks, Bruce

[v-qiuyu-msft]

Hi @bcarver,

1. Does the report get data from SSAS tabular 2017 only have Line or Area Chart visual can't load data? How about other visuals? 

2. After you publish the report to Power BI service, as it connects to SSAS, please check if you have configure the dataset to use on-premise data gateway. If current Power BI service signed in email address doesn't have corresponding UPN on SSAS side, please create a user mapping role in Manage Gateways.  For more information, see: https://docs.microsoft.com/en-us/power-bi/service-gateway-enterprise-manage-ssas#usernames-with-anal...

3. Please update the on-premise data gateway to the latest version. 

Best Regards,
Qiuyun Yu

[bcarver]

OK, as to question 1) I believe it’s limited to the Line and Area chart visuals. I have other types in the same report that work find (Stacked area chart, KPI, Stacked Column Chart, …). It also seems related to the data I’m requesting – one line chart pattern works fine and another fails. The failing pattern includes a Legend, if I remove it – it works.

As a detailed test I edited the report with issues in the cloud Power BI editor and changed visual types sequentially, one to the next. I only saw issues with the Line and Area Charts using that technique. (Admittedly, the data doesn’t always support such a change, but the other visuals didn’t fail outright).

As to 2) I’m using my standard O365 ID, used to publish a lot of other content successfully. I have a Power BI pro license and I’m in the Power BI Admin role. I’m signing in to Power BI as myself under that ID when I see the issues. I have a single gateway established and the problem report is using it – and the gateway shows “good to go”. I’m among the Administrators of the gateway. The report shows it’s connected to that gateway, though the refresh history shows it failing repeatedly. I can publish a second report against another data source (another cube on the save 2017 Analysis Services instance) and replicate the issue.  

As to 3) – I’m testing a newer version now – the gateway currently in use for these reports is the Nov 2017 version, I’m testing one from Dec 2018.  As soon as I get it stablized (insert lots of user errors here) I'll test the issue using that path 🙂

Thanks for your help on this,

• Bruce

[bcarver]

I've installed the newer gateway version (14.16.6549.2) and I'm now using it in place of the old version (14.16.6423.3) for this specific report & connection supporting the problem report.  But, I'm seeing the same behavior as before.  All the same visuals are failing.  I'm quite sure I have the connection using the newer gatweway - it failed as I removed the original connection, then displayed the new gateway as I reset it to use the new one.

This test accesses a 2016 version cube on a 2017 instance of Analysis Services.

Thanks, Bruce

[bcarver]

I'm still seeing this issue - and it's preventing me from using the newest version of Analysis Services Tabular (2017).

I've upgraded my tabular instance to teh most recent RTM/CU6, my gateway to 14.16.6670.1 (April 2018), validated that I'm really using this gateway and tabualr instance with my test report, edited the report in the newest desktop version (May 2018) and republished it to the Power BI Service - and I'm still getting the "Can't display the visual" message:

Couldn't load the data for this visual

Couldn't retrieve the data for this visual. Please try again later.
Please try again later or contact support. If you contact support, please provide these details.

Activity IDccb71bd2-2452-4835-90fb-44ac2d22385f

Request ID1ada8d66-d9e0-d777-513e-a837b4dd0a9f

Correlation IDf0984cf7-1c0e-414e-69cd-90dc547f0533

TimeWed May 16 2018 07:13:43 GMT-0400 (Eastern Daylight Time)

Version13.0.5248.195

Cluster URI  https://wabi-us-north-central-redirect.analysis.windows.net

And I really need to move on from SQL Server 2016 tabular to 2017 and use line and area charts.

[bcarver]

An update:  (and a fix)

I noticed that the Microsoft.PowerBI.enterpriseGateway.exe process was loading the msolap110.dll (using Process Explorer, searching for msolap with Find Handle or DLL)... the MSOLAP.5 version.

- I installed the SQL Server 2016 OLE driver for OLAP  SQL_AS_OLEDB.msi

- I unregistered the msolap110.dll using:

regsvr32 /u "C:\Program Files\Microsoft Analysis Services\AS OLEDB\110\msolap110.dll"

- I restarted the gateway and SQL Server browser

The Microsoft.PowerBI.enterpriseGateway.exe process is now showing "c:\Program Files\On-premises gateway\msolap.dll" as being loaded... and the graphic works. 

I then uninstalled the SQL Server 2016 OLE driver  and restarted services - the msolap driver from "c:\Program Files\On-premises gateway" is still in use - I didn't really need the SQL Server 2016 OLE driver .

And my broken graphic continues to display correctly in the Power BI service when using the OLE driver DLL that came with the gateway ...

[bcarver]

So - this occurred yet again, after a SQL Server 2012 patch was applied to the machine running the Gateway.  Line and area chart graphics failed to display in the Power BI cloud service - they were fine on the desktop.

Sysinternals Process Explorer showed an older Analysis Server driver in use.  Either SQL Sever 2012 SP4 or one of the subsequent updates I was testing reinstalled the older MSAS driver.

I ran the regsvr32 /u "C:\Program Files\Microsoft Analysis Services\AS OLEDB\110\msolap110.dll" command - and then performed a Gateway restart - and this immediately corrected the issue.

The Gateway now loads the "c:\Program Files\On-premises data gateway\msaolap.dll" file.

[I'll have to include this conflict in the build plan when I have a machine dedicated to the Gateway].