Hierarchical Rollup Validation

business analytics

slug: topic-map-business-analytics-hierarchical-rollup-validation

Vocabulary:

• rollup: Aggregation from detailed to summary level
• drill_down: Navigation from summary to detail
• aggregation_path: Sequence from finest grain to totals
• parent_child_relationship: Level N-1 is parent of level N
• reconciliation: Ensuring detail sums match summary
• aggregation_error: Discrepancy between detail sum and summary value
• precision_loss: Small differences due to rounding
• data_quality: Accuracy and consistency of data
• referential_integrity: Child records reference valid parents
• orphan_record: Detail record with no matching parent
• double_counting: Same value counted multiple times
• missing_aggregation: Parent missing some child contributions

Concepts:

• hierarchical_consistency: Data consistent across all levels
• bottom_up_validation: Sum details and compare to summary
• top_down_validation: Distribute summary and compare to details
• additive_measures: Measures that can be summed (revenue, count)
• non_additive_measures: Measures that can't be summed (average, rate)
• cube_integrity: Cube structure maintains mathematical correctness
• level_completeness: All expected rows present at each level
• aggregation_logic_validation: Ensure formulas work correctly
• temporal_consistency: Same validation across time periods

Concepts_advanced:

• semi_additive_measures: Additive in some dimensions (time) but not others (account balance)
• calculated_measures: Derived measures that need special validation
• allocation_validation: When distributing top-down, proportions correct
• cross_cube_consistency: Multiple cubes with shared dimensions reconcile
• slowly_changing_dimensions: Historical changes don't break aggregations

Procedures:

• identify_parent_child_pairs: Map each level to its parent level
• aggregate_children: Sum detail records by parent grouping
• compare_to_parent: Child sum vs parent value
• calculate_variance: Difference between rolled-up and stored values
• flag_discrepancies: Mark mismatches beyond tolerance
• identify_missing_children: Parents with no children
• identify_orphan_children: Children with no parent
• validate_grand_total: Level 0 should equal sum of all detail
• check_measure_additivity: Ensure measure can be summed
• audit_trail: Track where discrepancies originate

Procedures_detailed:

• bottom_up_aggregation: GROUP BY parent dimensions, SUM children, compare to parent
• tolerance_check: |variance| / parent_value < threshold (e.g., 0.001 = 0.1%)
• orphan_detection: Children where parent dimension values don't exist in parent level
• completeness_check: Expected number of children per parent vs actual
• cross_level_consistency: Validate each level pair (0-1, 1-2, 2-3, etc.)

Topics:

• data_quality_assurance
• cube_validation
• etl_testing
• financial_reconciliation
• aggregation_verification
• data_governance
• audit_trail_creation
• dimension_integrity_checks
• measure_validation
• reporting_accuracy

Categories:

• data_quality
• validation_testing
• reconciliation
• integrity_checking
• quality_assurance

Themes:

• trust_in_data: Users must trust cube numbers
• early_error_detection: Catch problems before reporting
• root_cause_identification: Pinpoint where aggregation breaks
• continuous_monitoring: Ongoing validation not one-time check

Trends:

• automated_validation_pipelines: Validation built into ETL
• anomaly_based_validation: ML detects unusual aggregation patterns
• real_time_reconciliation: Continuous validation as data loads
• blockchain_audit_trails: Immutable record of data lineage
• self_healing_cubes: Automatically correct minor aggregation errors

Use_cases:

• finance: "Validate regional revenue rolls up to total - catch accounting errors"
• retail: "Ensure store-level sales sum to district, district to region, region to total"
• manufacturing: "Production by line should sum to plant, plant to division, division to company"
• healthcare: "Patient counts by department should sum to hospital, hospital to system"
• saas: "User metrics by team should sum to account, account to segment, segment to total"
• supply_chain: "Warehouse inventory should sum to region, region to network total"
• marketing: "Campaign metrics by tactic should sum to channel, channel to total spend"
• education: "Student counts by classroom sum to grade, grade to school, school to district"