OCNS/CEFAS DataViews

by

📘 Purpose

This page provides an overview of the product-related data views based on OCNS and CEFAS environmental regulatory sources. These views support reporting, environmental compliance, and substitution warning analysis across various product categories.

🔍 Use Cases

  • Identify expired or soon-to-expire products by Expiry_Date
  • Filter products with high-risk HQ_Band
  • Highlight substitution-recommended products
  • Segment product availability by region or use-case

🗂️ DataViews Summary

The following views are published within the cefas schema:

DataView NameCategory DescriptionNotes / Source
Cementing_ProductsCementing ProductsFrom CEFAS import
CompletionWorkover_ProductsCompletionWorkover ProductsFrom OCNS product registry
WBM_Drilling_ProductsWBM Drilling ProductsWater-based mud group
Production_ProductsProduction ProductsGeneral production use
Non_CHARMable_productsNon-CHARMable ProductsOut-of-scope for CHARM

📐 Standard Schema

Each product view includes the following structure:

FieldDescription
Product_NameName of the registered product
CompanyCompany responsible for the product
HQ_BandHazard Quotient (HQ) band classification
Product_WarningGeneral warning associated with the product
Substitution_WarningIndicates whether a substitution is recommended or required
FunctionPrimary function of the product (e.g., cementing, drilling fluid)
Expiry_DateExpiry date for product registration
Version_NoVersion number of the product information
Registration_NoOCNS/CEFAS registration number
Registered_for_Use_inCountries or regions where product use is approved
NL_HMCS_CategoryNetherlands HMCS classification, where applicable
pipeline_logTechnical log information from ingestion pipeline
file_dateDate the product file was ingested into the system

💡 Multi-Category Product Query – Summary of Work

We developed a robust and reusable query framework to extract and categorise products from OCNS and CEFAS source files across multiple product categories in a single operation.

This approach:

  • Reduces duplication
  • Improves performance
  • Lays a foundation for maintainable and scalable data views

🛠️ Highlights

  • Single source ingestion: One raw file powers all views
  • Dynamic category allocation: Category assignment via rules or mappings
  • Consistent schema and logic: Standard fields across all views
  • Separation of logic and presentation: Category views are filtered subsets

🧾 Power Query Implementation

🧱 Create: Product View Summary Table

This table should contain view_name and category for each DataView:

powerqueryCopyEditProductViews = Table.FromRecords({
    [view_name = "Cementing_Products", category = "Cementing Products"],
    [view_name = "CompletionWorkover_Products", category = "CompletionWorkover Products"],
    [view_name = "WBM_Drilling_Products", category = "WBM Drilling Products"],
    [view_name = "Production_Products", category = "Production Products"],

[view_name = “Non_CHARMable_products”, category = “Non-CHARMable Products”]

})

🔧 Create: LoadProductCategory Function

powerqueryCopyEdit(viewName as text, category as text) =>
let
    Source = Sql.Database(DataMart_Host, DataMart_Endpoint,
        [Query = "EXEC get.myView @viewName = '" & viewName & "', @token = '" & DataMart_Token & "', @version = 'beta'"]),
    AddCategory = Table.AddColumn(Source, "product_category", each category)
in
    AddCategory

📌 This defines a reusable function to load a product view and append its category.

🔁 Apply Function Across Views

powerqueryCopyEditlet
    SourceTable = ProductViews,
    AddData = Table.AddColumn(SourceTable, "Data", each LoadProductCategory([view_name], [category])),
    Expanded = Table.Combine(AddData[Data])
in
    Expanded

✅ This loads and combines all product views, each tagged with their category — ideal for unified analysis, dashboards, and regulatory summaries.

Leave a Comment