Data Quality Monitoring Reports
(→Example 7: Unique Value Rule) |
(→Example 1.1: Missing Properties / Values (OWL Full Design)) |
||
(65 intermediate revisions by 2 users not shown) | |||
Line 1: | Line 1: | ||
− | + | Data Quality Monitoring Reports are an excellent instrument to quickly identify instances with requirement violations. | |
==Prerequisites== | ==Prerequisites== | ||
− | With the DQM-Vocabulary you can easily create | + | With the DQM-Vocabulary you can easily create Data Quality Monitoring reports. All you need is a SPARQL-Endpoint that supports [http://www.w3.org/TR/sparql11-query/ SPARQL 1.1] or SPARQL extensions that cover datatype conversion and conversion of strings to [[Wikipedia:URI|URIs]]. |
Before you can automatically analyze your data for requirement violations, you need to perform the following steps: | Before you can automatically analyze your data for requirement violations, you need to perform the following steps: | ||
Line 8: | Line 8: | ||
* [[Create Data Requirements]] | * [[Create Data Requirements]] | ||
− | == | + | ==Data Quality Reports with SPARQL== |
Each type of data requirement has different problem reports, since the nature of data requirements differs. Moreover, the actual SPARQL query you can use depends on the design option you have chosen to make your ontology elements an instance of [[dqm:TestedClass]] and [[dqm:TestedProperty]] (See [[Create Data Requirements#Define Tested Elements]] for an explanation). In the following you find several SPARQL queries that make use of your data requirements to identify class instances with requirement violations. | Each type of data requirement has different problem reports, since the nature of data requirements differs. Moreover, the actual SPARQL query you can use depends on the design option you have chosen to make your ontology elements an instance of [[dqm:TestedClass]] and [[dqm:TestedProperty]] (See [[Create Data Requirements#Define Tested Elements]] for an explanation). In the following you find several SPARQL queries that make use of your data requirements to identify class instances with requirement violations. | ||
− | ===Example 1.1: | + | ===Example 1.1: Missing Properties / Values (OWL Full Design)=== |
− | + | {|class="wikitable" | |
− | '''Scenario:''' Find all instances of a certain class that miss a property or a property value. | + | |'''Scenario:'''||Find all instances of a certain class that miss a property or a property value. |
− | + | |- | |
− | '''Input:''' All property completeness rules (in OWL Full Design) | + | |'''Input:'''||All property completeness rules (in OWL Full Design) |
− | + | |- | |
− | '''Output:''' All instances with missing values | + | |'''Output:'''||All instances with missing properties and values |
− | + | |} | |
− | <syntaxhighlight lang=" | + | <syntaxhighlight lang="n3"> |
SELECT ?dqr ?i | SELECT ?dqr ?i | ||
WHERE { | WHERE { | ||
Line 31: | Line 31: | ||
{ | { | ||
?i a ?tclass . | ?i a ?tclass . | ||
− | + | FILTER NOT EXISTS{ | |
?i ?tprop ?value . | ?i ?tprop ?value . | ||
} | } | ||
Line 40: | Line 40: | ||
} | } | ||
</syntaxhighlight> | </syntaxhighlight> | ||
− | [[Create Data Requirements#Example 1: | + | [[Create Data Requirements#Example 1: Property Completeness Rule |Click here to learn how to define a data requirement for this report]] |
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
+ | ===Example 1.2: Missing Properties / Values (OWL DL Design)=== | ||
+ | {|class="wikitable" | ||
+ | |'''Scenario:'''||Find all instances that miss a property or a property value. | ||
+ | |- | ||
+ | |'''Input:'''||All property completeness rules (in OWL DL Design) | ||
+ | |- | ||
+ | |'''Output:'''||All instances with missing properties and values | ||
+ | |} | ||
<syntaxhighlight lang="sparql"> | <syntaxhighlight lang="sparql"> | ||
SELECT ?dqr ?i | SELECT ?dqr ?i | ||
Line 75: | Line 73: | ||
} | } | ||
</syntaxhighlight> | </syntaxhighlight> | ||
− | [[Create Data Requirements#Example 1: | + | [[Create Data Requirements#Example 1: Property Completeness Rule|Click here to learn how to define a data requirement for this report]] |
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
+ | ===Example 2: Conditional Missing Properties / Values (1 Condition, OWL DL Design)=== | ||
+ | {|class="wikitable" | ||
+ | |'''Scenario:'''||Find all instances in a subset of a certain class that miss a property or a property value. | ||
+ | |- | ||
+ | |'''Input:'''||All conditional property completeness rules (in OWL DL Design) | ||
+ | |- | ||
+ | |'''Output:'''||Instances with missing properties and values | ||
+ | |} | ||
<syntaxhighlight lang="sparql"> | <syntaxhighlight lang="sparql"> | ||
SELECT ?dqr ?i ?condvalue | SELECT ?dqr ?i ?condvalue | ||
Line 95: | Line 93: | ||
?tclass dqm:hasURI ?tclassreal . | ?tclass dqm:hasURI ?tclassreal . | ||
?tprop dqm:hasURI ?tpropreal . | ?tprop dqm:hasURI ?tpropreal . | ||
− | ?dqr dqm: | + | ?dqr dqm:hasCondition1 ?cond1 . |
?cond1 dqm:conditionalProperty ?cprop . | ?cond1 dqm:conditionalProperty ?cprop . | ||
?cprop dqm:hasURI ?cpropreal . | ?cprop dqm:hasURI ?cpropreal . | ||
?cond1 dqm:equals ?condvalue . | ?cond1 dqm:equals ?condvalue . | ||
+ | MINUS{ | ||
+ | ?dqr dqm:hasCondition2 ?cond2 . | ||
+ | } | ||
+ | MINUS{ | ||
+ | ?dqr dqm:hasCondition3 ?cond3 . | ||
+ | } | ||
+ | MINUS{ | ||
+ | ?dqr dqm:hasCondition4 ?cond4 . | ||
+ | } | ||
+ | MINUS{ | ||
+ | ?dqr dqm:hasCondition5 ?cond5 . | ||
+ | } | ||
BIND (IRI(str(?tclassreal)) AS ?tclassURI) . | BIND (IRI(str(?tclassreal)) AS ?tclassURI) . | ||
BIND (IRI(str(?tpropreal)) AS ?tpropURI) . | BIND (IRI(str(?tpropreal)) AS ?tpropURI) . | ||
Line 117: | Line 127: | ||
</syntaxhighlight> | </syntaxhighlight> | ||
− | [[Create Data Requirements#Example 2: Conditional Property Completeness Rule|Click here to learn how to define a data requirement for this report]] | + | [[Create Data Requirements#Example 2: Conditional Property Completeness Rule (1 Condition, OWL DL)|Click here to learn how to define a data requirement for this report]] |
− | ===Example 3: Syntax | + | ===Example 3: Syntax Violations (OWL DL Design)=== |
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
+ | {|class="wikitable" | ||
+ | |'''Scenario:'''||Find all instances of a certain class with property values that violate a syntax rule. | ||
+ | |- | ||
+ | |'''Input:'''||All syntax rules (in OWL DL Design) | ||
+ | |- | ||
+ | |'''Output:'''||All instances with property values that have syntax violations | ||
+ | |} | ||
<syntaxhighlight lang="sparql"> | <syntaxhighlight lang="sparql"> | ||
SELECT ?dqr ?i | SELECT ?dqr ?i | ||
Line 146: | Line 156: | ||
[[Create Data Requirements#Example 3: Syntax Rule (OWL DL)|Click here to learn how to define a data requirement for this report]] | [[Create Data Requirements#Example 3: Syntax Rule (OWL DL)|Click here to learn how to define a data requirement for this report]] | ||
− | ===Example 4: Conditional Syntax | + | ===Example 4: Conditional Syntax Violations (1 Condition, OWL DL Design)=== |
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
+ | {|class="wikitable" | ||
+ | |'''Scenario:'''||Find all syntax violations of property values in a subset of a certain class. | ||
+ | |- | ||
+ | |'''Input:'''||All conditional syntax rules(in OWL DL Design). | ||
+ | |- | ||
+ | |'''Output:'''||All instances of the subset of the class that have syntax violations. | ||
+ | |} | ||
<syntaxhighlight lang="sparql"> | <syntaxhighlight lang="sparql"> | ||
SELECT ?dqr ?i | SELECT ?dqr ?i | ||
Line 168: | Line 178: | ||
?cprop dqm:hasURI ?cpropreal . | ?cprop dqm:hasURI ?cpropreal . | ||
?cond1 dqm:equals ?condvalue . | ?cond1 dqm:equals ?condvalue . | ||
+ | MINUS{ | ||
+ | ?dqr dqm:hasCondition2 ?cond2 . | ||
+ | } | ||
+ | MINUS{ | ||
+ | ?dqr dqm:hasCondition3 ?cond3 . | ||
+ | } | ||
+ | MINUS{ | ||
+ | ?dqr dqm:hasCondition4 ?cond4 . | ||
+ | } | ||
+ | MINUS{ | ||
+ | ?dqr dqm:hasCondition5 ?cond5 . | ||
+ | } | ||
BIND (IRI(str(?tpropreal)) AS ?tpropURI) . | BIND (IRI(str(?tpropreal)) AS ?tpropURI) . | ||
BIND (IRI(str(?tclassreal)) AS ?tclassURI) . | BIND (IRI(str(?tclassreal)) AS ?tclassURI) . | ||
Line 178: | Line 200: | ||
</syntaxhighlight> | </syntaxhighlight> | ||
− | + | [[Create Data Requirements#Example 4: Conditional Syntax Rule (1 Condition, OWL DL)|Click here to learn how to define a data requirement for this report]] | |
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
+ | ===Example 5: Out of Range Values (OWL DL Design)=== | ||
+ | {|class="wikitable" | ||
+ | |'''Scenario:'''||Find all instances that have property values out of a specified range. | ||
+ | |- | ||
+ | |'''Input:'''||All legal value range rules (in OWL DL Design) | ||
+ | |- | ||
+ | |'''Output:'''||All instances with out of range values | ||
+ | |} | ||
<syntaxhighlight lang="sparql"> | <syntaxhighlight lang="sparql"> | ||
SELECT ?dqr ?i | SELECT ?dqr ?i | ||
Line 208: | Line 232: | ||
} | } | ||
</syntaxhighlight> | </syntaxhighlight> | ||
− | |||
− | |||
− | + | [[Create Data Requirements#Example 5: Legal Value Range Rule (OWL DL)|Click here to learn how to define a data requirement for this report]] | |
− | + | ||
− | + | ||
+ | ===Example 6: Illegal Values (OWL DL Design)=== | ||
+ | {|class="wikitable" | ||
+ | |'''Scenario:'''||Find all instances that have illegal property values, i.e. values that are not listed in the trusted reference property. | ||
+ | |- | ||
+ | |'''Input:'''||All legal value rules (in OWL DL Design). | ||
+ | |- | ||
+ | |'''Output:'''||All instances with values not listed in the trusted reference. | ||
+ | |} | ||
<syntaxhighlight lang="sparql"> | <syntaxhighlight lang="sparql"> | ||
SELECT ?dqr ?i | SELECT ?dqr ?i | ||
Line 242: | Line 270: | ||
</syntaxhighlight> | </syntaxhighlight> | ||
− | ===Example 7: | + | [[Create Data Requirements#Example 6: Legal Value Rule (OWL DL)|Click here to learn how to define a data requirement for this report]] |
+ | |||
+ | ===Example 7: Uniqueness Violations (OWL DL Design)=== | ||
{|class="wikitable" | {|class="wikitable" | ||
|'''Scenario:'''||Find all instances with property values that are not unique although they should. | |'''Scenario:'''||Find all instances with property values that are not unique although they should. | ||
Line 268: | Line 298: | ||
</syntaxhighlight> | </syntaxhighlight> | ||
− | ===Example 8: Functional Dependency Value Rule=== | + | [[Create Data Requirements#Example 7: Unique Value Rule (OWL DL)|Click here to learn how to define a data requirement for this report]] |
+ | |||
+ | |||
+ | ===Example 8: Functional Dependency Violations (1 Condition, OWL DL Design)=== | ||
+ | |||
+ | {|class="wikitable" | ||
+ | |'''Scenario:'''||Find all instances with inconsistent property values. | ||
+ | |- | ||
+ | |'''Input:'''||All functional dependency value rules with 1 condition (in OWL DL Design). | ||
+ | |- | ||
+ | |'''Output:'''||All instances with inconsistent property values. | ||
+ | |} | ||
+ | <syntaxhighlight lang="sparql"> | ||
+ | SELECT ?dqr ?i ?dvalue ?value | ||
+ | WHERE { | ||
+ | ?dqr a dqm:FuncDepValueRule . | ||
+ | ?dqr dqm:testedProperty1 ?tprop . | ||
+ | ?dqr dqm:testedClass ?tclass . | ||
+ | ?dqr dqm:equals ?dvalue . | ||
+ | ?tclass dqm:hasURI ?tclassreal . | ||
+ | ?tprop dqm:hasURI ?tpropreal . | ||
+ | ?dqr dqm:hasCondition1 ?cond1 . | ||
+ | ?cond1 dqm:conditionalProperty ?cprop1 . | ||
+ | ?cprop1 dqm:hasURI ?cpropreal1 . | ||
+ | ?cond1 dqm:equals ?condvalue1 . | ||
+ | MINUS{ | ||
+ | ?dqr dqm:hasCondition2 ?cond2 . | ||
+ | } | ||
+ | MINUS{ | ||
+ | ?dqr dqm:hasCondition3 ?cond3 . | ||
+ | } | ||
+ | MINUS{ | ||
+ | ?dqr dqm:hasCondition4 ?cond4 . | ||
+ | } | ||
+ | MINUS{ | ||
+ | ?dqr dqm:hasCondition5 ?cond5 . | ||
+ | } | ||
+ | BIND (IRI(str(?tpropreal)) AS ?tpropURI) . | ||
+ | BIND (IRI(str(?tclassreal)) AS ?tclassURI) . | ||
+ | BIND (IRI(str(?cpropreal1)) AS ?cpropURI1) . | ||
+ | ?i a ?tclassURI . | ||
+ | ?i ?cpropURI1 ?condvalue1 . | ||
+ | ?i ?tpropURI ?value . | ||
+ | FILTER (str(?dvalue) != str(?value)) . | ||
+ | } | ||
+ | </syntaxhighlight> | ||
+ | |||
+ | [[Create Data Requirements#Example 8: Functional Dependency Value Rule (1 Condition, OWL DL)|Click here to learn how to define a data requirement for this report]] | ||
+ | |||
+ | ===Example 9: Functional Dependency Violations (2 Conditions, OWL DL Design)=== | ||
+ | |||
+ | {|class="wikitable" | ||
+ | |'''Scenario:'''||Find all instances with inconsistent property values. | ||
+ | |- | ||
+ | |'''Input:'''||All functional dependency value rules with 2 conditions (in OWL DL Design). | ||
+ | |- | ||
+ | |'''Output:'''||All instances with inconsistent property values. | ||
+ | |} | ||
+ | <syntaxhighlight lang="sparql"> | ||
+ | SELECT ?dqr ?i | ||
+ | WHERE { | ||
+ | ?dqr a dqm:FuncDepValueRule . | ||
+ | ?dqr dqm:testedProperty1 ?tprop . | ||
+ | ?dqr dqm:testedClass ?tclass . | ||
+ | ?dqr dqm:equals ?dvalue . | ||
+ | ?tclass dqm:hasURI ?tclassreal . | ||
+ | ?tprop dqm:hasURI ?tpropreal . | ||
+ | ?dqr dqm:hasCondition1 ?cond1 . | ||
+ | ?cond1 dqm:conditionalProperty ?cprop1 . | ||
+ | ?cprop1 dqm:hasURI ?cpropreal1 . | ||
+ | ?cond1 dqm:equals ?condvalue1 . | ||
+ | ?dqr dqm:hasCondition2 ?cond2 . | ||
+ | ?cond2 dqm:conditionalProperty ?cprop2 . | ||
+ | ?cprop2 dqm:hasURI ?cpropreal2 . | ||
+ | ?cond2 dqm:equals ?condvalue2 . | ||
+ | MINUS{ | ||
+ | ?dqr dqm:hasCondition3 ?cond3 . | ||
+ | } | ||
+ | MINUS{ | ||
+ | ?dqr dqm:hasCondition4 ?cond4 . | ||
+ | } | ||
+ | MINUS{ | ||
+ | ?dqr dqm:hasCondition5 ?cond5 . | ||
+ | } | ||
+ | BIND (IRI(str(?tpropreal)) AS ?tpropURI) . | ||
+ | BIND (IRI(str(?tclassreal)) AS ?tclassURI) . | ||
+ | BIND (IRI(str(?cpropreal1)) AS ?cpropURI1) . | ||
+ | BIND (IRI(str(?cpropreal2)) AS ?cpropURI2) . | ||
+ | ?i a ?tclassURI . | ||
+ | ?i ?cpropURI1 ?condvalue1 . | ||
+ | ?i ?cpropURI2 ?condvalue2 . | ||
+ | ?i ?tpropURI ?value . | ||
+ | FILTER (str(?dvalue) != str(?value)) . | ||
+ | } | ||
+ | </syntaxhighlight> | ||
+ | |||
+ | [[Create Data Requirements#Example 9: Functional Dependency Value Rule (2 Conditions, OWL DL)|Click here to learn how to define a data requirement for this report]] | ||
+ | |||
+ | ===Example 10: Outdated / Expired Values (OWL DL Design)=== | ||
+ | |||
+ | {|class="wikitable" | ||
+ | |'''Scenario:'''||Find all outdated instances. | ||
+ | |- | ||
+ | |'''Input:'''||All expiry rules (in OWL DL Design) and validity date of instances. | ||
+ | |- | ||
+ | |'''Output:'''||All instances with that are not valid anymore. | ||
+ | |} | ||
+ | <syntaxhighlight lang="sparql"> | ||
+ | SELECT ?dqr ?i | ||
+ | WHERE { | ||
+ | ?dqr a dqm:ExpiryRule . | ||
+ | ?dqr dqm:testedProperty1 ?tprop . | ||
+ | ?dqr dqm:testedClass ?tclass . | ||
+ | ?tclass dqm:hasURI ?tclassreal . | ||
+ | ?tprop dqm:hasURI ?tpropreal . | ||
+ | BIND (IRI(str(?tpropreal)) AS ?tpropURI) . | ||
+ | BIND (IRI(str(?tclassreal)) AS ?tclassURI) . | ||
+ | BIND ((now()) AS ?today) | ||
+ | ?i a ?tclassURI . | ||
+ | ?i ?tpropURI ?expiry . | ||
+ | FILTER(?expiry<?today) | ||
+ | } | ||
+ | </syntaxhighlight> | ||
+ | |||
+ | [[Create Data Requirements#Example 10: Expiry Rule (OWL DL)|Click here to learn how to define a data requirement for this report]] | ||
+ | |||
+ | ===Example 11: Outdated / Not Updated Values (OWL DL Design)=== | ||
+ | |||
+ | {|class="wikitable" | ||
+ | |'''Scenario:'''||Find all outdated instances. | ||
+ | |- | ||
+ | |'''Input:'''||All update rules (in OWL DL Design) and timestamps of instances. | ||
+ | |- | ||
+ | |'''Output:'''||All instances with that are potentially outdated. | ||
+ | |} | ||
+ | <syntaxhighlight lang="sparql"> | ||
+ | SELECT ?dqr ?i | ||
+ | WHERE { | ||
+ | ?dqr a dqm:UpdateRule . | ||
+ | ?dqr dqm:testedClass ?tclass . | ||
+ | ?dqr dqm:testedProperty1 ?tprop . | ||
+ | ?dqr dqm:expectedUpdateInterval ?duration . | ||
+ | ?tclass dqm:hasURI ?tclassreal . | ||
+ | ?tprop dqm:hasURI ?tpropreal . | ||
+ | BIND (IRI(str(?tpropreal)) AS ?tpropURI) . | ||
+ | BIND (IRI(str(?tclassreal)) AS ?tclassURI) . | ||
+ | BIND((dqf:requiredTimestamp(str(?duration))) AS ?reqTimestamp) . | ||
+ | ?i a ?tclassURI . | ||
+ | ?i ?tpropURI ?timestamp . | ||
+ | FILTER(?reqTimestamp>?timestamp) | ||
+ | } | ||
+ | </syntaxhighlight> | ||
+ | |||
+ | [[Create Data Requirements#Example 11: Update Rule (OWL DL)|Click here to learn how to define a data requirement for this report]] | ||
+ | |||
+ | ===Example 12: Duplicate Instance Rule (3 Properties, OWL DL Design)=== | ||
+ | |||
+ | {|class="wikitable" | ||
+ | |'''Scenario:'''||Find duplicate instances. | ||
+ | |- | ||
+ | |'''Input:'''||Duplicate instance rules with 3 tested properties. | ||
+ | |- | ||
+ | |'''Output:'''||All instances that have identical values for the 3 tested properties. | ||
+ | |} | ||
+ | <syntaxhighlight lang="sparql"> | ||
+ | PREFIX dqm:<http://purl.org/dqm-vocabulary/v1.1/dqm#> | ||
+ | PREFIX xsd:<http://www.w3.org/2001/XMLSchema#> | ||
+ | SELECT ?dqr ?i | ||
+ | WHERE { | ||
+ | ?dqr a dqm:DuplicateInstanceRule . | ||
+ | ?dqr dqm:testedClass ?tclass . | ||
+ | ?tclass dqm:hasURI ?tclassreal . | ||
+ | BIND (IRI(str(?tclassreal)) AS ?tclassURI) . | ||
+ | ?dqr dqm:testedProperty1 ?tprop1 . | ||
+ | ?tprop1 dqm:hasURI ?tpropreal1 . | ||
+ | BIND (IRI(str(?tpropreal1)) AS ?tpropURI1) . | ||
+ | ?dqr dqm:testedProperty2 ?tprop2 . | ||
+ | ?tprop2 dqm:hasURI ?tpropreal2 . | ||
+ | BIND (IRI(str(?tpropreal2)) AS ?tpropURI2) . | ||
+ | ?dqr dqm:testedProperty3 ?tprop3 . | ||
+ | ?tprop3 dqm:hasURI ?tpropreal3 . | ||
+ | BIND (IRI(str(?tpropreal3)) AS ?tpropURI3) . | ||
+ | ?i a ?tclassURI . | ||
+ | ?i ?tpropURI1 ?value11 . | ||
+ | ?i2 ?tpropURI1 ?value12 . | ||
+ | ?i ?tpropURI2 ?value21 . | ||
+ | ?i2 ?tpropURI2 ?value22 . | ||
+ | ?i ?tpropURI3 ?value31 . | ||
+ | ?i2 ?tpropURI3 ?value32 . | ||
+ | FILTER(?i!=?i2 && str(?value11) = str(?value12) && str(?value21) = str(?value22) && str(?value31) = str(?value32)) | ||
+ | } | ||
+ | GROUP BY ?dqr | ||
+ | </syntaxhighlight> | ||
+ | |||
+ | |||
+ | ===Example 13: Functional Dependency Violations (2 Reference Properties, OWL DL Design)=== | ||
+ | |||
+ | {|class="wikitable" | ||
+ | |'''Scenario:'''||Find functional dependency violations between two property values. | ||
+ | |- | ||
+ | |'''Input:'''||Functional dependency reference rules with 2 tested properties. | ||
+ | |- | ||
+ | |'''Output:'''||All instances that have violating dependencies between the two tested property values. | ||
+ | |} | ||
+ | |||
+ | <syntaxhighlight lang="sparql"> | ||
+ | PREFIX dqm:<http://purl.org/dqm-vocabulary/v1.1/dqm#> | ||
+ | PREFIX xsd:<http://www.w3.org/2001/XMLSchema#> | ||
+ | SELECT ?dqr ?i | ||
+ | WHERE { | ||
+ | ?dqr a dqm:FuncDepReferenceRule . | ||
+ | ?dqr dqm:assessment "true"^^xsd:boolean . | ||
+ | ?dqr dqm:testedClass ?tclass . | ||
+ | ?tclass dqm:hasURI ?tclassreal . | ||
+ | BIND (IRI(str(?tclassreal)) AS ?tclassURI) . | ||
+ | ?dqr dqm:testedProperty1 ?tprop1 . | ||
+ | ?tprop1 dqm:hasURI ?tpropreal1 . | ||
+ | BIND (IRI(str(?tpropreal1)) AS ?tpropURI1) . | ||
+ | ?dqr dqm:testedProperty2 ?tprop2 . | ||
+ | ?tprop2 dqm:hasURI ?tpropreal2 . | ||
+ | BIND (IRI(str(?tpropreal2)) AS ?tpropURI2) . | ||
+ | ?dqr dqm:referenceClass ?trefclass . | ||
+ | ?trefclass dqm:hasURI ?trefclassreal . | ||
+ | BIND (IRI(str(?trefclassreal)) AS ?trefclassURI) . | ||
+ | ?dqr dqm:referenceProperty1 ?trefprop1 . | ||
+ | ?trefprop1 dqm:hasURI ?trefpropreal1 . | ||
+ | BIND (IRI(str(?trefpropreal1)) AS ?trefpropURI1) . | ||
+ | ?dqr dqm:referenceProperty2 ?trefprop2 . | ||
+ | ?trefprop2 dqm:hasURI ?trefpropreal2 . | ||
+ | BIND (IRI(str(?trefpropreal2)) AS ?trefpropURI2) . | ||
+ | ?i a ?tclassURI . | ||
+ | ?i ?tpropURI1 ?value1 . | ||
+ | ?i ?tpropURI2 ?value2 . | ||
+ | BIND(STRDT(str(?value1),xsd:string) AS ?value11 ) | ||
+ | BIND(STRDT(str(?value2),xsd:string) AS ?value12 ) | ||
+ | NOT EXISTS{ | ||
+ | ?i2 a ?trefclassURI . | ||
+ | ?i2 ?trefpropURI1 ?value11 . | ||
+ | ?i2 ?trefpropURI2 ?value12 . | ||
+ | } | ||
+ | } | ||
+ | </syntaxhighlight> |
Latest revision as of 16:11, 1 July 2012
Data Quality Monitoring Reports are an excellent instrument to quickly identify instances with requirement violations.
Prerequisites
With the DQM-Vocabulary you can easily create Data Quality Monitoring reports. All you need is a SPARQL-Endpoint that supports SPARQL 1.1 or SPARQL extensions that cover datatype conversion and conversion of strings to URIs. Before you can automatically analyze your data for requirement violations, you need to perform the following steps:
- Declare the elements of your ontology that shall be used in the DQM-Vocabulary
- Create Data Requirements
Data Quality Reports with SPARQL
Each type of data requirement has different problem reports, since the nature of data requirements differs. Moreover, the actual SPARQL query you can use depends on the design option you have chosen to make your ontology elements an instance of dqm:TestedClass and dqm:TestedProperty (See Create Data Requirements#Define Tested Elements for an explanation). In the following you find several SPARQL queries that make use of your data requirements to identify class instances with requirement violations.
Example 1.1: Missing Properties / Values (OWL Full Design)
Scenario: | Find all instances of a certain class that miss a property or a property value. |
Input: | All property completeness rules (in OWL Full Design) |
Output: | All instances with missing properties and values |
SELECT ?dqr ?i WHERE { ?dqr a dqm:PropertyCompletenessRule . ?dqr dqm:testedClass ?tclass . ?dqr dqm:testedProperty1 ?tprop . ?dqr dqm:requiredValue "true"^^xsd:boolean . ?dqr dqm:requiredProperty "true"^^xsd:boolean . { ?i a ?tclass . FILTER NOT EXISTS{ ?i ?tprop ?value . } }UNION{ ?i a ?tclass . ?i ?tprop "" . } }
Click here to learn how to define a data requirement for this report
Example 1.2: Missing Properties / Values (OWL DL Design)
Scenario: | Find all instances that miss a property or a property value. |
Input: | All property completeness rules (in OWL DL Design) |
Output: | All instances with missing properties and values |
SELECT ?dqr ?i WHERE { ?dqr a dqm:PropertyCompletenessRule . ?dqr dqm:testedClass ?tclass . ?dqr dqm:testedProperty1 ?tprop . ?dqr dqm:requiredValue "true"^^xsd:boolean . ?dqr dqm:requiredProperty "true"^^xsd:boolean . ?tclass dqm:hasURI ?tclassreal . ?tprop dqm:hasURI ?tpropreal . BIND (IRI(str(?tpropreal)) AS ?tpropURI) . BIND (IRI(str(?tclassreal)) AS ?tclassURI) . { ?i a ?tclassURI . NOT EXISTS{ ?i ?tpropURI ?value . } }UNION{ ?i a ?tclassURI . ?i ?tpropURI "" . } }
Click here to learn how to define a data requirement for this report
Example 2: Conditional Missing Properties / Values (1 Condition, OWL DL Design)
Scenario: | Find all instances in a subset of a certain class that miss a property or a property value. |
Input: | All conditional property completeness rules (in OWL DL Design) |
Output: | Instances with missing properties and values |
SELECT ?dqr ?i ?condvalue WHERE { ?dqr a dqm:ConditionalPropertyCompletenessRule . ?dqr dqm:testedClass ?tclass . ?dqr dqm:testedProperty1 ?tprop . ?dqr dqm:requiredProperty "true"^^xsd:boolean . ?dqr dqm:requiredValue "true"^^xsd:boolean . ?tclass dqm:hasURI ?tclassreal . ?tprop dqm:hasURI ?tpropreal . ?dqr dqm:hasCondition1 ?cond1 . ?cond1 dqm:conditionalProperty ?cprop . ?cprop dqm:hasURI ?cpropreal . ?cond1 dqm:equals ?condvalue . MINUS{ ?dqr dqm:hasCondition2 ?cond2 . } MINUS{ ?dqr dqm:hasCondition3 ?cond3 . } MINUS{ ?dqr dqm:hasCondition4 ?cond4 . } MINUS{ ?dqr dqm:hasCondition5 ?cond5 . } BIND (IRI(str(?tclassreal)) AS ?tclassURI) . BIND (IRI(str(?tpropreal)) AS ?tpropURI) . BIND (IRI(str(?cpropreal)) AS ?cpropURI) . { ?i a ?tclassURI . ?i ?cpropURI ?value NOT EXISTS{ ?i ?tpropURI ?value1 . } }UNION{ ?i a ?tclassURI . ?i ?cpropURI ?value . ?i ?tpropURI "" . } FILTER (str(?value) = str(?condvalue)) . }
Click here to learn how to define a data requirement for this report
Example 3: Syntax Violations (OWL DL Design)
Scenario: | Find all instances of a certain class with property values that violate a syntax rule. |
Input: | All syntax rules (in OWL DL Design) |
Output: | All instances with property values that have syntax violations |
SELECT ?dqr ?i WHERE { ?dqr a dqm:SyntaxRule . ?dqr dqm:testedProperty1 ?tprop . ?dqr dqm:testedClass ?tclass . ?dqr dqm:regex ?regex . ?tclass dqm:hasURI ?tclassreal . ?tprop dqm:hasURI ?tpropreal . BIND (IRI(str(?tpropreal)) AS ?tpropURI) . BIND (IRI(str(?tclassreal)) AS ?tclassURI) . ?i a ?tclassURI . ?i ?tpropURI ?value . FILTER (!regex(str(?value), ?regex)) . }
Click here to learn how to define a data requirement for this report
Example 4: Conditional Syntax Violations (1 Condition, OWL DL Design)
Scenario: | Find all syntax violations of property values in a subset of a certain class. |
Input: | All conditional syntax rules(in OWL DL Design). |
Output: | All instances of the subset of the class that have syntax violations. |
SELECT ?dqr ?i WHERE { ?dqr a dqm:ConditionalSyntaxRule . ?dqr dqm:testedClass ?tclass . ?dqr dqm:testedProperty1 ?tprop . ?dqr dqm:regex ?regex . ?tclass dqm:hasURI ?tclassreal . ?tprop dqm:hasURI ?tpropreal . ?dqr dqm:hasCondition1 ?cond1 . ?cond1 dqm:conditionalProperty ?cprop . ?cprop dqm:hasURI ?cpropreal . ?cond1 dqm:equals ?condvalue . MINUS{ ?dqr dqm:hasCondition2 ?cond2 . } MINUS{ ?dqr dqm:hasCondition3 ?cond3 . } MINUS{ ?dqr dqm:hasCondition4 ?cond4 . } MINUS{ ?dqr dqm:hasCondition5 ?cond5 . } BIND (IRI(str(?tpropreal)) AS ?tpropURI) . BIND (IRI(str(?tclassreal)) AS ?tclassURI) . BIND (IRI(str(?cpropreal)) AS ?cpropURI) . ?i a ?tclassURI . ?i ?cpropURI ?condvalueplain . ?i ?tpropURI ?value . FILTER (str(?condvalue) = str(?condvalueplain) && !regex(str(?value), ?regex)) . }
Click here to learn how to define a data requirement for this report
Example 5: Out of Range Values (OWL DL Design)
Scenario: | Find all instances that have property values out of a specified range. |
Input: | All legal value range rules (in OWL DL Design) |
Output: | All instances with out of range values |
SELECT ?dqr ?i WHERE { ?dqr a dqm:LegalValueRangeRule . ?dqr dqm:testedClass ?tclass . ?dqr dqm:testedProperty1 ?tprop . OPTIONAL{ ?dqr dqm:upperLimit ?upperLimit . } OPTIONAL{ ?dqr dqm:lowerLimit ?lowerLimit . } ?tclass dqm:hasURI ?tclassreal . ?tprop dqm:hasURI ?tpropreal . BIND (IRI(str(?tpropreal)) AS ?tpropURI) . BIND (IRI(str(?tclassreal)) AS ?tclassURI) . ?i a ?tclassURI . ?i ?tpropURI ?value . FILTER (STRDT(str(?value), xsd:float) > ?upperLimit || STRDT(str(?value), xsd:float) < ?lowerLimit) . }
Click here to learn how to define a data requirement for this report
Example 6: Illegal Values (OWL DL Design)
Scenario: | Find all instances that have illegal property values, i.e. values that are not listed in the trusted reference property. |
Input: | All legal value rules (in OWL DL Design). |
Output: | All instances with values not listed in the trusted reference. |
SELECT ?dqr ?i WHERE { ?dqr a dqm:LegalValueRule . ?dqr dqm:testedClass ?tclass . ?dqr dqm:testedProperty1 ?tprop . ?tclass dqm:hasURI ?tclassreal . ?tprop dqm:hasURI ?tpropreal . BIND (IRI(str(?tpropreal)) AS ?tpropURI) . BIND (IRI(str(?tclassreal)) AS ?tclassURI) . ?dqr dqm:referenceClass ?rclass . ?dqr dqm:referenceProperty1 ?rprop . ?rclass dqm:hasURI ?rclassvalue . ?rprop dqm:hasURI ?rpropvalue . BIND (IRI(str(?rpropvalue)) AS ?rpropURI) . BIND (IRI(str(?rclassvalue)) AS ?rclassURI) . ?i a ?tclassURI . ?i ?tpropURI ?value . OPTIONAL { ?i2 a ?rclassURI . ?i2 ?rpropURI ?value1 . FILTER (str(?value1) = str(?value)) . } . FILTER (!bound(?value1)) . }
Click here to learn how to define a data requirement for this report
Example 7: Uniqueness Violations (OWL DL Design)
Scenario: | Find all instances with property values that are not unique although they should. |
Input: | All unique value rules (in OWL DL Design). |
Output: | All instances with non-unique values of property that must have unique values for each instance. |
SELECT ?dqr ?i WHERE { ?dqr a dqm:UniqueValueRule . ?dqr dqm:testedClass ?tclass . ?dqr dqm:testedProperty1 ?tprop . ?tclass dqm:hasURI ?tclassreal . ?tprop dqm:hasURI ?tpropreal . BIND (IRI(str(?tpropreal)) AS ?tpropURI) . BIND (IRI(str(?tclassreal)) AS ?tclassURI) . ?i a ?tclassURI . ?i ?tpropURI ?uniqueValue1 . ?i2 ?tpropURI ?uniqueValue2 . FILTER(?i!=?i2 && (str(?uniqueValue1) = str(?uniqueValue2))) } GROUP BY ?dqr ?i
Click here to learn how to define a data requirement for this report
Example 8: Functional Dependency Violations (1 Condition, OWL DL Design)
Scenario: | Find all instances with inconsistent property values. |
Input: | All functional dependency value rules with 1 condition (in OWL DL Design). |
Output: | All instances with inconsistent property values. |
SELECT ?dqr ?i ?dvalue ?value WHERE { ?dqr a dqm:FuncDepValueRule . ?dqr dqm:testedProperty1 ?tprop . ?dqr dqm:testedClass ?tclass . ?dqr dqm:equals ?dvalue . ?tclass dqm:hasURI ?tclassreal . ?tprop dqm:hasURI ?tpropreal . ?dqr dqm:hasCondition1 ?cond1 . ?cond1 dqm:conditionalProperty ?cprop1 . ?cprop1 dqm:hasURI ?cpropreal1 . ?cond1 dqm:equals ?condvalue1 . MINUS{ ?dqr dqm:hasCondition2 ?cond2 . } MINUS{ ?dqr dqm:hasCondition3 ?cond3 . } MINUS{ ?dqr dqm:hasCondition4 ?cond4 . } MINUS{ ?dqr dqm:hasCondition5 ?cond5 . } BIND (IRI(str(?tpropreal)) AS ?tpropURI) . BIND (IRI(str(?tclassreal)) AS ?tclassURI) . BIND (IRI(str(?cpropreal1)) AS ?cpropURI1) . ?i a ?tclassURI . ?i ?cpropURI1 ?condvalue1 . ?i ?tpropURI ?value . FILTER (str(?dvalue) != str(?value)) . }
Click here to learn how to define a data requirement for this report
Example 9: Functional Dependency Violations (2 Conditions, OWL DL Design)
Scenario: | Find all instances with inconsistent property values. |
Input: | All functional dependency value rules with 2 conditions (in OWL DL Design). |
Output: | All instances with inconsistent property values. |
SELECT ?dqr ?i WHERE { ?dqr a dqm:FuncDepValueRule . ?dqr dqm:testedProperty1 ?tprop . ?dqr dqm:testedClass ?tclass . ?dqr dqm:equals ?dvalue . ?tclass dqm:hasURI ?tclassreal . ?tprop dqm:hasURI ?tpropreal . ?dqr dqm:hasCondition1 ?cond1 . ?cond1 dqm:conditionalProperty ?cprop1 . ?cprop1 dqm:hasURI ?cpropreal1 . ?cond1 dqm:equals ?condvalue1 . ?dqr dqm:hasCondition2 ?cond2 . ?cond2 dqm:conditionalProperty ?cprop2 . ?cprop2 dqm:hasURI ?cpropreal2 . ?cond2 dqm:equals ?condvalue2 . MINUS{ ?dqr dqm:hasCondition3 ?cond3 . } MINUS{ ?dqr dqm:hasCondition4 ?cond4 . } MINUS{ ?dqr dqm:hasCondition5 ?cond5 . } BIND (IRI(str(?tpropreal)) AS ?tpropURI) . BIND (IRI(str(?tclassreal)) AS ?tclassURI) . BIND (IRI(str(?cpropreal1)) AS ?cpropURI1) . BIND (IRI(str(?cpropreal2)) AS ?cpropURI2) . ?i a ?tclassURI . ?i ?cpropURI1 ?condvalue1 . ?i ?cpropURI2 ?condvalue2 . ?i ?tpropURI ?value . FILTER (str(?dvalue) != str(?value)) . }
Click here to learn how to define a data requirement for this report
Example 10: Outdated / Expired Values (OWL DL Design)
Scenario: | Find all outdated instances. |
Input: | All expiry rules (in OWL DL Design) and validity date of instances. |
Output: | All instances with that are not valid anymore. |
SELECT ?dqr ?i WHERE { ?dqr a dqm:ExpiryRule . ?dqr dqm:testedProperty1 ?tprop . ?dqr dqm:testedClass ?tclass . ?tclass dqm:hasURI ?tclassreal . ?tprop dqm:hasURI ?tpropreal . BIND (IRI(str(?tpropreal)) AS ?tpropURI) . BIND (IRI(str(?tclassreal)) AS ?tclassURI) . BIND ((now()) AS ?today) ?i a ?tclassURI . ?i ?tpropURI ?expiry . FILTER(?expiry<?today) }
Click here to learn how to define a data requirement for this report
Example 11: Outdated / Not Updated Values (OWL DL Design)
Scenario: | Find all outdated instances. |
Input: | All update rules (in OWL DL Design) and timestamps of instances. |
Output: | All instances with that are potentially outdated. |
SELECT ?dqr ?i WHERE { ?dqr a dqm:UpdateRule . ?dqr dqm:testedClass ?tclass . ?dqr dqm:testedProperty1 ?tprop . ?dqr dqm:expectedUpdateInterval ?duration . ?tclass dqm:hasURI ?tclassreal . ?tprop dqm:hasURI ?tpropreal . BIND (IRI(str(?tpropreal)) AS ?tpropURI) . BIND (IRI(str(?tclassreal)) AS ?tclassURI) . BIND((dqf:requiredTimestamp(str(?duration))) AS ?reqTimestamp) . ?i a ?tclassURI . ?i ?tpropURI ?timestamp . FILTER(?reqTimestamp>?timestamp) }
Click here to learn how to define a data requirement for this report
Example 12: Duplicate Instance Rule (3 Properties, OWL DL Design)
Scenario: | Find duplicate instances. |
Input: | Duplicate instance rules with 3 tested properties. |
Output: | All instances that have identical values for the 3 tested properties. |
PREFIX dqm:<http://purl.org/dqm-vocabulary/v1.1/dqm#> PREFIX xsd:<http://www.w3.org/2001/XMLSchema#> SELECT ?dqr ?i WHERE { ?dqr a dqm:DuplicateInstanceRule . ?dqr dqm:testedClass ?tclass . ?tclass dqm:hasURI ?tclassreal . BIND (IRI(str(?tclassreal)) AS ?tclassURI) . ?dqr dqm:testedProperty1 ?tprop1 . ?tprop1 dqm:hasURI ?tpropreal1 . BIND (IRI(str(?tpropreal1)) AS ?tpropURI1) . ?dqr dqm:testedProperty2 ?tprop2 . ?tprop2 dqm:hasURI ?tpropreal2 . BIND (IRI(str(?tpropreal2)) AS ?tpropURI2) . ?dqr dqm:testedProperty3 ?tprop3 . ?tprop3 dqm:hasURI ?tpropreal3 . BIND (IRI(str(?tpropreal3)) AS ?tpropURI3) . ?i a ?tclassURI . ?i ?tpropURI1 ?value11 . ?i2 ?tpropURI1 ?value12 . ?i ?tpropURI2 ?value21 . ?i2 ?tpropURI2 ?value22 . ?i ?tpropURI3 ?value31 . ?i2 ?tpropURI3 ?value32 . FILTER(?i!=?i2 && str(?value11) = str(?value12) && str(?value21) = str(?value22) && str(?value31) = str(?value32)) } GROUP BY ?dqr
Example 13: Functional Dependency Violations (2 Reference Properties, OWL DL Design)
Scenario: | Find functional dependency violations between two property values. |
Input: | Functional dependency reference rules with 2 tested properties. |
Output: | All instances that have violating dependencies between the two tested property values. |
PREFIX dqm:<http://purl.org/dqm-vocabulary/v1.1/dqm#> PREFIX xsd:<http://www.w3.org/2001/XMLSchema#> SELECT ?dqr ?i WHERE { ?dqr a dqm:FuncDepReferenceRule . ?dqr dqm:assessment "true"^^xsd:boolean . ?dqr dqm:testedClass ?tclass . ?tclass dqm:hasURI ?tclassreal . BIND (IRI(str(?tclassreal)) AS ?tclassURI) . ?dqr dqm:testedProperty1 ?tprop1 . ?tprop1 dqm:hasURI ?tpropreal1 . BIND (IRI(str(?tpropreal1)) AS ?tpropURI1) . ?dqr dqm:testedProperty2 ?tprop2 . ?tprop2 dqm:hasURI ?tpropreal2 . BIND (IRI(str(?tpropreal2)) AS ?tpropURI2) . ?dqr dqm:referenceClass ?trefclass . ?trefclass dqm:hasURI ?trefclassreal . BIND (IRI(str(?trefclassreal)) AS ?trefclassURI) . ?dqr dqm:referenceProperty1 ?trefprop1 . ?trefprop1 dqm:hasURI ?trefpropreal1 . BIND (IRI(str(?trefpropreal1)) AS ?trefpropURI1) . ?dqr dqm:referenceProperty2 ?trefprop2 . ?trefprop2 dqm:hasURI ?trefpropreal2 . BIND (IRI(str(?trefpropreal2)) AS ?trefpropURI2) . ?i a ?tclassURI . ?i ?tpropURI1 ?value1 . ?i ?tpropURI2 ?value2 . BIND(STRDT(str(?value1),xsd:string) AS ?value11 ) BIND(STRDT(str(?value2),xsd:string) AS ?value12 ) NOT EXISTS{ ?i2 a ?trefclassURI . ?i2 ?trefpropURI1 ?value11 . ?i2 ?trefpropURI2 ?value12 . } }