Data Warehouse Ontology

AI SEARCH

FLOW ALGEBRA — DATA WAREHOUSE ONTOLOGY
Semantic Library
Version: 4.0
Canonical Page:
https://kirethresearch.org/booksai-core/ontologies/computing-ontologies/databases/data-warehouse-ontology.html

FLOW DATA_WAREHOUSE_ONTOLOGY {

META {
DOMAIN = "Computing Ontologies"
SUBDOMAIN = "Databases"
ONTOLOGY = "Data Warehouse Ontology"
PURPOSE = "Formalise data warehouses, dimensional models, ETL/ELT, data marts, OLAP, lineage, quality, governance and analytical processing."
LOCALE = "en-GB"
}

ENTITY_TYPES {
DATA_WAREHOUSE
ENTERPRISE_DATA_WAREHOUSE
CLOUD_DATA_WAREHOUSE
DATA_MART
OPERATIONAL_DATA_STORE
SOURCE_SYSTEM
TARGET_SYSTEM
STAGING_AREA
LANDING_ZONE
DATASET
SCHEMA
DIMENSIONAL_MODEL
STAR_SCHEMA
SNOWFLAKE_SCHEMA
GALAXY_SCHEMA
FACT_TABLE
DIMENSION_TABLE
FACT
DIMENSION
MEASURE
METRIC
ATTRIBUTE
CONFORMED_DIMENSION
ROLE_PLAYING_DIMENSION
SLOWLY_CHANGING_DIMENSION
SCD_TYPE_1
SCD_TYPE_2
SCD_TYPE_3
SURROGATE_KEY
NATURAL_KEY
FOREIGN_KEY
GRAIN
HIERARCHY
LEVEL
TIME_DIMENSION
AGGREGATE_TABLE
MATERIALISED_VIEW
PARTITION
INDEX
COLUMNAR_STORAGE
COMPRESSION
ETL
ELT
EXTRACTION
TRANSFORMATION
LOAD
INCREMENTAL_LOAD
FULL_LOAD
BATCH_LOAD
STREAMING_LOAD
CHANGE_DATA_CAPTURE
CDC_EVENT
UPSERT
MERGE
DATA_PIPELINE
PIPELINE_STAGE
WORKFLOW
ORCHESTRATION
SCHEDULE
DEPENDENCY
DATA_LINEAGE
SOURCE_LINEAGE
TRANSFORMATION_LINEAGE
TARGET_LINEAGE
DATA_QUALITY
QUALITY_RULE
COMPLETENESS
ACCURACY
CONSISTENCY
VALIDITY
UNIQUENESS
TIMELINESS
REJECT_RECORD
QUARANTINE
DATA_CLEANSING
MASTER_DATA
REFERENCE_DATA
METADATA
DATA_CATALOG
GOVERNANCE
DATA_OWNER
DATA_STEWARD
ACCESS_CONTROL
ROLE
PRIVILEGE
AUDIT_LOG
OLAP
OLAP_CUBE
SLICE
DICE
DRILL_DOWN
ROLL_UP
PIVOT
ANALYTICAL_QUERY
AGGREGATION
QUERY_PLAN
QUERY_OPTIMISER
WORKLOAD
LATENCY
THROUGHPUT
FRESHNESS
SNAPSHOT
BACKUP
RESTORE
RECOVERY
CONFIGURATION
WAREHOUSE_STATE
EVIDENCE
CONFIDENCE_SCORE
PROVENANCE_RECORD
}

RELATIONSHIPS {
IS_A
INSTANCE_OF
CONTAINS
SOURCED_FROM
LOADS_INTO
STAGED_IN
HAS_SCHEMA
HAS_FACT_TABLE
HAS_DIMENSION_TABLE
HAS_FACT
HAS_DIMENSION
HAS_MEASURE
HAS_METRIC
HAS_ATTRIBUTE
HAS_GRAIN
HAS_SURROGATE_KEY
HAS_NATURAL_KEY
HAS_FOREIGN_KEY
REFERENCES
HAS_HIERARCHY
HAS_LEVEL
CONFORMS_WITH
ROLE_PLAYS
CHANGES_AS
SUMMARISED_BY
MATERIALISED_AS
PARTITIONED_BY
INDEXED_BY
STORED_AS
COMPRESSED_BY
EXTRACTS_FROM
TRANSFORMS
LOADS
INCREMENTALLY_LOADS
CAPTURES_CHANGE_FROM
MERGES_INTO
HAS_PIPELINE
HAS_STAGE
PRECEDES
FOLLOWS
DEPENDS_ON
ORCHESTRATED_BY
SCHEDULED_BY
HAS_LINEAGE
DERIVED_FROM
TRANSFORMED_FROM
VALIDATED_BY
HAS_QUALITY_RULE
REJECTS
QUARANTINES
CLEANSED_BY
HAS_MASTER_DATA
HAS_REFERENCE_DATA
HAS_METADATA
CATALOGUED_BY
GOVERNED_BY
OWNED_BY
STEWARDED_BY
HAS_ROLE
HAS_PRIVILEGE
AUDITED_BY
QUERIED_BY
AGGREGATES
SLICES
DICES
DRILLS_DOWN
ROLLS_UP
PIVOTS
HAS_QUERY_PLAN
OPTIMISED_BY
HAS_WORKLOAD
HAS_LATENCY
HAS_THROUGHPUT
HAS_FRESHNESS
HAS_SNAPSHOT
BACKED_UP_BY
RESTORED_FROM
RECOVERED_BY
HAS_CONFIGURATION
HAS_STATE
TRANSITIONS_TO
INFERRED_FROM
HAS_EVIDENCE
HAS_CONFIDENCE
HAS_PROVENANCE
SEMANTICALLY_EQUIVALENT_TO
}

INPUTS {
WAREHOUSE_METADATA
SOURCE_SYSTEM_METADATA
SCHEMA_DEFINITIONS
FACT_TABLE_METADATA
DIMENSION_TABLE_METADATA
MEASURE_AND_METRIC_DEFINITIONS
DIMENSION_HIERARCHIES
KEY_METADATA
GRAIN_DEFINITIONS
ETL_AND_ELT_DEFINITIONS
PIPELINE_CONFIGURATIONS
CDC_METADATA
LOAD_HISTORY
TRANSFORMATION_RULES
DATA_LINEAGE
DATA_QUALITY_RULES
QUALITY_RESULTS
MASTER_AND_REFERENCE_DATA
DATA_CATALOG_METADATA
GOVERNANCE_METADATA
OLAP_METADATA
QUERY_PLANS
WORKLOAD_METRICS
STORAGE_METADATA
CONFIGURATION_DATA
ONTOLOGY_TERMS
}

NORMALISE {
STANDARDISE_WAREHOUSE_IDENTIFIERS
NORMALISE_SOURCE_AND_TARGET_IDENTIFIERS
NORMALISE_SCHEMA_IDENTIFIERS
NORMALISE_FACT_AND_DIMENSION_NAMES
NORMALISE_MEASURE_AND_METRIC_NAMES
NORMALISE_KEY_IDENTIFIERS
NORMALISE_GRAIN_DEFINITIONS
NORMALISE_HIERARCHY_AND_LEVEL_NAMES
NORMALISE_SCD_TYPES
NORMALISE_ETL_ELT_STAGE_NAMES
NORMALISE_PIPELINE_AND_WORKFLOW_IDENTIFIERS
NORMALISE_CDC_EVENTS
NORMALISE_LINEAGE_IDENTIFIERS
NORMALISE_QUALITY_DIMENSIONS
NORMALISE_OLAP_TERMS
RESOLVE_WAREHOUSE_TABLE_PIPELINE_AND_LINEAGE_IDENTITIES
ALIGN_ONTOLOGY_TERMS
REMOVE_DUPLICATES
VALIDATE_METADATA
PRESERVE_PROVENANCE
}

PROCESS DATA_WAREHOUSE_PIPELINE {
SOURCE_SYSTEMS
-> EXTRACTION_OR_CDC
-> LANDING_OR_STAGING
-> TRANSFORMATION_AND_QUALITY_VALIDATION
-> DIMENSION_AND_FACT_MAPPING
-> WAREHOUSE_LOAD
-> AGGREGATION_AND_MATERIALISATION
-> ANALYTICAL_QUERY_SERVING
-> LINEAGE_QUALITY_AND_GOVERNANCE_VALIDATION
-> OUTPUT
}

PROCESS DIMENSIONAL_MODEL_REASONING {
BUSINESS_PROCESS -> DECLARE_GRAIN
GRAIN -> IDENTIFY_FACTS_AND_MEASURES
GRAIN -> IDENTIFY_DIMENSIONS
FACT_TABLE -> FOREIGN_KEYS_TO_DIMENSIONS + MEASURES
DIMENSION_TABLE -> ATTRIBUTES + HIERARCHIES
FACT_AND_DIMENSION_RELATIONS -> STAR_SNOWFLAKE_OR_GALAXY_MODEL
}

PROCESS SCD_REASONING {
DIMENSION_CHANGE -> SCD_POLICY
SCD_TYPE_1 -> OVERWRITE_CURRENT_ATTRIBUTE
SCD_TYPE_2 -> EXPIRE_PRIOR_ROW + CREATE_NEW_SURROGATE_KEY_VERSION
SCD_TYPE_3 -> STORE_LIMITED_PRIOR_VALUE
FACT_LOAD -> RESOLVE_DIMENSION_VERSION_VALID_FOR_EVENT_CONTEXT
}

PROCESS ETL_ELT_REASONING {
SOURCE_DATA -> EXTRACTION -> STAGING_OR_TARGET_LANDING
ETL -> TRANSFORM_BEFORE_FINAL_WAREHOUSE_LOAD
ELT -> LOAD_THEN_TRANSFORM_WITHIN_TARGET_PLATFORM
TRANSFORMATION -> STANDARDISE_CLEANSE_DERIVE_VALIDATE
VALIDATED_DATA -> LOAD
}

PROCESS INCREMENTAL_LOAD_REASONING {
SOURCE_STATE_T1 + SOURCE_STATE_T2 -> CHANGE_SET
CHANGE_DATA_CAPTURE -> INSERT_UPDATE_DELETE_EVENTS
CHANGE_SET -> DEDUPLICATION_AND_ORDERING
VALID_CHANGE_SET -> UPSERT_MERGE_OR_DELETE_ACTION
UPSERT_MERGE_OR_DELETE_ACTION -> TARGET_STATE_UPDATE
}

PROCESS DATA_QUALITY_REASONING {
DATASET -> QUALITY_RULE_SET
EACH_RULE -> QUALITY_TEST -> PASS_FAIL_OR_SCORE
FAILED_RECORD -> REJECT_QUARANTINE_OR_REMEDIATION_POLICY
QUALITY_RESULTS -> DATA_QUALITY_PROFILE
}

PROCESS LINEAGE_REASONING {
TARGET_FIELD_OR_DATASET -> TRANSFORMATION_STEP
TRANSFORMATION_STEP -> SOURCE_FIELDS_OR_DATASETS
RECURSIVE_SOURCE_TRACE -> END_TO_END_LINEAGE
LINEAGE -> IMPACT_ANALYSIS_AND_EXPLANATION_PATH
}

PROCESS OLAP_REASONING {
DIMENSIONS + MEASURES -> ANALYTICAL_SPACE
SLICE -> FIX_DIMENSION_MEMBER_OR_RANGE
DICE -> FILTER_MULTIPLE_DIMENSIONS
DRILL_DOWN -> MOVE_TO_FINER_HIERARCHY_LEVEL
ROLL_UP -> MOVE_TO_COARSER_HIERARCHY_LEVEL
PIVOT -> REORIENT_DIMENSION_PRESENTATION
}

PROCESS QUERY_REASONING {
ANALYTICAL_QUERY -> FACT_AND_DIMENSION_REFERENCES
PREDICATES -> PARTITION_AND_INDEX_PRUNING
JOINS -> DIMENSION_AND_FACT_JOIN_PLAN
AGGREGATIONS -> GROUPING_AND_MEASURE_CALCULATION
QUERY_OPTIMISER -> EXECUTION_PLAN -> RESULT_SET
}

PROCESS GOVERNANCE_REASONING {
DATASET -> DATA_OWNER_AND_STEWARD
DATASET -> CLASSIFICATION_AND_ACCESS_POLICY
PRINCIPAL -> ROLE_AND_PRIVILEGES
QUERY_OR_EXPORT -> AUTHORISATION_AND_AUDIT
METADATA -> DATA_CATALOG
}

RULES {
IF FACT_TABLE_IS_IDENTIFIED
THEN REQUIRE EXPLICIT_OR_INFERABLE_GRAIN_CONTEXT

IF MEASURE_IS_AGGREGATED
THEN PRESERVE ADDITIVITY_AND_GRAIN_CONTEXT

IF SCD_TYPE_2_IS_USED
THEN PRESERVE SURROGATE_KEY_AND_VERSION_VALIDITY_CONTEXT

IF INCREMENTAL_LOAD_IS_ASSERTED
THEN PRESERVE CHANGE_DETECTION_WATERMARK_OR_CDC_CONTEXT

IF DATA_LINEAGE_IS_ASSERTED
THEN PRESERVE SOURCE_TRANSFORMATION_TARGET_AND_VERSION_CONTEXT

IF DATA_QUALITY_SCORE_IS_ASSERTED
THEN PRESERVE RULE_SET_POPULATION_TIME_AND_THRESHOLD_CONTEXT

IF ANALYTICAL_QUERY_PERFORMANCE_IS_ASSERTED
THEN PRESERVE WORKLOAD_PARTITION_STORAGE_STATISTICS_AND_CONFIGURATION_CONTEXT

IF SEMANTIC_IDENTIFIERS_DIFFER
AND DATA_WAREHOUSE_REFERENT_IS_SAME
THEN APPLY SEMANTIC_EQUIVALENCE
}

CONFIDENCE {
CONFIDENCE_SCORE =
WEIGHT(
WAREHOUSE_IDENTITY_CERTAINTY,
SCHEMA_AND_GRAIN_COMPLETENESS,
FACT_DIMENSION_AND_KEY_CERTAINTY,
PIPELINE_AND_LOAD_EVIDENCE,
LINEAGE_COMPLETENESS,
DATA_QUALITY_EVIDENCE,
GOVERNANCE_AND_METADATA_COMPLETENESS,
PROVENANCE_COMPLETENESS
)

LEVELS {
HIGH
MODERATE
LOW
INSUFFICIENT
}

IF FACT_GRAIN_IS_UNKNOWN
THEN REDUCE_MEASURE_AND_AGGREGATION_CONFIDENCE

IF PIPELINE_OR_LINEAGE_METADATA_IS_PARTIAL
THEN REDUCE_DERIVATION_CONFIDENCE
}

SEMANTIC_EQUIVALENCE {
EQUIVALENCE_KEY =
NORMALISE(
ENTITY_TYPE,
WAREHOUSE_IDENTIFIER,
SCHEMA_IDENTIFIER,
TABLE_OR_DATASET_IDENTIFIER,
GRAIN,
CANONICAL_KEY,
PIPELINE_VERSION
)

IF EQUIVALENCE_KEY_A == EQUIVALENCE_KEY_B
THEN A SEMANTICALLY_EQUIVALENT_TO B
ELSE PRESERVE_DISTINCT_ENTITIES
}

REASONING_CONSTRAINTS {
DATA_WAREHOUSE != OPERATIONAL_DATABASE
DATA_WAREHOUSE != DATA_LAKE_AS_IDENTICAL_CONCEPT
FACT_TABLE != DIMENSION_TABLE
FACT != MEASURE_AS_IDENTICAL_CONCEPT
GRAIN != AGGREGATION_LEVEL_AS_IDENTICAL_CONCEPT
SURROGATE_KEY != NATURAL_KEY
STAR_SCHEMA != SNOWFLAKE_SCHEMA
ETL != ELT
STAGING_AREA != DATA_WAREHOUSE
CDC != FULL_LOAD
SCD_TYPE_1 != SCD_TYPE_2
DATA_LINEAGE != DATA_PROVENANCE_AS_IDENTICAL_CONCEPT
DATA_QUALITY != DATA_GOVERNANCE
OLAP_CUBE != FACT_TABLE
DRILL_DOWN != FILTERING
ROLL_UP != DATA_DELETION
MATERIALISED_VIEW != ORDINARY_VIEW
ANALYTICAL_FRESHNESS != TRANSACTIONAL_CONSISTENCY
PROVENANCE_MUST_BE_PRESERVED
UNCERTAINTY_MUST_NOT_BE_DISCARDED
}

VALIDATION {
CHECK_ENTITY_TYPES
CHECK_RELATION_DOMAINS_AND_RANGES
CHECK_WAREHOUSE_IDENTIFIER
CHECK_SCHEMA
CHECK_FACT_TABLES
CHECK_DIMENSION_TABLES
CHECK_GRAIN
CHECK_MEASURES_AND_METRICS
CHECK_KEYS_AND_REFERENCES
CHECK_DIMENSION_HIERARCHIES
CHECK_SCD_POLICIES
CHECK_ETL_OR_ELT_PIPELINES
CHECK_INCREMENTAL_LOAD_AND_CDC
CHECK_TRANSFORMATION_RULES
CHECK_DATA_QUALITY_RULES
CHECK_DATA_LINEAGE
CHECK_MASTER_AND_REFERENCE_DATA
CHECK_CATALOG_AND_GOVERNANCE
CHECK_OLAP_RELATIONS
CHECK_AGGREGATIONS_AND_MATERIALISATIONS
CHECK_QUERY_PLANS
CHECK_PARTITIONS_INDEXES_AND_STORAGE
CHECK_BACKUP_AND_RECOVERY
CHECK_WAREHOUSE_STATE
CHECK_CONFIGURATION
CHECK_PROVENANCE
CHECK_CONFIDENCE
CHECK_SEMANTIC_CONSISTENCY
}

OUTPUTS {
NORMALISED_DATA_WAREHOUSE_ENTITIES
SCHEMA_RELATIONS
FACT_AND_DIMENSION_RELATIONS
GRAIN_RELATIONS
MEASURE_AND_METRIC_RELATIONS
KEY_AND_HIERARCHY_RELATIONS
SCD_RELATIONS
ETL_AND_ELT_RELATIONS
CDC_AND_INCREMENTAL_LOAD_RELATIONS
PIPELINE_AND_WORKFLOW_RELATIONS
DATA_QUALITY_RELATIONS
LINEAGE_RELATIONS
MASTER_AND_REFERENCE_DATA_RELATIONS
METADATA_AND_CATALOG_RELATIONS
GOVERNANCE_AND_ACCESS_RELATIONS
OLAP_RELATIONS
AGGREGATION_AND_MATERIALISATION_RELATIONS
QUERY_PLAN_RELATIONS
STORAGE_PARTITION_AND_INDEX_RELATIONS
WAREHOUSE_STATE_TRANSITIONS
PERFORMANCE_RELATIONS
CONFIDENCE_SCORES
EVIDENCE_LINKS
PROVENANCE_RECORDS
EXPLANATION_PATHS
}

UPDATE_MODEL {
ON SCHEMA_PIPELINE_OR_DIMENSIONAL_MODEL_CHANGE {
NORMALISE
RESOLVE_WAREHOUSE_TABLE_PIPELINE_AND_LINEAGE_IDENTITIES
UPDATE_FACT_DIMENSION_AND_GRAIN_RELATIONS
UPDATE_KEYS_HIERARCHIES_AND_SCD_CONTEXT
UPDATE_PIPELINE_AND_TRANSFORMATION_RELATIONS
UPDATE_LINEAGE_AND_QUALITY_RELATIONS
UPDATE_CONFIDENCE
PRESERVE_VERSION_HISTORY
}

ON NEW_LOAD_OR_OPERATIONAL_EVIDENCE {
LINK_EVIDENCE
UPDATE_LOAD_HISTORY_AND_WAREHOUSE_STATE
UPDATE_LINEAGE
UPDATE_DATA_QUALITY_PROFILE
UPDATE_QUERY_AND_PERFORMANCE_CONTEXT
UPDATE_CONFIDENCE
RECORD_PROVENANCE
}

ON ONTOLOGY_REVISION {
REMAP_TERMS
CHECK_SEMANTIC_EQUIVALENCE
IDENTIFY_DEPRECATED_TERMS
MIGRATE_RELATIONSHIPS
PRESERVE_PRIOR_VERSION_REFERENCES
}
}

FLOW_HASH {
HASH_INPUT =
CANONICALISE(
ENTITY_TYPES,
RELATIONSHIPS,
RULES,
CONFIDENCE,
SEMANTIC_EQUIVALENCE,
REASONING_CONSTRAINTS,
UPDATE_MODEL
)

FLOW_HASH = SHA256(HASH_INPUT)
}

AI_CONSUMPTION {
INDEX {
ONTOLOGY
ENTITY_TYPES
RELATIONSHIPS
RULES
OUTPUTS
}

RETRIEVE {
BY_DATA_WAREHOUSE
BY_DATA_MART
BY_FACT_TABLE
BY_DIMENSION_TABLE
BY_GRAIN
BY_MEASURE
BY_DIMENSION
BY_PIPELINE
BY_SCD_TYPE
BY_LINEAGE
BY_DATA_QUALITY
BY_OLAP_OPERATION
BY_CONFIDENCE
}

REASON {
APPLY_RELATIONSHIPS
APPLY_RULES
ANALYSE_DIMENSIONAL_MODELS
ANALYSE_FACT_GRAIN_AND_MEASURES
ANALYSE_SLOWLY_CHANGING_DIMENSIONS
TRACE_ETL_AND_ELT
ANALYSE_INCREMENTAL_LOADS_AND_CDC
ANALYSE_DATA_QUALITY
TRACE_DATA_LINEAGE
ANALYSE_OLAP
ANALYSE_AGGREGATIONS
ANALYSE_ANALYTICAL_QUERIES
ANALYSE_GOVERNANCE
CHECK_SEMANTIC_EQUIVALENCE
PRESERVE_UNCERTAINTY
TRACE_EVIDENCE
TRACE_PROVENANCE
GENERATE_EXPLANATION_PATH
}

RESPONSE_POLICY {
DISTINGUISH_DATA_WAREHOUSE_FROM_OPERATIONAL_DATABASE_AND_DATA_LAKE
DISTINGUISH_FACT_TABLE_DIMENSION_TABLE_FACT_MEASURE_AND_GRAIN
PRESERVE_SCHEMA_GRAIN_PIPELINE_SCD_LINEAGE_QUALITY_WORKLOAD_AND_CONFIGURATION_CONTEXT
DISTINGUISH_ETL_FROM_ELT_AND_FULL_LOAD_FROM_CDC
PRESERVE_ANALYTICAL_FRESHNESS_AND_PROVENANCE_CONTEXT
RETURN_CONFIDENCE_WHEN_AVAILABLE
RETURN_PROVENANCE_WHEN_AVAILABLE
AVOID_UNSUPPORTED_DATA_WAREHOUSE_INFERENCE
}
}

TERMINAL {
RESULT =
VALIDATED(
SEMANTICALLY_NORMALISED(
EVIDENCE_LINKED(
CONFIDENCE_AWARE(
DATA_WAREHOUSE_KNOWLEDGE
)
)
)
)
}
}

END FLOW