DD_ORG_OTC_METADATA_VX
What is DD_ORG_OTC_METADATA_VX?
DD_ORG_OTC_METADATA_VX is a specialized view in the Oracle Data Decision (DD) schema that consolidates metadata for the OverTheCounter (OTC) trading domain. It integrates information from several underlying tables such as DD_ORG_OTC_INSTRUMENTS, DD_ORG_OTC_POSITIONS, and DD_ORG_OTC_PRICING, providing a single point of access for downstream reporting, analytics, and data quality checks.
Key Purpose
- Expose a readonly, flattened representation of OTC instrument metadata.
- Support regulatory reporting (e.g., EMIR, DoddFrank) by delivering required attributes in a compliant format.
- Facilitate datalineage tracking every column can be traced back to its source table and field.
- Accelerate adhoc queries and datascience workloads without joining multiple heavy tables.
Core Columns
| Column | Data Type | Description | Source Table/Field |
|---|---|---|---|
| INSTRUMENT_ID | VARCHAR2(30) | Unique identifier for the OTC instrument. | DD_ORG_OTC_INSTRUMENTS.INSTRUMENT_ID |
| INSTRUMENT_TYPE | VARCHAR2(20) | Type of instrument (e.g., IRS, CCS, XCCY). | DD_ORG_OTC_INSTRUMENTS.TYPE |
| NOTIONAL_AMOUNT | NUMBER(18,2) | Notional amount in the reporting currency. | DD_ORG_OTC_POSITIONS.NOTIONAL |
| CURRENCY | VARCHAR2(3) | ISO 4217 currency code. | DD_ORG_OTC_POSITIONS.CCY |
| START_DATE | DATE | Effective start date of the contract. | DD_ORG_OTC_INSTRUMENTS.EFF_START |
| END_DATE | DATE | Maturity or termination date. | DD_ORG_OTC_INSTRUMENTS.EFF_END |
| COUNTERPARTY_ID | VARCHAR2(30) | Identifier of the counterparty. | DD_ORG_OTC_POSITIONS.CPTY_ID |
| PRICING_MODEL | VARCHAR2(25) | Name of the model used for valuation. | DD_ORG_OTC_PRICING.MODEL_NAME |
| MARKET_VALUE | NUMBER(18,2) | Current market value of the instrument. | DD_ORG_OTC_PRICING.MKT_VAL |
| LAST_UPDATE_TS | TIMESTAMP(6) | Timestamp of the latest refresh. | DD_ORG_OTC_INSTRUMENTS.UPDATED_AT |
How the View Is Built
The definition of DD_ORG_OTC_METADATA_VX follows a classic starschema pattern:
- Base tables The three source tables store instrument details, position data, and pricing information respectively.
- Join logic A
LEFT OUTER JOINis used fromDD_ORG_OTC_INSTRUMENTSto the other two tables onINSTRUMENT_ID. This guarantees that every instrument appears in the view even if pricing or position records are temporarily missing. - Derived columns Some fields are calculated, e.g.
EXPOSURE_USD = NOTIONAL_AMOUNT * FX_RATE_TO_USD, where the FX rate comes from a reference tableDD_REF_FX_RATES. - Data quality flag A Boolean flag
IS_COMPLETEis set to1when all mandatory fields are present; otherwise0.
The view is refreshed nightly via a materialized view log. Because it is readonly, no DML statements (INSERT/UPDATE/DELETE) are permitted directly against it.
Typical Use Cases
- Regulatory Reporting: Export the view to XML/CSV for submission to trade repositories.
- Risk Analytics: Pull a snapshot into VaR or CVA models to calculate portfoliolevel sensitivities.
- Data Governance: Run completeness checks:
SELECT COUNT(*) FROM DD_ORG_OTC_METADATA_VX WHERE IS_COMPLETE = 0;
- Business Intelligence: Create dashboards showing notional distribution by instrument type, region, or counterparty.
Performance Considerations
Because the view aggregates multiple large tables, queries can become resourceintensive. The following best practices are recommended:
- Predicate pushdown: Always filter on indexed columns such as
INSTRUMENT_ID,CURRENCY, orSTART_DATEto limit row scans. - Materialized view: For highfrequency reporting, create a materialized view that replicates the most used columns and refreshes every hour.
- Selective columns: Retrieve only the fields required for the task; avoid SELECT *.
Security and Access Control
Access to DD_ORG_OTC_METADATA_VX is controlled through Oracles rolebased security:
DD_OTC_READONLYGrants SELECT privileges to analytics teams.DD_OTC_DATA_STEWARDAllows SELECT plus the ability to run refresh procedures.- All access is logged via Oracle Auditing for traceability.
Future Enhancements (Roadmap)
- Incorporate TradeLevel attributes such as settlement instructions.
- Add a column for Regulatory Classification (e.g., EMIR OTC Derivative) mapped from the new taxonomy table.
- Expose a JSON view (
DD_ORG_OTC_METADATA_JSON_VX) for API consumers. - Introduce incremental refresh to reduce nightly ETL window.
Getting Started
Below is a simple query you can run to view the first 20 rows of the view:
SELECT INSTRUMENT_ID, INSTRUMENT_TYPE, NOTIONAL_AMOUNT, CURRENCY, START_DATE, END_DATE, COUNTERPARTY_ID, MARKET_VALUE, IS_COMPLETEFROM DD_ORG_OTC_METADATA_VXWHERE ROWNUM <= 20;
For more detailed examples, consult the internal datadictionary wiki or contact the Data Steward.
