Ik ben vanmiddag tegen dit probleem aangelopen, het lijkt erop alsof het nog niet (volledig) is opgelost.
Wat is de huidige status van het onderzoek?
De voornaamste oorzaak van het resterende incident was een beperking binnen Power BI zelf. Sindsdien zijn er geen klachten meer binnengekomen, maar dat sluit niet uit dat het nog optreedt.
Is het mogelijk om de request ID van de bijbehorende download en de foutmelding toe te voegen als antwoord?
Duidelijk, echter heb ik geprobeerd de SalesInvoiceLinesIncremental naar Excel te halen, alleen blijft de query hangen (zo lijkt het) op 11,2 MB.
Request ID = 0HN3N78ULHKOG:00000002
Foutmelding is niet bekend, de query blijft oneindig lang draaien.
Ik heb de query handmatig stilgezet na 1.5 miljoen ms.
De query is:
select t.[InvoiceDate], t.[InvoiceID], t.[InvoiceNumber], t.[Item], t.[LineNumber], t.[OrderDate], t.[OrderedBy], t.[OrderNumber], t.[Quantity], t.[UnitPrice], t.[VATPercentage]
from ExactOnlineREST.Incremental.SalesInvoiceLinesIncremental@eol t
where [Division] = :w1
met :w1 = 1234567.
Voor request 0HN3N78ULHKOG:00000002 is de download gestart om 12:40:00.192. Omstreeks 12:40:19.418 waren er 8.000 rijen teruggegeven. Om 13:03:54.785 heeft de gebruiker verzocht de download af te breken, wat om 13:09.38.796 gelukt is met een itgenpmr037. In de tussenliggende tijd is er een te lange spin-avoiding sleep geweest met origin itgenpmr053.
Deze download is inderdaad blijven hangen. Een collega zal een diepere analyse maken.
1 like
Is hier intussen al een statusupdate over bekend? Zo ja, hoor ik deze graag!
Gelieve nogmaals te proberen. Een aantal verbeteringen bij het annuleren van downloads zijn in productie genomen.
Zojuist nogmaals geprobeerd de SalesInvoiceLinesIncremental naar Excel te halen, alleen de query blijft nog steeds hangen op 11,2 MB.
Request ID = 0HN461V9GK5HC:00000002.
Met onderstaande query:
met :w1 = 1234567.
De query blijft wederom oneindig doorgaan. Na meer dan 5 minuten draaien & handmatig de query te stoppen, zijn er nog geen rijen opgehaald:
Zelfs als we de query plat draaien, dus zonder aanpassingen dan haalt de query geen rijen op. Zie onderstaande:
Request ID = 0HN461V9GK5JM:00000002
Query:
met :w1 = 1234567.
Wederom handmatig gestopt na meer dan 5 minuten en 0 rijen:
Dank voor de aanvullende informatie!
Op basis hiervan zal het probleem verder bestudeerd worden.
Vanuit PowerBI.com gedurende de nacht lukt het wel.
Nogmaals vanuit Excel geprobeerd. Deze bleef hangen na circa 10.000 rijen (11,2 MB).
Download afgebroken.
Vervolgens met curl geprobeerd:
curl --user john.doe@acme.com:secret --verbose --output test.json https://bridge-online.cloud/pbi_1234_xx/odata4/ExactOnlineREST.Incremental.SalesInvoiceLinesIncremental@eol?$filter=Division%20eq%202626737&$select=InvoiceDate%2CInvoiceID%2CInvoiceNumber%2CItem%2CLineNumber%2COrderDate%2COrderedBy%2COrderNumber%2CQuantity%2CUnitPrice%2CVATPercentage
Curl haalde geen rijen op en bleef hangen na itgenboe154 in de fases van het request (ID is 0HN46MPIDA756:00000001).
In overleg met Invantive is door hun Bridge Online herstart. Hierna liep de download via curl vlekkeloos door (241.000 rijen in 103 seconden).
Daarna is de download nogmaals vanuit Excel gestart. Deze kan niet beantwoord worden uit cache omdat de request headers andere eisen stellen zoals gzip-compressie.
De download blijft hangen op 9.000 rijen. Vreemd genoeg zijn er met 1x aanklikken van de verversbutton in Excel op de dataset toch twee downloads gestart.
Na nogmaals herstart van Invantive Bridge Online loopt de eerste download vanuit Excel door. Excel lijkt in de achtergrond automatisch herhaaldelijk te proberen. Als er meerdere Excel werkboeken openstaan met vergelijkbare queries, dan worden die ook parallel in de achtergrond ververst.
Het probleem is niet op te wekken met curl zoals:
curl --header "MaxDataServiceVersion: 3.0" --header "OData-MaxVersion: 4.0" --header "Accept: application/json;odata.metadata=minimal;q=1.0,application/json;odata=minimalmetadata;q=0.9,application/atomsvc+xml;q=0.8,application/atom+xml;q=0.8,application/xml;q=0.7,text/plain;q=0.7" --http1.1 --compressed --output c:\temp\x.dmp --user john.doe@acme.com:secret https://bridge-online.cloud/pbi_acme/odata4/ExactOnlineREST.Incremental.SalesInvoiceLinesIncremental@eol?$filter=Division%20eq%202626737&$select=InvoiceDate%2CInvoiceID%2CInvoiceNumber%2CItem%2CLineNumber%2COrderDate%2COrderedBy%2COrderNumber%2CQuantity%2CUnitPrice%2CVATPercentage
Ondanks een identiek HTTP-verzoek en HTTP/1.1 protocol blijft curl stabiel werken, ook bij afgebroken en hervatte downloads.
De 11,2 MB is mogelijkerwijs te herleiden naar de omvang van de metadata. Omdat er nog geen of weinig rijen geretourneerd worden, kan dit nooit 11,2 MB zijn.
Vanuit Microsoft Excel met Power Query treedt het probleem echter consistent op, ook na verwijdering van de OData response cache van de gebruikers, evenals diens *Incremental-cache.
Vanuit Power BI, curl en Postman is het probleem niet reproduceerbaar. Met 1 download wordt de data binnengehaald.
Inmiddels is vastgesteld dat dit probleem zowel optreedt bij de Mac-versie als bij de Windows-versie van Power Query, maar uitsluitend binnen Excel.
Het eigenaardige is dat het teruggeven van rijen stap, maar dat op enig moment later de Excel-versie van Power Query een tweede (identiek) request start voor de data. Dit tweede request wordt op Bridge Online in de parkeerstand gezet binnen Bridge Online omdat het een identieke kopie is van het oorspronkelijke en nog draaiende request (fasecode itgentmm022). Het SQL-statement wordt niet bepaald en de bijbehorende executie wordt niet gestart.
De oorspronkelijke download stopt echter op dezelfde seconde met het verder verwerken van data.
Het is bekend dat de Power Query-versie binnen Microsoft Excel een andere versie is dan de versie binnen Power BI, maar hiervan is weinig tot geen openbare documentatie over beschikbaar. Uit het geheugen van betrokkenen zou er mogelijk sprake zijn van Power Query versie 3 in Excel versus Power Query versie 4 in Power BI.
De Power Query in Windows voor Excel heeft assembly informatieve bestandsversie 2.124.628.0 (23.12) in combinatie met schijnbaar .net core 5.0 voor Windows.
De Power Query in Mac voor Excel heeft assembly informatieve bestandsversie 2.129.181.0 (24.05) in combinatie met .net core 8.0 voor MacOS.
Een wisseling aan de serverzijde tussen .net 6 en .net 8 leverde geen verschillen op qua gedrag.
Inmiddels is met zekerheid vastgesteld dat Power Query in Excel afwijkend gedrag vertoond ten opzichte van andere OData-consumers.
Het starten van het tweede (identieke) request lijkt een herstart te zijn van de oorspronkelijke download, zelfs als al data binnengekomen was op de oorspronkelijke download.
De oorspronkelijke download lijkt verlaten te worden, maar niet netjes afgebroken.
Bij het sluiten van Excel worden de geopende HTTP-verbindingen afgesloten en ook binnen Invantive Bridge Online dan opgeruimd. Echter, bij herstart van Excel met het model worden ook weer meerdere identieke downloads gestart, waardoor een herstart ook niet succesvol is.
Workaround 1 in Power Query
Een workaround is gevonden in Power Query door het tussenvoegen van een Table.Buffer stap.
De oorspronkelijke code is dan bijvoorbeeld:
let
Bron = OData.Feed("https://bridge-online.cloud/acme/odata4", null, [Implementation="2.0", Timeout=#duration(0,4,0,0)]),
#"ExactOnlineREST.Incremental.SalesInvoiceLinesIncremental@eol_table" = Bron{[Name="ExactOnlineREST.Incremental.SalesInvoiceLinesIncremental@eol",Signature="table"]}[Data],
#"Rijen gefilterd" = Table.SelectRows(#"ExactOnlineREST.Incremental.SalesInvoiceLinesIncremental@eol_table", each [Division] = 2626737)
in
#"Rijen gefilterd"
maar na aanpassing naar:
let
Bron = OData.Feed("https://bridge-online.cloud/acme/odata4", null, [Implementation="2.0", Timeout=#duration(0,4,0,0)]),
#"ExactOnlineREST.Incremental.SalesInvoiceLinesIncremental@eol_table" = Bron{[Name="ExactOnlineREST.Incremental.SalesInvoiceLinesIncremental@eol",Signature="table"]}[Data],
#"Rijen gefilterd" = Table.SelectRows(#"ExactOnlineREST.Incremental.SalesInvoiceLinesIncremental@eol_table", each [Division] = 2626737),
#"BufferedTable" = Table.Buffer(#"Rijen gefilterd"),
in
#"BufferedTable"
wordt geen tweede download gestart door Power Query in Excel. De Table.Buffer doorbreekt dit patroon en de materialisatie die die veroorzaakt hangt mogelijk samen met een codebug in Power Query in Excel met bijvoorbeeld LINQ, die in Power BI niet optreedt.
Workaround 2 in PowerBI.com
Een tweede workaround is om de dataset te laten verversen door PowerBI.com en die in Excel binnen te halen. Dit werkt wel betrouwbaar.
Overige Adviezen
Daarnaast is het advies om een aantal instellingen in Power Query aan te passen om extra downloads te vermijden:
- Ga naar Power Query, bijvoorbeeld door een query te bewerken.
- Schakel achtergrondanalyse uit zoals beschreven in Why does my query run multiple times - Power Query | Microsoft Learn.
- Kies de opties voor optimale prestaties zoals beschreven onder “Set Fast Combine options” zoals beschreven in Set privacy levels (Power Query) - Microsoft Support.
Workaround
Inmiddels is duidelijk geworden wat de oorzaak is. Dit probleem lijkt alleen op te treden met Power Query in een deel van de Excel-versies. Het probleem lijkt zich te concentreren rond recente Excel-versies, bijvoorbeeld die van Office 365. Op oudere versies is reproductie nog niet geslaagd; de downloads lopen dan gewoon altijd netjes door.
Oorzaak
De oorzaak is vergelijkbaar met het Dining Philisopher’s-probleem (zie Filosofenprobleem - Wikipedia), waarbij er twee filosofen aan tafel zitten met daarop twee borden en in totaal twee vorken. De filosofen zijn Excel en Bridge Online; de vorken zijn twee downloads van dezelfde URL.
De Excel-filosoof wil het volgende gedrag laten zien:
- Start download 1 via de URL x.
- Wacht tot een aantal rijen ontvangen zijn of een zekere tijd is verstreken.
- Pauzeer download 1 door geen data meer binnen te halen totdat download 2 afgerond is.
- Start download 2 van exact dezelfde URL x.
- Haal alle rijen binnen via download 2.
- Sluit download 2 af.
- Voer onbekende interne magie uit.
- Zet download 2 voort.
- Haal de resterende rijen binnen.
- Sluit download 1 af.
De Bridge Online-filosoof wil het volgende gedrag laten zien:
- Ontvang een download-verzoek 1 voor de URL x.
- Noteer om alle toekomstige verzoeken voor URL x te parkeren totdat download-verzoek 1 klaar is zodat hetzelfde resultaat opnieuw gebruikt kan worden uit de OData-response cache (zie Invantive Cloud Structuur).
- Start het retourneren van data naar download-verzoek 1.
- Stuur alle rijen op.
- Sla het resultaat op in de OData-response cache.
- Ontvang een download-verzoek 2 voor dezelfde URL x.
- Retourneer de gegevens uit de OData-response cache.
Hierbij ontstaat de situatie dat download 2 van de Excel-filosoof wacht op een antwoord, terwijl de Bridge Online-filosoof wacht totdat download 1 van de Excel-filosoof zijn werk afrondt vooraleer download 2 te bedienen. Een zogenaamde "dead-lock’.
Oplossingsrichtingen
Voor de oplossingsrichtingen is het belangrijk om het gebruik van CPU en geheugen op de betrokken Bridge Online-server te beperken. Momenteel werkt Bridge Online grotendeels met “streaming data”, waardoor net zoals bij fabrieken die via stromen produceren, de totale hoeveelheid data in het werkgeheugen beperkt blijft.
Aangezien de verwerking datasets van meer dan 5 GB niet ongebruikelijk zijn terwijl het huidige geheugengebruik enkele ordes van grootte kleiner is, is het ongewenst om datasets volledig in het werkgeheugen te houden. Volledig in het geheugen vergroot het geheugenbeslag, plus kan eventueel leiden tot prestatieverlies door minder dakpangewijze verwerking van data over de gehele verwerkingsketen.
Merk op dat bij het ophalen van een dataset op een achterliggend platform vooraf niet bekend is hoe groot deze zal worden.
Workaround
Het gebruik van een Table.Buffer-stap in het Power Query-script is een snelle oplossing voor dit probleem. Download-verzoek 2 wordt dan niet door Power Query op Excel geĂŻnitieerd. Excel Power Query zal dan eerst alle data ophalen en bufferen op de PC of Mac.
Detectie
De detectie kan gebeuren indien er (tenminste) twee downloadverzoeken bekend zijn met de volgende eigenschappen:
- Afkomstig van hetzelfde Internet IP-adres (want Excel Power Query).
- Dezelfde Invantive Cloud-gebruiker.
- Dezelfde URL.
- Dezelfde user agent “Mashup”.
- Dezelfde response data cache key, dus dezelfde compressie e.d.
- Een eerste downloadverzoek is actief en heeft tenminste 10 rijen retour gegeven.
- Een tweede downloadverzoek is gearriveerd en wacht op afhandeling.
- Het eerste downloadverzoek heeft geen rijen meer opgehaald sinds het tweede downloadverzoek is gearriveerd.
Vermijden
Een manier om de tweede download mogelijk te maken is het openen van een meerdere databasecontexten in plaats van een voor een gebruiker. Gezien de impact heeft dit niet de voorkeur.
Een andere manier is om download 1 intern alvast zijn data naar de response cache te laten schrijven als de download nog niet alle rijen ophaalt door bijvoorbeeld bij detectie (en alleen na detectie) tijdelijk de producer/consumer-buffers indien nodig intern te vergroten en resultaten weg te schrijven in de OData response cache. Zolang downloadverzoek 1 niet eindigt blijven de buffers groot (wat leidt tot extra resourcegebruik).
Zodra de cache berekend is, kan hiermee na vrijgave downloadverzoek 2 beantwoord worden. Voor downloadverzoek 1 moet de download dan voortgezet worden waar gebleven door de niet-verwerkte rest van de buffers te verwerken.
Het totale geheugengebruik in de tijd kan verlaagd worden door downloadverzoek 1 af te breken.
Afhandeling 1: e-mail sturen aan gebruiker
Een afhandelingsmethode is om een mail te sturen aan de betrokken Invantive Cloud-gebruiker met uitleg. In deze e-mail staat de tip om de download af te breken en de tip om Table.Buffer toe te voegen direct na de OData-download in Excel Power Query.
Afhandeling 2: afbreken en e-mail
Een afhandelingsmethode is om de eerste download volledig af te breken indien mogelijk vanuit de SQL-engine dataproducent. En daarna de e-mail te sturen.
Merk op dat indien de tweede download succesvol wordt afgerond, de data in de OData response cache komt. Echter, metingen geven aan dat dan nog steeds een twee downloadverzoeken door Excel gedaan kunnen worden met hetzelfde dead-lock probleem tot gevolg.
Een verbeterde versie van Invantive Bridge Online die in het bovenbeschreven scenario het hangenblijven oplost van Power Query in Excel is in productie genomen.
Zie voor meer details:
Het gebruik van Table.Buffer is hierbij niet meer nodig.
Deze vraag is automatisch gesloten na 1 week inactiviteit. Het laatste gegeven antwoord is gemarkeerd als oplossing.
Gelieve een nieuwe vraag te stellen via een apart topic als het probleem opnieuw optreedt. Gelieve in de nieuwe vraag een link naar dit topic op te nemen door de URL er van in de tekst te plakken.
Dit topic is 3 dagen na het laatste antwoord automatisch gesloten. Nieuwe antwoorden zijn niet meer toegestaan.