Easyflex Data validatie ds_bi_declaratieregels

Vragen
,
1

Op hoofdlijnen komt de omzet en loonkosten uit de Invantive API niet overeen met de informatie in Easyflex. Het gaat specifiek om ds_bi_declaratieregels waarbij in Invantive het gaat om View.declaratieregels.

Specifiek voorbeeld is 29 januari 2024 waarbij twee flexwerkers geen reiskosten hebben (loononbelast), terwijl in via ds_bi_declaratieregels en in Easyflex die informatie daar wel wordt getoond.

Wanneer in Invantive zonder enig filter de View.declaratieregels wordt opgehaald komen de getallen niet overeen. Aangezien Invantive een view gebruikt kan ik onderstaande informatie niet vergelijken.

Hieronder de terugkoppeling vanuit Easyflex:

Ik heb de declaratie van week 5 / 2024 opgehaald voor flexwerker 4280344 via ds_bi_declaratieregels. Hier zit inderdaad de reiskostenvergoeding. Het veld <bi_loononbelast> heeft wel een waarde van 25.60 (5.00 x 5.12)
<bi_fwaantal xsi:type=“xsd:float”>5.00</bi_fwaantal>
<bi_rlaantal xsi:type=“xsd:float”>5.00</bi_rlaantal>
<bi_eenheid xsi:type=“xsd:int”>24172</bi_eenheid>
<bi_fwgeldbelast xsi:type=“xsd:float”>0.00</bi_fwgeldbelast>
<bi_fwgeldonbelast xsi:type=“xsd:float”>5.12</bi_fwgeldonbelast>
<bi_rltarief xsi:type=“xsd:float”>5.12</bi_rltarief>
<bi_loonbelast xsi:type=“xsd:float”>-0.04</bi_loonbelast>
<bi_loononbelast xsi:type=“xsd:float”>25.60</bi_loononbelast>
Ook bij flexwerker 4278939 is de waarde van <bi_loononbelast> ingevuld (46.88). Heb je wellicht voor mij meer informatie waar precies het verschil zit?

2

image
image1570×586 57.2 KB

3

De view Declaratieregels is geintroduceerd omdat Power BI niet kan omgaan met tabelfuncties. Echter, deze kunnen wel los opgevraagd worden via de UniversalSQL-editor.

Open hiervoor de editor en gebruik bijvoorbeeld de volgende query om de gegevens over januari en februari 2024 op te vragen:

select drl.*
from   DataService.BITool.Declaratieregels(jaar => 2024, maand => 1) drl
where  fwregistratienummer = 4280344
union all
select drl.*
from   DataService.BITool.Declaratieregels(jaar => 2024, maand => 2) drl
where  fwregistratienummer = 4280344

Hierbij wordt er van uit gegaan dat het flexwerkernummer als fwregistratienummer doorgegeven is. Andere velden zijn ook mogelijk.

De webserviceverzoeken worden hier 1-op-1 van afgeleid i.h.k.v. herleidbaarheid. Indien de inhoud van achterliggende webserviceverzoeken benodigd zijn, dan kunnen de URL’s opgevraagd worden binnen de UniversalSQL-editor zoals beschreven in Welke API calls heeft Invantive UniversalSQL daadwerkelijk uitgevoerd?.

Ook kan de volledige XML-payload van het verzoek en het antwoord geraadpleegd worden. Installeer hiervoor het Invantive Query Tool en meld aan op Easyflex.

Vervolgens kan het verkeer bekeken worden zoals beschreven in Collect Native Platform Call Data (Engels).

4

Een andere wijze om deze analyse te doen is door te filteren niet op maand (zoals in de view gebeurt), maar op week. Een voorbeeld dat meteen een trace log maakt is (vervang hierbij jaar, week en registratienummer door de gewenste informatie):

set log-native-calls-to-trace true

select drl.*
from   DataService.BITool.Declaratieregels
       ( jaar => 2024
       , week => 5
       , fwregistratienummer => 4280344
       ) drl

De bovenstaande query geeft een dataset met de declaratieregels van week 5 voor 4280344.

Hierbij geldt dat dergelijke datasets een deelverzameling moeten zijn van grotere reeksen, dus bijvoorbeeld de volgende query MOET nul rijen bevatten:

select drl.*
from   DataService.BITool.Declaratieregels(jaar => 2024, week => 5) drl
minus
select *
from   ( select drl.*
         from   DataService.BITool.Declaratieregels(jaar => 2024, maand => 1) drl
         union all
         select drl.*
         from   DataService.BITool.Declaratieregels(jaar => 2024, maand => 2) drl
       )

Vraag: klopt het dat hier 0 rijen uit komen?

Dit geldt ook voor:

select drl.*
from   DataService.BITool.Declaratieregels(jaar => 2024, week => 5, fwregistratienummer => 4280344) drl
minus
select *
from   ( select drl.*
         from   DataService.BITool.Declaratieregels(jaar => 2024, maand => 1, fwregistratienummer => 4280344) drl
         union all
         select drl.*
         from   DataService.BITool.Declaratieregels(jaar => 2024, maand => 2, fwregistratienummer => 4280344) drl
       )

Vraag: klopt het dat hier 0 rijen uit komen?

5

Zojuist beide queries uitgevoerd en ontvang niet dezelfde responses:

Bij deze query komen er zes regels terug (zoals verwacht)

select drl.*
from   DataService.BITool.Declaratieregels( jaar => 2024, week => 5) drl
where  fwregistratienummer = 4280344

Bij deze query komen er vijf regels terug. Een minder en dat is precies de reiskosten:

select *
from   Easyflex.Views.DeclaratieRegels@efx t
where  (([Jaar] = 2024) and ([DECLPERIODENUMMER] = 5) and ([FWREGISTRATIENUMMER] = 4280344))

Ik ben benieuwd waarom de output niet hetzelfde is en hoe dat kan worden opgelost in de view.

6

Advies is om ook de tweede en derde bovenstaande query uit te voeren van Easyflex Data validatie ds_bi_declaratieregels - 4 van forums; hiermee kan gecontroleerd worden dat Easyflex de vertaalslag weken naar dagen, en maanden naar dagen dekkend maakt.

De tweede query bij Easyflex Data validatie ds_bi_declaratieregels - 5 van khk direct hierboven heeft een andere betekenis en maakt het niet mogelijk om een oorzaak vast te stellen.

7

Vreemd genoeg levert query voor specifieke flexwerker 0 regels op, maar dezelfde query voor alle flexwerkers levert 134 rijen op. En in de laatste query komt die specifieke medewerker met 1regel wel naar boven en dat is precies het verschil.

8

Zojuist de opgegeven query aangepast en dan komt ook dezelfde regel naar boven. Kennelijk gaat daar iets niet goed met maand en week

select drl.*
from   DataService.BITool.Declaratieregels(jaar => 2024, week => 5) drl
where	fwregistratienummer = 4280344
minus
select *
from   ( select drl.*
         from   DataService.BITool.Declaratieregels(jaar => 2024, maand => 1) drl
		where	fwregistratienummer = 4280344
         union all
         select drl.*
         from   DataService.BITool.Declaratieregels(jaar => 2024, maand => 2) drl
		where	fwregistratienummer = 4280344
       )
9

Op welk veld is de maand en week gebaseerd is dat DECLPERIODENUMMER?

10

Het lukt niet om de laatste drie toevoegingen te interpreteren.

Is het mogelijk om een antwoord toe te voegen voor de laatste drie voorgaande antwoorden met daarin telkens:

  • exact welke query wordt uitgevoerd,
  • wat de uitkomst is,
  • of die aansluit bij de verwachtingen.
13
  • Deze query is uitgevoerd
  • Resultaat: 1 regel
  • Zoals verwacht, want de data is niet volledig en dit is precies de regel die ontbreekt

Zonder filter op flexwerker (fwregistratienummer) blijven er meer regels over en dat klopt want er ontbreekt meer informatie.

14

Volgens deze query vallen een deel van de declaratieregels van week 5 van 2024 niet in de regels van maand 1 en ook niet in de regels van maand 2 van 2024.

Dit zou op basis van de te verwachten werking van de Easyflex-API’s wel het geval zijn. Mogelijkerwijs hanteert Easyflex een andere interpretatie dan verwacht van week en/of maand als meegegeven parameter.

Advies is om een native call log op te sturen naar Easyflex. In deze native call log (zoals boven beschreven) is via een teksteditor na te gaan dat deze regel van 4280344 ontbreekt in de maanden, maar wel aanwezig is in de weken.

Hierbij kunt u bijvoorbeeld zoeken op de declaratie ID (uniek) via >116861652</bi_declid>. Deze hoort er - indien alle dagen van week 5 vallen in maand 1 of maand 2 - precies 2x in te staan als het juist werkt.

15

Terugkoppeling ontvangen vanuit Easyflex en in de soap statements van Easyflex blijkt dat zowel bij maand of week filter regels niet tussen wal en schip vallen. Speciek gaat het bijvoorbeeld om id 116861652 deze komt zowel met maand als week filter terug in de output.

16

Helaas kunnen we dit niet bevestigen in de native call log. De bovengenoemde waarde 116861652 komt eenmalig voor. Omdat de afwijking niet reproduceerbaar te herleiden is naar de Invantive UniversalSQL-driver of engine, is het advies om een alternatief te zoeken.

Dat zou bijvoorbeeld kunnen zijn het gebruik van de weektabel die blijkbaar wel het verwacht resultaat geeft in deze omgeving zoals boven getoond:

select drl.*
from   DataService.BITool.Declaratieregels(jaar => 2024, week => 5) drl
where	fwregistratienummer = 4280344

Dit zou u kunnen combineren in een eigen view (zie Eigen database-views gebruiken voor SQL en BI-tools) met Calendar@DataDictionary ongeveer zoals in onderstaand voorbeeld waarbij niet op maand maar op weeknummer gestuurd wordt:

select clr.year_number Jaar
,      clr.month_number Maand
,      drl.*
from   ( select min(jaar) jaar
         from   DataService.Werkmaatschappij.Loonjaren
       ) jr
join   CALENDAR@DataDictionary clr
on     ( clr.YEAR_NUMBER between coalesce(jr.jaar - 1, year(sysdateutc) - 4) and year(sysdateutc) + 1)
and    clr.day_in_month = 1
join   DataService.BITool.Declaratieregels(jaar => clr.year_number, maand => clr.month_number) drl
17

Via de mail zijn de SOAP statements gedeeld waarbij zowel het maand als week filter is gebruikt. In beide uitkomst komt het specifieke voorbeeld 116861652 terug. Kan daar een analyse op worden gedaan? Het is niet wenselijk om een eigen view te maken als dit standaard aanwezig moet zijn.

18

Uit de eigen analyse kwam een ander resultaat. Er zijn geen plannen om een verdere analyse uit te voeren.

Advies is om een eigen view te maken die het voor u gewenste resultaat teruggeeft.

19

Het verrast mij dat dit niet wordt onderzocht terwijl vanuit Easyflex wordt aangegeven dat beide dezelfde uitkomst opleveren, waarom zou dat niet bij Invantive zo zijn.

20

Uit de eigen analyse kwam een ander resultaat. Uitgaande van de contractuele voorwaarden van de support is hier voor Invantive meer dan het maximaal haalbare geleverd.

Advies is om gebruik te maken van de geadviseerde workaround. Deze is er op gebaseerd dat onderstaande query wel het gewenste resultaat geeft en zal waarschijnlijk dan een voor uw situatie beter-passend resultaat geven:

select drl.*
from   DataService.BITool.Declaratieregels(jaar => 2024, week => 5) drl
where	fwregistratienummer = 4280344
minus
select *
from   ( select drl.*
         from   DataService.BITool.Declaratieregels(jaar => 2024, maand => 1) drl
         where  fwregistratienummer = 4280344
         union all
         select drl.*
         from   DataService.BITool.Declaratieregels(jaar => 2024, maand => 2) drl
         where  fwregistratienummer = 4280344
       )
21

Zojuist dit voorbeeld uitgeprobeerd maar specifieke data van 29 januari 2024 - Decl ID - 116861652 komt niet naar boven. Kennelijk lijkt dit toch niet te werken.

22

Uit nader onderzoek op basis van consulting services blijkt het volgende:

Aantal rijen in januari

Bij het opvragen van het aantal rijen in januari middels onderstaande query komt het getal 5509:

select count(*)
from   range@datadictionary(8, 1) rge
join   DataService.BITool.Declaratieregels(jaar => 2024, week => rge.value) drl
on     month(drl.decldatum) = 1

Hierbij is de aanname gedaan dat de maand gelijk is aan de kalendermaand van de declaratiedatum (het veld decldatum). De periode qua weken is bewust breed genomen met 8 weken zodat er geen randgevallen zijn.

Echter, de volgende query op basis van tabelfunctieparameter maand gelijk aan 1 geeft aan dat er maar 5154 regels zijn:

select count(*)
from   DataService.BITool.Declaratieregels(jaar => 2024, maand => 1) drl

Er is dus een wezenlijk verschil in uitkomst.

Plaatsing 10558589

Een diepere analyse is uitgevoerd op basis van plaatsingsnummer uit Easyflex. Het plaatsingsnummer van de declaratie 116861652 van registratie 4280344 is 10558589 (bepaald via de bovenstaande queries).

Specifieke filtering op deze plaatsing via de tabelfunctieparameter plaatsingnummer geeft 16 rijen:

select drl.*
from   DataService.BITool.Declaratieregels(jaar => 2024, maand => 1, plaatsingnummer => 10558589) drl

maar zonder een tabelfunctieparameter met filtering alleen in de UniversalSQL-driver (dus client-side) komen er maar 15 rijen uit:

select drl.*
from   DataService.BITool.Declaratieregels(jaar => 2024, maand => 1) drl
where  plaatsingnummer = 10558589

Merk op dat de Easyflex UniversalSQL-driver op dit moment niet automatisch de where-clause omschrijft naar een tabelfunctieparameter zoals bijvoorbeeld bij Teamleader Focus wel vaak gebeurt (zie bijvoorbeeld Teamleader V2 performance improvements in server-side filtering of date columns).

Om uit te sluiten dat de where-clause niet juist geimplementeerd is in Invantive UniversalSQL, is ook de volgende variant uitgevoerd. Deze gaf echter 16 rijen retour, dus het client-side filter heeft geen merkbare invloed en het wel/niet meegeven van de tabelfunctieparameter wel:

select drl.*
from   DataService.BITool.Declaratieregels(jaar => 2024, maand => 1, plaatsingnummer => 10558589) drl
where  plaatsingnummer = 10558589

Paginering

Easyflex werkt met paginering. Indien paginering niet juist geimplementeerd is binnen de UniversalSQL-driver voor Easyflex dan zou dat kunnen leiden tot de gesignaleerde afwijking. Bij problemen met paginering zijn de twee meest waarschijnlijke oorzaken:

  • geen paginering gebruikt terwijl die er wel moet zijn,
  • gaten tussen pagina’s waar meestal 1 rij tussen pagina’s “verdwijnt”.

De paginagrootte van Easyflex voor de achterliggende API is 5000 (zie https://easyflex.atlassian.net/wiki/spaces/WEBDATAKLNT/pages/530219856/5.+BI-tool+dataservice+operaties).

Een code review van de Invantive UniversalSQL-driver voor Easyflex toont aan dat paginering aanwezig is. De driver geeft geen totenmet mee en altijd een vanaf plus de maximale paginagrootte (per API mogelijk anders).

Na ontvangst van een pagina met gegevens wordt gekeken of de hoeveelheid data overeenstemt met de maximale paginagrootte. Zo ja, dan wordt een volgende pagina opgehaald. Zo nee, dan is de dataset compleet.

Merk op dat de Invantive UniversalSQL-engine “streaming” is over de gehele keten zolang er geen groepsfuncties zijn (zoals count); er worden dus al rijen teruggegeven voordat de volledige dataset verzameld is.

Het aantal rijen voor maand 1 is 5154, terwijl er gemeten via de weekmethode 5509 uitkomen. Dit is ruim meer dan de maximale 5.000, dus dat duidt er op dat paginering plaatsvindt. Tenslotte kunnen er conform documentatie maar 5000 rijen per pagina terugkomen.

Het ontbrekende aantal van 355 rijen is significant meer dan het standaardgat dat soms in een algoritme optreedt doordat er inclusief of exclusief grenzen gewerkt wordt. In dat geval zou de afwijking 1 zijn; 5509 rijen zouden twee pagina’s behoeven, waardoor bij het verkeerd inclusief/exclusief werken 2 - 1 = 1 rij theoretisch zou kunnen wegvallen.

Een nader onderzoek van de daadwerkelijk uitgevoerde sessie I/O’s laat zien dat voor 5154 rijen echter maar een API-call nodig was via:

select call_safe_name
,      parameter_list
from   sessionios@datadictionary
where  data_container_alias = 'efx'
order
by     id desc

Mogelijk leidt het ontbreken van de parameter totenmet tot problemen. De parameter is gedocumenteerd als “O” (optioneel), maar het zou kunnen dat bij weglating het gedrag van de API-server niet de documentatie weerspiegelt.

Er blijken echter ook 5154 rijen geretourneerd te worden als specifiek het aantal van 5.000 meegegeven wordt in de query:

select /*+ http_disk_cache(false) http_memory_cache(false) */ 
       count(*)
from   DataService.BITool.Declaratieregels(jaar => 2024, maand => 1, totenmet => 5000) drl

Dat is eigenaardig.

Rij-aantallen

Door de totenmet te varieren is uiteindelijk een specifieke case gevonden via de volgende query:

select /*+ http_disk_cache(false) http_memory_cache(false) */ 
       *
from   DataService.BITool.Declaratieregels(jaar => 2024, maand => 3, totenmet => 31) drl
minus
select /*+ http_disk_cache(false) http_memory_cache(false) */ 
       *
from   DataService.BITool.Declaratieregels(jaar => 2024, maand => 3, totenmet => 30) drl

De verwachte uitkomst is 31 - 30 = 1 rij. Echter, er worden twee rijen geretourneerd die vrijwel identiek zijn. Het gaat bij twee regels voor beiden declid 375450208. Enkel de kolommen businessunit en businessunitnaam hebben een andere waarde. Dit is gecontroleerd doordat de volgende query maar 1 regel teruggeeft:

select /*+ http_disk_cache(false) http_memory_cache(false) */ 
       * except businessunit, businessunitnaam
from   DataService.BITool.Declaratieregels(jaar => 2024, maand => 3, totenmet => 31) drl
minus
select /*+ http_disk_cache(false) http_memory_cache(false) */ 
       * except businessunit, businessunitnaam
from   DataService.BITool.Declaratieregels(jaar => 2024, maand => 3, totenmet => 30) drl

Het valt hierbij op dat beide regels dezelfde waarde voor declid hebben. Dat is eigenaardig. Mogelijk is dat een hint naar de oorzaak.

Het aantal declaraties is bekeken via:

select count(distinct declid)
from   DataService.BITool.Declaratieregels(jaar => 2024, maand => 3, totenmet => 5000) drl

Dit aantal blijkt exact 5000 te zijn.

De volgende hypothese is dat het XML-formaat van de Easyflex SOAP API-server misschien verschillende XML-niveau’s kent, en dat UniversalSQL mogelijk het onderste niveau telt (waar dan 5149 regels uit volgen) en Easyflex het bovenste niveau (waar 5000 knopen zitten). Een voorbeeld daarvan is onderstaand weergegeven en is vergelijkbaar met boekstukkop met meerdere boekstukregels:

<root>
  <niveau1>
     <VELD1>waarde</VELD1>
     <niveau2><VELD2>waarde</VELD2></niveau2>
     <niveau2><VELD2>waarde</VELD2></niveau2>
  </niveau1>
</root>

Echter, het door Easyflex gehanteerde XML-formaat kent maar 1 niveau en is erg eenvoudig:

...
<ds_bi_declaratieregels_result>
<item><VELD1>waarde</VELD1>...</item>
<item><VELD1>waarde</VELD1>...</item>
<item><VELD1>waarde</VELD1>...</item>
</ds_bi_declaratieregels_result>
...

Al met al kan gesteld worden dat het volgende deel van de documentatie niet juist geformuleerd is:

totenmet - xsd:int - O - Eindpunt van te selecteren regels (max = 5000 per keer).

Duiding

Het woord “regels” zou kunnen duiden op “declaratieregelnummers”.

Mogelijkerwijs is deze fout historisch ontstaan omdat de documentatie een hint geeft dat er twee semantische betekenissen mogelijk zijn van declaratieregels:

  • Het is ook mogelijk dat meerdere businessunits zijn gekoppeld aan een plaatsing in Easyflex met een procentuele verdeling van omzet/kosten.
    Dan wordt per afzonderlijke businessunit een declaratieregel geretourneerd met daarin het idnr van een business unit en enkel die uren, kosten en omzet die horen bij dezelfde businessunit.

Het gevonden voorbeeld heeft verschillende businessunits, maar twee keer hetzelfde declaratienummer. Volgens bovenstaande tekst zijn dat er twee declaratieregels: een per afzonderlijke businessunit.

Uit de voorgaande zinsnede lijkt het alsof de per afzonderlijke businessunit geretourneerde <item/> ook een declaratieregel is, maar dan een ander dan gebruikt voor de telling.

Daarnaast zou het kunnen zijn dat het woord “regels” helemaal niet verwijst naar “declaratieregelnummers” of eventueel “declaratieregels” (variant 1) of “declaratieregels” (variant 2), maar gewoon naar regels uitvoerresultaat voor de paginering. Dit is bij verre het meest gebruikelijk - zo niet universeel - als industriestandaard.

Het blijft gokken; advies is om de leverancier van Easyflex de documentatie te laten bijstellen zodat:

  • de tenminste twee verschillende betekenissen van “declaratieregels” gesplitst of samengevoegd worden zodat de definities eenduidig zijn;
  • op vergelijkbare wijze de betekenis van alle voorkomens van “regels” te corrigeren door splitsing of samenvoeging;
  • duidelijk uit de documentatie volgt wat het aantal verwachte <item/> elementen per pagina is;
  • de werking van de software conform nieuwe documentatie is.

Zodra dit gebeurt is kan indien nog nodig de driver verbeterd worden.

Het uitgangspunt van de Invantive UniversalSQL-engine is dat aantoonbare correctheid bij resultaat zwaarder weegt dan werking uberhaupt. In de volgende release van Easyflex zal daarom een application control itgenefx026 opgenomen zijn dat bij retour van meer rijen dan verwacht een fatale fout optreedt.

Een regressietest is uitgevoerd voor alle Easyflex API’s. Het blijkt dat de volgende API’s onder hetzelfde euvel leiden (en dus meer rijen terug kunnen geven dan verwacht):