Utility (Commodity) Views

by

Bridging Structured Data with Usability

1️⃣ Understanding the Layers: CDS vs. Utility Views

The Common Data Standard (CDS) defines a structured, specialist-oriented view of data, ensuring consistency and modularity in reporting. However, for non-specialists, the Utility (Commodity) View simplifies data consumption by cross-combining relevant details into a single, user-friendly dataset.

View TypePurposeAudienceExample Views
CDS ViewsStructured modular datasets (.core, .codes, .value, .status)Report Writers, Data Engineers, Analystsproject.core, customer.codes, employee.value
Utility ViewsCross-combined datasets with dates, values, and codesBusiness Users, General Staff, Decision Makersproject.details_plus, customer.fullview

📌 Key Differentiation:

  • CDS views maintain structure and enable efficient joins.
  • Utility views pre-join relevant fields for quick, accessible insights.

2️⃣ Purpose of Utility Views (Commodity Views)

The Utility View exists to make complex data accessible to non-technical users. It does this by:

✅ Merging Core Business Data – Bringing together .core, .codes, .value, and .status fields.
✅ Reducing Complexity – Simplifying complex relationships into readable, consumable formats.
✅ Minimising the Need for Joins – Providing ready-to-use datasets for business users.
✅ Enhancing Decision-Making – Delivering insights without requiring deep SQL or reporting expertise.

3️⃣ Examples of Utility Views

Utility Views serve as pre-packaged datasets that make commonly accessed data available in a single, user-friendly format.

📌 Project Details Plus (project.details_plus)

✅ Merges key elements from project.core, project.codes, project.value, and project.status.
✅ Provides all essential project details in a single table for project managers and business users.

Example Schema:

sqlCopyEditCREATE OR REPLACE VIEW project.details_plus AS
SELECT 
    p.project_id, 
    p.project_name, 
    p.project_category, 
    p.start_date, 
    p.end_date, 
    p.pru_value, 
    c.cost_centre_code, 
    c.programme_code, 
    v.total_cost, 
    v.total_revenue, 
    s.project_status 
FROM 
    project.core p
LEFT JOIN project.codes c ON p.project_id = c.project_id
LEFT JOIN project.value v ON p.project_id = v.project_id
LEFT JOIN project.status s ON p.project_id = s.project_id;

🔹 Simplifies project reporting for managers without requiring separate queries.
🔹 Combines core attributes, financial values, and status updates in a single dataset.

📌 Customer Full View (customer.fullview)

✅ Combines customer metadata, financials, and risk data.
✅ Includes customer status, total orders, and last interaction date.

Example Schema:

sqlCopyEditCREATE OR REPLACE VIEW customer.fullview AS
SELECT 
    c.customer_id, 
    c.customer_name, 
    c.industry, 
    c.customer_segment, 
    c.currency_code, 
    v.lifetime_value, 
    v.outstanding_balance, 
    s.customer_status, 
    s.last_order_date 
FROM 
    customer.core c
LEFT JOIN customer.value v ON c.customer_id = v.customer_id
LEFT JOIN customer.status s ON c.customer_id = s.customer_id;

🔹 Provides a single reference point for sales and finance teams.
🔹 Eliminates the need for complex joins in daily reporting.

📌 Employee Overview (employee.overview)

✅ Designed for HR and team leads needing an all-in-one employee dataset.
✅ Brings together employment type, compensation, and leave balances.

Example Schema:

sqlCopyEditCREATE OR REPLACE VIEW employee.overview AS
SELECT 
    e.employee_id, 
    e.full_name, 
    e.job_title, 
    e.department, 
    e.employment_type, 
    v.base_salary, 
    v.annual_bonus, 
    s.employee_status, 
    s.fte, 
    s.annual_leave_balance 
FROM 
    employee.core e
LEFT JOIN employee.value v ON e.employee_id = v.employee_id
LEFT JOIN employee.status s ON e.employee_id = s.employee_id;

🔹 Gives HR a single report for workforce management.
🔹 Combines pay, employment status, and work hour metrics.

4️⃣ When to Use CDS vs. Utility Views

Use CaseBest ApproachExample View
Building advanced reportsUse CDS Viewsproject.core, customer.codes
Self-service business insightsUse Utility Viewsproject.details_plus, customer.fullview
Combining values and datesUse Utility Viewsemployee.overview, customer.fullview
Highly complex data joinsUse CDS + Aggregationproject.value, order.value

📌 Utility Views are best for everyday users needing a single source of combined data, while CDS Views enable data engineers and report writers to build structured analytics.

5️⃣ Future Development & Considerations

  • ✅ Refining Commodity Views – Ensuring they balance usability with performance.
  • ✅ Expanding Business-Centric Views – Adding pre-built summaries for executive reporting.
  • ✅ Integrating Time-Based Insights – Ensuring history tracking and forecasting is possible in Utility Views.
  • ✅ Optimising for Performance – Keeping joins efficient and manageable at scale.

🌟 Final Thoughts

📌 CDS Views provide structured, specialist datasets, while Utility Views simplify reporting for everyday users.
📌 Utility Views like project.details_plus reduce complexity by pre-joining critical data elements.
📌 The goal is accessibility – ensuring both report writers and business users can easily access relevant data.

Leave a Comment