153 ) List of Chellenges of Data modeling ?

 Problems / Chellenges of Data modeling ?

----------------------------------------------------------------

 Challenge 1 : SCD 1 to SCD 2 issue

----------------------------------------------------------------

Step-by-Step Resolution for an Existing Production Model

Step 1: Identifying the Anomaly & Root Cause Analysis

• The Production Symptom: Finance and sales teams noticed that historical monthly revenue reports changed after a routine product catalog update because past orders automatically reflected new prices and names.

• Root Cause Analysis: The data model used a standard design where the Fact_Orders table pointed directly to a single Dim_Product table via a foreign key.

• The Flaw: We found that Dim_Product acted as a mutable lookup table (SCD Type 0/1). When a product was renamed or repriced, the dimension row was overwritten, destroying historical point-in-time accuracy.

Step 2: Modifying the Existing Schema in Erwin & Database (Alter & Backfill)

Instead of dropping or recreating tables, we altered the existing structure and handled historical backfill:

1. In Erwin Data Modeler & Database:

   • Alter the existing dim_product table to add temporal columns: effective_start_date, effective_end_date, and is_current.

   • Add the auto-incrementing product_sk as the new primary key while retaining product_id as the natural business key.

2. Handling Existing Stored Data:

   • Run a one-time migration script using historical audit/CDC logs to seed past product versions into dim_product from day one.

   • Update existing rows in fact_order_line to point to the correct historical product_sk based on the order date.

Step 3: Implementing the SCD Type 2 ETL Pipeline

For ongoing data changes, the ingestion pipeline executes an Upsert/Merge pattern rather than a simple overwrite.

1. Expire the old record when an attribute changes:

UPDATE dim_product SET effective_end_date = CURRENT_DATE - INTERVAL '1 day', is_current = FALSE FROM stg_product_catalog stg WHERE dim_product.product_id = stg.product_id AND dim_product.is_current = TRUE AND (dim_product.unit_price <> stg.unit_price OR dim_product.product_name <> stg.product_name);

2. Insert the new active version with a new surrogate key:

INSERT INTO dim_product ( product_id, product_name, unit_price, effective_start_date, effective_end_date, is_current ) SELECT stg.product_id, stg.product_name, stg.unit_price, CURRENT_DATE AS effective_start_date, NULL AS effective_end_date, TRUE AS is_current FROM stg_product_catalog stg WHERE NOT EXISTS ( SELECT 1 FROM dim_product dp WHERE dp.product_id = stg.product_id AND dp.is_current = TRUE AND dp.unit_price = stg.unit_price AND dp.product_name = stg.product_name );

Step 4: Power BI Report Design & DAX Validation

• Model Relationships: Connect fact_order_line[product_sk] to dim_product[product_sk] using a standard Many-to-One (*:1) relationship.

• DAX Measure Result: Because the fact table points to the historical product_sk corresponding to the exact date the order was placed, aggregation formulas calculate values cleanly without retroactive distortion.

Total Historical Revenue = SUMX( fact_order_line, fact_order_line[quantity] * RELATED(dim_product[unit_price]) )

----------------------------------------------------------------

 Challenge 2 : Multi-Currency Global Sales Reporting Discrepancies

----------------------------------------------------------------

 

Step 1: Identifying the Anomaly & Root Cause Analysis

• The Production Symptom: Finance and leadership noticed severe financial discrepancies in global revenue reports, where cross-border orders showed massive variance compared to local bank ledger settlements.

• Root Cause Analysis: The data model stored international transaction amounts in local currencies but converted them using only the current day's exchange rate rather than the historical rate active on the exact day of the sale.

• The Flaw: We found that the star schema lacked a proper historical time-to-currency mapping, relying instead on a static exchange rate attribute inside the fact table or converting dynamically at query time using today's rates.

Step 2: Modifying the Existing Schema in Erwin & Database (Alter & Backfill)

Instead of rebuilding the entire data warehouse, we altered the existing structure to incorporate a historical currency mapping:

1. In Erwin Data Modeler & Database:

   • Create a new dim_currency_exchange_rate table containing natural keys for currency_code, target_currency_code, exchange_rate, and effective_date.

   • Alter the existing fact_order table to include a foreign key linking transactions to the currency dimension based on the transaction date.

2. Handling Existing Stored Data:

   • Run a one-time migration script to populate historical daily exchange rates across all operating regions from inception.

   • Update existing fact records to bind them to the correct historical exchange rate matching the order date.

Step 3: Implementing the Currency Conversion ETL Pipeline

For ongoing processing, the ingestion pipeline maps incoming transactions to the precise daily financial conversion value.

1. Inserting or refreshing daily exchange rates from financial feeds:

INSERT INTO dim_currency_exchange_rate (from_currency, to_currency, exchange_rate, rate_date) SELECT stg.from_currency, stg.to_currency, stg.rate, stg.rate_date FROM stg_daily_fx_rates stg;

2. Fact ingestion joining to the exact daily exchange rate:

INSERT INTO fact_order_line (order_id, order_date, local_currency_amount, currency_code) SELECT stg.order_id, stg.order_date, stg.amount, stg.currency_code FROM stg_orders stg JOIN dim_currency_exchange_rate fx ON stg.currency_code = fx.from_currency AND stg.order_date = fx.rate_date;

Step 4: Power BI Report Design & DAX Validation

• Model Relationships: Connect fact_order_line to dim_currency_exchange_rate using a Many-to-One (*:1) relationship based on both currency code and transaction date.

• DAX Measure Result: Because the fact table resolves the exact daily rate active when the order occurred, global revenue aggregates cleanly into base currency without audit variance.

Total Converted Revenue = SUMX( fact_order_line, fact_order_line[local_currency_amount] * RELATED(dim_currency_exchange_rate[exchange_rate]) )

----------------------------------------------------------------

 Challenge 3 :  Insurance - missing columns for claims in policy table

---------------------------------------------------------------- 

Step 1: Identifying the Anomaly & Root Cause Analysis

• The Production Symptom: Actuarial and finance teams discovered severe inaccuracies in reserving triangles and loss-ratio reports because claim payments and outstanding case reserves were being linked to the wrong policy underwriting year or coverage split.

• Root Cause Analysis: The data model enforced a simplistic 1:1 relationship between a claim and a policy number, failing to handle complex multi-line commercial policies where a single incident triggered multiple coverage parts (e.g., General Liability, Property, and Workers' Compensation) with separate sub-limits and deductibles.

• The Flaw: We found that treating claims as flat records attached directly to a master policy ignored the intermediate Coverage Layer / Risk Unit grain, causing payouts to bleed across unrelated coverage buckets and distorting actuarial reserving calculations.

Step 2: Modifying the Existing Schema in Erwin & Database (Alter & Backfill)

Instead of keeping a direct policy-to-claim link, we restructured the schema to introduce an intermediate coverage split entity:

1. In Erwin Data Modeler & Database:

   • Create a new bridging or dimension table called dim_policy_coverage containing coverage_id, policy_id, line_of_business, sub_limit_amount, and deductible.

   • Alter the existing fact_claim table to foreign-key link to coverage_id instead of directly to policy_id, ensuring every claim transaction maps to its specific legal coverage line.

2. Handling Existing Stored Data:

   • Run a one-time migration script parsing historical claim notes and payment logs to re-allocate past claim payouts to their correct sub-coverage buckets based on the loss description and policy schedule.

Step 3: Implementing the Claim-to-Coverage Allocation Pipeline

For ongoing claim processing, the ingestion pipeline maps payment transactions and reserve adjustments to the exact coverage line.

1. Inserting or updating claim transaction records tied to specific coverages:

INSERT INTO fact_claim_transaction (claim_id, coverage_id, transaction_type, transaction_amount, transaction_date) SELECT stg.claim_id, cov.coverage_id, stg.tx_type, stg.amount, stg.tx_date FROM stg_claim_feed stg JOIN dim_policy_coverage cov ON stg.policy_id = cov.policy_id AND stg.line_of_business = cov.line_of_business;

2. Calculating net incurred losses against specific coverage sub-limits:

SELECT cov.line_of_business, SUM(tx.transaction_amount) AS total_incurred FROM fact_claim_transaction tx JOIN dim_policy_coverage cov ON tx.coverage_id = cov.coverage_id GROUP BY cov.line_of_business;

Step 4: Power BI Report Design & DAX Validation

• Model Relationships: Connect fact_claim_transaction to dim_policy_coverage using a Many-to-One (*:1) relationship, which then relates back to dim_policy.

• DAX Measure Result: Because claims are segmented by the precise coverage line rather than a blanket policy header, actuarial loss development triangles aggregate without cross-liability distortion.

Total Incurred Loss = CALCULATE( SUM(fact_claim_transaction[transaction_amount]), fact_claim_transaction[transaction_type] IN {"Payment", "Outstanding Reserve"} )

----------------------------------------------------------------

 Challenge 4 :  Insurance - missing columns for claims in policy table

----------------------------------------------------------------