Data Warehousing in DBMS

 Data Warehouse kya hota hai?

Data Warehouse ek central storage system hota hai jahan par alag-alag jagah se data collect karke store kiya jata hai. Ye data mainly analysis aur reporting ke liye use hota hai, na ki daily operations ke liye.

 Simple Definition:

Data warehouse ek aisi jagah hai jahan historical data (purana data) ko collect karke analysis aur decision making ke liye store kiya jata hai.

 Real Life Example:

Socho ek shopping mall chain hai jiske alag-alag cities mein stores hain. Har store ka sales data ek central system (data warehouse) mein store hota hai. Fir managers analysis kar sakte hain:

• Sabse zyada bikne waala product kaunsa hai?

• Kis city mein sabse zyada sales hui?

• Sales ka trend kya hai last 6 mahine ka?

 Features of Data Warehouse:

1. Subject-Oriented
   Focus specific topics pe hota hai jaise ki sales, customers, ya inventory.

2. Integrated
   Alag-alag sources se data ko mila ke ek jaisa format banaya jata hai.

3. Time-Variant
   Purana (historical) data bhi store kiya jata hai, taaki trends samajh aaye.

4. Non-Volatile
   Data ko sirf read kiya jata hai, change ya delete nahi kiya jata.

 Data Warehouse Architecture

---

 ETL Process in Data Warehouse

ETL ka full form hai:
Extract → Transform → Load

1. Extract –

Data ko alag-alag sources (databases, Excel, CRM) se uthaya jata hai.

2. Transform –

Data ko clean aur format kiya jata hai (e.g. date format sahi karna, duplicates hataana).

3. Load –

Cleaned data ko data warehouse mein store kiya jata hai analysis ke liye.

 Diagram:

 Example Use Case: E-Commerce Website

→ Ye sab data warehouse mein store hota hai
→ Query run ki ja sakti hai jaise:

“Kaunse product ne sabse achha feedback aur highest sales diya Q1 mein?”

---

 Types of Data Warehouses

1. Enterprise Data Warehouse (EDW)
   Pure organization ka data store karta hai.

2. Data Mart
   Sirf ek department (e.g. Finance ya HR) ka data hota hai.

3. Operational Data Store (ODS)
   Real-time data hota hai, jo daily use ke liye hota hai.

---

 DBMS vs Data Warehouse

 Benefits of Data Warehousing

• Fast analysis aur reporting

• Decision making mein help karta hai

• Large data ko manage aur visualize karna easy

• Purane trends samajhne mein help karta hai

 Challenges

• Shuru mein setup cost zyada hoti hai

• Data ko integrate karna mushkil ho sakta hai

• Regular maintenance chahiye

---

 Summary