Business Analytics: Data Reduction Techniques Using Excel and R

Explore data reduction techniques from machine learning and how to integrate your methods in Excel, R, and Power BI.

Introduction

• Use data reduction for valuable insights
• What you should know
• Introducing the course project
• Configuring Excel Solver Add-in
• Working with R
• Configuring R in Power BI

1. Working with Large Datasets

• AI and machine learning
• Numerosity
• Dimensionality
• Aggregating or grouping data
• Histograms
• Binning
• Correlation and covariance
• Challenge: Getting the data
• Solution: Getting the data

2. Clustering

• Calculating distances
• Hierarchical clustering
• Heatmaps and dendrograms
• K-means clustering in one dimension
• K-means clustering in two dimensions
• Determining k
• Challenge: Clustering
• Solution: Clustering

3. PCA

• Visualizing PCA
• Using Excel Solver to find solutions
• Solving for principal components axes
• Eigenvalues
• Eigenvectors
• PCA projection space
• Scree plot
• Challenge: PCA
• Solution: PCA

4. Selecting Dimensions

• Analyzing potential model dimensions
• Removing or replacing null values

5. Power BI and R

• Setting up R in Power Query Editor
• Creating custom code with R standard visual
• Challenge: Power BI
• Solution: Power BI

Conclusion

• Next steps