✨Data Quality Management report requirements (Central) Worship Services

Requirements for reports to check the data quality on the production environment.

SPARQL queries available on Gitbook: https://github.com/bdevloed/op-data-quality

Legend

• ❌ 20 results = Manual check and data manipulation of 20 records in Excel

• ✅ no result = OK = No manual check needed. The results table is empty.

• ⚠ ERROR = Couldn't create a result via the sparql query, an error message was shown

Missing data available in Excel tables in SharePoint folder 05b_Data_Quality_WorshipServices_SPARQLqueries

Errors to be checked.

1. Administrative Units

1.1. Core data of a (central) worship service

If a list is provided for the following items, we would like the list to also contain the following data:

• uri of the (central) worship service

• label of the (central) worship service

• the type of worship service

• the province of the primary site

​​🔴 A clear issue

Completeness

• 1.1.1. A count and list of an active (central) worship service without a KBO number ❌ 18 results

• 1.1.2. A count and list of an active (central) worship service without a - complete - address for their Primary Site (Province, Gemeente, Postal code, Street and Street number) ❌ 1 result

• 1.1.3. A count and list of (central) worship service without a type of worship service ✅ no result = OK

• 1.1.4. A count and list of (central) worship service without a status ✅ no result = OK

Uniqueness

• 1.1.5. A count and list of all types of administrative units with a non unique KBO number ❌ 12 results

• 1.1.6. A count and list of all types of administrative units with a non unique SharePoint identificator ❌ 4 results OK - Because it cannot be changed on SharePoint

🟠 A potential issue

Completeness

• 1.1.7. A count and list of an active (central) worship service without a primary or secondary telephone number for the related Primary Site ❌ 1.927 results OK - Low priority

• 1.1.8. A count and list of active (central) worship services without a SharePoint identificator ❌ 1 result OK - Low priority

• 1.1.9. A count and list of active (central) worship services without a Representative body (Representatief Orgaan) ✅ no result = OK

Uniqueness

• 1.1.10. 👨‍🏭A count and list of all types of all administrative units with a non unique name ⚠ ERROR - Yassin 12/8/'22 - indeed, still not working

  • Yassin 30/8/'22 - now I get this error: - Error (#502)

Extra queries

• 1.1.11. A count and list of (central) worship services that have no primary site selected Nordine TO DO: created on 24/08/2022

1.2. Local Municipal Involvement

If a list is provided for the following items, we would like the list to also contain the following data:

• uri of the (central) worship service

• label of the (central) worship service

• the type of worship service

• the province of the primary site

🔴 A clear issue

Completeness

• 1.2.1 👨‍🏭A count and list of - active - worship services that don't have the local involvement of the type of 'toezichthoudend' with a percentage higher than 0%.

  ❌ Yassin 12/8/'22 - CB's need to be excluded!

  • Yassin 30/8/'22 - Only needs to contain active or in oprichting organizations not inactive organizations

Uniqueness

• 1.2.2 A count and list of worship services that have the local involvement of the type of 'toezichthoudend en financierend' more than once.

  ✅ no result = OK

  • Why? Only one gemeente or province can have the 'toezichthoudend en financierend' relationship with a (central) worship service

• 1.2.3 A count and list of worship services that have more than one local involvement relationship with the same administrative unit.

  ✅ 1 result = OK - case where Brussels needs to be added, and we added Vilvoorde twice instead

  • Why? It is not possible for a gemeente or province to have more than one type of local involvement (type betrokkenheid) with the same worship service.

Accuracy

• 1.2.4 A count and list of worship services with "Grensoverschrijdend=No" of the type Roman-Catholic, Protestant, Anglican or Israeli that have more than one local involvement relationship ❌ 1 result

  • Should be limited to the type "Anglican" or "Roman-catholic" or "Protestant" or "Israeli"

• 1.2.5 A count and list of worship services with "Grensoverschrijdend=Yes" that has only one local involvement relationship ✅ no result = OK

• 1.2.6 A count and list of worship services that has the local involvement of the type of 'toezichthoudend en financierend' with a percentage higher than 100% combined. ✅ no result = OK

• 1.2.7 : A count and list of worship services that has the local involvement of the type of 'toezichthoudend en financierend' of the type "Orthodox" or "Islamic" that is related to a "Gemeente" ✅ no result = OK

• 1.2.8 : A count and list of worship services that has the local involvement of the type of 'toezichthoudend en financierend' of the type "Anglican" or "Roman-catholic" or "Protestant" or "Israeli" that is related to a "Province" ✅ no result = OK - got back results of cathedrals - experts are informed of this

• 1.2.9 : A count and list of worship services that has the local involvement of the type of 'adviserend' of the type "Anglican" or "Roman-catholic" or "Protestant" or "Israeli" ✅ no result = OK

• 1.2.10: A count and list of active (central) worship services that have a local involvement with an organisation that is not active. Why do we need this? ❌ 2 results (14/03/2023: informed the business to change this in OP)

  • There might be non-active municipalities due to mergers. The new municipality should be linked to the (central) worship service.

  • This has an impact on the business rules used in Loket. In WOP the non active worship services are not shown.

1.3. Bestuursorganen

If a list is provided for the following items, we would like the list to also contain the following data:

• uri of the (central) worship service

• label of the (central) worship service

• the type of worship service

• the province of the primary site

🔴 A clear issue

Completeness

• 1.3.1 A count and list of an active (central) worship services with no active governing body (bestuursorgaan)

  ✅ no result = OK

• 1.3.2 A count and list of an active (central) worship services with more than 1 active governing body (bestuursorgaan)

  ✅ no result = OK

• 1.3.3 A count and list of active bestuursorganen of an inactive (central) worship service ❌ 47 results OK, best to be solved with an automatic script if (central) worship service is put to inactive

• 1.3.4 A count and list of active bestuursorganen of an active (central) worship service without an end date ✅ no result = OK

• 1.3.5 A count and list of - active - bestuursorganen of an active (central) worship service without a start and an end date ✅ no result = OK

Yassin Boullauazan: 1.3.4 has the same results as 1.3.5 (to double check by Boris?)

Claire, 26/08/22: I double checked, the query seems correct and gives different results on dev (800 without end date, 798 without start AND end date)

🟠 A potential issue

• 1.3.6 A count and list of active bestuursorganen of an active (central) worship service without a start date ❌ 2.301 results

  • ❌ Yassin 30/8/'22 - I expect only "active" bestuursorganen to be included in the results

• 1.3.7 👨‍🏭A count and list of bestuursorganen of an active (central) worship service where the difference between start and end date is lower than 2 years ❌ 42 results OK - this is possible due to corona election delay

• 1.3.8 👨‍🏭A count and list of bestuursorganen of a (central) worship service with more than 7 6 active members (Question: Is this the same in every type of religion? Maybe we can specify this) ✅ no result = OK??????

Claire, 26/08/22: Indeed, not OK, the query was wrong because if was considering only positions with an end date and it was counting the persons and not the mandatarissen. I prepared a fix.

Sofie - 13/07/22: Can you ask Boris for extra queries?

Yassin 30/8/'22 - Sofie M - regarding?

In order to refine the number of check on - active - members. We need to discuss this with Peggy. This is dependent on the type of worship service.

YB - 10/5/'22 update:

• Artikel 5. - De kerkraad bestaat uit vijf leden en de door het erkend representatief orgaan aangestelde verantwoordelijke van de parochie of zijn vervanger, die er van rechtswege deel van uitmaakt.

• Artikel 82. - De bestuursraad bestaat uit vijf verkozen leden en de predikant of zijn vervanger, die in de kerkgemeente zijn ambt uitoefent, die er van rechtswege deel van uitmaakt.

• Artikel 190. - De kerkfabriekraad bestaat uit vijf verkozen leden en de kerkbedienaar of zijn vervanger, die in de parochie zijn ambt uitoefent, die er van rechtswege deel van uitmaakt.

• Artikel 233 - Het comité bestaat uit vijf verkozen leden en de eerste imam of zijn vervanger, die in de islamitische gemeenschap zijn ambt uitoefent, die er van rechtswege deel van uitmaakt.

Why?

Loket (and OP) uses a codelist viewing the "bestuursorganen in a bestuursperiode" in a codelist. The start- and end-date is also important to know, to be able to add a position to the correct bestuursorgaan.

1.4. Mandates & their ContactInfo

Peggy: ISD is responsible for this part for the worship service (for the data correction). Managing the mandates is a high-effort task. Key positions are probably in SP. Updates happen based on 'notulen', but not for missing, this is caused by data correction.

• Sofie M - this is a seperate ticket for me to investigate (Yassin)

If a list is provided for the following items, we would like the list to also contain the following data:

• uri of the (central) worship service

• label of the (central) worship service

• the type of worship service

• the province of the primary site

• the governing body to which a mandate is being related to

• if possible the name of the position

Sofie 15/07/22: Reminder ... the contact details of the 'bestuursleden' need to be added by the business via the copy mechanism. I've created a new ticket for this https://binnenland.atlassian.net/browse/OP-1603

Only worship services have a penningmeester. Central worship services don't have that position in the governing body.

🔴 A clear issue

Completeness

• 1.4.1 A count and list (for each type of position) of - active - governing bodies without a

  • 1.4.1.A 👨‍🏭'voorzitter', ❌ 163 results

  • 1.4.1.B 'secretaris' or ❌ 228 results

  • 1.4.1.C 'penningmeester' ❌ 466 results

• 1.4.2 A count and list (for each type of position) of - active - governing bodies with an active 'voorzitter', 'secretaris' and/or 'penningmeester' without a primary or secondary phone number

  • 1.4.2.A 👨‍🏭'voorzitter' ❌ 309 results

  • 1.4.2.B 👨‍🏭'secretaris' ❌ 210 results

  • 1.4.2.C 👨‍🏭'penningmeester' ❌ 101 results

• 1.4.3 A count and list (for each type of position) of - active - governing bodies with a 'voorzitter', 'secretaris' and/or 'penningmeester' without a start date (start mandaat)

  • 1.4.3.A 'voorzitter' ❌ 27 results

  • 1.4.3.B 'secretaris' ❌ 27 results

  • 1.4.3.C 'penningmeester' ❌ 91 results

Validity

• 1.4.4 👨‍🏭A count and list of active bestuursorgaan where the difference between the start and planned or effective end date for a mandate is bigger than 3 years

  • 1.4.4.A = ❌ 285 results (difference between start and planned end date)

  • 1.4.4.B = ❌ 1 result (difference between start and effective end date)

  Sofie 15/7/22: Query 1.4.4 doesn't exist anymore, it has been split into A and B

• 1.4.5 A count and list of active bestuursorgaan where the end date of the bestuursorganen is earlier than the effective/planned end date of a position, that is part of that bestuursorgaan. ❌ 472 results

• 1.4.6 👨‍🏭A count and list of active bestuursorgaan where the start date of the bestuursorganen is later than the start date of a position, that is part of that bestuursorgaan. ❌ 285 results

• 1.4.7 A count and list of active mandates where the planned end date (geplande einddatum) is earlier than the start date (start mandaat). We should make a validation rule for this ❌ 54 results

• 1.4.8 A count and list of active mandates where the effective end date (effectieve einddatum) is earlier than the start date (start mandaat). We should make a validation rule for this ❌ 53 results

🟠 A potential issue

• 1.4.9 👨‍🏭A count and list of active mandates (part of active bestuursorganen) where the difference between the start and planned or effective end date is lower than 2 years (Validity)

  • 1.4.9.A = (difference between start and effective end date) ❌ 66 results

  • 1.4.9.B = (difference between start and planned end date) ❌ 65 results

  Sofie 15/7/22: Query 1.4.9 doesn't exist anymore, it has been split into A and B

• 1.4.10 A count and list (for each type of position) of - active - governing bodies with an active 'voorzitter', 'secretaris' or 'penningmeester' without an address

  • 1.4.10.A 'voorzitter' ❌ 216 results

  • 1.4.10.B 'secretaris' ❌ 251 results

  • 1.4.10.C 'penningmeester' ❌ 49 results

• 1.4.11 A count and list (for each type of position) of - active - governing bodies with an active 'voorzitter', 'secretaris' or 'penningmeester' without an email address

  • 1.4.11.A 'voorzitter' ❌ 110 results

  • 1.4.11.B 'secretaris' ❌ 149 results

  • 1.4.11.C 'penningmeester' ❌ 68 results

• 1.4.12 A count and list (for each type of position) of - active - governing bodies with an active 'voorzitter', 'secretaris' or 'penningmeester' with the start date 1/1/1900

  • 1.4.12.A 'voorzitter' ❌ 284 results

  • 1.4.12.B 'secretaris' ❌ 178 results

  • 1.4.12.C 'penningmeester' ❌ 124 results

Boris De Vloed: some (central) worship services have a "full address" field visible in the front-end. This is caused by the data migration, where the addresses are not split up, and saved as one string. For example. Are they saved separately in the graph? Which would require a query to identify them.

Sofie 15/07/2022 update: The full address problem has been solved via:

• https://binnenland.atlassian.net/browse/OP-1408

• https://binnenland.atlassian.net/browse/OP-1427

Boris De Vloed: Does query 1.4.10 need changes because of that?

Why?

ISD and LOW need to be able to contact a president or secretary in case of urgent issues. For LF the penningmeester is an ideal contact point. The old decree stipulates that LF in case of financial audit the penningmeester needs to receive a letter regarding their book keeping.

🚩1.5. 🔽 Ministers & their ContactInfo

If a list is provided for the following items, we would like the list to also contain the following data:

• uri of the (central) worship service

• label of the (central) worship service

• the type of worship service

• the province of the primary site

🔴 A clear issue

Completeness

• 1.5.1 👨‍🏭A count and list (for each type of position) of active worship services without at least one active minister ❌ 1604 results

• 1.5.2 👨‍🏭A count and list (for each type of position) of active worship services with active ministers without a start date (startdatum) ❌ 1604 results

• 1.5.3 👨‍🏭A count and list of active ministers where the end date (geplande einddatum) is earlier than the start date (start mandaat). We should make a validation rule for this. ✅ no results

🟠 A potential issue

• 1.5.4 👨‍🏭A count and list (for each type of position) of active worship services with a minister without a primary/secondary phone number ❌ 114 results

  • 25/08/2022: query fix requested in https://binnenland.atlassian.net/browse/OP-1694

• 1.5.5 👨‍🏭A count and list (for each type of position) of active worship services with a minister without an address ❌ 126 results

  • 25/08/2022: query fix requested in https://binnenland.atlassian.net/browse/OP-1694

• 1.5.6 👨‍🏭A count and list (for each type of position) of active worship services with a minister without an email address ❌ 126 results

  • 25/08/2022: query fix requested in https://binnenland.atlassian.net/browse/OP-1694

• 1.5.7. A count and list of positions 'Imam in rang' in a worship service that has the status 'in oprichting'

  • Why do we need this? All worship services that have the status ‘in oprichting’ can only have the position ‘Waarnemend Imam’ and not an ‘Imam in rang'. ‘Waarnemend Imam' is a recently added position. So previously the end-users couldn’t select this option. Instead, they choose one of the available options: ‘Eerste Imam in rang’, ‘Tweede Imam in rang’ or ‘Derde Imam in rang’.

Why?

ISD would like to have an overview of active ministers. This data however is currently not at hand.

Sofie 15/07/22: I added a flag to make it clear that the ministers quality check is currently blocked and no Excel overviews have been made. Can we use an other source to add this data?

Yassin 12/08/22: Is it possible to explain why it is blocked? After running the scripts it seems that ISD needs to do some data management hence there are wrongly registered position types used

1.5. Related organisations

If a list is provided for the following items, we would like the list to also contain the following data:

• uri of the (central) worship service

• label of the (central) worship service

• the type of worship service

• the province of the primary site

🔴 A clear issue

Accuracy

1.5.1: A count and list of (central) worship services that have related organisations that are not active. Why do we need this?

• There might be non-active municipalities due to mergers. The new municipality should be linked to the (central) worship service. This has to be done manually and might be forgotten.

2. Persons

If a list is provided for the following items, we would like the list to also contain the following data:

• uri of the (central) worship service

• label of the (central) worship service

• the type of worship service

• the province of the primary site

🔴 A clear issue

Completeness

• 2.1 A count and list of persons that don't have a position related to them (👨‍🏭fixed) ❌ 116 results

• 2.2 👨‍🏭A count and list (for each type of position) of - active - governing bodies with persons without positions (bestuursfunctie) ❌ 36 results

  • Why? This issue (compared to the first point) is more acute for end-users

  • ⚠ ERROR - Yassin 30/8/'22 - It contains inactive organizations

🟠 A potential issue

Uniqueness

• 2.3 👨‍🏭A count and list of persons that have a matching first and last name

  • Ideally, a fuzzy matching, so that capital letters, accents on characters are also deemed as a match

  Note in query: # the query is heavy and seems to go through when we run it via virtuoso but not via the SPARQL endpoint Claire - 26/08/2022: The query is already as light as it can

• 2.3.1 List of possible typos. A count and list of persons that have almost the same first and last name and have a position in the same worship service or in the connected Central worship service.

  • Boris TO DO: created on 11/08/2022

    Case insensitive & accent tolerant query

• 2.4 👨‍🏭A count and list of persons that are related to positions that are all inactive (thus all have an end date in the past) ❌ 4.037 results

• 2.5 A count and list of persons positions of persons without municipality in the address ❌ 9.106 results Sofie 15/07/22: I think we should only search for active positions. Change query?

• 2.6 A count and list of persons positions of persons without province in the address ❌ 14.239 results Sofie 15/07/22: I think we should only search for active positions. Change query? Can this be solved automatically? We we add an address now via CRAB, the province appears automatically.

Why?

We want to avoid having duplicate persons. Additionally, we want to check if there are persons that have no active positions, that can be considered for archiving.