Beginning Big Data with Power BI and Excel 2013

Beginning Big Data with Power BI and Excel 2013

Released September 2015
Publisher(s): Apress
ISBN: 9781484205297

Read it now on the O’Reilly learning platform with a 10-day free trial.

O’Reilly members get unlimited access to books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Start your free trial

Book description

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, and case 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).
    •

    Table of contents

    1. Cover
    2. Title
    3. Copyright
    4. Contents at a Glance
    5. Contents
    6. About the Author
    7. About the Technical Reviewer
    8. Acknowledgments
    9. Introduction
    10. Chapter 1: Big Data
      1. Big Data As the Fourth Factor of Production
      2. Big Data As Natural Resource
      3. Data As Middle Manager
      4. Early Data Analysis
        1. First Time Line
        2. First Bar Chart and Time Series
        3. Cholera Map
      5. Modern Data Analytics
        1. Google Flu Trends
        2. Google Earth
        3. Tracking Malaria
        4. Big Data Cost Savings
      6. Big Data and Governments
        1. Predictive Policing
        2. A Cost-Saving Success Story
      7. Internet of Things or Industrial Internet
        1. Cutting Energy Costs at MIT
      8. The Big Data Revolution and Health Care
        1. The Medicalized Smartphone
      9. Improving Reliability of Industrial Equipment
      10. Big Data and Agriculture
      11. Cheap Storage
        1. Personal Computers and the Cost of Storage
        2. Review of File Sizes
        3. Data Keeps Expanding
      12. Relational Databases
        1. Normalization
        2. Database Software for Personal Computers
      13. The Birth of Big Data and NoSQL
        1. Hadoop Distributed File System (HDFS)
        2. Big Data
        3. The Three V’s
        4. The Data Life Cycle
        5. Apache Hadoop
        6. CAP Theorem
        7. NoSQL
        8. Spark
      14. Microsoft Self-Service BI
      15. Summary
    11. Chapter 2: Excel As Database and Data Aggregator
      1. From Spreadsheet to Database
      2. Interpreting File Extensions
      3. Using Excel As a Database
      4. Importing from Other Formats
        1. Opening Text Files in Excel
        2. Importing Data from XML
        3. Importing XML with Attributes
        4. Importing JSON Format
      5. Using the Data Tab to Import Data
        1. Importing Data from Tables on a Web Site
      6. Data Wrangling and Data Scrubbing
        1. Correcting Capitalization
        2. Splitting Delimited Fields
        3. Splitting Complex, Delimited Fields
        4. Removing Duplicates
      7. Input Validation
      8. Working with Data Forms
      9. Selecting Records
      10. Summary
    12. Chapter 3: Pivot Tables and Pivot Charts
      1. Recommended Pivot Tables in Excel 2013
      2. Defining a Pivot Table
        1. Defining Questions
        2. Creating a Pivot Table
        3. Changing the Pivot Table
        4. Creating a Breakdown of Sales by Salesperson for Each Day
        5. Showing Sales by Month
      3. Creating a Pivot Chart
      4. Adjusting Subtotals and Grand Totals
      5. Analyzing Sales by Day of Week
      6. Creating a Pivot Chart of Sales by Day of Week
      7. Using Slicers
      8. Adding a Time Line
      9. Importing Pivot Table Data from the Azure Marketplace
      10. Summary
    13. Chapter 4: Building a Data Model
      1. Enabling PowerPivot
      2. Relational Databases
      3. Database Terminology
      4. Creating a Data Model from Excel Tables
      5. Loading Data Directly into the Data Model
      6. Creating a Pivot Table from Two Tables
      7. Creating a Pivot Table from Multiple Tables
      8. Adding Calculated Columns
      9. Adding Calculated Fields to the Data Model
      10. Summary
    14. Chapter 5: Using SQL in Excel
      1. History of SQL
      2. NoSQL
      3. NewSQL
      4. SQL++
      5. SQL Syntax
      6. SQL Aggregate Functions
      7. Subtotals
      8. Joining Tables
      9. Importing an External Database
      10. Specifying a JOIN Condition and Selected Fields
      11. Using SQL to Extract Summary Statistics
      12. Generating a Report of Total Order Value by Employee
      13. Using MSQuery
      14. Summary
    15. Chapter 6: Designing Reports with Power View
      1. Elements of the Power View Design Screen
      2. Considerations When Using Power View
      3. Types of Fields
      4. Understanding How Data Is Summarized
      5. A Single Table Example
      6. Viewing the Data in Different Ways
      7. Creating a Bar Chart for a Single Year
      8. Column Chart
      9. Displaying Multiple Years
      10. Adding a Map
      11. Using Tiles
      12. Relational Example
      13. Customer and City Example
      14. Showing Orders by Employee
      15. Aggregating Orders by Product
      16. Summary
    16. Chapter 7: Calculating with Data Analysis Expressions (DAX)
      1. Understanding Data Analysis Expressions
        1. DAX Operators
        2. Summary of Key DAX Functions Used in This Chapter
      2. Updating Formula Results
        1. Creating Measures or Calculated Fields
        2. Analyzing Profitability
      3. Using the SUMX Function
      4. Using the CALCULATE Function
      5. Calculating the Store Sales for 2009
      6. Creating a KPI for Profitability
      7. Creating a Pivot Table Showing Profitability by Product Line
      8. Summary
    17. Chapter 8: Power Query
      1. Installing Power Query
      2. Key Options on Power Query Ribbon
      3. Working with the Query Editor
        1. Key Options on the Query Editor Home Ribbon
      4. A Simple Population
      5. Performance of S&P 500 Stock Index
      6. Importing CSV Files from a Folder
        1. Group By
      7. Importing JSON
      8. Summary
    18. Chapter 9: Power Map
      1. Installing Power Map
      2. Plotting a Map
      3. Key Power Map Ribbon Options
      4. Troubleshooting
        1. Plotting Multiple Statistics
        2. Adding a 2D Chart
        3. Showing Two or More Values
        4. Creating a 2D Chart
      5. Summary
    19. Chapter 10: Statistical Calculations
      1. Recommended Analytical Tools in 2013
      2. Customizing the Status Bar
      3. Inferential Statistics
      4. Review of Descriptive Statistics
        1. Calculating Descriptive Statistics
        2. Measures of Dispersion
        3. Excel Statistical Functions
      5. Charting Data
      6. Excel Analysis ToolPak
        1. Enabling the Excel Analysis ToolPak
        2. A Simple Example
        3. Other Analysis ToolPak Functions
      7. Using a Pivot Table to Create a Histogram
      8. Scatter Chart
      9. Summary
    20. Chapter 11: HDInsight
      1. Getting a Free Azure Account
      2. Importing Hadoop Files into Power Query
        1. Creating an Azure Storage Account
        2. Provisioning a Hadoop Cluster
        3. Importing into Excel
        4. Creating a Pivot Table
        5. Creating a Map in Power Map
      3. Summary
    21. Index

    Product information

    • Title: Beginning Big Data with Power BI and Excel 2013
    • Author(s):
    • Release date: September 2015
    • Publisher(s): Apress
    • ISBN: 9781484205297