Beginning Power BI with Excel 2013 (eBook)

Dan Clark is a senior business intelligence (BI)/programming consultant specializing in Microsoft technologies. He is focused on learning new BI/data technologies and on training others how to best implement the technology. Dan has published several books and numerous articles on .NET programming and BI development. He is a regular speaker at various developer/database conferences and user group meetings and enjoys interacting with the Microsoft developer and database communities. In a previous life, he was a physics teacher; he is still inspired by the wonder and awe of studying the universe and figuring out why things behave the way they do. Dan can be reached at Clark.drc@gmail.com.

Contents at a Glance 3
Contents 302
About the Author 307
About the Technical Reviewers 308
Acknowledgments 309
Introduction 5
Part 1: Building Models in Power Pivot 6
Chapter 1: Introducing Power Pivot 7
Why Use Power Pivot? 7
The xVelocity In-memory Analytics Engine 8
Enabling Power Pivot for Excel 9
Exploring the Data Model Manager Interface 12
Summary 22
Chapter 2: Importing Data into Power Pivot 23
Importing Data from Relational Databases 23
Importing Data from Text Files 38
Importing Data from a Data Feed 42
Importing Data from an OLAP Cube 43
Reusing Existing Connections to Update the Model 46
Summary 55
Chapter 3: Creating the Data Model 56
What Is a Data Model? 56
Creating Table Relations 58
Creating a Star Schema 61
Understanding When to Denormalize the Data 62
Creating Linked Tables 64
Creating Hierarchies 66
Making a User-Friendly Model 66
Summary 73
Chapter 4: Creating Calculations with DAX 74
What Is DAX? 74
Implementing DAX Operators 76
Working with Text Functions 78
Using DAX Date and Time Functions 79
Using Informational and Logical Functions 81
Getting Data from Related Tables 82
Using Math, Trig, and Statistical Functions 83
Tips for Creating Calculations in Power Pivot 84
Summary 89
Chapter 5: Creating Measures with DAX 90
Measures versus Attributes 90
Creating Common Aggregates 92
Mastering Data Context 95
Altering the Query Context 96
Using Filter Functions 98
Creating KPIs 103
Summary 114
Chapter 6: Incorporating Time Intelligence 115
Date-Based Analysis 115
Creating a Date Table 117
Time Period–Based Evaluations 119
Shifting the Date Context 120
Using Single Date Functions 123
Creating Semi-additive Measures 124
Summary 134
Chapter 7: Data Analysis with Pivot Tables and Charts 135
Pivot Table Fundamentals 135
Slicing the Data 137
Adding Visualizations to a Pivot Table 140
Working with Pivot Charts 144
Using Multiple Charts and Tables 147
Using Cube Functions 150
Summary 159
Part 2: Building Interactive Reports and Dashboards with Power View 160
Chapter 8: Optimizing Power Pivot Models for Power View 161
Visualizing Data with Power View 161
Creating a Basic Report 165
Improving the Power View Experience 167
Summary 178
Chapter 9: Creating Standard Visualizations with Power View 179
Creating Tables and Matrices 179
Constructing Bar, Column, and Pie Charts 184
Building Line and Scatter Charts 188
Creating Map-Based Visualizations 191
Summary 198
Chapter 10: Creating Interactive Dashboards with Power View 199
Linking Visualizations in Power View 199
Using Tiles to Organize the Data 202
Filtering Groups and Views 205
Exposing the Dashboard 208
Summary 214
Part 3: Exploring and Presenting Data with Power Query and Power Map 215
Chapter 11: Data Discovery with Power Query 216
Discovering and Importing Data 216
Transforming, Cleansing, and Filtering Data 219
Merging and Shaping Data 222
Grouping and Aggregating Data 226
Inserting Calculated Columns 227
Summary 233
Chapter 12: Geospatial Analysis with Power Map 234
Preparing Data for Mapping 234
Creating a Map-Based Graph 235
Creating Heat and Region Maps 239
Adding Multiple Layers to a Power Map 243
Analyzing Changes over Time 244
Creating a Tour 245
Summary 252
Chapter 13: Mining Your Data with Excel 253
Table Analysis Tools in Excel 253
Analyzing Key Influencers 256
Identifying Data Groups 259
Forecasting Future Trends 263
Using Shopping Basket Analysis 265
Summary 272
Chapter 14: Creating a Complete Solution 273
Use Case 1: Reseller Sales Analysis 273
Load the Data 273
Create the Model 274
Create Calculated Columns 276
Create Measures 276
Create the Dashboard 279
Use Case 2: Sales Quota Analysis 282
Load the Data 283
Create the Model 283
Create Measures 286
Create the Dashboard 286
Use Case 3: Sensor Analysis 288
Load the Data 289
Summary 292
Index 293