Beginning Big Data with Power BI and Excel 2013 (eBook)

Neil Dunlop is Professor of Business and Computer Information Systems, Berkeley City College, Berkeley, CA. The author has served as chair of the Business and Computer Information Systems Departments for many years. He has over thirty-five years experience as a computer programmer and software designer and is the author of three books on database management. He is listed in Marquis Who’s Who in America and Who’s Who in the World.

Contents at a Glance 4
Contents 5
About the Author 12
About the Technical Reviewer 13
Acknowledgments 14
Introduction 15
Chapter 1: Big Data 16
Big Data As the Fourth Factor of Production 16
Big Data As Natural Resource 16
Data As Middle Manager 17
Early Data Analysis 17
First Time Line 17
First Bar Chart and Time Series 18
Cholera Map 18
Modern Data Analytics 19
Google Flu Trends 19
Google Earth 20
Tracking Malaria 20
Big Data Cost Savings 20
Big Data and Governments 20
Predictive Policing 20
A Cost-Saving Success Story 21
Internet of Things or Industrial Internet 21
Cutting Energy Costs at MIT 21
The Big Data Revolution and Health Care 21
The Medicalized Smartphone 22
Improving Reliability of Industrial Equipment 23
Big Data and Agriculture 23
Cheap Storage 23
Personal Computers and the Cost of Storage 23
Review of File Sizes 23
Data Keeps Expanding 24
Relational Databases 24
Normalization 24
Database Software for Personal Computers 25
The Birth of Big Data and NoSQL 26
Hadoop Distributed File System (HDFS) 26
Big Data 26
The Three V’s 27
The Data Life Cycle 27
Apache Hadoop 27
MapReduce Algorithm 27
Hadoop Distributed File System (HDFS) 28
Commercial Implementations of Hadoop 28
CAP Theorem 28
NoSQL 28
Characteristics of NoSQL Data 28
Implementations of NoSQL 29
Spark 29
Microsoft Self-Service BI 29
Summary 29
Chapter 2: Excel As Database and Data Aggregator 30
From Spreadsheet to Database 30
Interpreting File Extensions 31
Using Excel As a Database 31
Importing from Other Formats 33
Opening Text Files in Excel 33
Importing Data from XML 34
Importing XML with Attributes 35
Importing JSON Format 37
Using the Data Tab to Import Data 38
Importing Data from Tables on a Web Site 38
Data Wrangling and Data Scrubbing 40
Correcting Capitalization 40
Splitting Delimited Fields 41
Splitting Complex, Delimited Fields 44
Removing Duplicates 45
Input Validation 46
Working with Data Forms 47
Selecting Records 49
Summary 49
Chapter 3: Pivot Tables and Pivot Charts 50
Recommended Pivot Tables in Excel 2013 50
Defining a Pivot Table 51
Defining Questions 52
Creating a Pivot Table 52
Changing the Pivot Table 54
Creating a Breakdown of Sales by Salesperson for Each Day 55
Showing Sales by Month 56
Creating a Pivot Chart 57
Adjusting Subtotals and Grand Totals 58
Analyzing Sales by Day of Week 58
Creating a Pivot Chart of Sales by Day of Week 60
Using Slicers 62
Adding a Time Line 63
Importing Pivot Table Data from the Azure Marketplace 64
Summary 69
Chapter 4: Building a Data Model 70
Enabling PowerPivot 70
Relational Databases 72
Database Terminology 72
Creating a Data Model from Excel Tables 73
Loading Data Directly into the Data Model 77
Creating a Pivot Table from Two Tables 81
Creating a Pivot Table from Multiple Tables 82
Adding Calculated Columns 85
Adding Calculated Fields to the Data Model 87
Summary 89
Chapter 5: Using SQL in Excel 91
History of SQL 91
NoSQL 91
NewSQL 91
SQL++ 92
SQL Syntax 92
SQL Aggregate Functions 93
Subtotals 93
Joining Tables 94
Importing an External Database 94
Specifying a JOIN Condition and Selected Fields 100
Using SQL to Extract Summary Statistics 103
Generating a Report of Total Order Value by Employee 105
Using MSQuery 108
Summary 112
Chapter 6: Designing Reports with Power View 113
Elements of the Power View Design Screen 113
Considerations When Using Power View 114
Types of Fields 114
Understanding How Data Is Summarized 114
A Single Table Example 115
Viewing the Data in Different Ways 118
Creating a Bar Chart for a Single Year 119
Column Chart 120
Displaying Multiple Years 121
Adding a Map 122
Using Tiles 123
Relational Example 125
Customer and City Example 129
Showing Orders by Employee 134
Aggregating Orders by Product 136
Summary 140
Chapter 7: Calculating with Data Analysis Expressions (DAX) 141
Understanding Data Analysis Expressions 141
DAX Operators 142
Summary of Key DAX Functions Used in This Chapter 142
Updating Formula Results 142
Creating Measures or Calculated Fields 144
Analyzing Profitability 146
Using the SUMX Function 149
Using the CALCULATE Function 150
Calculating the Store Sales for 2009 152
Creating a KPI for Profitability 154
Creating a Pivot Table Showing Profitability by Product Line 156
Summary 158
Chapter 8: Power Query 159
Installing Power Query 159
Key Options on Power Query Ribbon 160
Working with the Query Editor 160
Key Options on the Query Editor Home Ribbon 161
A Simple Population 163
Performance of S& P 500 Stock Index
Importing CSV Files from a Folder 169
Group By 174
Importing JSON 176
Summary 186
Chapter 9: Power Map 187
Installing Power Map 187
Plotting a Map 187
Key Power Map Ribbon Options 188
Troubleshooting 189
Plotting Multiple Statistics 194
Adding a 2D Chart 198
Showing Two or More Values 205
Creating a 2D Chart 207
Summary 215
Chapter 10: Statistical Calculations 216
Recommended Analytical Tools in 2013 216
Customizing the Status Bar 218
Inferential Statistics 219
Review of Descriptive Statistics 219
Calculating Descriptive Statistics 220
Measures of Dispersion 220
Excel Statistical Functions 221
Charting Data 221
Excel Analysis ToolPak 221
Enabling the Excel Analysis ToolPak 221
A Simple Example 223
Other Analysis ToolPak Functions 227
Using a Pivot Table to Create a Histogram 227
Scatter Chart 232
Summary 237
Chapter 11: HDInsight 238
Getting a Free Azure Account 238
Importing Hadoop Files into Power Query 239
Creating an Azure Storage Account 239
Provisioning a Hadoop Cluster 242
Importing into Excel 247
Creating a Pivot Table 251
Creating a Map in Power Map 252
Summary 254
Index 255