CSV105-Business analytics

(autonomous)

III YEAR / CSE / v SEM

Subject Handler,
Dr. [Link]
Business intelligence
• Business intelligence (BI) is the process of using technology, tools and
strategies to collect, analyze, and transform raw organizational data into
actionable insights.
• How Business Intelligence Works
• Modern BI platforms operate through a distinct lifecycle to deliver value:
• Data Gathering: Raw data is pulled from multiple sources—such as CRM
software, ERP systems, e-commerce platforms, and external market data.
• Data Transformation (ETL): The data is cleaned, structured, and organized
into centralized repositories like a Data Warehouse.
• Data Analysis: Users explore this data using software to uncover
operational bottlenecks, customer behaviors, or financial trends.
• Data Visualization: The results are presented in easily digestible formats
like pie charts, graphs, and live dashboards so non-technical users can
quickly understand the state of the business.
Core Concepts of BI
• The foundational pillars of business intelligence encompass:
• Data Integration: Combining fragmented information from disparate
sources into a unified, consistent view.
• Data Warehousing: Centralizing and storing massive volumes of data
for efficient retrieval and analysis.
• Data Mining: Exploring large datasets using AI or statistical algorithms
to discover hidden patterns and behaviors.
• Data Visualization: Translating complex numbers into intuitive visual
interfaces for faster executive decision-making.
 Data Warehousing
• A Data Warehousing (DW) is process for collecting and managing data from
varied sources to provide meaningful business insights.
• A Data warehouse is typically used to connect and analyse
business data from heterogeneous sources.
• The data warehouse is the core of the BI system which is built for data
analysis and reporting.
• It is a blend of technologies and components which aids the strategic use
of data.
• It is electronic storage of a large amount of information by a business
which is designed for query and analysis instead of transaction processing.
• It is a process of transforming data into information and making it available
to users in a timely manner to make a difference.
Other Names for Data Warehouse
• Data warehouse system is also known by the following name:
• Decision Support System (DSS)
• Executive Information System
• Management Information System
• Business Intelligence Solution
• Analytic Application
• Data Warehouse
How Data Warehouse Works
• The information comes from one or more data sources and is sent to a data
warehouse, which stores it all in one place.
• A transactional system and several relational databases send data to a data
warehouse.
• The data warehouse works by collecting and putting together data into a
complete database. After collecting the data, it is put into different tables
based on what it is and how it is organized.
• Data could be:
• Structured
• Semi-structured
• Unstructured
• After changing, transforming, and ingesting the data, users can access it in
the data warehouse using business intelligence tools, SQL clients, and
spreadsheets.
Primary Data Warehouse Types
• Enterprise Data Warehouse (EDW): A massive, centralized repository
that integrates data from all departments across an entire
organization. It acts as a "single source of truth" for corporate
decision-making and complex business intelligence (BI).
• Data Mart: A smaller, department-specific subset of an EDW.
Designed for individual business units like sales, marketing, or
finance, data marts allow teams to quickly access relevant, localized
data without querying the entire corporate database.
• Operational Data Store (ODS): Unlike traditional warehouses focused
on historical data, an ODS contains up-to-date, real-time or near real-
time operational data. It is primarily used for day-to-day tasks and
immediate, tactical decision-making.
Components of Data Warehouse (1)
Components of Data Warehouse (2)
• OLTP data
• OLTP (Online Transactional Processing) is a category of data processing that is
focused on transaction-oriented tasks. OLTP typically involves inserting, updating,
and/or deleting small amounts of data in a database.
• OLTP mainly deals with large numbers of transactions by a large number of users.
• OLTP databases are generally the main source for the data warehouse.
• ETL Process
• ETL is short for extract, transform, load, three database functions that are
combined into one tool/pipeline to pull data out of one database and place it into
another database.
• 1. Extracting data from source databases
• 2. Transforming data to match a unified format for specific business purposes
• 3. Loading reformatted data to the data warehouse
Components of Data Warehouse (3)
• Data Marts
• A data mart is a subset of a data warehouse that is designed for a particular line
of business, such as sales, marketing, or finance.
• In a data mart, data can be derived from an enterprise-wide data warehouse or
data can also be collected directly from sources.
• Reporting and Analytics
• When data warehouse and data marts are built. The base work is done. Data
prepared and ingested, modelled and cleaned.
• Reporting is the process of organizing data into informational summaries to
monitor how different areas of a business are performing. Measuring core
metrics and presenting them — whether in an email, a slide deck, or online
dashboard — falls under this category.
• Analytics is the process of exploring data and reports to extract meaningful
insights, which can be used to better understand and improve business
performance.
Data Warehouse Applications(1)
• Airline: In the Airline system, it is used for operation purpose like crew
assignment, analyses of route profitability, frequent flyer program
promotions, etc
• Banking: It is widely used in the banking sector to manage the
resources available on desk effectively. Few banks also used for the
market research, performance analysis of the product and operations.
• Healthcare: Healthcare sector also used Data warehouse to strategize
and predict outcomes, generate patient's treatment reports, share
data with tie-in insurance companies, medical aid services, etc.
• Public sector: In the public sector, data warehouse is used for
intelligence gathering. It helps government agencies to maintain and
analyze tax records, health policy records for every individual
Data Warehouse Applications(2)
• Investment and Insurance sector: In this sector, the warehouses are
primarily used to analyze data patterns, customer trends, and to track
market movements.
• Retail chain: In retail chains, Data warehouse is widely used for distribution
and marketing. It also helps to track items, customer buying pattern,
promotions and also used for determining pricing policy.
• Telecommunication: A data warehouse is used in this sector for product
promotions, sales decisions and to make distribution decisions.
• Hospitality Industry: This Industry utilizes warehouse services to design as
well as estimate their advertising and promotion campaigns where they
want to target clients based on their feedback and travel patterns.
Data Warehouse Tools
Data Warehouse Tools (1)
• Cloud-Based Data Warehouses
• Amazon Redshift
• A data warehouse service that can be optimized for a specific use-case and fully
managed by AWS, when it comes to analyzing huge volumes of data.
• It has a column storage model to facilitate the query of structured information.
• Microsoft Azure Synapse Analytics
• A suite of data warehouse programs such as Azure Synapse Analytics that takes a
cloud computing system approach.
• It helps to build, deploy and manage data warehousing solutions with machine
learning capabilities within its architecture. Google BigQuery
• Google BigQuery
• A tool for analyzing Big Data that is specifically for data warehouses without having
to manage the servers.
• It is pay-as-you-go and boasts faster query operations with the inclusion
of SQL support.
Data Warehouse Tools (2)
• NoSQL Data Stores
• Amazon DynamoDB
• A fully managed NoSQL database service for high-performance data access. It excels in handling
large volumes of key-value and document data with high availability and scalability.
• Relational Databases Used in Warehousing
• PostgreSQL
• A powerful, open-source relational database management system (RDBMS) known for its
reliability and feature richness. It supports complex queries and integrates well with various BI
tools.
• Object Storage (Used in Modern Data Warehousing)
• Amazon S3
• A scalable and cost-effective object storage service for storing large amounts of unstructured data
like images, videos and log files. It offers high durability, security and easy access to data.
• Enterprise Data Warehouse
• Teradata
• A robust data warehouse platform known for its MPP architecture and ability to handle massive
data volumes. It excels in real-time data processing and complex analytics tasks for large
enterprises.
Data Warehouse Tools (3)
• Managed Relational Cloud Services
• Amazon RDS
• Managed relational databases for scalable backend data storage.
• Oracle Autonomous Warehouse
• A self-driving cloud data warehouse service by Oracle. It automates administration tasks like
provisioning, scaling and security, simplifying data warehouse management.
• Open-Source Relational Databases
• MariaDB: A popular open-source relational database management system known for its
compatibility with MySQL and its focus on performance and ease of use.
• NoSQL Document Databases
• MarkLogic: A multi-model NoSQL database that excels at storing and managing large
volumes of XML and JSON documents. It offers powerful querying capabilities for
complex data structures.
• Enterprise Data Cloud Platform
• Cloudera: A platform designed for building and managing data warehouses and data
lakes in a hybrid or multi-cloud environment. It provides tools and security features for
data governance and analytics at scale.
 Data Mart
• A Data Mart is a subset of a data warehouse designed to serve the
needs of a specific department or business unit within an
organization.

• Types of Data Marts:

• Dependent Data Marts
• Independent Data Marts
• Hybrid Data Marts
 Data Warehouse Data Mart
Centralized system Decentralized system

Uses top-down approach Uses bottom-up approach

Lightly denormalized Highly denormalized

Difficult and complex to build Easy and quick to build

Uses fact constellation schema Uses star and snowflake schemas

Flexible in nature Less flexible

Data-oriented Project-oriented / department-oriented

Has a long life span Has a shorter life span

Stores detailed data Stores summarized data

Very large in size Smaller in size

Size ranges from 100 GB to 1 TB+ Size is usually less than 100 GB

Implementation takes months to years Deployment takes a few months

Contains comprehensive operational data Operational data usually not present

Collects data from multiple sources Stores data from a data warehouse

Processing time is longer Processing time is shorter

Schema and view design is complex Schema and view design is simple
Knowledge Management
• Knowledge Management (KM) is the process of identifying, creating,
capturing, organizing, storing, sharing, and using knowledge within an
organization.
• It involves managing valuable resources such as employee skills, best
practices and information systems.
• The purpose of KM is to ensure that important knowledge is easily
accessible and applied to improve decision-making, efficiency,
collaboration and innovation.
Phases of the Knowledge Management Life cycle
Key components of KM (1)
Knowledge management (KM) relies on four foundational components:
1. people (who create and share insights),
2. processes (the workflows governing knowledge flows),
3. technology (the digital platforms used to store and access
information) and
4. content/strategy (the actual data assets aligned with business
objectives)
Key components of KM (2)
People
• Employees and leadership are the core of KM. They are responsible for generating new ideas,
capturing their experiences (tacit knowledge), and sharing expertise with peers through
collaboration. A strong, knowledge-sharing culture is often necessary to motivate participation.
Processes
• Processes outline the structured workflows for the KM lifecycle. This includes the steps for
identifying, capturing, validating, approving, and retiring information. Established processes
ensure that knowledge is consistently maintained and updated.
Technology & Infrastructure
• These are the tools and systems that house and connect knowledge. Common technologies
include:
• Knowledge Repositories / Knowledge Bases: Centralized digital libraries for documents, manuals,
and FAQs.
• Collaboration Tools: Platforms like Slack, Microsoft Teams, or dedicated intranets for real-time
exchange.
• Learning Management Systems (LMS): Platforms used for onboarding, training, and continuous
employee development.
• Search and AI: Built-in search functionalities and AI features to help users quickly discover
relevant information.
Key components of KM (3)
Content and Information
• Content represents the actual knowledge assets being managed.
• This includes both explicit knowledge (e.g., reports, spreadsheets, standard
operating procedures) and implicit knowledge (e.g., meeting notes, recorded
discussions).
• Proper taxonomies, tags, and metadata organize this information for easy
retrieval.
Strategy & Governance
• This component aligns the knowledge management system with the broader
business objectives.
• Strategy identifies knowledge gaps and determines how KM will improve
decision-making and efficiency.
• Governance provides the rules, standards, and performance metrics required to
ensure the system remains structured and impactful over time.
Objectives of Knowledge Management (KM)
• Improve Decision Making
• KM provides employees with timely, accurate, and relevant information, allowing them to evaluate situations more clearly and choose the best
possible course of action.
• Enhance Organizational Learning
• KM strengthens a culture where employees continuously learn from one another through shared experiences, insights, and expertise. This helps the
organization steadily grow its intellectual capabilities.
• Promote Innovation and Creativity
• A strong KM system enables employees to combine diverse ideas, analyze past solutions, and explore new ways of doing things, KM helps individuals
think creatively, experiment confidently, and introduce innovative improvements in products, processes, or services.
• Increase Efficiency and Productivity
• KM reduces redundancy and minimizes the time employees spend searching for information. This leads to higher productivity and better utilization of
organizational resources.
• Preserve Critical Organizational Knowledge
• KM ensures that essential expertise, experience, and insights do not disappear when employees retire, resign, or change roles. This preservation
strengthens continuity, reduces knowledge loss, and maintains organizational stability.
• Strengthen Collaboration and Teamwork
• By enabling smoother knowledge flow across departments, KM enhances communication and coordination among employees. This builds a
cooperative environment where collective knowledge is valued and utilized.
• Support Better Customer Service
• With quick access to updated information, employees are better equipped to respond to customer queries, solve issues, and offer personalized
assistance. KM ensures that best practices and customer insights are available to everyone, improving overall service quality and customer
satisfaction.
• Build Competitive Advantage
• Organizations that manage knowledge effectively can adapt faster, innovate more frequently, and operate more efficiently than their competitors. KM
strengthens strategic decision making, supports rapid problem solving, and helps the organization remain resilient in a changing environment,
ultimately creating a sustainable competitive edge.
Types of Decision
1. Strategic Decisions
• Focus: High-level, long-term direction (3–10 years). They involve significant risk, require
judgment, and shape the entire enterprise.
• Who makes them: Top-level management, C-suite executives, and the Board of Directors.
• Examples: Entering a new international market, acquiring a competitor, or entirely replacing a
core technological system.

• 2. Tactical Decisions
• Focus: Medium-term actions (months to a few years) that translate strategy into specific,
measurable projects. They deal with resource allocation and departmental planning.
• Who makes them: Middle management, department heads, and project managers.
• Examples: Designing a regional marketing campaign for a new product, hiring a new sales team,
or negotiating a vendor contract.

• 3. Operational Decisions
• Focus: Day-to-day and week-to-week activities. These are highly structured, rule-based, and focus
on the efficient execution of tasks so that tactical goals can be met.
• Who makes them: Frontline supervisors, junior managers, and automated business rule engines.
• Examples: Approving a daily production schedule, processing a customer's specific refund, or
assigning staff shifts for the week.
• An Executive information system (EIS),
also known as an Executive support
system (ESS),is a type of management
support system that facilitates and
supports senior executive information
and decision-making needs.
• It provides easy access to internal and
external information relevant to
organizational goals. It is commonly
considered a specialized form of
decision support system (DSS).
Decision Support System
• A Decision Support System (DSS) is an information system that
supports business or organizational decision-making activities.
• DSSs serve the management, operations and planning levels of an
organization (usually mid and higher management) and help people
make decisions about problems that may be rapidly changing and not
easily specified in advance—i.e. unstructured and semi-structured
decision problems.
• Decision support systems can be either fully computerized or human-
powered, or a combination of both.
• Group decision-support systems (GDSS) are specialized systems that
provide a group electronic environment in which managers and teams
can collectively make decision support and design solutions for
unstructured and semi structured problems.
• GDSS guided meetings takes place in a conference rooms with special
software and hardware tools to facilitate group decision making.
• It makes possible to increase the meeting size and increase in
productivity. Because individuals contribute simultaneously at the
same time rather than one at a time
Steps – Decision Making Process
Decision Support Systems
• Decision Support Systems (DSS) are interactive computer-based tools
designed to assist individuals, teams, or organizations in making better
decisions.
• Here are some defining characteristics:
• - Interactivity: DSS allow users to interact with data, models, and analytical
tools. Users can explore different scenarios, adjust parameters, and
evaluate outcomes.
• - Support for Semi-Structured and Unstructured Problems: DSS handle
problems that don't have clear-cut solutions. These problems often involve
incomplete information, multiple criteria, and subjective judgment.
• - Integration of Data, Models, and User Interface: DSS integrate data from
various sources, mathematical models, and user-friendly interfaces. They
bridge the gap between raw data and actionable insights.
Types of Decision Support Systems

• - Model-Driven DSS: These systems rely heavily on mathematical models.

For instance:
• - What-If Analysis: Users explore different scenarios by adjusting input
variables. An example is financial forecasting.
• - Optimization Models: Linear programming, integer programming, and
goal programming fall under this category.
• - Data-Driven DSS: These systems analyze historical data to identify
patterns and trends. Examples include data mining and predictive
analytics.
• - Document-Driven DSS: These focus on unstructured information. text
analytics tools help extract insights from documents, emails, and reports.
Examples
• - Inventory Management: A retail company uses a DSS to optimize
inventory levels. The system considers sales data, lead times, and storage
costs to recommend reorder points.
• - Healthcare: clinical decision support systems assist doctors in diagnosing
diseases. They analyze patient data, suggest potential diagnoses, and
recommend treatments.
• - financial Portfolio management: Investment firms use DSS to allocate
assets across different investment options. The system considers risk,
return, and market conditions.
• - Supply Chain Optimization: DSS help companies optimize supply chain
logistics, considering factors like transportation costs, demand fluctuations,
and warehouse capacities.
• I-Driven Decision Support:
• - Machine Learning (ML) and Natural Language Processing (NLP) are revolutionizing DSS. AI
algorithms analyze vast datasets, identify patterns, and predict outcomes.
• For instance, in healthcare, AI-powered DSS can assist doctors in diagnosing diseases by analyzing
medical images or patient records.
• 2. real-Time decision Support:
• - Traditional DSS often operate on historical data, but the future lies in real-time insights.
Technologies like stream processing and edge computing enable DSS to process data as it arrives.
• - Example: A logistics company monitors real-time traffic data to optimize delivery routes
dynamically, minimizing delays and fuel consumption.
• 3. Personalization and Context-Awareness:
• - DSS are becoming more personalized, tailoring recommendations based on individual
preferences, roles, and contexts.
• Context-awareness considers factors like location, time, and user behavior.
• - Example: An e-commerce platform recommends products based on a user's browsing history,
recent purchases, and current location.
• 4. Ethical Decision Support:
• - As AI-driven DSS become more influential, ethical considerations are crucial.
• Ensuring fairness, transparency, and accountability in decision-making is essential.
• - Example: An insurance company uses an ethical DSS to avoid biased pricing based on gender or
ethnicity.
• 5. collaborative Decision-making:
• - DSS are shifting from individual decision-makers to collaborative teams.
Platforms facilitate communication, knowledge sharing, and consensus-building.
• - Example: A project management DSS allows team members to collectively
evaluate risks, allocate resources, and prioritize tasks.
• 6. Integration with IoT and Big Data:
• - The proliferation of Internet of Things (IoT) devices generates massive data
streams.
• DSS can harness this data for predictive analytics and real-time decision-making.
• - Example: Smart cities use DSS to optimize traffic flow, reduce energy
consumption, and enhance public safety by integrating data from sensors,
cameras, and social media.
• 7. Blockchain for Trust and Security:
• - Blockchain technology ensures data integrity, transparency, and security. DSS
can leverage blockchain for secure transactions and audit trails.
• - Example: Supply chain DSS use blockchain to track product provenance,
ensuring authenticity and preventing counterfeiting.
Business intelligence
• Business intelligence (BI) is the process of using technology, tools and
strategies to collect, analyze, and transform raw organizational data into
actionable insights.
• How Business Intelligence Works
• Modern BI platforms operate through a distinct lifecycle to deliver value:
• Data Gathering: Raw data is pulled from multiple sources—such as CRM
software, ERP systems, e-commerce platforms, and external market data.
• Data Transformation (ETL): The data is cleaned, structured, and organized
into centralized repositories like a Data Warehouse.
• Data Analysis: Users explore this data using software to uncover
operational bottlenecks, customer behaviors, or financial trends.
• Data Visualization: The results are presented in easily digestible formats
like pie charts, graphs, and live dashboards so non-technical users can
quickly understand the state of the business.
Importance of Business Intelligence
• Faster, Data-Driven Decisions: Instead of relying on gut feelings or waiting days
for manual reports, managers use automated BI dashboards to track Key
Performance Indicators (KPIs) in real time.
• Cost and Resource Optimization: BI pinpoints operational bottlenecks, supply
chain inefficiencies, and areas of financial waste, allowing companies to allocate
resources optimally and improve the bottom line.
• Actionable Customer Insights: By unifying data from sales, website traffic, and
customer support, BI reveals purchasing trends and sentiment. This enables
highly personalized marketing and improved customer satisfaction.
• Competitive Advantage: Companies can benchmark their performance against
industry standards, identify gaps in the market, and proactively respond to
market shifts before competitors do.
• Enhanced Productivity: By automating routine data collection and reporting, BI
eliminates repetitive tasks, freeing employees to focus on high-value, strategic
work.
Examples of Business Intelligence System used in Practice

• Retail and Inventory Control

• Clothing stores use BI systems to check local weather and past sales to stock the right
clothes in the right towns.
• Supermarkets look at customer purchase habits to place related items next to each
other.
• Finance and Sales
• Finance teams combine profit, loss, and cost data into single live screens to watch cash
flow.
• Companies track customer value over time to find their most profitable buyers.
• Operations and Supply Chain
• Factories track machine speeds and repair times to stop production delays.
• Human resources check staff attendance and work output to plan better shift schedules
Four main types of Business Intelligence (BI)
Strategic Users (Executives)
• Who they are: CEOs, CFOs, owners and top directors.
• What they do: Make high-level, long-term plans for the whole company.
• BI needs: High-level summaries, key performance indicators (KPIs), and executive dashboards.
Tactical Users (Middle Managers)
• Who they are: Sales managers, HR heads, and department leads.
• What they do: Plan medium-term goals and manage department budgets and resources.
• BI needs: Department-specific performance reports and trend tracking.
Operational Users (Day-to-Day Staff)
• Who they are: Customer support, sales reps, and inventory workers.
• What they do: Handle routine tasks and react to immediate, daily issues.
• BI needs: Real-time metrics, live status updates, and simple operational dashboards.
Analysts and Power Users
• Who they are: Data analysts, business controllers, and BI specialists.
• What they do: Dig deep into complex data to find hidden trends, patterns, and answers.
• BI needs: Advanced query tools, data mining software, and deep-dive reporting systems.
Advantages and Disadvantages of BI
Key Advantages of BI
• Better Decisions: Managers use real-time data instead of guessing to run the business.
• Time Saving: Automated reports and shared dashboards cut down manual work and
speed up tasks.
• Spot Issues Fast: Clear charts show operational bottlenecks and process errors before
they get worse.
• Clear Goals: Teams can track performance metrics easily and align with company targets.

Key Disadvantages of BI
• High Costs: Software licenses, server setup, and maintenance can be very expensive.
• Hard to Learn: Staff often need special training to use advanced features and master
complex query languages.
• Data Mess: If input data is flawed or incomplete, the final reports will also be wrong.
• User Resistance: Employees may stick to old habits and refuse to change how they work.
OLAP
• OLAP (Online Analytical Processing) is a core software technology in Business Intelligence (BI).
• The primary purpose of Online Analytical Processing (OLAP) is to enable business analysts,
managers and executives to perform rapid, interactive and multi-dimensional analysis of large
volumes of consolidated data.
• It allows users to gain insights into business performance by viewing data from multiple
perspectives (such as time, geography, product, and sales channels) to support informed,
strategic decision-making.
• The OLAP Cube, Measures, and Dimensions
• Rather than reading traditional, flat two-dimensional tables with rows and columns, OLAP
technology organizes data into a multidimensional structure called an OLAP Cube. This structure
allows analysts to model business performance across multiple variables simultaneously.
• Measures: The numerical metrics being tracked, such as total revenue, profit margins, unit costs,
or inventory levels.
• Dimensions: The categories or perspectives used to break down the measures, such as time,
geography, product lines, or customer segments.
• Hierarchies: Nested sub-categories within dimensions
(e.g., Year → Quarter → Month → Day) that let users view data at different granularities
Core OLAP Operations
• OLAP empowers business users to interactively manipulate cubes to
discover trends without needing complex coding or database administrator
assistance:
• Roll-Up: Aggregates data by climbing up a hierarchy
(e.g., viewing sales by Country instead of individual Cities).
• Drill-Down: Breaks down high-level summaries into finer details
(e.g., expanding Annual Profit to look at Monthly Profit).
• Slice: Picks a single dimension to view a specific cross-section of data
(e.g., pulling data for only the Year 2026).
• Dice: Filters multiple dimensions to define a smaller sub-cube
(e.g., examining Electronics sold in Europe during Q1).
• Pivot: Rotates the data axes on the screen to view the same data block
from a different angle
 OLAP - CUBES
• Unlike relational databases, OLAP tools do not store individual transaction
records in two-dimensional, row-by-column format, like a worksheet, but instead
use multidimensional database structures—known as Cubes in OLAP
terminology—to store arrays of consolidated information.
• The data and formulas are stored in an optimized multidimensional database,
while views of the data are created on demand.
• Drill down allows a user to move from high-level data (e.g., annual
sales) to a lower level (e.g., monthly sales
• So, in the ―time dimension, we can move down from yearly figures
to weekly or even daily records.
• Roll up is the opposite of drill down, as it basically lifts the data in
hierarchy levels. Both operations either make the data more or less
detailed, or add/remove dimensions for the analysis.
• Slice operations help you divide a certain dimension into a separate
table (one dimension view). ―Slice can detach, say, the city‘s
dimension from the rest of the cube, which ‖ will create a separate
spreadsheet. This way we can analyze low-level information in the
isolated environment
• Dice provides the same separation functionality, but allows you to
choose more than one dimension, producing a separate cube
• Pivot is a similar operation to create pivot tables in Excel. This
function allows us to rotate a cube to get a different representation of
data in between the dimensions.
Storage Architectures & Modern Tools
• Depending on how business intelligence teams choose to store data,
OLAP generally falls into one of three structural types:
• MOLAP (Multidimensional OLAP): Relies on pre-calculated data
arrays stored in actual physical cubes, resulting in exceptionally fast
response times.
• ROLAP (Relational OLAP): Computes analytical queries dynamically
against standard relational databases using optimized SQL engines.
• HOLAP (Hybrid OLAP): Combines both, utilizing pre-computed cubes
for summary data and relational tables for granular detail tracking
OLAP vs OLTP
Common analytic functions in SQL
• Analytic functions always use the OVER() clause, which defines the
"window" or subset of data the function looks at.
• 1. Value Access Functions (LEAD & LAG)
• These functions allow you to look ahead or look behind to pull values
from other rows without doing a self-join. They are perfect for period-
over-period growth calculations.
• LAG(): Fetches a value from a previous row.
• LEAD(): Fetches a value from a subsequent row.
• 2. Ranking Functions (ROW_NUMBER, RANK, & DENSE_RANK)
• These assign a sequential integer to rows based on their ordering
within a partition.
• They handle ties differently.
• ROW_NUMBER(): Assigns a unique, strict sequential number (1, 2, 3,
4).
• RANK(): Assigns the same rank to ties, but skips the next numbers (1,
2, 2, 4).
• DENSE_RANK(): Assigns the same rank to ties without skipping
numbers (1, 2, 2, 3).
• 3. Aggregate Windows (SUM & AVG)
• Using standard aggregate functions with an OVER() clause turns them
into running calculations.

• SUM() OVER(): Calculates a running total or cumulative sum.

• AVG() OVER(): Calculates a moving or running average.
[Link] Question
1 Define Data Warehouse and list the components of DW.
2 Differentiate between a Data Warehouse and a Data Mart.
3 What is meant by Knowledge Management (KM)?
4 Draw the Lifecycle of KM and explain.
5 List the types of decisions and explain.
6 State the four phases of Herbert Simon's decision-making process.
7 Define Decision Support System (DSS).
8 Differentiate between the types of DSS.
9 Define the term "Business Intelligence.“ and the types of BI users.
10 Explain the primary purpose of Online Analytical Processing (OLAP).
11 Illustrate the core operations in OLAP with a brief scenario.
12 What is the purpose of analytic functions in SQL/databases?
13 Discuss few analytic functions.