test post

  

Step 1: The Client's Existing System and Tables

In my previous insurance project, the client was using two completely different systems to run their daily business operations:

 (Oracle Database):  -           ( Policy System  )  -  had customer , policies ,agents data

 (SQL Server Database):  -  (Claims System  ) -   had claims , claim_transactions

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

Step 2: The Problems They Faced

Because policy data and claims data lived in two separate databases, the business teams faced major roadblocks every day:

• Inter database queries : If managers wanted to see which agents policies had highest claims 

• 
  

• Manual export and slow reporting:

• Every month, data people had to exported manual  from Oracle and SQL Server into local Excel files and MS Access databases. It took days just to merge correctlythese files together.

• Zero historical tracking:

• If a customer details are updated  the system overwrote and had not historical data to lookback. 

 

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

Step 3: The Client's Expectation and Why

• The Goal: They wanted a single, centralized data warehouse where policy data and claims data lived together in one place.

• The Reason: 

• They needed fast, automatic reports to track monthly policy renewals, 
• spot high claims payouts quickly, 
• and check agent performance without waiting on manual Excel exports.
• 
  

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

Step 4: The Solution Design I Proposed

To solve these problems, I designed a modern, centralized dimensional data model (often called a Star Schema) using cloud storage and a data warehouse. Here are the exact steps I followed to design it:

1. Defined the Business Process First

Instead of just copying tables over, I sat down with business users to understand what they wanted to measure. We decided our main focus areas (fact tables) would be Policy Sales/Renewals and Claim Payouts, while everything else would be descriptive details (dimension tables).

2. Designed the Dimension Tables (The Context)

I created shared lookup tables so we could slice and dice our data by anything:

• Dim_Customer: Kept a full history of customer details. If a customer moved to a new city, we tracked it properly.
• Dim_Agent: Linked every sale and claim back to the correct agent and branch.
• Dim_Date: Allowed the business to run reports easily by day, month, quarter, or year.
• Dim_Policy_Type: Categorized policies into Auto, Home, or Life insurance.

3. Designed the Fact Tables :  I built transaction and monthly snapshot tables to store all the numbers:

• Fact_Policy_Transactions: Stored every new policy created and every renewal, along with the total premium amount collected.
• Fact_Claim_Transactions: Stored every claim event and the exact payout amount given to the customer.

 

4. Built the Data Pipeline (ETL Strategy)

Since data was coming from Oracle and SQL Server, I designed an automated data pipeline:

• Extract & Load: Every night, automated scripts pull the data changes from the Oracle and SQL Server operational databases and load them into a staging area.
• 
  
• Transform: We cleaned the data—fixing mismatched IDs, formatting dates uniformly, and linking the Oracle policy numbers with the SQL Server claim numbers.
• 
  
• Load to Warehouse: Finally, the clean data populated our new dimension and fact tables so it was ready for the reporting team every morning.

 

 

•