Dunlop Beginning Big Data with Power BI and Excel 2013

Big Data Processing and Analysis Using PowerBI in Excel 2013
1. Auflage 2015
ISBN: 978-1-4842-0529-7
Verlag: APRESS
Format: PDF
Kopierschutz: 1 - PDF Watermark

E-Book, Englisch, 258 Seiten, eBook

In Beginning Big Data with Power BI and Excel 2013 , you will learn to solve business problems by tapping the power of Microsoft’s Excel and Power BI to import data from NoSQL and SQL databases and other sources, create relational data models, and analyze business problems through sophisticated dashboards and data-driven maps. While Beginning Big Data with Power BI and Excel 2013 covers prominent tools such as Hadoop and the NoSQL databases, it recognizes that most small and medium-sized businesses don’t have the Big Data processing needs of a Netflix, Target, or Facebook. Instead, it shows how to import data and use the self-service analytics available in Excel with Power BI. As you’ll see through the book’s numerous case examples, these tools—which you already know how to use—can perform many of the same functions as the higher-end Apache tools many people believe are required to carry out in Big Data projects. Through instruction, insight, advice, andcase studies, Beginning Big Data with Power BI and Excel 2013 will show you how to: Import and mash up data from web pages, SQL and NoSQL databases, the Azure Marketplace and other sources.Tap into the analytical power of PivotTables and PivotCharts and develop relational data models to track trends and make predictions based on a wide range of data.Understand basic statistics and use Excel with PowerBI to do sophisticated statistical analysis—including identifying trends and correlations.Use SQL within Excel to do sophisticated queries across multiple tables, including NoSQL databases.Create complex formulas to solve real-world business problems using Data Analysis Expressions (DAX).

Zielgruppe

Popular/general

Autoren/Hrsg.

Dunlop, Neil

Inhaltsverzeichnis

1;Contents at a Glance;4
2;Contents;5
3;About the Author;12
4;About the Technical Reviewer;13
5;Acknowledgments;14
6;Introduction;15
7;Chapter 1: Big Data;16
7.1; Big Data As the Fourth Factor of Production;16
7.2; Big Data As Natural Resource;16
7.3; Data As Middle Manager;17
7.4; Early Data Analysis;17
7.4.1; First Time Line;17
7.4.2; First Bar Chart and Time Series;18
7.4.3; Cholera Map;18
7.5; Modern Data Analytics;19
7.5.1; Google Flu Trends;19
7.5.2; Google Earth;20
7.5.3; Tracking Malaria;20
7.5.4; Big Data Cost Savings;20
7.6; Big Data and Governments;20
7.6.1; Predictive Policing;20
7.6.2; A Cost-Saving Success Story;21
7.7; Internet of Things or Industrial Internet;21
7.7.1; Cutting Energy Costs at MIT;21
7.8; The Big Data Revolution and Health Care;21
7.8.1; The Medicalized Smartphone;22
7.9; Improving Reliability of Industrial Equipment;23
7.10; Big Data and Agriculture;23
7.11; Cheap Storage;23
7.11.1; Personal Computers and the Cost of Storage;23
7.11.2; Review of File Sizes;23
7.11.3; Data Keeps Expanding;24
7.12; Relational Databases;24
7.12.1; Normalization;24
7.12.2; Database Software for Personal Computers;25
7.13; The Birth of Big Data and NoSQL;26
7.13.1; Hadoop Distributed File System (HDFS);26
7.13.2; Big Data;26
7.13.3; The Three V’s;27
7.13.4; The Data Life Cycle;27
7.13.5; Apache Hadoop;27
7.13.5.1;MapReduce Algorithm;27
7.13.5.2;Hadoop Distributed File System (HDFS);28
7.13.5.3;Commercial Implementations of Hadoop;28
7.13.6; CAP Theorem;28
7.13.7; NoSQL;28
7.13.7.1;Characteristics of NoSQL Data;28
7.13.7.2;Implementations of NoSQL;29
7.13.8; Spark;29
7.14; Microsoft Self-Service BI;29
7.15; Summary;29
8;Chapter 2: Excel As Database and Data Aggregator;30
8.1; From Spreadsheet to Database;30
8.2; Interpreting File Extensions;31
8.3; Using Excel As a Database;31
8.4; Importing from Other Formats;33
8.4.1; Opening Text Files in Excel;33
8.4.2; Importing Data from XML;34
8.4.3; Importing XML with Attributes;35
8.4.4; Importing JSON Format;37
8.5; Using the Data Tab to Import Data;38
8.5.1; Importing Data from Tables on a Web Site;38
8.6; Data Wrangling and Data Scrubbing;40
8.6.1; Correcting Capitalization;40
8.6.2; Splitting Delimited Fields;41
8.6.3; Splitting Complex, Delimited Fields;44
8.6.4; Removing Duplicates;45
8.7; Input Validation;46
8.8; Working with Data Forms;47
8.9; Selecting Records;49
8.10; Summary;49
9;Chapter 3: Pivot Tables and Pivot Charts;50
9.1; Recommended Pivot Tables in Excel 2013;50
9.2; Defining a Pivot Table;51
9.2.1; Defining Questions;52
9.2.2; Creating a Pivot Table;52
9.2.3; Changing the Pivot Table;54
9.2.4; Creating a Breakdown of Sales by Salesperson for Each Day;55
9.2.5; Showing Sales by Month;56
9.3; Creating a Pivot Chart;57
9.4; Adjusting Subtotals and Grand Totals;58
9.5; Analyzing Sales by Day of Week;58
9.6; Creating a Pivot Chart of Sales by Day of Week;60
9.7; Using Slicers;62
9.8; Adding a Time Line;63
9.9; Importing Pivot Table Data from the Azure Marketplace;64
9.10; Summary;69
10;Chapter 4: Building a Data Model;70
10.1; Enabling PowerPivot;70
10.2; Relational Databases;72
10.3; Database Terminology;72
10.4; Creating a Data Model from Excel Tables;73
10.5; Loading Data Directly into the Data Model;77
10.6; Creating a Pivot Table from Two Tables;81
10.7; Creating a Pivot Table from Multiple Tables;82
10.8; Adding Calculated Columns;85
10.9; Adding Calculated Fields to the Data Model;87
10.10; Summary;89
11;Chapter 5: Using SQL in Excel;91
11.1; History of SQL;91
11.2; NoSQL;91
11.3; NewSQL;91
11.4; SQL++;92
11.5; SQL Syntax;92
11.6; SQL Aggregate Functions;93
11.7; Subtotals;93
11.8; Joining Tables;94
11.9; Importing an External Database;94
11.10; Specifying a JOIN Condition and Selected Fields;100
11.11; Using SQL to Extract Summary Statistics;103
11.12; Generating a Report of Total Order Value by Employee;105
11.13; Using MSQuery;108
11.14; Summary;112
12;Chapter 6: Designing Reports with Power View;113
12.1; Elements of the Power View Design Screen;113
12.2; Considerations When Using Power View;114
12.3; Types of Fields;114
12.4; Understanding How Data Is Summarized;114
12.5; A Single Table Example;115
12.6; Viewing the Data in Different Ways;118
12.7; Creating a Bar Chart for a Single Year;119
12.8; Column Chart;120
12.9; Displaying Multiple Years;121
12.10; Adding a Map;122
12.11; Using Tiles;123
12.12; Relational Example;125
12.13; Customer and City Example;129
12.14; Showing Orders by Employee;134
12.15; Aggregating Orders by Product;136
12.16; Summary;140
13;Chapter 7: Calculating with Data Analysis Expressions (DAX);141
13.1; Understanding Data Analysis Expressions;141
13.1.1; DAX Operators;142
13.1.2; Summary of Key DAX Functions Used in This Chapter;142
13.2; Updating Formula Results;142
13.2.1; Creating Measures or Calculated Fields;144
13.2.2; Analyzing Profitability;146
13.3; Using the SUMX Function;149
13.4; Using the CALCULATE Function;150
13.5; Calculating the Store Sales for 2009;152
13.6; Creating a KPI for Profitability;154
13.7; Creating a Pivot Table Showing Profitability by Product Line;156
13.8; Summary;158
14;Chapter 8: Power Query;159
14.1; Installing Power Query;159
14.2; Key Options on Power Query Ribbon;160
14.3; Working with the Query Editor;160
14.3.1; Key Options on the Query Editor Home Ribbon;161
14.4; A Simple Population;163
14.5; Performance of S&P 500 Stock Index;165
14.6; Importing CSV Files from a Folder;169
14.6.1; Group By;174
14.7; Importing JSON;176
14.8; Summary;186
15;Chapter 9: Power Map;187
15.1; Installing Power Map;187
15.2; Plotting a Map;187
15.3; Key Power Map Ribbon Options;188
15.4; Troubleshooting;189
15.4.1; Plotting Multiple Statistics;194
15.4.2; Adding a 2D Chart;198
15.4.3; Showing Two or More Values;205
15.4.4; Creating a 2D Chart;207
15.5; Summary;215
16;Chapter 10: Statistical Calculations;216
16.1; Recommended Analytical Tools in 2013;216
16.2; Customizing the Status Bar;218
16.3; Inferential Statistics;219
16.4; Review of Descriptive Statistics;219
16.4.1; Calculating Descriptive Statistics;220
16.4.2; Measures of Dispersion;220
16.4.3; Excel Statistical Functions;221
16.5; Charting Data;221
16.6; Excel Analysis ToolPak;221
16.6.1; Enabling the Excel Analysis ToolPak;221
16.6.2; A Simple Example;223
16.6.3; Other Analysis ToolPak Functions;227
16.7; Using a Pivot Table to Create a Histogram;227
16.8; Scatter Chart;232
16.9; Summary;237
17;Chapter 11: HDInsight;238
17.1; Getting a Free Azure Account;238
17.2; Importing Hadoop Files into Power Query;239
17.2.1; Creating an Azure Storage Account;239
17.2.2; Provisioning a Hadoop Cluster;242
17.2.3; Importing into Excel;247
17.2.4; Creating a Pivot Table;251
17.2.5; Creating a Map in Power Map;252
17.3; Summary;254
18;Index;255

Über Autor(innen)

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.