**DD_ORG_OTC_METADATA_VX** and Reference File Download Link
https://eu2.contabostorage.com/00f3241116844f24b628f46d81abb929:st1/folder6/6303/1655937002_org_otc_datamart_metadata_-_Standar_Format.xlsx
2026-05-30 03:56:05 - Admin
<style> body{ font-family:Arial,Helvetica,sans-serif; line-height:1.6; margin:0; padding:0; background:#fafafa; color:#333; } .container{ max-width:960px; margin:auto; padding:20px; } h1{ text-align:center; color:#006699; margin-bottom:0.5em; } h2{ color:#004466; border-bottom:2px solid #cce5ff; padding-bottom:4px; } table{ width:100%; border-collapse:collapse; margin:20px 0; } th, td{ border:1px solid #ddd; padding:8px; } th{ background:#e6f2ff; text-align:left; } a{ color:#006699; text-decoration:none; } a:hover{ text-decoration:underline; } .note{ background:#fff8e1; border-left:4px solid #ffeb3b; padding:10px 15px; margin:20px 0; } </style><div class="container"> <h1>DD_ORG_OTC_METADATA_VX</h1> <section> <h2>What is DD_ORG_OTC_METADATA_VX?</h2> <p> <strong>DD_ORG_OTC_METADATA_VX</strong> is a specialized view in the Oracle Data Decision (DD) schema that consolidates metadata for the <em>OverTheCounter</em> (OTC) trading domain. It integrates information from several underlying tables such as <code>DD_ORG_OTC_INSTRUMENTS</code>, <code>DD_ORG_OTC_POSITIONS</code>, and <code>DD_ORG_OTC_PRICING</code>, providing a single point of access for downstream reporting, analytics, and data quality checks. </p> </section> <section> <h2>Key Purpose</h2> <ul> <li>Expose a readonly, flattened representation of OTC instrument metadata.</li> <li>Support regulatory reporting (e.g., EMIR, DoddFrank) by delivering required attributes in a compliant format.</li> <li>Facilitate datalineage tracking every column can be traced back to its source table and field.</li> <li>Accelerate adhoc queries and datascience workloads without joining multiple heavy tables.</li> </ul> </section> <section> <h2>Core Columns</h2> <table> <thead> <tr> <th>Column</th> <th>Data Type</th> <th>Description</th> <th>Source Table/Field</th> </tr> </thead> <tbody> <tr> <td>INSTRUMENT_ID</td> <td>VARCHAR2(30)</td> <td>Unique identifier for the OTC instrument.</td> <td>DD_ORG_OTC_INSTRUMENTS.INSTRUMENT_ID</td> </tr> <tr> <td>INSTRUMENT_TYPE</td> <td>VARCHAR2(20)</td> <td>Type of instrument (e.g., IRS, CCS, XCCY).</td> <td>DD_ORG_OTC_INSTRUMENTS.TYPE</td> </tr> <tr> <td>NOTIONAL_AMOUNT</td> <td>NUMBER(18,2)</td> <td>Notional amount in the reporting currency.</td> <td>DD_ORG_OTC_POSITIONS.NOTIONAL</td> </tr> <tr> <td>CURRENCY</td> <td>VARCHAR2(3)</td> <td>ISO 4217 currency code.</td> <td>DD_ORG_OTC_POSITIONS.CCY</td> </tr> <tr> <td>START_DATE</td> <td>DATE</td> <td>Effective start date of the contract.</td> <td>DD_ORG_OTC_INSTRUMENTS.EFF_START</td> </tr> <tr> <td>END_DATE</td> <td>DATE</td> <td>Maturity or termination date.</td> <td>DD_ORG_OTC_INSTRUMENTS.EFF_END</td> </tr> <tr> <td>COUNTERPARTY_ID</td> <td>VARCHAR2(30)</td> <td>Identifier of the counterparty.</td> <td>DD_ORG_OTC_POSITIONS.CPTY_ID</td> </tr> <tr> <td>PRICING_MODEL</td> <td>VARCHAR2(25)</td> <td>Name of the model used for valuation.</td> <td>DD_ORG_OTC_PRICING.MODEL_NAME</td> </tr> <tr> <td>MARKET_VALUE</td> <td>NUMBER(18,2)</td> <td>Current market value of the instrument.</td> <td>DD_ORG_OTC_PRICING.MKT_VAL</td> </tr> <tr> <td>LAST_UPDATE_TS</td> <td>TIMESTAMP(6)</td> <td>Timestamp of the latest refresh.</td> <td>DD_ORG_OTC_INSTRUMENTS.UPDATED_AT</td> </tr> </tbody> </table> </section> <section> <h2>How the View Is Built</h2> <p> The definition of <code>DD_ORG_OTC_METADATA_VX</code> follows a classic <em>starschema</em> pattern: </p> <ol> <li><strong>Base tables</strong> The three source tables store instrument details, position data, and pricing information respectively.</li> <li><strong>Join logic</strong> A <code>LEFT OUTER JOIN</code> is used from <code>DD_ORG_OTC_INSTRUMENTS</code> to the other two tables on <code>INSTRUMENT_ID</code>. This guarantees that every instrument appears in the view even if pricing or position records are temporarily missing.</li> <li><strong>Derived columns</strong> Some fields are calculated, e.g. <code>EXPOSURE_USD = NOTIONAL_AMOUNT * FX_RATE_TO_USD</code>, where the FX rate comes from a reference table <code>DD_REF_FX_RATES</code>.</li> <li><strong>Data quality flag</strong> A Boolean flag <code>IS_COMPLETE</code> is set to <code>1</code> when all mandatory fields are present; otherwise <code>0</code>.</li> </ol> <p class="note"> 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. </p> </section> <section> <h2>Typical Use Cases</h2> <ul> <li><strong>Regulatory Reporting:</strong> Export the view to XML/CSV for submission to trade repositories.</li> <li><strong>Risk Analytics:</strong> Pull a snapshot into VaR or CVA models to calculate portfoliolevel sensitivities.</li> <li><strong>Data Governance:</strong> Run completeness checks: <pre>SELECT COUNT(*) FROM DD_ORG_OTC_METADATA_VX WHERE IS_COMPLETE = 0;</pre> </li> <li><strong>Business Intelligence:</strong> Create dashboards showing notional distribution by instrument type, region, or counterparty.</li> </ul> </section> <section> <h2>Performance Considerations</h2> <p> Because the view aggregates multiple large tables, queries can become resourceintensive. The following best practices are recommended: </p> <ul> <li><strong>Predicate pushdown:</strong> Always filter on indexed columns such as <code>INSTRUMENT_ID</code>, <code>CURRENCY</code>, or <code>START_DATE</code> to limit row scans.</li> <li><strong>Materialized view:</strong> For highfrequency reporting, create a materialized view that replicates the most used columns and refreshes every hour.</li> <li><strong>Selective columns:</strong> Retrieve only the fields required for the task; avoid SELECT *.</li> </ul> </section> <section> <h2>Security and Access Control</h2> <p> Access to <code>DD_ORG_OTC_METADATA_VX</code> is controlled through Oracles rolebased security: </p> <ul> <li><code>DD_OTC_READONLY</code> Grants SELECT privileges to analytics teams.</li> <li><code>DD_OTC_DATA_STEWARD</code> Allows SELECT plus the ability to run refresh procedures.</li> <li>All access is logged via Oracle Auditing for traceability.</li> </ul> </section> <section> <h2>Future Enhancements (Roadmap)</h2> <ol> <li>Incorporate <em>TradeLevel</em> attributes such as settlement instructions.</li> <li>Add a column for <strong>Regulatory Classification</strong> (e.g., EMIR OTC Derivative) mapped from the new taxonomy table.</li> <li>Expose a JSON view (<code>DD_ORG_OTC_METADATA_JSON_VX</code>) for API consumers.</li> <li>Introduce incremental refresh to reduce nightly ETL window.</li> </ol> </section> <section> <h2>Getting Started</h2> <p>Below is a simple query you can run to view the first 20 rows of the view:</p> <pre>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; </pre> <p> For more detailed examples, consult the internal datadictionary wiki or contact the <a href="mailto:data.steward@example.com">Data Steward</a>. </p> </section></div>```