Assignment 1
(No Code Excel & Power BI)CSU1888
Name – Rishav Sood
Reg. No. - GF202565158
Faculty Name – Dr. Ruchika Sharma
Submitted by Submitted to
Rishav Sood Dr. Ruchika Sharma
Yogananda School of AI, Computers and Data Sciences
Shoolini University of Biotechnology and Management
SciencesSolan H.P, India
Data Warehouse:
A Data Warehouse is a centralized repository that stores
integrated, historical, and structured data from multiple
sources. It is a core component of Business Intelligence
(BI) systems, providing the foundation for reporting,
analytics, and decision-making.
Role of Data Warehousing in BI:
a. Data Integration
• Combines data from different sources into a single,
unified format.
• Helps BI tools access consistent and clean data for
analysis.
b. Data Quality and Consistency
• Cleanses, filters, and transforms data during the ETL
(Extract, Transform, Load) process.
• Ensures accurate, reliable, and standardized data for
business reporting.
c. Historical Data Storage
• Stores large volumes of historical data, allowing
organizations to analyze trends and patterns over
time.
d. Fast Query Performance
• Optimized for analytical queries and reporting rather
than transactional processing.
• Enables quick access to summarized and aggregated
data.
e. Supports BI Tools
• Acts as a backend for BI tools (like Power BI, Tableau,
QlikView).
• BI tools use warehouse data to create dashboards,
KPIs, and visual reports.
f. Better Decision-Making
• Provides managers with insights through accurate
reports and analytics.
• Enhances strategic, tactical, and operational
decision-making.
Data Warehousing Supports Decision-
Making in Organizations:
a. Data Integration
• Combines data from multiple sources (ERP, CRM,
spreadsheets, etc.) into one consistent database.
• Helps managers access a unified and accurate view
of the organization.
b. Historical Analysis
• Stores large volumes of historical data.
• Enables trend analysis, forecasting, and performance
comparison over time.
c. Faster Query Performance
• Optimized for complex queries and analytical
processing (OLAP).
• Reduces time to gather insights and improves
responsiveness to market changes.
d. Improved Data Quality and Consistency
• Data cleaning and transformation ensure accuracy
and reliability.
• Supports confident, evidence-based decision-
making.
e. Strategic and Tactical Decisions
• Strategic: Long-term planning, investment, and
growth.
• Tactical: Short-term actions, performance
monitoring, and process improvement.
f. Support for Business Intelligence Tools
• Works with tools like Power BI, Tableau, or Cognos.
• Provides dashboards, reports, and visual analytics for
executives.
For Eg:
Amazon:
Uses data warehousing (AWS Redshift) to analyze
customer purchase behaviour, recommend products, and
optimize inventory.
Hospital Management:
Patient and billing data stored in a Data Warehousing
helps predict resource needs and improve service quality.
Diagram of Data Warehousing in BI:
Business Intelligence (BI): Process,
Technologies, and Roles
BI Process
The Business Intelligence (BI) process transforms raw data
into meaningful and useful information for business
analysis and decision-making.
It consists of several key stages:
a. Data Collection
• Data is gathered from multiple internal and external
sources such as ERP systems, CRM systems,
databases, and social media.
b. Data Integration
• The collected data is cleaned, transformed, and
loaded into a central repository called a Data
Warehouse using ETL (Extract, Transform, Load) tools.
c. Data Storage
• The integrated data is stored in a Data Warehouse or
Data Mart, where it is structured and organized for
efficient analysis.
d. Data Analysis
• Analytical tools such as OLAP (Online Analytical
Processing), data mining, and predictive analytics are
used to identify trends, correlations, and insights.
e. Data Visualization and Reporting
• The analyzed data is presented in dashboards,
scorecards, and reports for decision-makers using
visualization tools (e.g., Power BI, Tableau).
f. Decision Making
• Executives and managers use insights derived from BI
to make data-driven strategic, tactical, and
operational decisions.
BI Technologies:
BI technologies include a broad range of software and
practices focused on collecting, storing, analyzing, and
visualizing data. Common BI technologies are:
• Data Warehousing systems for storing integrated data
from multiple sources
• ETL (Extract, Transform, Load) tools for data
integration
• Analytical processing tools, such as OLAP (Online
Analytical Processing)
• Reporting and dashboard tools (e.g., Tableau, Power
BI, Qlik)
• Data mining and predictive analytics platforms
• Big Data frameworks (e.g., Hadoop, Spark)
These technologies enable organizations to:
• Gather and integrate data efficiently
• Analyze large and complex datasets
• Visualize near-real-time insights for strategic and
operational decisions
Roles and Responsibilities of BI:
Roles Responsibilities
BI Analyst Collect requirements, run analyses, build
reports, interpret data; help stakeholders
understand trends and metrics.
BI Developer Design, develop, and maintain BI
solutions and dashboards; work with
databases and ETL processes.
Data Design and implement data pipelines;
Engineer ensure data is clean, integrated, and
accessible for analytics.
Data Perform advanced analytics, build
Scientist predictive models, apply machine
learning techniques.
BI Architect Design overall BI solutions, choose
technologies, ensure data integration and
system scalability.
BI Manager Lead BI teams, set strategy, manage
projects, liaise between departments,
ensure BI solutions meet organizational
needs.
Database Manage data warehouses, ensure
Administrator security and performance, handle data
backup and recovery.
Diagram Of BI Architecture: