INSY 2299 · Information Systems

Session 6: Business Intelligence & Pivot Tables

Introduction to BI · Multidimensional data cubes · Pivot table practice

Dominik Molitor · Fall 2026 | Session 6

INSY 2299 · Session 6Fordham University
Session 6

Agenda

Click a line to jump straight to it.

INSY 2299 · Session 6Fordham University
Data and Decision Making

What is business intelligence?

Business intelligence (BI)

A term combining aspects of reporting, data exploration, and ad hoc queries*, and sophisticated data modeling and analysis.

→ BI helps to turn raw data into information (and knowledge).

Example: AutoZone · Which region should we investigate for problems with immediate parts availability?

January 2026 · one row = region × product category · counts are part units

* Will be defined in a later session

INSY 2299 · Session 6Fordham University
Data and Decision Making

Firms such as Facebook, Netflix, and Amazon leverage data to…

Three toy models of what the data does. Illustrative numbers, not company data.

1Personalize services

Hours watched last month

Rows on Ana’s home screen

    2Rank ads in the news feed

    One ad slot in the feed. Three advertisers bid per click. Which ad does the viewer see?

    3Optimize operations & supply chains

    How many units should a store stock? Demand is 60–140 units, equally likely; price $10, cost $4, leftovers are lost.

    INSY 2299 · Session 6Fordham University
    Business Intelligence Infrastructure

    Business intelligence infrastructure

    Two main tasks

    1. How to analyze data to make better informed decisions This semester

    2. How to organize the storage and processing of data and data sources Next semester

    Key components

    Data Warehousing: centralized storage of data

    Data Mining / Machine Learning: extracting patterns from large datasets

    Reporting & Dashboards: presenting data insights through visual tools

    The next slide shows where each piece sits — and which part we work on today.

    INSY 2299 · Session 6Fordham University
    Business Intelligence Infrastructure

    From data sources to decisions

    Data analysis part
    Business Intelligence
    Presentations
    Today’s focus!Dashboards
    Decision Support
    Data Cube (OLAP)
    Data management part
    Data Warehouse
    Data Mart
    Data Mart
    Data Mart
    ↑↑↑
    Data Warehouse
    Meta-Data
    Data Integration
    ↑ Extraction, Transformation, Loading (ETL) ↑
    Data sources
    Legacy Systems
    OLTP-Systems
    EDI, Web Services

    OLAP: Online Analytical Processing · OLTP: Online Transaction Processing

    INSY 2299 · Session 6Fordham University
    Business Analytics

    Three different types of business analytics (in Excel)

    Last two weeks were predictive and prescriptive. This week moves back to the left.

    Descriptive This week’s focus

    Analyzes historical data to understand past business performance (e.g., describe sales from last three years).

    Predictive

    Uses (statistical) techniques to forecast future outcomes (e.g., predict next year’s sales based on historical data).

    Prescriptive

    Recommends actions to optimize business outcomes (e.g., find the profit-maximizing price for a new product).

    INSY 2299 · Session 6Fordham University
    Data and Decision Making

    Examples for descriptive reporting / data exploration

    Retailers such as Walmart, Target, Amazon ask:

    Pick a question to see the formulas that answer it · ▾ filters and sorts · double-click Units or Unit Price to edit

    XRetail_Sales_H1-2026.xlsxIllustrative data
    A2
    fx
    Ready
    INSY 2299 · Session 6Fordham University
    Multidimensional Data Cubes

    Multidimensional data cubes – the concept behind pivot tables

    One small cube — sales of four products, in three locations, over three years — and the three things you can do to it. Every pivot table you build is one of these moves.

    INSY 2299 · Session 6Fordham University
    Multidimensional Data Cubes

    Multidimensional data cubes – the concept behind pivot tables

    Definition

    • Data Cube: stores data in multiple dimensions for fast analysis
    • Pivot Table: tool in Excel* that lets you explore and summarize cube data interactively

    Key features

    • Dimensions: categories such as time, region, product
    • Measures: quantitative values to analyze, e.g., sales, quantity

    How they connect

    • Data cubes organize the information
    • Pivot tables let users pivot dimensions into rows, columns, or filters to view it differently

    Benefits

    • Speed: pre-aggregated data for quick analysis
    • Flexibility: explore from different perspectives and granularities
    • Insight: discover patterns and trends in large datasets

    * And related tools

    INSY 2299 · Session 6Fordham University
    Multidimensional Data Cubes

    A cube is a table with one column per dimension

    2014 (front)20152016 (back)

    Point at a row of the list — its cell lights up in the cube (dashed = hidden behind the front layer).

    Dimensions: Locations, Products, Years
    Measure: Sales

    Three cells of the cube are empty, so the list has no row for them: the data holds 33 rows, not 3 × 4 × 3 = 36.

    INSY 2299 · Session 6Fordham University
    Multidimensional Data Cubes

    Three dimensions, one layer at a time

    People are much better at perceiving data in 2 dimensions.

    Our goal is to reduce the number of dimensions!

    INSY 2299 · Session 6Fordham University
    Multidimensional Data Cubes

    One way to get rid of a dimension: slice along it

    Slice along

    If we sliced along the Products dimension, how many panes would we get?
    INSY 2299 · Session 6Fordham University
    Multidimensional Data Cubes

    Another way: pretend the dimension doesn’t exist

    Collapse
    Combine the panes with
    =
    INSY 2299 · Session 6Fordham University
    Multidimensional Data Cubes

    Slicing & dicing the cube in a pivot table

    Cells of the cube that feed the table (point at a number to see which):

    PivotTable Fields 33 rows: Location, Sales, Year, Product

    Σ Values: Sales
    Grand totals
    INSY 2299 · Session 6Fordham University
    Multidimensional Data Cubes

    Real cubes have many more dimensions

    Dimensions in this cube: 3

    Locations · Products · Years

    Other dimensions

    • Online, Wholesale, Retail
    • Products & Services
    • Product Types
    • Salespeople
    • Cities
    • Months
    INSY 2299 · Session 6Fordham University
    Pivot Table Practice

    Go to Blackboard / Datasets and download the Pivot Table file

    Datasets

    • Sales Forecasting
    • Lookup Example
    • Simulation
    • Pivot Table Practice File

    Pivot Table

    PRACTICE

    Open it beside the deck. Insert › PivotTable, then drag fields into Rows, Columns, Filters and Values.

    INSY 2299 · Session 6Fordham University
    Pivot Table Practice

    Pivot tables in action (1/2): the data

    → Go to Blackboard and download “Pivot Table Practice File”

    Example: sales data (in dollars) organized by

    INSY 2299 · Session 6Fordham University
    Pivot Table Practice

    Pivot tables in action (2/2): run the following five analyses

      → If it helps your analysis, you can also insert a PivotChart.  ·  Check the exact column headers in the file.

      INSY 2299 · Session 6Fordham University
      Assignment & Logistics

      Reminder & assignments

      Reminder

      • Tutorial Module #7 is due today (day of class), 11 PM.
      • Pivot Table Assignment is due next week (day of class), 11 PM.

      Final exam date

      • Wednesday, December 16, 4 pm