Big Data Analysis using Excel & SQL

Week 1: Large-Scale Data Handling in Excel

• The Power Pivot Engine: Handling datasets that exceed the 1-million row limit by using Excel’s Data Model (Internal VertiPaq engine).

• Advanced Data Cleaning (Power Query): Automating ETL (Extract, Transform, Load) processes; merging multiple large CSVs and folder-level data imports.

• DAX Foundations for Excel: Writing “Data Analysis Expressions” for complex measures like Year-to-Date (YTD) totals and Distinct Counts across millions of rows.

• Big Data Visualization: Creating high-performance dashboards using PivotCharts, Slicers, and Timelines that remain responsive with large underlying data.

• Statistical Modeling: Using the Solver Add-in and “What-If” Analysis (Goal Seek, Data Tables) to perform predictive modeling on historical trends.

Week 2: High-Volume Querying with SQL

• Database Optimization: Understanding Indexes (Clustered vs. Non-Clustered) and Execution Plans to speed up queries on tables with millions of records.

• Complex Joins & Set Operations: Handling “Data Silos” by merging massive tables using JOIN, UNION ALL, and INTERSECT.

• Analytical Window Functions: Performing advanced ranking, cumulative sums, and moving averages using OVER(PARTITION BY...) without collapsing rows.

• Data Aggregation for Big Data: Using GROUP BY with ROLLUP and CUBE for multi-dimensional reporting; managing subqueries vs. CTEs for query readability.

• Big Data Integration: Exporting processed SQL datasets back into Excel via ODBC/OLEDB connectors for a seamless “SQL-to-Excel” analytical pipeline.

• Capstone Project: Analyze a “Million-Row” retail dataset—querying the trends in SQL and building a dynamic executive summary dashboard in Excel.