PowerBI.com Service OData-download issue: "An existing connection was forcibly closed by the remote host."

[forums]

Bij het regelmatig en automatisch ophalen van gegevens met behulp van de PowerBI.com Service treden incidenteel fouten op. Dit topic behandelt de situatie voor zover bekend. Deze problemen zijn specifiek voorbehouden aan PowerBI.com; het downloaden via Power BI Desktop en Azure Data Factory kent deze problemen niet.

Wanneer van toepassing?

Beantwoord de volgende vragen om te beoordelen of dit topic van toepassing is:

• Treedt het downloadprobleem op na 10 minuten en enkele seconden? Zo ja, dan is dit topic niet van toepassing en volg de instructies op Vermijd time-out fout bij Power BI OData download.
• Treedt het downloadprobleem ook op vanuit Power BI Desktop? Zo ja, dan is dit topic niet van toepassing. Zoek onder andere topics of maak een nieuw topic.
• Staat er incidenteel of vaak in de refresh history van scheduled downloads “Status:Failed, Message: There was an error when processing the data in the dataset.”? Zo nee, dan is dit topic niet van toepassing. Zoek onder andere topics of maak een nieuw topic.
• Treedt het downloadprobleem op terwijl er in Bridge Online Monitoring een HTTP-status “200 OK” laat zien bij het verzoek of treedt het downloadprobleem op na 5 minuten en enkele seconden? Zo nee, dan is dit topic niet van toepassing. Zoek onder andere topics of maak een nieuw topic.
• Gebruikt u al een PPPU (Power BI Premium Per User)-abonnement? Zo nee, gelieve dan eerst te upgraden naar PPPU en nogmaals te proberen.

Situatie

Er zijn momenteel twee problemen die incidenteel optreden bij het downloaden van gegevens via de PowerBI.com service. Deze problemen treden niet op bij het gebruik van Power BI Desktop en Azure Data Factory.

Probleem 1: Telkens opnieuw downloaden ondanks succesvol opgehaald

Downloads vanuit de PowerBI.com Service eindigen incidenteel met een foutmelding, zoals:

Data source error
OData:
Unable to read data from the transport connection:
An existing connection was forcible closed by the remote host.
DataSourceKind = OData
DataSourcePath = https://bridge-online.cloud/naam/odata4/Twinfield.Twinfield.GeneralLedgerDetailsV3@tfd
OData: Unable to read data from the transport connection:
An existing connection was forcibly closed by the remote host.
The exception was raised by the IDataReader interface.
Please review the error message and provider documentation for further information and corrective action.
Cluster URI: WABI-NORTH-EUROPE-redirect.analysis.windows.net
Activity ID: f2282333-c403-4b78-9362-b10e081cd568
Request ID: 7b3d9747-b8d1-9c4f-1b67-1feb49d1779f
Time: 2022-09-27 04:50:57Z

De essentie is de tekst “An existing connection was forcible closed by the remote host.”, de andere foutmelding over IDataReader is nikszeggend.

Binnen Invantive Bridge Online Monitoring is te zien op het verzoek dat de data netjes teruggegeven is en door Power BI opgehaald (HTTP-status 200 OK).

Dat de data succesvol en volledig is overgedragen aan PowerBI.com komt ook in Invantive’s eigen tracelogging naar voren zoals (deze is niet extern zichtbaar):

itgenboe195
In total returned 996,563 rows from query on ‘Invantive.Twinfield.Twinfield.GeneralLedgerDetailsV3@tfd’, approximate size 1,666,690,366 bytes.

Het tijdsverschil tussen het registreren van een foutmelding op PowerBI.com en het beëindigen van de download op Invantive Bridge Online is bij dit probleem langer dan enkele seconden, bijvoorbeeld 1 of 2 minuten.

Analyse en Adviezen Probleem 1: Telkens opnieuw downloaden ondanks succesvol opgehaald

Hypothese is dat de PowerBI.com Service (incidenteel) crasht op het datavolume en dat dat vaker gebeurt bij grotere volumes.

We proberen hier beter de vinger achter te krijgen en een workaround te bieden, maar tot dusver komen we niet verder dan steeds sterker de conclusie te kunnen trekken dat dit een bug in PowerBI.com is, waarbij de foutmelding verwarrend is.

Daarom enkele suggesties:

• Splits de download op in brokken met een lager volume en voeg die later samen.
• Dien een ticket in binnen het Power BI Premium-abonnement.
• Geef een upvote te geven aan de betrokken ideeën (Power BI verbetersuggesties).
• Controleer dat de optimalisatie ODataVersion=4, OmitValues=ODataOmitValues.Nulls ingeschakeld is zoals beschreven in Snellere OData downloads met kleinere omvang met kleine aanpassing in Power BI en Power BI Service: The timeout period elapsed prior to completion of the operation & andere foutmeldingen - #2 door forums. Op Twinfield scheelt dit meer dan 90% qua belasting van het netwerk en mogelijk ook voor de software van PowerBI.com.
• Vervang PowerBI.com door bijvoorbeeld Azure Data Factory of create or replace tabel@eigendatabase as select * from tabel@alias.

Probleem 2: Na circa 5 minuten verdwijnt PowerBI.com Service als HTTP-client (met HTTP2 RST_STREAM bericht)

Downloads vanuit de PowerBI.com Service eindigen incidenteel met een foutmelding zoals:

Data source error
OData:
Unable to read data from the transport connection:
An existing connection was forcible closed by the remote host.
DataSourceKind = OData

Bij Invantive Bridge Online Monitoring staat de volgende melding in samenhang met een HTTP-statuscode 499 (“Client Closed Request”):

itgenboe161
De gegevensdownload werd geannuleerd na 5 minuten, 33 seconden, waarschijnlijk door de gebruiker.

zoals:

Blijkens de detailregistratie van een verzoek trad ook een itgeniee011 op, waardoor de uitvoering afgebroken was:

Of zoals zichtbaar in de interne logging:

code ‘itgenpmr003’: The execution was cancelled.

waar uit de code itgenpmr003 blijkt dat de HTTP-client (PowerBI.com) de verbinding verbroken heeft.

Anders dan bij het andere probleem, start PowerBI.com geen tweede of volgende downloadpoging, waardoor de logging op PowerBI.com een fout aangeeft.

Analyse en Adviezen Probleem 2: Na circa 5 minuten verdwijnt PowerBI.com Service als HTTP-client (met HTTP2 RST_STREAM bericht)

Door het ontbreken van een automatische herstart betekent het optreden van probleem 2 dat de download definitief gefaald is. De oorzaak is onduidelijk; het enige bijzondere is dat de download altijd na 5 minuten plus aantal seconden stopt als hij stopt.

De enige momenteel bekende workaround is de download nogmaals opstarten vanuit PowerBI.com.

Hoe verder?

Deze twee problemen zijn uitermate vervelend en aanhoudend; we krijgen de vingers er niet achter. Het gaat voor zover wij kunnen overzien om twee problemen in PowerBI.com:

1. Een incidentele crash na afronding van het ophalen (HTTP status 200 OK), waarna enige tijd (minuut) later PowerBI.com lijkt te crashen en opnieuw downloadt.
2. Een incidentele crash waarbij PowerBI.com als OData-consumer gedurende het ophalen opeens plotsklaps weg is (stuurt een HTTP2 RST_STREAM, toont bij monitoring als HTTP status 499), opvallend vaak na 5 minuten en enkele seconden. Er vindt geen hernieuwde poging plaats.

Het eerste probleem is niet meetbaar binnen Invantive Cloud, want de data is al aangeboden en volledig afgehaald.

Van het tweede probleem is wel een bovengrens zichtbaar, maar met alleen filteren op 499 of RST_STREAM wordt ook veel end-user activiteit meegenomen (download annuleren of wegnavigeren). De schatting van het tweede probleem betreft enkele promille van alle downloads.

Buiten PowerBI.com treden deze problemen voor zover bekend niet op. Helaas kent PowerBI.com naast deze twee problemen ook een serieus hiaat aan herleidbaarheid en controleerbaarheid, waardoor het analyseren nog lastiger is.

Het afgelopen jaar is duidelijker geworden voor Invantive dat deze twee problemen niet aan Invantive Cloud liggen, maar het is niet gelukt een reproduceerbaar scenario te realiseren. Duizenden tests zijn uitgevoerd zonder storingen op testdata.

Op dit moment proberen we uit te zoeken hoe deze problemen het meest effectief bij Microsoft aangekaart kunnen worden, ondanks het feit dat ze incidenteel optreden.

[khk]

Na wat speurwerk blijkt dat OData Feed niet geschikt is voor grote hoeveelheden data. Een alternatief volgens microsoft is het connecten als website. Is dat mogelijk met Invantive?

[forums]

Heb je een bron waar staat dat de OData-feed niet geschikt is?

Wat is “connecten als website”?

[khk]

Dit is de website. Verder heb ik het issue gedeeld met Microsoft en zij bevelen ook aan om te connecten als Web i.p.v. OData.

Optimising OData Refresh Performance in Power Query for Power BI and Excel - BI Insight

Nog een interessant topic: OData performance issues - Microsoft Power BI Community

Overigens ben ik geen expert, maar het is wel opvallend dat er meer over wordt gezegd.

[forums]

Is de tekst aan te geven waar in het artikel staat dat het er niet voor geschikt is en de bijbehorende Microsoft bron? Voor zover te overzien is dit een mening van een non-Microsoft medewerker. Of is het mogelijk om de correspondentie met Microsoft te ontvangen?

[khk]

Some of the M functions like PromoteHeaders will generate additional calls for this type of data source, so to avoid requesting each file more than once, you can buffer the file binary content into memory, for example if you are using Web.Contents, below changes can be tested:

• Before: Source = Csv.Document(Web.Contents(“[https…csv”),[Delimiter=“,”]https:/.csv) , Columns=80, Encoding=1252, QuoteStyle=QuoteStyle.None]),
• After Source = Csv.Document(Binary.Buffer(Web.Contents(“[https…csv”)),Delimiter=“,” , Columns=80, Encoding=1252, QuoteStyle=QuoteStyle.None]),

Another example when using Sharepoint folder connector:

• Before: Source = SharePoint.Files(ParameterSource, [ApiVersion = 15]), #“Filtered Rows” = Table.SelectRows(Source, each ([Folder Path] = H_FolderPath)), Navigation1 = #“Filtered Rows”{0}[Content]
• After: Source = SharePoint.Files(ParameterSource, [ApiVersion = 15]), #“Filtered Rows” = Table.SelectRows(Source, each ([Folder Path] = H_FolderPath)), Navigation1 = Binary.Buffer(#“Filtered Rows”{0}[Content])

You can also try to connect to Odata using web as connector in Power Bi Desktop.

Also please check if there is any firewall stopping from datasource end.

Dit is een deel van de communicatie, maar de oplossing is nog niet gevonden. En een reactie op de foutmelding evenmin. Kortom het contact met Microsoft wordt vervolgd.

[forums]

M.b.t. het artikel https://www.biinsight.com/optimising-odata-refresh-performance-in-power-query-for-power-bi-and-excel/: het gaat ons er om om duidelijk te krijgen of Microsoft de verbindingsproblemen beschouwt als hetzij een bug hetzij “by design”.

Het is zeker dat er tenminste twee problemen in de huidige implementatie van de OData-feed op PowerBI.com zitten (op Power BI Desktop, Power Query en Azure Data Factory treden ze niet op). Het is ons tot op heden nog niet gelukt om met de juiste personen binnen Microsoft hierover in contact te komen.

Als een Microsoft-bron aangeeft dat er een grens is (welke grens dan ook), dan kan hierop ingespeeld worden. Anders zijn het bugs, hetgeen twee totaal verschillende processen met zich mee brengt.

Volledigheidshalve: de OData-implementatie van Invantive bevat de in het artikel genoemde uitdagingen niet. Het ontwerp is zo gemaakt dat ook tientallen gigabytes aan data conform OData-specificaties binnen enkele seconden uitgeleverd kunnen worden als zogenaamde “OData producer”. De zogenaamde “OData consumer” (Power BI Desktop, Power Query, Azure Data Factory en hier PowerBI.com) zou dit ook moeten kunnen verwerken.

Is het mogelijk om de bron van deze quote te weten? De tekst is niet via Google te vinden en heeft ook geen betrekking op de Invantive connector.

@khk

Some of the M functions like PromoteHeaders will generate additional calls for this type of data source

[khk]

De bron van deze quote is van microsoft support.

Wat betreft OData. Ik kan niet beoordelen of de OData beperkingen gelden bij Invantive, maar zoals ik het lees is het wel een algemeen probleem. Bovendien hebben wij bij een andere applicatie die niet via Invantive wordt ontsloten precies dezelfde foutmelding ook met OData.

Daarom ben ik benieuwd of i.p.v. OData de connectie via web.content zou kunnen helpen.

[forums]

Ja, grote vraag n.a.v. bovenstaande is inderdaad of het een “probleem” is of een “by design”. Als het een probleem is, dan kan in samenwerking met Microsoft aan bugfixes voor PowerBI.com gewerkt worden.

Als workaround zijn ook andere protocollen beschikbaar, maar die missen een aantal features.

Voor duidelijkheid: met web.content wordt bedoeld: Web.Contents - PowerQuery M | Microsoft Learn ?

[khk]

Het gaat inderdaad om Web.Contents waar de link naar verwijst. Zojuist aan Microsoft gevraagd waarom zij aanbevelen om Web.Contents te gebruiken in plaats van OData.Feed.

[forums]

Dank voor bevestiging. Deze mogelijkheid voor het gebruik van web.contents is standaard reeds aanwezig en wordt bijvoorbeeld gebruik door Qlik-gebruikers. Zie bijvoorbeeld Laad Exact Online in Qlik Sense en Tableau.

Op Qlik werkt dit goed in het JSON-formaat, ook met grote volumes, maar het is onbekend of de PowerBI Service ook grote volumes betrouwbaar kan verwerken.

Net zoals bij de OData-feed is er sprake van streaming data, maar de blokgrootte is minder fijnkorrelig.

Een eenvoudige oplossing is de OData-feed binnenhalen via web.contents. OData antwoorden zijn bij Invantive Bridge Online niet meer dan JSON-array met wat versierselen en dan is snel vast te stellen of enkele gigabytes zich via web.contents in jullie scenario zich wel betrouwbaar laten verwerken. De URL inclusief filters is snel terug te vinden via Bridge Online Monitoring.

[khk]

Het gaat dan om de URL die te vinden is onder Pad in volgende scherm:

[khk]

Is dan met bovenstaande URL het niet meer nodig om een JSON applicatie aan te maken zoals toegelicht in: Laad Exact Online in Qlik Sense en Tableau - invantive?

Of is de JSON applicatie technisch anders dan de URL?

[khk]

Via de URL met JSON werkt de download goed, maar ik krijg nu een time out error bij circa 1 minuut en 40 sec (100ms). Moet dat apart worden ingesteld?

[forums]

De OData-request kan ook gebruikt worden. Een maatwerk JSON-applicatie biedt meer mogelijkheden.

[forums]

Gezien de consistente en vrij nauwkeurige time-out na 100 seconden, is dit de standaard waarde zoals beschreven op Web.Contents - PowerQuery M | Microsoft Learn. Advies is om de waarde serieus te verhogen in de Advanced Editor.

[khk]

Klopt, ik heb inderdaad de timeout aangepast op web.contents. Interessant genoeg kan nu alle data [in OData-formaat] worden opgehaald zonder enige foutmelding in Power BI Desktop en Power BI Online/Service. Zelfs de geplande vernieuwing verloopt consequent zonder fouten. Het verversen duurt alleen circa 20-30 minuten.

[khk]

We willen graag een mogelijkheid die we breder kunnen toepassen. Is dan de instructie van hierboven relevant of wat is de beste optie?

[forums]

Fijn te horen dat dezelfde OData-feed wel via web.contents verwerkt kan worden, terwijl het via OData-feed een foutmelding optreedt. Dit onderbouwt dat er sprake is van een issue in de OData-verwerking van Power BI, maar is nog geen duurzame oplossing.

[forums]

Het is lastig te beoordelen of een bredere toepassing van web.contents verstandig is. Het is “uncharted territory” zeg maar.

Is het mogelijk om de gebruikte code als alternatief voor OData hier te delen? Ik laat het een developer dan bekijken op te verwachten problemen en/of risico’s. Daarnaast is het prettig als het ticketnummer bij Microsoft vermeld kan worden; er loopt inmiddels een paralleltraject.