Achter de schermen van de firewall voor gegevensprivacy
Als u een Power Query hebt gebruikt, hebt u dit waarschijnlijk ervaren. Daar bent u, als u query's uitvoert, wanneer u plotseling een fout krijgt die geen enkele hoeveelheid online zoeken, query's aanpassen of bashing op het toetsenbord kan oplossen. Een fout zoals:
Formula.Firewall: Query 'Query1' (step 'Source') references other queries or steps, so it may not directly access a data source. Please rebuild this data combination.
Of misschien:
Formula.Firewall: Query 'Query1' (step 'Source') is accessing data sources that have privacy levels which cannot be used together. Please rebuild this data combination.
Deze fouten zijn het resultaat van Power Query's Data Privacy Firewall (ook wel de firewall) die soms lijkt te bestaan tot gefrustreerde gegevensanalisten over de Formula.Firewall hele wereld. De firewall dient echter een belangrijk doel. In dit artikel gaan we dieper in op de manier waarop het werkt. Met meer begrip kunt u in de toekomst hopelijk firewallfouten beter diagnosticeren en oplossen.
Wat is het?
Het doel van de firewall voor gegevensbescherming is eenvoudig: het bestaat om te voorkomen dat Power Query onbedoeld gegevens tussen bronnen lekken.
Waarom is dit nodig? U kunt zeker een M maken die een waarde SQL aan een OData-feed. Maar dit zou opzettelijke gegevenslekken zijn. De mashup-auteur zou (of in ieder geval moeten) weten dat hij dit doet. Waarom is er dan bescherming nodig tegen onbedoelde gegevenslekken?
Het antwoord? Vouwen.
Vouwen?
Folding is een term die verwijst naar het converteren van expressies in M (zoals filters, namen, joins, etc.) naar bewerkingen voor een onbewerkte gegevensbron (zoals SQL, OData, etc.). Een groot deel van de kracht van Power Query is het feit dat PQ de bewerkingen die een gebruiker uitvoert via de gebruikersinterface kan converteren naar complexe SQL- of andere back-ende gegevensbrontalen, zonder dat de gebruiker die talen kent. Gebruikers profiteren van het prestatievoordeel van systeemeigen gegevensbronbewerkingen, met het gebruiksgemak van een gebruikersinterface waarbij alle gegevensbronnen kunnen worden getransformeerd met behulp van een gemeenschappelijke set opdrachten.
Als onderdeel van het vouwen kan PQ soms bepalen dat de meest efficiënte manier om een bepaalde mashup uit te voeren, is om gegevens uit de ene bron te halen en door te geven aan een andere. Als u bijvoorbeeld een klein CSV-bestand aan een zeer grote SQL-tabel wilt toevoegen, wilt u waarschijnlijk niet dat PQ het CSV-bestand leest, de hele SQL-tabel leest en deze vervolgens samen op uw lokale computer samen op uw computer maakt. U wilt waarschijnlijk dat PQ de CSV-gegevens inline in een SQL-instructie en de SQL-database vraagt om de join uit te voeren.
Dit is hoe onbedoelde gegevenslekken kunnen plaatsvinden.
Imagine als u lid was van SQL-gegevens met de sociaal-beveiligingsnummers van werknemers bij de resultaten van een externe OData-feed en u plotseling ontdekte dat de sociaal-beveiligingsnummers van SQL werden verzonden naar de OData-service. Slecht nieuws, toch?
Dit is het soort scenario dat de firewall moet voorkomen.
Hoe werkt het?
De firewall bestaat om te voorkomen dat gegevens van de ene bron onbedoeld naar een andere bron worden verzonden. Eenvoudig genoeg.
Hoe wordt deze missie bereikt?
Dit wordt gedaan door uw M-query's te verdelen in zogenaamde partities en vervolgens de volgende regel af te afdwingen:
- Een partitie kan toegang krijgen tot compatibele gegevensbronnen of verwijzen naar andere partities, maar niet beide.
Eenvoudige... maar verwarrend. Wat is een partitie? Wat maakt twee gegevensbronnen 'compatibel'? En waarom is het voor de firewall belangrijk als een partitie toegang wil krijgen tot een gegevensbron en naar een partitie wil verwijzen?
Laten we dit eens opsnaf en de bovenstaande regel één voor één bekijken.
Wat is een partitie?
Op het meest eenvoudige niveau is een partitie slechts een verzameling van een of meer querystappen. De meest gedetailleerde partitie die mogelijk is (ten minste in de huidige implementatie) is één stap. De grootste partities kunnen soms meerdere query's omvatten. (Meer informatie later.)
Als u niet bekend bent met de stappen, kunt u deze weergeven aan de rechterkant van het venster Power Query Editor nadat u een query hebt geselecteerd in het deelvenster Toegepaste stappen. Stappen houden alles bij wat u hebt gedaan om uw gegevens te transformeren naar de uiteindelijke vorm.
Partities die verwijzen naar andere partities
Wanneer een query wordt geëvalueerd met de firewall aan, verdeelt de firewall de query en alle afhankelijkheden ervan in partities (dat wil zeggen groepen stappen). Elke keer dat een partitie naar iets in een andere partitie verwijst, vervangt de firewall de verwijzing door een aanroep van een speciale functie met de naam Value.Firewall . Met andere woorden, de firewall staat partities niet willekeurig toegang tot elkaar toe. Alle verwijzingen worden gewijzigd om via de firewall te gaan. U kunt de firewall zien als een gatekeeper. Een partitie die naar een andere partitie verwijst, moet de machtiging van de firewall krijgen om dit te doen en de firewall bepaalt of de gegevens waarnaar wordt verwezen wel of niet in de partitie worden toegestaan.
Dit lijkt allemaal nogal abstract, dus laten we eens kijken naar een voorbeeld.
Stel dat u een query hebt met de naam Werknemers, die enkele gegevens opvraagt uit SQL database. Stel dat u ook een andere query hebt (EmployeesReference), die gewoon verwijst naar Werknemers.
shared Employees = let
Source = Sql.Database(…),
EmployeesTable = …
in
EmployeesTable;
shared EmployeesReference = let
Source = Employees
in
Source;
Deze query's worden uiteindelijk onderverdeeld in twee partities: één voor de query Werknemers en één voor de query EmployeesReference (die verwijst naar de partitie Werknemers). Wanneer de firewall is geëvalueerd, worden deze query's als de volgende herschreven:
shared Employees = let
Source = Sql.Database(…),
EmployeesTable = …
in
EmployeesTable;
shared EmployeesReference = let
Source = Value.Firewall("Section1/Employees")
in
Source;
U ziet dat de eenvoudige verwijzing naar de query Werknemers is vervangen door een aanroep naar , die de volledige naam van de Value.Firewall Werknemers-query heeft.
Wanneer EmployeesReference wordt geëvalueerd, wordt de aanroep van onderschept door de firewall, die nu de mogelijkheid heeft om te bepalen of (en hoe) de aangevraagde gegevens naar de Value.Firewall("Section1/Employees") employeesReference-partitie stromen. Het kan een aantal dingen doen: de aanvraag weigeren, de aangevraagde gegevens bufferen (waardoor er geen verdere vouwing naar de oorspronkelijke gegevensbron plaatsvindt), en meer.
Dit is hoe de firewall controle houdt over de gegevensstroom tussen partities.
Partities die rechtstreeks toegang hebben tot gegevensbronnen
Stel dat u een query Query1 met één stap definieert (houd er rekening mee dat deze query met één stap overeenkomt met één firewallpartitie) en dat deze ene stap toegang heeft tot twee gegevensbronnen: een SQL-databasetabel en een CSV-bestand. Hoe gaat de firewall hiermee om, omdat er geen partitieverwijzing is en er dus geen aanroep naar is om deze Value.Firewall te onderscheppen? Laten we eens kijken naar de regel die eerder is vermeld:
- Een partitie kan toegang krijgen tot compatibele gegevensbronnen of verwijzen naar andere partities, maar niet beide.
Als u wilt dat uw query met één partitie maar twee gegevensbronnen mag worden uitgevoerd, moeten de twee gegevensbronnen 'compatibel' zijn. Met andere woorden, het moet geen probleem zijn als er gegevens tussen de twee worden gedeeld. In termen van de Power Query ui betekent dit dat de privacyniveaus van de SQL- en CSV-gegevensbronnen openbaar moeten zijn of beide organisatie zijn. Als ze beide als Privé zijn gemarkeerd, of als een van beide als Openbaar is gemarkeerd en één als Organisatie, of als ze zijn gemarkeerd met behulp van een andere combinatie van privacyniveaus, is het niet veilig voor beide om in dezelfde partitie te worden geëvalueerd. Dit zou betekenen dat er onveilige gegevenslekken kunnen optreden (als gevolg van vouwen) en dat de firewall deze niet kan voorkomen.
Wat gebeurt er als u toegang probeert te krijgen tot incompatibele gegevensbronnen in dezelfde partitie?
Formula.Firewall: Query 'Query1' (step 'Source') is accessing data sources that have privacy levels which cannot be used together. Please rebuild this data combination.
Hopelijk begrijpt u nu een van de foutberichten die aan het begin van dit artikel worden vermeld.
Houd er rekening mee dat deze compatibiliteitsvereiste alleen van toepassing is binnen een bepaalde partitie. Als een partitie verwijst naar andere partities, hoeven de gegevensbronnen van de waarnaar wordt verwezen, niet met elkaar compatibel te zijn. Dit komt doordat de firewall de gegevens kan bufferen, waardoor de oorspronkelijke gegevensbron niet verder kan worden gevouwen. De gegevens worden in het geheugen geladen en behandeld alsof ze afkomstig zijn van een onwelding.
Waarom niet beide?
Stel dat u een query definieert met één stap (die opnieuw overeenkomt met één partitie) die toegang heeft tot twee andere query's (dat wil zeggen, twee andere partities). Wat moet u doen als u in dezelfde stap ook rechtstreeks toegang wilt krijgen tot SQL database? Waarom kan een partitie niet verwijzen naar andere partities en rechtstreeks toegang krijgen tot compatibele gegevensbronnen?
Zoals u eerder hebt gezien, fungeert de firewall, wanneer een partitie verwijst naar een andere partitie, als de gatekeeper voor alle gegevens die in de partitie stromen. Om dit te doen, moet het kunnen bepalen in welke gegevens is toegestaan. Als er gegevensbronnen worden gebruikt in de partitie en gegevens die worden binnengestroomd vanuit andere partities, verliest deze de mogelijkheid om de gatekeeper te zijn, omdat de binnenstromende gegevens kunnen worden gelekt naar een van de intern toegankelijk gegevensbronnen zonder dat deze er iets van weten. Zo voorkomt de firewall dat een partitie die toegang heeft tot andere partities rechtstreeks toegang heeft tot gegevensbronnen.
Wat gebeurt er als een partitie probeert te verwijzen naar andere partities en ook rechtstreeks toegang heeft tot gegevensbronnen?
Formula.Firewall: Query 'Query1' (step 'Source') references other queries or steps, so it may not directly access a data source. Please rebuild this data combination.
Nu krijgt u hopelijk meer inzicht in het andere foutbericht dat aan het begin van dit artikel wordt vermeld.
Uitgebreide partities
Zoals u waarschijnlijk kunt raden uit de bovenstaande informatie, is de manier waarop query's worden gepartitief zeer belangrijk. Als u een aantal stappen hebt die verwijzen naar andere query's en andere stappen die toegang hebben tot gegevensbronnen, zult u nu hopelijk zien dat het trekken van de partitiegrenzen op bepaalde plaatsen firewallfouten veroorzaakt, terwijl u ze op andere plaatsen tekent, kan uw query gewoon goed worden uitgevoerd.
Hoe worden query's precies gepart partitioneerd?
Deze sectie is waarschijnlijk het belangrijkste om te begrijpen waarom u firewallfouten ziet en om te begrijpen hoe u deze kunt oplossen (waar mogelijk).
Hier volgt een overzicht op hoog niveau van de partitioneringslogica.
- Initiële partitionering
- Hiermee maakt u een partitie voor elke stap in elke query
- Statische fase
- Deze fase is niet afhankelijk van evaluatieresultaten. In plaats daarvan is het afhankelijk van de manier waarop de query's zijn gestructureerd.
- Parameter inkorten
- Trimt parameter-esque partities, dat wil zeggen, een partitie die:
- Verwijst niet naar andere partities
- Bevat geen functie-aanroepen
- Is niet cyclisch (dat wil zeggen, het verwijst niet naar zichzelf)
- Houd er rekening mee dat het 'verwijderen' van een partitie deze effectief bevat in alle andere partities waarnaar wordt verwezen.
- Als u parameterpartities bijwerkt, kunnen parameterverwijzingen die worden gebruikt in functie-aanroepen van de gegevensbron (bijvoorbeeld ) werken, in plaats van fouten met de fout 'partitie kan niet verwijzen naar gegevensbronnen en andere
Web.Contents(myUrl)stappen'.
- Trimt parameter-esque partities, dat wil zeggen, een partitie die:
- Groeperen (statisch)
- Partities worden samengevoegd, met behoud van de scheiding tussen:
- Partities in verschillende query's
- Partities die verwijzen naar andere partities versus partities die dat niet doen
- Partities worden samengevoegd, met behoud van de scheiding tussen:
- Dynamische fase
- Deze fase is afhankelijk van evaluatieresultaten, waaronder informatie over gegevensbronnen die toegankelijk zijn via verschillende partities.
- Trimmen
- Partities die aan alle volgende vereisten voldoen, worden opgedeeld:
- Heeft geen toegang tot gegevensbronnen
- Verwijst niet naar partities die toegang hebben tot gegevensbronnen
- Is niet cyclisch
- Partities die aan alle volgende vereisten voldoen, worden opgedeeld:
- Groeperen (dynamisch)
- Nu onnodige partities zijn bijgesneden, kunt u proberen bronpartities te maken die zo groot mogelijk zijn.
- Voeg alle partities samen met hun invoerpartities als elk van de invoer:
- Maakt deel uit van dezelfde query
- Verwijst niet naar andere partities
- Er wordt alleen naar verwezen door de huidige partitie
- Is niet het resultaat (dat wil zeggen, laatste stap) van een query
- Is niet cyclisch
Wat betekent dit allemaal?
Laten we een voorbeeld nemen om te illustreren hoe de hierboven beschreven complexe logica werkt.
Hier is een voorbeeldscenario. Het is een vrij eenvoudige samenvoeging van een tekstbestand (Contactpersonen) met een SQL-database (Werknemers), waarbij de SQL-server een parameter (DbServer) is.
De drie query's
Hier is de M-code voor de drie query's die in dit voorbeeld worden gebruikt.
shared DbServer = "montegoref6" meta [IsParameterQuery=true, Type="Text", IsParameterQueryRequired=true];
shared Contacts = let
Source = Csv.Document(File.Contents("C:\contacts.txt"),[Delimiter=" ", Columns=15, Encoding=1252, QuoteStyle=QuoteStyle.None]),
#"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"ContactID", Int64.Type}, {"NameStyle", type logical}, {"Title", type text}, {"FirstName", type text}, {"MiddleName", type text}, {"LastName", type text}, {"Suffix", type text}, {"EmailAddress", type text}, {"EmailPromotion", Int64.Type}, {"Phone", type text}, {"PasswordHash", type text}, {"PasswordSalt", type text}, {"AdditionalContactInfo", type text}, {"rowguid", type text}, {"ModifiedDate", type datetime}})
in
#"Changed Type";
shared Employees = let
Source = Sql.Databases(DbServer),
AdventureWorks = Source{[Name="AdventureWorks"]}[Data],
HumanResources_Employee = AdventureWorks{[Schema="HumanResources",Item="Employee"]}[Data],
#"Removed Columns" = Table.RemoveColumns(HumanResources_Employee,{"HumanResources.Employee(EmployeeID)", "HumanResources.Employee(ManagerID)", "HumanResources.EmployeeAddress", "HumanResources.EmployeeDepartmentHistory", "HumanResources.EmployeePayHistory", "HumanResources.JobCandidate", "Person.Contact", "Purchasing.PurchaseOrderHeader", "Sales.SalesPerson"}),
#"Merged Queries" = Table.NestedJoin(#"Removed Columns",{"ContactID"},Contacts,{"ContactID"},"Contacts",JoinKind.LeftOuter),
#"Expanded Contacts" = Table.ExpandTableColumn(#"Merged Queries", "Contacts", {"EmailAddress"}, {"EmailAddress"})
in
#"Expanded Contacts";
Hier is een weergave op een hoger niveau, waarin de afhankelijkheden worden weergegeven.

Laten we partitioneren
Laten we een beetje inzoomen en stappen in de afbeelding opnemen en beginnen met het doorlopen van de partitioneringslogica. Hier is een diagram van de drie query's, met de eerste firewallpartities in het groen. U ziet dat elke stap begint in een eigen partitie.

Vervolgens gaan we parameterpartities inkorten. DbServer wordt dus impliciet opgenomen in de bronpartitie.

Nu voeren we de statische groepering uit. Hierdoor wordt de scheiding tussen partities in afzonderlijke query's behouden (houd er bijvoorbeeld rekening mee dat de laatste twee stappen van Werknemers niet worden gegroepeerd met de stappen van Contactpersonen), evenals tussen partities die verwijzen naar andere partities (zoals de laatste twee stappen van Werknemers) en partities die dat niet doen (zoals de eerste drie stappen van Werknemers).

Nu gaan we naar de dynamische fase. In deze fase worden de bovenstaande statische partities geëvalueerd. Partities die geen toegang hebben tot gegevensbronnen, worden bijgesneden. Partities worden vervolgens gegroepeerd om bronpartities te maken die zo groot mogelijk zijn. In dit voorbeeldscenario hebben alle resterende partities echter toegang tot gegevensbronnen en is er geen verdere groepering die kan worden uitgevoerd. De partities in ons voorbeeld veranderen dus niet tijdens deze fase.
Laten we doen alsof
Ter illustratie bekijken we echter wat er zou gebeuren als de query Contactpersonen, in plaats van afkomstig uit een tekstbestand, in M in code zou zijn gecodeerd (mogelijk via het dialoogvenster Gegevens invoeren).
In dit geval heeft de query Contactpersonen geen toegang tot gegevensbronnen. Dit zou dus worden afgekort tijdens het eerste deel van de dynamische fase.

Als de partitie Contactpersonen is verwijderd, verwijzen de laatste twee stappen van Werknemers niet meer naar partities, behalve de partities die de eerste drie stappen van Werknemers bevatten. De twee partities worden dus gegroepeerd.
De resulterende partitie ziet er als deze uit.

Voorbeeld: Gegevens van de ene gegevensbron doorgeven aan een andere
Goed, genoeg abstracte uitleg. Laten we eens kijken naar een algemeen scenario waarin u waarschijnlijk een firewallfout tegenkomt en de stappen om deze op te lossen.
Imagine u een bedrijfsnaam wilt zoeken vanuit de OData-service Northwind en vervolgens de bedrijfsnaam wilt gebruiken om een Bing uitvoeren.
Eerst maakt u een Bedrijfsquery om de bedrijfsnaam op te halen.
let
Source = OData.Feed("https://services.odata.org/V4/Northwind/Northwind.svc/", null, [Implementation="2.0"]),
Customers_table = Source{[Name="Customers",Signature="table"]}[Data],
CHOPS = Customers_table{[CustomerID="CHOPS"]}[CompanyName]
in
CHOPS
Vervolgens maakt u een zoekquery die verwijst naar Company en deze door geeft aan Bing.
let
Source = Text.FromBinary(Web.Contents("https://www.bing.com/search?q=" & Company))
in
Source
Op dit moment hebt u problemen. Als u Zoeken evalueert, wordt er een firewallfout weergegeven.
Formula.Firewall: Query 'Search' (step 'Source') references other queries or steps, so it may not directly access a data source. Please rebuild this data combination.
Dit komt doordat de bronstap van Zoeken verwijst naar een gegevensbron (bing.com) en ook verwijst naar een andere query/partitie (bedrijf). Het is in strijd met de bovenstaande regel ('een partitie kan toegang krijgen tot compatibele gegevensbronnen of verwijzen naar andere partities, maar niet beide').
Wat u moet doen? Een optie is om de firewall helemaal uit te schakelen (via de optie Privacy met het label De privacyniveaus negeren en de prestaties mogelijk verbeteren). Maar wat als u de firewall ingeschakeld wilt laten?
Als u de fout wilt oplossen zonder de firewall uit te zetten, kunt u Bedrijf en Zoeken combineren tot één query, zoals:
let
Source = OData.Feed("https://services.odata.org/V4/Northwind/Northwind.svc/", null, [Implementation="2.0"]),
Customers_table = Source{[Name="Customers",Signature="table"]}[Data],
CHOPS = Customers_table{[CustomerID="CHOPS"]}[CompanyName],
Search = Text.FromBinary(Web.Contents("https://www.bing.com/search?q=" & CHOPS))
in
Search
Alles gebeurt nu binnen één partitie. Ervan uitgaande dat de privacyniveaus voor de twee gegevensbronnen compatibel zijn, zou de firewall nu tevreden moeten zijn en krijgt u geen fout meer.
Dat is een wrap
Hoewel er nog veel meer over dit onderwerp kan worden gezegd, is dit inleidende artikel al lang genoeg. Hopelijk krijgt u een beter begrip van de firewall en helpt het u om firewallfouten te begrijpen en op te lossen wanneer u deze in de toekomst tegenkomt.