Week 2
Data in Business
- Instructors
- Maclean Gaulin
Agenda
- What is data?
- How do firms handle data?
- Data types & formats
Data in 2025
- There’s a lot of it
- The 5 Vs:
- Volume
- Velocity
- Variety
- Veracity
- Value
Key concept
Modern business data is characterized by five distinct dimensions that drive complexity and value.
Firm Generated Data
- Accounting Information System
- Records, processes, and reports accounting data
- Supply Chain Management system
- Vendor, order, price and demand information
- Customer Relationship Management system
- Existing and potential customer information, sales
- Human Resource Management system
- Information and interactions with employees
- Enterprise Resource Planning
- Umbrella often encompassing all the above
Key concept
The Enterprise Resource Planning (ERP) system acts as the central database of a firm's operational footprint.
External Data
- Bank and payment data
- Customer data, leads systems
- Market data, demand estimation, competitor info
- Economic indicators, sentiment, risk, etc.
- Regulations and tax code/schedules
Key concept
External data provides critical context (market, regulatory, macro) beyond the boundary of the firm.
Industrial Scale Issues
- Data is harder to use the more of it there is
- Data governance and quality establishes policies to ensure data remains usable
- Security and privacy protects sensitive information and ensures compliance with regulations
- Cost grows with amount of data stored & processed
- ROI can be hard to quantify and justify
- Integration and automation are imperative for ROI
Key concept
As data volume grows, firms face mounting governance, security, and cost pressures.
Move to the (Private) cloud
- Only pay for storage (bits) and usage (CPU cycles)
- Server hardware maintained by third party
- Shifting CapEx to OpEx with EBITDA impact
- Provider shares resources amongst clients (or not)
- Server software maintained by third party
- Firm avoids dealing with software updates, fixes, etc.
- Customization often more difficult (platform lock-in)
- Leverage external analytics expertise
Key concept
Cloud models replace fixed physical assets with rented, shared server capacity on demand.
Cloud Providers
- Generic providers of cloud servers and software
- Amazon Web Services (AWS), Microsoft Azure, Google Cloud Platform (GCP)
- Business focused providers
- Oracle, IBM,
- ERP / Accounting specific providers
- SAP, Microsoft, Sage, Quickbooks, Freshbooks, Xero
Key concept
The cloud market is split between general infrastructure giants and business or accounting SaaS tools.
Cautions and caveats
- Cloud systems solve problems (tech assets, access, scale) but create others (security, complexity)
- ROI on moving to the cloud is unclear
- 66% of execs say cloud hasn’t lowered IT cost (KPMG 2022)
- Resource management is difficult (did you shut down that instance, or leave it running overnight?)
- Can create audit-trail concerns (right to audit)
Key concept
Cloud environments resolve physical resource limits but introduce billing volatility and right-to-audit issues.
How do firms store data?
- Excel: still a workhorse, also called “flat file”
- Access by opening file
- Limited to one user at a time
- Database: structured storage of data
- Relational, key-value, hierarchical, graph, network
- Access via specific software
- Can handle multiple users
Key concept
Relational databases replace individual flat files to manage millions of rows and avoid duplication.
Big Data Storage
- Data Lake: centralized hub of unstructured data
- Stores data in “raw” unprocessed format (as-is)
- Because storage is “cheap,” firms now often keep all data in its raw form, and process later when needed
- Data Warehouse: centralized hub of structured data
- Stores pre-processed data (schema-on-write)
- Governance and quality controls ensure datasets are properly formatted, normalized, ready to use
Key concept
Data lakes store all raw records immediately; warehouses structure and clean data before it is written.
Internal Data Structure
- Internal systems arise organically
- This often means multiple different siloed systems
- Multiple internal systems need translation layer to talk together or be merged cleanly
Key concept
Siloed systems arise organically in business and require translation layers to integrate cleanly.
Data Marts
- Data marts are a customized view of the data, often with combining, merging and aggregation already done behind the scenes
- Effectively a nice filter on messier data
- Also limits access to full data for security & ICFR
Key concept
Data marts simplify querying by pre-aggregating raw records and restricting database access.
Common data interaction – hands off
- Ask for some data product
- Dashboard
- Automatic periodically updated report
- Excel file
- Review preliminary product or mockups
- This step is often a few back-and-forths
- Get data product
Key concept
Hands-off data consumption relies on pre-built reports and dashboards, offering high control but low flexibility.
Common Data Interaction – hands on
- Ask for data
- Wait (a short to unacceptably long amount of time)
- Get data (hope it’s good, if not goto Step 1)
- Create report in Excel
- Deliver Excel file
Key concept
Typical business users extract raw tabular data to run manual manipulations in Excel.
A more DIY data interaction
- Ask for data access
- Write query to extract data from Data Mart
- Tableau allows custom SQL or graphical query
- PowerQuery in Excel builds query step by step
- Do basic manipulation or analysis
- Pivot & summarize data
- Create visualizations
- Deliver dashboard or Excel file
Key concept
Semi-DIY users write queries inside extraction tools to pull clean data directly from Data Marts.
A Very DIY data interaction
- Get a task to produce some result
- Determine what data you need
- Request access (use existing access) to various systems and databases
- Larn about what’s available, explore data
- Discard some, add others
- Get data, clean, merge, analyze, visualize
- Deliver requested result
Key concept
Advanced analysts determine their own data needs, request database access, and build the analytical pipeline.
Types of data
- Structured data – fixed, consistent format
- Unstructured data – everything else
- Semi-structured data – in-between the two, providing structured flexibility
Key concept
Data falls along a spectrum of organization, from rigid tables to free-form text.
Unstructured Data
- Text, images, etc.
- Structure is missing, not-consistent, or complex
- 80-90% of collected data is unstructured
- Needs processing before it can be used
- AI is making processing this data far easier
- Examples:
- Invoices, receipts
- Regulations, FAQs, instructions, guidance
Key concept
Unstructured data contains the narrative details of business, representing 80-90% of all data generated.
Structured Data
- Think of a spreadsheet with columns (variables) and rows (observations)
- Often quantitative, easily processed for analytics
- Can be broken into multiple, related tables (RDB)
- Examples:
- General ledger
- Chart of accounts
Key concept
Structured data is the foundation of traditional accounting systems, enabling automated calculations.
Wide vs Long
| ID | First Name | Last Name | # Sales |
|---|---|---|---|
| 1 | Aryll | Zelda | 394 |
| 2 | Byrne | Yunobo | 604 |
| 3 | Cia | Xani | 12 |
| ID | Variable | Value |
|---|---|---|
| 1 | First Name | Aryll |
| 1 | Last Name | Zelda |
| 1 | # Sales | 394 |
| 2 | First Name | Byrne |
| 2 | Last Name | Yunobo |
| 2 | # Sales | 604 |
| 3 | First Name | Cia |
| 3 | Last Name | Xani |
| 3 | # Sales | 12 |
- Wide format: add data in columns — each column is a different variable
- Long format: add data in rows — repeat IDs, columns for variable and value
Key concept
Wide format is optimal for human reporting, while long format is designed for computer processing.
Sparse Data
- General Ledger entries only affect one account
- Multiple if were considering a full transaction
- DR 1000 Cash $5
- CR 4000 Revenue $5
- Should GL be stored in wide or long format?
- Sparse data has missing values in wide format
Wide format
- Easier for humans to interact with
- Good for “dense” data with little missing column data
- “Matrix” format often required for analytical models
- Adding new column applies to every observation
- Adding “dimension” is costly (e.g. ID ID & time)
- Can lead to many columns, inefficient storage
Key concept
Wide formats are intuitive for spreadsheets but scale poorly as new dimensions are added.
Long format
- Easier for analytical tasks like aggregation
- Allows for multiple groups (e.g., customer, date)
- Often more efficient storage & relations
- Harder for humans to interact with
- Requires manipulation (pivot!) for some analyses
- Less effective for dense data (no missing values)
Key concept
Long formats are standard for databases, facilitating scalable aggregation and multi-dimensional analysis.
Long to Wide – Pivot Tables
| ID | Variable | Value |
|---|---|---|
| 1 | First Name | Aryll |
| 1 | Last Name | Zelda |
| 1 | # Sales | 394 |
| 2 | First Name | Byrne |
| 2 | Last Name | Yunobo |
| 2 | # Sales | 604 |
| 3 | First Name | Cia |
| 3 | Last Name | Xani |
| 3 | # Sales | 12 |
| ID | First Name | Last Name | # Sales |
|---|---|---|---|
| 1 | Aryll | Zelda | 394 |
| 2 | Byrne | Yunobo | 604 |
| 3 | Cia | Xani | 12 |
- Each distinct value of
Variablebecomes its own column -
IDbecomes the row key — one row per unique ID -
Valuefills the cells where the ID and the new column meet
Key concept
A pivot moves values out of rows and into columns, one column per distinct variable.
Pivot Table Aggregation
- When transforming from multiple rows to a column, data is often aggregated (e.g. sum of amounts)
- Pivot general ledger to sum entries by account
- Aggregation on multiple keys
- Pivot general ledger to sum by account and month
- Pivot table is a specific “format” of aggregation
- Group by: for all rows that match the group, do some calculation (sum, average, count, etc.)
Key concept
Pivoting many rows into one column forces a choice of aggregation — sum, average, or count.
Key Takeaways
Key concept
Analytics projects fail without a deep understanding of the business process that generated the data.