L1

Analytics Architecture

← Back to Index

Overview

The XOPS analytics architecture follows a medallion architecture pattern with three layers: Bronze (raw data), Silver (normalized data), and Gold (aggregated insights). Cerebro enhances all layers with AI-powered capabilities.

Data Flow

┌─────────────────┐
│ Telemetry       │
│ Sources         │
│ (IDP, UEM,      │
│ Procurement,    │
│ Vendor APIs)    │
└────────┬────────┘
         │
         ▼
┌─────────────────┐
│ Bronze Layer    │
│ (Raw Data)      │
└────────┬────────┘
         │
         ▼ L1 Analytics
┌─────────────────┐     ┌──────────────┐
│ Silver Layer    │◄────┤ Cerebro      │
│ (Normalized)    │     │ Memory       │
└────────┬────────┘     └──────────────┘
         │
         ▼ L2 Analytics
┌─────────────────┐     ┌──────────────┐
│ Gold Layer      │◄────┤ Cerebro      │
│ (Aggregated)    │     │ Intelligence │
└────────┬────────┘     │ & Reasoning  │
         │              └──────────────┘
         ▼
┌─────────────────┐
│ UI Components   │
│ (Experience     │
│ Center, Copilot)│
└─────────────────┘

L1 Analytics (Bronze → Silver)

Purpose

Transform raw telemetry data into a normalized, standardized format ready for analysis.

Key Operations

1. Entity Resolution

   • Canonicalize vendor names (Adobe Systems → Adobe Inc.)
   • Normalize software product names (MS Office 365 → Microsoft 365)
   • Resolve user identities across systems (email → PII ID)

2. Data Standardization

   • Convert currencies to base currency (USD)
   • Normalize date/time formats
   • Standardize license type classifications

3. Data Quality

   • Remove duplicates
   • Fill missing values with defaults
   • Validate data integrity

Cerebro Memory Integration

• Synonym Resolution: Map product name variations to canonical names
• Vendor M&A Tracking: Consolidate entities across acquisitions
• License Type Classification: Categorize license models consistently

Example L1 Transformation

-- Normalize procurement data
CREATE TABLE silver.procurement_normalized AS
SELECT
    canonical_vendor_id,           -- Resolved via Cerebro Memory
    canonical_software_product_id, -- Resolved via Cerebro Memory
    purchase_date,
    quantity,
    unit_cost,
    total_cost,
    contract_id,
    cost_center_id
FROM bronze.procurement_raw
WHERE is_software = TRUE;

L2 Analytics (Silver → Gold)

Purpose

Aggregate normalized data into business metrics and insights ready for presentation.

Key Operations

1. Metric Calculation

   • Utilization rates (active users / total licenses)
   • Spend trends (MoM, QoQ, YoY)
   • Compliance scores

2. Pattern Detection

   • Inactive licenses (no usage in 90 days)
   • Spending anomalies
   • Renewal opportunities

3. Aggregation Levels

   • By organization
   • By department
   • By vendor
   • By software product
   • By time period (daily, monthly, quarterly, annually)

Cerebro Intelligence Integration

• Pattern Recognition: Identify usage trends and anomalies
• Relationship Learning: Understand procurement patterns
• Benchmarking: Compare to industry standards

Cerebro Reasoning Integration

• Optimization Recommendations: Suggest license tier changes
• Risk Assessment: Identify compliance risks
• Strategic Insights: Recommend vendor consolidation

Example L2 Transformation

-- Calculate total software spend aggregations
CREATE TABLE gold.software_spend_summary AS
SELECT
    org_id,
    fiscal_year,
    fiscal_quarter,
    SUM(total_cost) as total_software_spend,
    SUM(CASE WHEN license_type = 'subscription' THEN total_cost END) as subscription_spend,
    SUM(CASE WHEN license_type = 'perpetual' THEN total_cost END) as perpetual_spend,
    COUNT(DISTINCT canonical_vendor_id) as unique_vendors,
    COUNT(DISTINCT canonical_software_product_id) as unique_products
FROM silver.procurement_normalized
GROUP BY org_id, fiscal_year, fiscal_quarter;

Cerebro Enhancement Layers

Cerebro Memory

• Purpose: Semantic understanding and entity resolution
• Capabilities:
  • Software synonym resolution
  • Vendor M&A tracking
  • License type classification
  • Historical context preservation

Cerebro Intelligence

• Purpose: Pattern recognition and relationship learning
• Capabilities:
  • Procurement relationship analysis
  • Usage pattern classification
  • Vendor pricing intelligence
  • Anomaly detection
  • Predictive analytics

Cerebro Reasoning

• Purpose: Decision-making and recommendations
• Capabilities:
  • Optimization recommendations
  • Tier change suggestions
  • Vendor consolidation opportunities
  • Risk assessment
  • Strategic planning insights

Data Refresh Frequencies

Source	Typical Refresh	Use Cases
IDP (Entra/Okta)	Hourly	License assignments, login events
UEM	Real-time	Application usage, device telemetry
Procurement	Daily	Purchase orders, invoices
Vendor APIs	Daily	Subscription costs, license counts
Finance	Monthly	GL entries, accruals
HR System	Daily	Employee status, org changes

Data Retention Policies

Layer	Retention Period	Rationale
Bronze	90 days	Raw data backup for reprocessing
Silver	2 years	Historical analysis and trending
Gold	5 years	Compliance, auditing, long-term trends

Performance Considerations

Indexing Strategy

• Primary keys: All entity IDs (pii_id, software_product_id, vendor_id)
• Foreign keys: Relationship columns
• Time-based: All date/timestamp columns for time-series queries

Partitioning Strategy

• Silver tables: Partitioned by month
• Gold tables: Partitioned by fiscal quarter

Query Optimization

• Materialized views for frequently accessed aggregations
• Pre-computed metrics for dashboard performance
• Caching layer for real-time queries

Next Steps

• VP (IT) Questions: See how these analytics answer business questions →
• Telemetry Sources Reference: Detailed API specifications →