idaa ch 4: master the data preparing data for analysis

data analytics process

• ask the question

• master the data

• perform the analysis

• share the story

database

• an organized collection of related data

• purpose: to store data

• ex., customers, invoices, and invoice lines

• excel is NOT an example of this

DBMS

• software that stores, retrieves and controls access to the data

• purpose: allows authorized users to search, add, update, and delete information

• ex., access, SQL server

relational database

• organizes related information

• each table stores one kind of record (but they are related)

  • ex., customer table, supplier table

row

• record

• tuple

• each one has a unique line item

column

• field, attribute, variable

• every ? describes that specific record

primary key

• uniquely identifies a record

• required and unique

• prevents you from having duplicate records

foreign key

• references a key in another table

  • but this is a primary key in a separate table..

  • links these tables

• may repeat across invoices

composite key

• a primary key made of two or more columns

• line item detail is in separate table (this allows you to have 1 line item or 500, but no blank lines)… each ivc has a diff no of line items.

• like 2 columns that come together (ivc id and item id in sales inventory table)

entity relationship diagram

• infinity sign→ each customer can have infinite ivcs

• 1<- each ivc can only have one customer

• infinity sign→ an invoice can have multiple line items

• 1← each line item must tie back to only one invoice

data integrity rules

• entity integrity

  • record identity; each row is a unique instance

  • ex., no duplicate or blank primary key

• referential integrity

  • valid relationships; foreign key must reference a real existing record

  • sales can only be made to existing customers

entity integrity

• record identity; each row is a unique instance

• ex., no duplicate or blank primary key

referential integrity

• valid relationships; foreign key must reference a real existing record

• sales can only be made to existing customers

relational databases benefits vs excel

• internal controls easier to enforce

• enhanced security around data entry and table access

• reduces redundancy

• maintains version control

blockchain

• trx initiated which is broadcast to network of computers (nodes)

• users verify and share info in real time

  • network participants (nodes) independently verify the trx using agreed upon rules

• once verified, trx is added to a block of other trxs

• block is linked to previous blocks (chronological and unchangeable)

• permanent, transparent, cannot be changed easily

• foundation of cryptocurrency

• decentralized distributed ledger, everyone can see it, so it’s difficult to manipulate.

• hashes, proof of work, time delay to prevent u from recreating the entire proof of work

NoSQL

• databases that store info differently from traditional relational databases

• flexible and adaptable

• ex., product could have different fields than other products wo requiring the database structure to change

• lets you do more analysis of unstructured data

• especially useful when need to

  • handle rapidly changing data

  • support enormous traffic

  • spread data across many serves

• consider when flexibility and large scale distribution are important

  • many companies use both ? and SQL

SQL

• structured and organized

• database - set of carefully organized spreadsheets

• every row follows same column structure and tables connect through defined relationships

• use when data is highly structured and consistency is critical

ETL: extract, transform, load

• what you do before you can even start doing analytics

• extract

  • obtain data from approved sources

• transform

  • clean, join, transform data

• load

  • place prepared data in its destination

prepping data for analysis

• ensure data quality

  • check data types match attributes

    • ex., string text, date, etc.

  • check all date data types

    • ex., differences in 07/09 vs 09/07

  • check all numerical data types

    • dollar amts have decimals, qty does not have decimals (unless it should)

    • 0 as o or 5 as s

  • check all text data types, including

    • international characters and encoding

    • words and measures

• validate data for completeness and integrity

  • record count

    • like system would time out in the middle and it wouldn’t download all the info…

  • compare descriptive analytics

    • maybe sum some your total dollar amt just to make sure it’s the same and all columns/rows transferred

  • validate date/time fields

    • make sure it does look like some number but a date

  • check string limits

    • cells may have a limit ex., 100 characters but your data has 200 characters- what to do

• data wrangling

  • remove headings and subtotals

  • remove leading zeros and nonprintable characters

  • properly format negative numbers

    • color may not work for the analysis (e.g., red with no - or parentheses)

  • fix inconsistencies

    • abbreviations vs no abbreviations

  • address missing values

    • leave as is

    • remove records w missing values

    • add values to replace the missing ones

• perform preliminary exploratory analysis

  • are you asking the right questions? do you have the data you need to answer the q?

  • start w descriptive statistics

ensure data quality

• check data types match attributes

  • ex., string text, date, etc.

• check all date data types

  • ex., differences in 07/09 vs 09/07

• check all numerical data types

  • dollar amts have decimals, qty does not have decimals (unless it should)

  • 0 as o or 5 as s

• check all text data types, including

  • international characters and encoding

  • words and measures

validate data for completeness and integrity

• record count

  • like system would time out in the middle and it wouldn’t download all the info…

• compare descriptive analytics

  • maybe sum some your total dollar amt just to make sure it’s the same and all columns/rows transferred

• validate date/time fields

  • make sure it does look like some number but a date

• check string limits

  • cells may have a limit ex., 100 characters but your data has 200 characters- what to do

data wrangling

• remove headings and subtotals

• remove leading zeros and nonprintable characters

• properly format negative numbers

  • color may not work for the analysis (e.g., red with no - or parentheses)

• fix inconsistencies

  • abbreviations vs no abbreviations

• address missing values

  • leave as is

  • remove records w missing values

  • add values to replace the missing ones

perform preliminary exploratory analysis

• are you asking the right questions? do you have the data you need to answer the q?

  • do these numbers just seem off contextually? investigate

• start w descriptive statistics