Data Mining: Sources and ETL

• Data mining is the process of analyzing large datasets to create new information that can be used to make unstructured business decisions and solve unstructured business problems.
• It is a process that includes the collection, aggregation, and often the visualization of large amounts of data to search for trends and ideas or to predict specific outcomes.

Data Sources

• Where data originates for data mining and analytics projects.
• A lot of data comes from operational databases.
• Company research (surveys, consulting firm findings), data from supply chain partners, and purchased access to third-party databases are also important data sources.

ETL (Extract-Transform-Load)

• The process of copying data from operational databases and other sources (extract), summarizing and cleaning the data (transform), and then inserting it into a new database, such as a data warehouse (load).

Transform step in ETL

• Includes significant data cleaning which aims to improve the quality of the data.
• Includes ensuring data is in the correct format (numeric vs. text), summarizing data for speed, or transforming data into a normal probability.
• Also involves filling in missing data or correcting inaccuracies using external databases.

ETL frequency

• Depends on the value of having timely data versus the cost of running the process.

Data Warehouse

• A separate database where transformed data is loaded and is optimized for ad hoc data analyses.
• It also relies on a relational database, but it uses different rules and schemas (e.g., star schema) than operational databases.
• They are used because it's not ideal to perform complex analyses directly on the operational database.

Data Lake

• Store rough or raw copies of data from all sources with very little transformation.
• They are viable because presentation tools can now handle data in almost any format.
• Organizations may use a data warehouse, a data lake, or a combination of both.

Predictive Analytics

• Involves procedures designed to predict the most likely future outcomes based on historical data.
• Predictive techniques are generally more valuable and complicated than descriptive techniques.

Clustering (Detecting categories)

• The process of grouping records in a database into clusters of related records based on their attributes
• Statistical algorithms can identify these clusters even with a high number of variables.

Key influencer analysis

• Measures the correlation (predictive potential) of a set of features (independent variables) on a label variable (dependent variable).
• Various statistical formulas are used for these predictions, such as Ordinary Least Squares Regression, Naive Bayes, Decision Trees, Neural Networks, and Support Vector Machines.

Forecasting

• The process of predicting future values over interval time periods based on known, measured values of the same interval periods.
• It always has a standard time period and is charted over time.
• Common measures forecasted include sales revenues, profit, costs, and market demand.
• ARMA and ARIMA formulas are commonly used.

Recommendation analysis (or market basket analysis)

• Analysis predicts consumer shopping patterns by examining products grouped together in the past to suggest related items a customer might want to purchase.
• The statistical technique used is called association analysis.

Descriptive analytics

• Includes procedures designed for reporting, analyzing, and monitoring data in ways that describe the past and immediate present state of business processes.
• Most tools and techniques referred to as data mining, data analytics, or BI are descriptive.

Goal of descriptive analytics

• Summarize large amounts of data into Key Performance Indicators (KPIs) or ad hoc measures.

Key Performance Indicators (KPIs)

• Planned measures carefully determined to indicate an organization's performance on a particular business process.
• They are considered crucial to a business's success and are often displayed in real-time on a dashboard.
• Dashboards can include KPIs, data descriptions, and even some predictive elements.

Characteristics of good performance measures

• Simple: Easy to understand and communicate.
• Easily obtainable: Easy to access and collect with minimal burden or cost.
• Precisely definable: Leaving little room for ambiguity in interpretation.
• Objective: Minimizing personal bias and subjectivity.
• Robust: Not causing large swings in values due to small external changes.
• Valid: Truly capturing what it is intended to measure.

Primary data

• Data generated from randomized experiments with treatments and is the only way to truly establish causality.

Operational (transactional) database

• Keeps track of every piece of data necessary for business processes in a non-redundant (or normalized) relational database.
• It needs to be fast and accurate for the business to run efficiently and is updated frequently to reflect ongoing transactions.

Analytical database (or data warehouse)

• Where recently transformed data is loaded.
• It is a relational database optimized for ad hoc data analyses and uses different schemas than operational databases.
• Data in an analytical database is typically updated less frequently than in an operational database, often through the ETL process.

Null Hypothesis (H0)

• The assertion that there is no difference.
• It's a premise or assertion that requires testing.
• In a formalized hypothesis test, it is one of two opposing assertions.
• Example: the null hypothesis might be that there is no difference in how well a product sells to educated and uneducated consumers.

Alternative Hypothesis (HA)

• The claim to be tested that is logically contradictory to the null hypothesis.
• States that there is a difference.
• Using the same example, the alternative hypothesis would be that the product sells better to educated consumers than to uneducated consumers.
• Scientists set up experiments to collect evidence to reject the null hypothesis and support the alternative hypothesis.

P-value

• When comparing pairs of groups with a t-test, the t-test produces a p-value indicating whether the difference between a pair of groups is due to chance (p > 0.05) or due to a real difference between the two categories (p < 0.05).
• In the context of ANOVA, a p-value associated with the F-statistic indicates whether there is a statistically significant difference among all of the categorical values.
• A statistical difference or relationship is often defined as having a p-value below 0.05.
• This p-value can be thought of as the probability that if a similar number of samples is collected again from the same population, the one-way ANOVA will produce a different F score.
• If this probability is low (p < 0.05), we can rely on the F score calculated, making the difference "statistically significant".

Dependent Variable

• Something that you want to predict or explain because it represents a valuable outcome of interest.
• Examples include customer purchase decisions and patient health status.
• In data science, this is also referred to as the label.
• In the insurance dataset example, the "charges" column is the label (dependent variable) because that's what the company wants to predict.

Independent Variable

• One that may explain or predict a dependent variable.
• Examples include personal factors like income, gender, and age, or product factors like color, cost, and functionality, which might explain customer purchase decisions.
• In data science, an independent variable is similar to a feature, which is a data field used to explain or predict the label.
• The remaining columns in the insurance dataset (age, sex, BMI, region, children, smoker) are the features (independent variables) used to predict insurance charges.
• For simplicity, all variables/columns/fields can sometimes be referred to as features (including the label(s)) when referring to all of them at once.

Effect Size

• The relationship of a feature with the label.
• It is the amount of effect that each feature has on the label, measured separately.
• The appropriate test of effect size between a feature and a label depends on whether each is numeric versus categorical.

R value (Correlation Coefficient)

• The Pearson correlation coefficient (r) is a useful metric for characterizing the spread of a scatter plot and describes the degree of linear relationships between two arrays of numbers.
• Correlations vary from -1 to 1.
• Stronger correlations are those closer to -1 and 1, while values closer to 0 are weaker correlations.
• The sign indicates the direction of the relationship (positive or negative), and the absolute value indicates the strength.
• For example, a correlation of -0.5 is as strong as 0.5.
• While it doesn't prove causality, a correlation coefficient might roughly be interpreted as the likelihood of a unit change in one variable resulting in a unit change in the other.

R-squared value (Coefficient of Determination)

• Another measure of effect size.
• When analyzing two variables, R2 is literally r * r. However, when multiple x variables are analyzed, R2 is calculated differently.
• Generally, R2 represents the percent of variance in the label that is explained (i.e., predicted) by the features in the model.
• It ranges from 0 to 1, with higher values indicating a better fit of the model to the data.

Effect size statistics and visualizations for different combinations of feature and label data types:

• Numeric feature and numeric label: Effect size statistic is Pearson correlation (r), and the visualization is a scatterplot.
• Numeric feature and categorical label: Effect size statistic is one-way ANOVA (F), and the visualization is a bar chart.
• Categorical feature and categorical label: Effect size statistic is Pearson chi-square (X2), and the visualization is a CrossTab.

Pearson Correlation Coefficient (CORREL()) purpose

• Estimate the relationship between two numeric variables.
• It describes the strength and direction of the linear relationship between them.
• The CORREL() function in Excel can be used to calculate this.

Regression (Linear Regression) purpose

• Find the "line of best fit" (a.k.a. trendline, regression line) through data points in a scatter plot.
• Help to model the relationship between two numeric variables using the equation y = m(x) + b.
• It can be used to make predictions based on this linear relationship.
• Multiple linear regression (MLR), which is a linear approach to modeling the relationship between a label and two-to-many features.

ANOVA (One-way Analysis of Variance) purpose

• Test whether there is a statistically significant difference among the means of two or more groups.
• Test whether the relationship between one categorical variable and one numeric variable is statistically significant.
• It produces an F-statistic and an associated p-value.

T-Test (TTEST()) purpose

• Compare the means of two groups and calculate the probability (p-value) that differences between pairs of categorical value comparisons are statistically significant.
• The TTEST() function in Excel returns the p-value directly.

Linear Regression Line in a Scatter Plot

• In a scatter plot showing the relationship between two numeric variables, a linear regression line (also called a trendline or line of best fit) is a line that best represents the general trend of the data points.
• It is represented by the equation y = m(x) + b, where 'y' is the dependent variable, 'x' is the independent variable, 'm' is the slope, and 'b' is the y-intercept.
• The slope (m) indicates how much the y-value changes, on average, for each unit of increase on the x-axis.
• It describes both the direction and the steepness of the line.
• A positive slope means that as x increases, y tends to increase, while a negative slope means that as x increases, y tends to decrease.
• The regression line is determined by minimizing the sum of the squared residuals (the vertical distances between the data points and the line).

Purpose of Solver (Optimization via Linear Programming)

• Solve optimization problems using a quantitative tool called linear programming (LP).
• Optimization aims to find the most favorable outcome to a problem, given a set of limitations or restrictions.
• Often translates to maximizing profit and minimizing costs and waste.

What Solver can do:

• Find exact answers to problems by accurately modeling a set of constraints.
• Solve various business problems in areas like management science, supply chain, and operations.
• Determine optimal production schedules to minimize costs.
• Determine the best set of worker-job assignments to minimize costs.
• Determine the optimal mix of products in a factory to maximize profit.
• Solve materials and inventory problems using Microsoft Excel.
• At its core, Solver helps businesses make the best decisions within the limitations they face by finding the optimal values for variables that affect their goals (like profit or cost).

How Solver finds solutions:

• Defining the Problem as a Linear Programming Model: Solver requires the user to define the problem in terms of three key components:
  • Objective Function: This is the goal you are trying to achieve, expressed mathematically, what you want to maximize (e.g., profit: 0.05 * H + 0.30 * R + 0.49 * S) or minimize (e.g., cost).
  • Decision Variables: These are the quantities you can control to influence the objective function.
  • Constraints: These are the limitations or restrictions you face. They define the feasible region within which Solver must find the optimal solution. Constraints are also expressed mathematically.