Azure Data Factory
Data fra Simplicate hentes fra Invantive Cloud ind i Microsoft Azure Data Factory. Dette fungerer for Simplicate, men også for mange andre cloud-platforme såsom Exact Online, Salesforce eller ActiveCampaign som beskrevet trin for trin på Retrieve a specific Exact Online OData4 data set using Azure Data Factory.
Leder du efter filtrering af OData på Power BI: en mere praktisk guide findes på Reduce data volume and improve performance of your Power BI report by Filtering.
Sørg for at forstå strukturen af Invantive Cloud, inden du begynder at optimere ydeevnen og reducere download. Lær strukturen på Invantive Cloud Structure. Der er også et flot billede i den!
Azure Data Factory gør det muligt at transportere og dirigere data fra OData-forbindelsen i Invantive Cloud og andre OData-udbydere (OData-kilder) til din data lake eller en database på din SQL Server-instans.
Invantive Cloud er en OData-udbyder (OData-kilde): den bygger bro mellem Simplicates interne API-protokoller og OData, tilføjer metadata og produktionsfunktioner såsom caching, genforsøg og logning.
Bemærk, at der findes to versioner af OData-protokollen med små forskelle. Ældre udbydere (kilder) arbejder med OData version 3, men OData 4 anbefales kraftigt til nuværende arbejde, da den har flere operatorer, giver bedre gennemløb og har en længere resterende levetid, samtidig med at den fortsat udvikles til at blive endnu mere avanceret.
OData v3-specifikationen er tilgængelig på OData Version 3.0 · OData - the Best Way to REST. Den godkendte OData v4-specifikation er tilgængelig på Documentation · OData - the Best Way to REST, mens kommende ændringer kan findes på GitHub - OData/odataorg.github.io: Staging site and collaboration repository for http://www.odata.org · GitHub.
OData v4 understøttes til brug af Azure Data Factory, Power Query, Power BI og alle andre kendte nyere versioner af Microsoft-produkter.
Med henblik på filtrering bliver en OData-forespørgsel:
- Først omskrevet til Invantive SQL, hvor hvert OData-filter oversættes til et filter i SQL where-klausulen.
- Dernæst eksekveres Invantive SQL mod eksisterende caches, og når det er nødvendigt, udføres API-kald mod Simplicates webservice-kilde af Invantive-driveren (en slags ODBC).
Bemærk, at Invantive Cloud altid bruger den aktuelle version 4 af OData i OData-forbindelsen. Den faktiske datakilde kan bruge et API med OData 2 eller OData 3 eller endda XML, SOAP eller blot JSON eller endda være et nativt relationelt databaseformat som PostgreSQL, IBM DB2 eller SQL Server.
Server-side filtrering
Oversættelsen foretaget af Invantive SQL til kald på Invantive-driveren (sammenlign det med brug af en ODBC-driver) abstraherer den faktiske protokol væk.
Denne oversættelse til Invantive SQL som et mellemliggende sprog for dine forespørgsler ved brug af Azure Data Factory, før forespørgslen videresendes (når det er nødvendigt for genindlæsning af caches) til den faktiske datakilde, understøtter også videresendelse af filteret til datakilden. For eksempel bliver et OData-filter med en lighedsoperator omskrevet til en lighedsoperator i Invantive SQL. Ligheden videresendes til datakilden med mange API’er. Ikke alle API’er har omfattende filtreringsstøtte, men det har Simplicate. Andre populære datakilder med fin filtrering er AFAS Profit, Twinfield, Chargebee, Visma.net Financials, Visma eAccounting og Exact Online. Denne videresendelse af filtre kaldes “server-side filtrering”.
Dette koncept med videresendelse af filterkriterier (kaldet “server-side filtrering”) gælder ikke altid.
For eksempel bliver filtre på felter, der er tilføjet manuelt af Invantive, oftest ikke videresendt. Der er nogle undtagelser som DivisionLabel på Exact Online. Selvom det ikke er en faktisk kolonne i Exact Online API’et, bliver filtre på den oversat til specifikke afdelinger, der skal forespørges.
Heller ikke alle API’er er i stand til at filtrere på et vilkårligt felt. For eksempel har Exact Online kolonne-specifikke undtagelser. Men også Teamleader har betydelige begrænsninger.
Om filtre på felter i sidste ende kan videresendes til cloud-platformen, kan findes i dokumentationen. Se kolonnen CAN_FILTER_SERVER_SIDE i SystemTableColumns@DataDictionary.
Bemærk endelig, at server-side filtrering ikke er mulig med mange mere avancerede filtre, såsom komplekse kombinationer af and, or og måske endda beregninger. Invantive SQL har en omfattende optimizer, som forenkler udtryk, udfører peephole-optimeringer og omskriver forespørgsler, men der er stadig mange tænkelige konstruktioner, der endnu ikke kan optimeres. Under regelmæssig overvågning forsøger vi at identificere yderligere optimeringsmuligheder, der påvirker mange brugere, og forsøger over tid også at optimere disse.
OData-filter
Det resterende trin er at konstruere OData $filter. Antag, at du ønsker at opnå denne effekt i SQL:
select *
from table
where created_at >= VALUE
skal du først tjekke datatypen for created_at.
Du vil bemærke, at Simplicate API’et har en typisk finurlighed: datatypen for datoer er faktisk ikke datoer, men en tekst-datatype. Den gode nyhed er, at datoens tekstrepræsentation sorterer leksikografisk identisk med datoerne som en dato-datatype, så for eksempel kommer 1. januar 2020 før 31. december 2020, både som tekstrepræsentation og som dato. Du kan finde mere information om Simplicates datoformat på Limit Data load from Simplicate on Invantive Cloud to Azure Data Factory.
Oversæt filter til OData i Power BI
SQL-sætningen skal oversættes til OData. OData-implementeringen i Power BI og Power Query er veludført, så den nemmeste måde at gøre det på er at bruge en OData-datakilde i Power BI og derefter observere de faktiske OData-forespørgsler, der sendes til OData-tjenesten.
Trinene til at opsætte et OData-filter på Simplicate (eller enhver anden understøttet datakilde) er:
- Start først Power BI og vælg “Hent data” og derefter “OData-feed”:
- Indtast OData Bridge-URL’en kopieret fra den virtuelle Simplicate-database på Invantive Cloud:
- Indtast dine legitimationsoplysninger ved hjælp af BASIC-autentificering, hvis du ikke allerede er registreret.
- Vælg tabellen
Simplicate.Hours.Hours@spe (eller enhver anden tabel):
- Anvend et filter i Power BI ved hjælp af knappen Transformer data:
- Tjek visuelt i Power BI, at filteret matcher dine behov.
Observationen gøres lettest ved hjælp af Invantive Bridge Online-overvågningen i Invantive Cloud, så udfør følgende trin:
- Åbn Bridge Online-overvågningen på https://bridge-online.cloud/monitoring:
- Du vil se alle dine tidligere OData-forespørgsler fra OData-forbindelsen i Power BI.
- Klik på Opdater i Power BI.
- En forespørgsel tilføjes øverst:
- Kopier og indsæt filteret, såsom:
/invantive-simplicate-1/odata4/Simplicate.Hours.Hours@spe?$filter=projectservice_name%20eq%20%27Consultancy%20op%20locatie%27&$top=1000
OData-filteret, der skal bruges, er teksten, der starter ved $filter. Filteret vil være blevet escaped til brug i en URL, så du skal unescape teksten. Du kan gøre dette manuelt eller bruge en online tjeneste som https://www.url-encode-decode.com/.
Siden juli 2021 viser overvågningen også OData-filteret ved siden af den SQL, der er genereret til det, som vist på https://forums.invantive.com/t/more-insight-with-new-bridge-online-monitoring/2463.
Konstruer manuelt et OData-filter
Et OData-filter kan også konstrueres manuelt. OData-specifikationen er tilgængelig online. Filtrene er en del af afsnit 11.2.6 om OData-forespørgsler.
Det grundlæggende er at angive dine filtre efter $filter i URL’en. Filtre kan kombineres ved hjælp af and og or, men brug også kriterier bestående af en venstreside (for eksempel et kolonnenavn), en operator som eq for “lig med” og en højreside (for eksempel en konstant værdi). Filtergrupper kan sammensættes ved hjælp af grupperingsoperatorerne ( og ).
OData-implementeringen af Invantive Bridge understøtter mange OData-filteroperatorer, men ikke alle. For eksempel er hassubset ikke tilgængelig. Hvis du har brug for at inkludere et filter, der ikke understøttes, bedes du rapportere det til Invantive support med en kort use case. Afhængigt af kompleksiteten og levedygtigheden af use casen vil vi overveje at tilføje det specifikke OData-filter til Invantive Bridge Online.
Som en midlertidig løsning kan du selvfølgelig altid bruge et filter inden i selve Azure Data Factory for at filtrere dine data efter dine behov.
Konstruer et OData-filter ved hjælp af Query Tool
Alle nyere Invantive SQL-baserede produkter inkluderer en driver til OData-datakilder på Bridge Online-tjenesten. De gør det muligt at hente data fra Invantive Cloud ved hjælp af Invantive SQL og OData-broen.
Udfør følgende trin:
- Start Invantive Query Tool.
- Åbn databasegruppen “Various”.
- Vælg databasen “Invantive Bridge Online”.
- Indtast databaseparametrene som følger:
- Klik på “Connect”.
- Konstruer din databaseforespørgsel som:
select *
from [Hours@Spe]
where created_at >= '2020-11-01'
- Bemærk, at tabelnavnet er
Hours@spe inklusive spe, da spe fortolkes på Bridge Online-tjenesten. Hvis du har et lokalt alias som bol for Bridge Online, vil det fulde navn være [Hours@spe]@bol.
- Invantive SQL oversætter kaldet til en OData-forespørgsel, inklusive et filter.
- Den faktiske OData-forespørgsel kan findes på Bridge Online-overvågningen.
- Og den faktiske OData-forespørgsel kan hentes fra den lokale Invantive SQL-instans ved hjælp af:
select url
from sessionios@datadictionary
med resultatet:
Nogle eksempler på OData-filtre
Som inspiration kan følgende liste med eksempler på OData-filtre til brug med forespørgsler hjælpe dig:
ExactOnlineREST.Cashflow.PaymentsBulk@eol?$filter=DivisionCompanyName ne 'ACME Holding' and InvoiceDate ge 2019-01-01T00:00:00+01:00: hent alle betalinger efter 1. januar 2019 på tværs af alle partitioner undtagen holdingselskabet.
ExactOnlineREST.Financial.GLAccountClassificationMappings@eol?$filter=GLSchemeDescription eq 'G/L Account Scheme': hent alle finanskontoklassifikationer for rapporteringsskemaet “G/L Account Scheme”.
ExactOnlineREST.Financial.ExchangeRates@eol?$filter=Created gt 2020-01-01T00:00:00+01:00: hent alle valutakurser oprettet efter 1. januar 2020.
Teamleader.V1Flat.ProjectMilestones@tlr?$filter=responsible_user_id eq 1 or responsible_user_id eq 2: hent alle milepæle på Teamleader-projekter, hvor brugerne med ID 1 eller 2 er ansvarlige.
ExactOnlineREST.FinancialTransaction.TransactionLinesBulk@eol?$filter=FinancialYear ge 2018 and Division eq 123456: hent alle finansielle transaktioner for regnskabsåret 2018 for Exact Online-virksomhed 123456.
Anvend OData $filter ved hjælp af Azure Data Factory på OData-kilde
Ved hjælp af filteret konstrueret til OData-tjenesten kan forespørgslen tilpasses som vist på følgende billede (eksempel med Data Factory på Exact Online Frankrig):