Skip to content

Phase 3: Workload Strategy

Lifecycle and infrastructure checkpoint

Compare cloud, on-premises and hybrid choices for identity, ownership, freshness, cost, data residency, sovereignty, connectivity and failure domains. Justify one choice and name measurements needed before asserting scalability or recovery performance. Lab 9 execution is optional; this comparison is required.

Extend the lifecycle through collection, preparation, reporting and retirement. For the AI scenario, identify evaluation data and derived predictions, who may read them, and which copies could survive source deletion. Specify refresh, retention, rollback and retirement responsibilities as proposals unless tested.

Designing the Boundary Between OLTP and OLAP

Overview

In Phase 2, you designed a Logical Data Model that represents business structure. Implemented database constraints can enforce selected integrity rules; negative tests provide evidence that those constraints reject invalid operations. A data-quality test alone does not create enforcement.

In Phase 3, you decide how that structure behaves under real workloads.

Assess interference between operational and analytical workloads.

Under competing load, risks include:

  • Performance degrades
  • Concurrency conflicts increase
  • Reporting becomes fragile
  • Revenue-impacting slowdowns occur

This phase transforms your model from a database into a platform.

The Architectural Mindset: Promote Structure Into Strategy

You are no longer just modeling entities.

You are deciding:

  • Where operational pressure belongs
  • Where analytical pressure belongs
  • How to prevent workload collisions
  • How to protect business continuity

If analytics run directly on production tables, you risk:

  • Slowing down live transactions
  • Lock contention during reporting spikes
  • Query plans breaking after schema changes
  • Inconsistent executive reporting

Scalable systems require workload boundaries.

Your responsibility is to design that boundary.

Core Architectural Principle

Separate:

OLTP (Online Transaction Processing)
Fast writes. High concurrency. Strict integrity. Day-to-day operations.

From:

OLAP (Online Analytical Processing)
Fast reads. Aggregations. Historical analysis. Executive reporting.

Production tables are optimized for transactions.

Analytical tables are optimized for insight.

This workload distinction does not require separate products, servers or databases. Justify physical separation or co-location using workload, isolation, freshness and cost requirements.

Your Task

Complete the Phase_3_OLTP_vs_OLAP section of your portfolio.

You must define how each entity behaves under operational and analytical workloads.

Part A: Entity Workload Classification

For each entity from Phase 2, determine:

  • Is it used in OLTP?
  • Is it used in OLAP?
  • If used in OLAP, is it:
  • Fact
  • Dimension
  • Not Used

You must justify every classification decision.

If an entity exists in both OLTP and OLAP, explain:

  • What transformation occurs
  • Whether a snapshot is created
  • Whether a curated or denormalized version exists
  • Whether aggregation changes grain

Architecture requires explicit transformation logic.

Part B: Facts vs Dimensions

Your OLAP layer must include:

  • At least one Fact table
  • At least four Dimension tables

Facts

Facts represent measurable business events.

Examples in a retail system:

  • Order transactions
  • Payment events
  • Returns
  • Shipments

A strong architectural test:

One row in my Fact table represents ______.

If you cannot clearly define grain, your analytical model is unstable.

Grain defines everything.

Dimensions

Dimensions provide reporting context.

Examples:

  • Customer
  • Product
  • Store
  • Date
  • Promotion

Dimensions must:

  • Be descriptive
  • Be stable
  • Support slicing and filtering
  • Remain independent from transactional volatility

Explain whether reporting requires a separate analytical layer for this workload. Justify isolation and freshness tradeoffs rather than assuming one topology is always correct.

Part C: Workload Defense Memo

Write a concise (100–150 words) architectural memo explaining:

Why do you recommend a star schema for this system? Compare its benefits and costs with reporting from operational tables, using the stated workload assumptions.

Your memo must include at least two technical arguments such as:

  • Reduced join complexity
  • Read/write workload separation
  • Indexing advantages
  • Concurrency isolation
  • Resilience to OLTP schema evolution
  • Predictable query performance

Avoid vague statements.

This is an architectural defense, not an opinion.

Evaluation Criteria

Demonstrate the following in your Phase 3 work:

Classification Accuracy

Do OLTP vs OLAP decisions reflect realistic system behavior?

Fact/Dimension Correctness

Are measurable events properly separated from descriptive context?

Architectural Coherence

Can the analytical layer operate independently from production tables?

Technical Precision

Does the defense memo include clear, technically grounded arguments?

A sound workload strategy identifies interference risks and specifies how to test them.

Why This Phase Matters

Many organizations fail at scale because:

  • Reporting runs on production systems
  • Analytical queries block transactional writes
  • Schema changes break executive dashboards
  • Performance tuning becomes reactive

Potential benefits of separating OLTP from OLAP, to evaluate against costs:

  • Improves scalability
  • Protects revenue operations
  • Stabilizes reporting
  • Enables performance tuning
  • Supports data warehouse evolution

This phase trains you to design for scale — not just correctness.

Final Perspective

Phase 2 asked:

Is the structure valid?

Phase 3 asks:

Can the structure survive real-world pressure?

Architectural maturity means anticipating workload stress.

A defensible design states its expected workload and the measurements needed to validate performance as that workload changes.

Author: Isaac K. Nti. Citation and reuse terms.