High Impact Data Visualization with Power View, Power Map, and Power BI (eBook)
XXVI, 552 Seiten
Apress (Verlag)
978-1-4302-6617-4 (ISBN)
High Impact Data Visualization with Power View, Power Map, and Power BI helps you take business intelligence delivery to a new level that is interactive, engaging, even fun, all while driving commercial success through sound decision-making. Learn to harness the power of Microsoft’s flagship, self-service business intelligence suite to deliver compelling and interactive insight with remarkable ease. Learn the essential techniques needed to enhance the look and feel of reports and dashboards so that you can seize your audience’s attention and provide them with clear and accurate information. Also learn to integrate data from a variety of sources and create coherent data models displaying clear metrics and attributes.
Power View is Microsoft's ground-breaking tool for ad-hoc data visualization and analysis. It's designed to produce elegant and visually arresting output. It's also built to enhance user experience through polished interactivity. Power Map is a similarly powerful mechanism for analyzing data across geographic and political units. Power Query lets you load, shape and streamline data from multiple sources. PowerPivot can extend and develop data into a dynamic model. Power BI allows you to share your findings with colleagues, and present your insights to clients.
High Impact Data Visualization with Power View, Power Map, and Power BI helps you master this suite of powerful tools from Microsoft. You'll learn to identify data sources, and to save time by preparing your underlying data correctly. You'll also learn to deliver your powerful visualizations and analyses through the cloud to PCs, tablets and smartphones.
- Simple techniques take raw data and convert it into information.
- Slicing and dicing metrics delivers interactive insight.
- Visually arresting output grabs and focuses attention on key indicators.
Adam Aspin is an independent Business Intelligence consultant based in the United Kingdom. He has worked with SQL Server for seventeen years, and Business Intelligence has been his principal focus for the last ten years. He has applied his skills for a range of clients, including J.P. Morgan, The Organisation for Economic Co-operation and Development (OECD), Tesco, Centrica, Harrods, Vodafone, Crédit Agricole, Cartier, Alfred Dunhill, The RAC and EMC Conchango.Adam is a graduate of Oxford University. He is a frequent contributor to SQLServerCentral.com. He has written numerous articles for various French IT publications. A fluent French speaker, Adam has worked in France and Switzerland for many years. He is the author of SQL Server 2012 Data Integration Recipes (Apress, 2012), and of High Impact Data Visualization with Power View Power Map, and Power BI (Apress, 2014).
High Impact Data Visualization with Power View, Power Map, and Power BI helps you take business intelligence delivery to a new level that is interactive, engaging, even fun, all while driving commercial success through sound decision-making. Learn to harness the power of Microsoft’s flagship, self-service business intelligence suite to deliver compelling and interactive insight with remarkable ease. Learn the essential techniques needed to enhance the look and feel of reports and dashboards so that you can seize your audience’s attention and provide them with clear and accurate information. Also learn to integrate data from a variety of sources and create coherent data models displaying clear metrics and attributes. Power View is Microsoft's ground-breaking tool for ad-hoc data visualization and analysis. It's designed to produce elegant and visually arresting output. It's also built to enhance user experience through polished interactivity. Power Map is a similarly powerful mechanism for analyzing data across geographic and political units. Power Query lets you load, shape and streamline data from multiple sources. PowerPivot can extend and develop data into a dynamic model. Power BI allows you to share your findings with colleagues, and present your insights to clients. High Impact Data Visualization with Power View, Power Map, and Power BI helps you master this suite of powerful tools from Microsoft. You'll learn to identify data sources, and to save time by preparing your underlying data correctly. You'll also learn to deliver your powerful visualizations and analyses through the cloud to PCs, tablets and smartphones.Simple techniques take raw data and convert it into information.Slicing and dicing metrics delivers interactive insight.Visually arresting output grabs and focuses attention on key indicators.
Adam Aspin is an independent Business Intelligence consultant based in the United Kingdom. He has worked with SQL Server for seventeen years, and Business Intelligence has been his principal focus for the last ten years. He has applied his skills for a range of clients, including J.P. Morgan, The Organisation for Economic Co-operation and Development (OECD), Tesco, Centrica, Harrods, Vodafone, Crédit Agricole, Cartier, Alfred Dunhill, The RAC and EMC Conchango.Adam is a graduate of Oxford University. He is a frequent contributor to SQLServerCentral.com. He has written numerous articles for various French IT publications. A fluent French speaker, Adam has worked in France and Switzerland for many years. He is the author of SQL Server 2012 Data Integration Recipes (Apress, 2012), and of High Impact Data Visualization with Power View Power Map, and Power BI (Apress, 2014).
Contents at a Glance 3
Contents 518
About the Author 534
About the Technical Reviewer 535
Acknowledgments 536
Introduction 5
Chapter 1: Self-Service Business Intelligence 7
The Microsoft Self-Service Business Intelligence Solution 7
The Excel BI Toolkit 8
Power BI 8
The Excel BI Toolkit and Power BI 8
Power Query 9
PowerPivot 9
Power View 10
Power Map 10
Power BI 10
Preparing the Self-Service BI Environment 11
PowerPivot 11
Power View 12
Power Query 13
Power Map 17
Power BI 17
Adding a Power BI Site 17
The Windows Power BI App 19
Corporate BI or Self-Service BI ? 19
The Excel Data Model 20
How This Book Is Designed to Be Read 21
Discovering Data 22
Creating a Data Model 22
Taking Data and Preparing It for Output 22
Taking Existing Excel BI and Sharing It 22
Delivering Geodata 22
Delivering Excel BI to Mobile Devices 22
To Learn the Product Suite Following a Real-World Path 23
The Self-Service Business Intelligence Universe 23
Conclusion 24
Chapter 2: Power View and Tables 25
The Power View Experience 26
Adding a Power View Sheet to an Excel Workbook 26
The Power View Interface 26
The Power View Ribbon 27
The Field List 29
Using the Field List 29
Renaming or Deleting a Power View Report 31
Tables in Power View 31
Adding a Table 31
Deleting a Table 34
Changing the Table Size and Position 34
Changing Column Order 34
Removing Columns from a Table 35
Types of Data 36
Data and Aggregations 37
Enhancing Tables 38
The Design Ribbon 38
Row Totals 40
Formatting Columns of Numbers 41
Default Formatting 42
Changing Column Widths 42
Font Sizes in Tables 43
Copying a Table 44
Sorting by Column 44
Table Granularity 45
Matrix Tables 46
Row Matrix 46
Column Matrix 48
Sorting Data in Matrix Tables 50
Drilling Through with Matrix Tables 52
Drilling Down 52
Drilling Up 54
Reapplying Matrix Visualization 54
Drilling Through with Column Hierarchies 54
Card Visualizations 56
Card Visualization Styles 58
Sorting Data in Card-View Tables 59
Switching Between Table Types 60
Key Performance Indicators (KPIs) 60
Conclusion 61
Chapter 3: Filtering Data in Power View 62
Filters 62
Hiding and Displaying the Filters Area 63
View Filters 63
Adding Filters 64
The (All) Filter 65
Clearing Filters 66
Deleting Filters 66
Expanding and Collapsing Filters 67
Subsetting Large Filter Lists 67
Searching for Specific Elements in a Filter 68
Clearing a Filter Subset 68
Filtering with Wildcards 69
Clearing a Filter Element Search 70
Filtering Different Data Types 70
Numeric Data 70
Range Filter Mode 70
List Filter Mode 71
Quickly Excluding Outliers 73
Date and Time Data 73
Other Data Types 75
Multiple Filters 75
Advanced Filters 75
Applying an Advanced Filter 76
Clearing an Advanced Filter 77
Advanced Wildcard Filters 78
Numeric Filters 79
Date and Time Filters 80
Complex Filters 81
Advanced Text Filter Options 82
Advanced Numeric Filter Options 83
Advanced Date Filter Options 83
Visualization-Level Filters 84
Filter Hierarchy 85
Filtering Tips 87
Don’t Filter Too Soon 87
Drill-Down and Filters 87
Annotate, Annotate, Annotate 88
Conclusion 88
Chapter 4: Charts in Power View 89
A First Chart 89
Creating a First Chart 90
Deleting a Chart 93
Basic Chart Modification 93
Basic Chart Types 94
Column Charts 95
Line Charts 95
Pie Charts 96
Essential Chart Adjustments 97
Resizing Charts 98
Repositioning Charts 99
Sorting Chart Elements 99
Font Size 102
Applying Color to Bar and Column Charts 103
Multiple Data Values in Charts 103
Data Details 106
The Layout Ribbon 107
Enhancing Charts 108
Chart Legends 108
Chart Title 109
Chart Data Labels 109
Drilling Down 111
Popping Charts Out and In 115
Chart Filters 117
Conclusion 121
Chapter 5: Advanced Charting with Power View 122
Multiple Charts 122
Multiple Bar or Column Charts 122
Specifying Vertical and Horizontal Selections 124
Specifying the Layout of Multiple Chart Visualizations 125
Creating Horizontal Multiples 125
Defining the Multiples Grid 125
Multiple Line Charts 127
Multiple Pie Charts 129
Drilling Down with Multiple Charts 130
Scatter Charts 131
Drilling Down with Scatter Charts 133
Scatter Charts to Display Flattened Hierarchies 134
Scatter Chart Multiple s 135
Bubble Charts 136
Bubble Chart Data Labels and Legend 137
Multiple Bubble Elements 138
Bubble Chart Multiple s 140
Play Axis 141
Tiles with Charts 143
Conclusion 143
Chapter 6: Interactive Data Selection 144
Tiles 144
Creating a Tiled Visualization from Scratch 145
Adjusting Tile Display 146
Some Variations on Ways of Creating Tiled Visualizations 146
Creating a Tiled Visualization from Scratch—Another Variant 147
Adding Tiles to an Existing Visualization 147
Adding Tiles to an Existing Visualization—Another Variant 148
Modifying an Existing Visualization Inside a Tile Container 148
Re-creating a Visualization Using Existing Tiles 148
Re-creating a Visualization Using Existing Tiles—A Simple Variant 149
Removing Tiles from a Visualization 150
Deleting a Tile Visualization 151
Tile Types 151
Using Tiles 152
Filtering Tiles 152
Tiles with No Data 153
Changing the Inner Visualization 154
Tiles and Multiple Charts 155
Slicers 155
Adding a Slicer 155
Applying a Slicer 156
Clearing a Slicer 157
Deleting a Slicer 158
Modifying a Slicer 158
Using Charts as Slicers 158
Charts as Slicers 159
Highlighting Chart Data 160
Cross-Chart Highlighting 161
Highlighting Data in Bubble Charts 163
Charts as Filters 164
Scatter Chart Filtering 166
Column and Bar Charts as Filters 167
Choosing the Correct Approach to Interactive Data Selection 171
Filter Granularity 171
Conclusion 174
Chapter 7: Images and Presentation 175
Titles 175
Adding a Title 176
Moving and Resizing Titles 176
Formatting a Title 177
The Text Ribbon 177
Adding Text Boxes to Annotate a Report 178
Deleting Text Boxes 179
The Context Menu 179
Altering the Font Used in a Report 181
Font Family 181
Text Size 181
Altering the Theme of a Report 182
Deciphering Themes 184
Applying a Report Background 185
Images 188
Image Sources 188
Background Images 189
Adding a Background Image 189
Fitting a Background Image 190
Removing a Background Image 192
Setting an Image’s Transparency 192
Images in Tables 194
Resizing Images Used in Tables 194
Images in Slicers 195
Images in Tiles 196
Independent Images 197
Layering Visualizations 198
Some Uses for Independent Images 200
Image File Format 201
Preparing Images 201
Conclusion 202
Chapter 8: Mapping Data in Power View 203
Bing Maps 203
Maps in Power View 204
Adjusting Map Display in Power View 206
Positioning the Map Elements 206
Zooming In or Out 207
Removing or Adding a Map Title 207
Modifying the Map Background 208
Filtering Map Data 209
Multi-Value Series 210
Highlighting Map Data 212
Adjusting a Legend 214
Adding Tiles to Maps 216
Multiple Maps 217
Multiple Maps by Region 218
Drilling Down in Maps 219
Conclusion 221
Chapter 9: PowerPivot Basics 222
The PowerPivot Environment 222
Using PowerPivot 223
The PowerPivot Ribbons 224
The Home Ribbon 224
The Design Ribbon 226
The Advanced Ribbon 227
Loading Data into PowerPivot 227
Loading Data from SQL Server 228
Preview and Filter Tables 233
Preview and Filter Options 235
Writing Queries to Select Data 236
Filtering Data Using the Table Import Wizard Design Dialog 237
List of Tables or Write a Query? 240
Importing Other Tables from an Existing Source 241
Modifying Existing Imports 242
Loading Data from Excel 243
Copying and Pasting Data from an Excel Workbook 243
Appending Data from Excel 244
Updating a Copied Table 245
Importing Data from an Excel Workbook 246
PowerPivot Data Sources 247
Refreshing Data 248
Refreshing Data from External Data Sources 249
Refreshing a Single Table Connected to an External Data Source 249
Refreshing All the Tables in the Data Set Connected to an External Data Source 250
Refreshing All the Tables in the Data Set Connected to an External Data Source 251
Refreshing Data from Linked Excel Worksheets 251
Refreshing a Single Table Connected to an Excel Worksheet 251
Refreshing All the Tables in the Data Set Connected to Excel Worksheets 251
Deleting a Connection 252
PowerPivot Data Types 252
Managing PowerPivot Data 253
Manipulating Tables 253
Renaming Tables 253
Deleting a Table 253
Moving a Table 253
Moving Around a Table 254
Selecting a Column from the List of Available Column Names 255
Searching for Metadata 255
Manipulating Columns 256
Renaming a Column 256
Deleting Columns 257
Moving Columns 258
Setting Column Widths 259
Freezing Columns 259
Other Currency Formats 261
Formatting PowerPivot Tables 260
Manipulating Data in PowerPivot 262
Sorting Data in PowerPivot Tables 262
Filtering Data in PowerPivot Tables 263
Clearing Filters 265
Custom Filters—Text 265
Custom Filters—Numeric 266
Custom Filters—Date 267
Conclusion 268
Chapter 10: Extending the Excel Data Model Using PowerPivot 269
Designing a PowerPivot Data Repository 269
Data View and Diagram View 270
Diagram View Display Options 271
Maximizing a Table 272
Creating Relationships 272
Creating Relationships Manually 273
Creating Relationships Automatically 274
Deleting Relationships 275
Managing Relationships 275
Preparing a Date Table 276
Marking a Table as a Date Table 278
Calculations 279
Calculated Columns 279
Simple Calculations 280
Using Formulas in Calculated Columns 282
Looking Up Related Data 285
Making Good Use of the Formula Bar 286
Multiline Formulas 286
Calculated Fields 287
A First Calculated Field: Number of Cars Sold 287
Basic Aggregations in Calculated Fields 288
More Advanced Aggregations 290
How It Works 291
Time-Dependent Calculations 292
YearToDate, QuarterToDate, and MonthToDate Calculations 292
Comparisons with Previous Years, Quarters, or Months 293
How It Works 294
Rolling Aggregations over a Period of Time 294
How It Works 295
Comparison with a Parallel Period in Time 295
How It Works 296
Other Possibilities 297
How It Works 298
Putting It All Together 298
A Few Comments and Notes on Using Calculated Fields 298
Calculation Options 298
Creating Pivot Tables from PowerPivot 299
Creating a Pivot Table 300
The PowerPivot Ribbon 301
Copying Data from PowerPivot 302
Conclusion 303
Chapter 11: PowerPivot for Self-Service BI 304
Default Field Set 305
Table Behavior 305
Sort Column 306
Row Identifier 307
Keep Unique Rows 308
Default Label 308
Set a Default Aggregation (Summarize By) 309
Preparing Images for Power View 310
Binary Images 311
Image URLs 311
Default Image 312
Preparing Hyperlinks for Power View 312
Creating Hierarchies 312
Modifying Hierarchies 313
Adding a Level to a Hierarchy 314
Removing a Level from a Hierarchy 314
Altering the Levels in a Hierarchy 314
Deleting a Hierarchy 315
Hiding the Original Field 315
Hiding Hierarchies from the Diagram View 315
Key Performance Indicators (KPIs) 315
Creating a KPI 316
KPI Options 317
KPI Descriptions 319
Calculated KPI Targets 320
Modifying a KPI 320
Deleting a KPI 321
Perspectives 321
Creating a Perspective 321
Applying a Perspective 323
Data Categories 323
Hiding Columns from Client Tools 324
Preparing Data for Natural Language Querying 326
Optimizing File Size 326
Conclusion 327
Chapter 12: Discovering and Loading Data with Power Query 328
The Power Query Interface 329
The Power Query Ribbon 329
Data Sources 330
File Sources 331
Databases 331
Other Sources 332
Loading Data 333
Web Pages 333
The Navigator Window 336
The Peek Popup 337
The WorkBook Queries Window 337
CSV Files 339
What Is a CSV File ? 340
Text Files 340
XML Files 341
Excel 342
Excel Data from the Current Workbook 343
Microsoft Access Databases 344
Loading Multiple Tables 344
Loading the List of Tables 345
Relational Databases 345
Database Metadata 348
Data Discovery or Data Load? 349
Peeking at Data 350
Searching for Data 350
Conclusion 353
Chapter 13: Transforming Data with Power Query 354
Modifying Data 354
The Power Query Window 355
The Power Query Ribbons 356
The Home Ribbon 356
The Insert Ribbon 358
Dataset Shaping 359
Using First Row as Headers 359
Renaming Columns 360
Reordering Columns 360
Removing Columns 361
Removing Records 361
Removing Duplicate Records 363
Removing Errors 364
Filtering Data 364
Selecting Specific Values 364
Finding Elements in the Filter List 365
Filtering Text Ranges 366
Filtering Numeric Ranges 366
Filtering Date and Time Ranges 367
Extending Data 368
Duplicating Column 368
Splitting Columns 368
Splitting Column by a Delimiter 369
Splitting Columns by Number of Characters 370
Custom Columns 371
Index Column 373
Data Cleansing and Modification 374
Changing Datatype 374
Replacing Values 375
Transforming Column Contents 376
Filling Down 378
Sorting Data 379
Managing the Transformation Process 379
Modifying a Step 380
Renaming a Step 380
Deleting a Step or a Series of Steps 381
Adding a Step 382
Altering Process Step Sequencing 382
An Approach to Sequencing 382
Changing the Data Structure 383
Unpivoting Tables 383
Grouping Records 384
Merging Data 386
Adding Reference Data 386
Aggregating Data During a Merge Operation 388
Appending Data 390
Adding the Contents of One Query to Another 390
Adding Multiple Files from a Source Folder 391
Data Destinations 392
The Power Query View Ribbon 393
Effective Use of the Formula Bar 393
Replacing Value s 393
Transforming 394
Grouping 394
Extending a Filter 394
Conclusion 395
Chapter 14: Power Map 396
Bing Maps 396
Running Power Map 397
The Power Map Window 397
The Power Map Ribbon 398
Region Maps 399
Power Map Source Data 400
Refreshing Data 401
Geographical Data Types 401
Define the Data Category in the Data Model 401
Add Multiple Levels of Geographical Information 401
Select the Correct Geographical Data Type in Power Map 402
Using the Task Panel 402
Showing and Hiding the Task Panel 402
Task Panel Panes 402
The Task Panel Data Views 404
Removing a Field 404
Moving Around in Power Map 405
Moving Around a Map 405
Zooming In or Out 406
Changing the Pitch of a Map 406
Flat Map and 3-D Globe 406
Going to a Specific Location 406
Power Map Aggregations 407
Map Types 408
The Various Map Types, by Example 409
Bubble Maps 409
Column Maps 412
Clustered Columns 413
Stacked Columns 414
Heat Maps 415
Region Maps 416
Presentation Options 417
The Settings View 417
Bubble Map Settings 419
Column Map Settings 419
Column Shapes 420
Region Map Settings 420
Heat Map Settings 420
Power Map Themes 421
Text Boxes 422
Timelines 424
Adding a Timeline 424
Using a Timeline 426
Playing the Timeline 426
Pausing the Timeline 426
Selecting Points along the Timeline 426
Setting Timeline Duration 427
Hiding the Time Decorator 428
Hiding the Timeline 428
Setting the Date Range for Playback 429
Date and Time Formats in the Time Decorator 429
Using Layers 430
2-D Charts 432
2-D Chart Types 433
Hiding the Map Data 433
Power Map Tours 434
Creating Power Map Tours 435
Deleting a Power Map Tour 436
Power Map Movies 437
Transitions 438
Exporting a Movie 438
Conclusion 440
Chapter 15: Self-Service Business Intelligence with Power BI 441
Using Excel Workbooks in Power BI 442
Adding a Workbook 442
Enabling Team Site Excel Files 444
Adding a Workbook to Featured Reports 445
Marking a Workbook as a Favorite 446
Interacting with a Workbook 447
Interacting with Power View in the Excel Web App 447
Editing Excel in the Excel Web App 448
Editing a BI Workbook in Excel 451
Downloading a Copy of an Excel Workbook 452
Removing a Workbook from Power BI 455
The Power BI App on Tablet Devices 455
Running the Power BI App 456
Adding Reports to the Power BI App from the Power BI Site 457
Removing Reports from the Power BI App 460
Using Reports in the Power BI App 460
Opening a Report in the Power BI App 461
Filtering a Report in the Power BI App 461
Highlighting or Slicing Data in a Report in the Power BI App 462
Switching Reports in the Power BI App 463
Synchronizing the Power BI App with the Power BI Site 464
Power BI on Other Mobile Devices 464
Keeping Power BI Data Up to Date 465
Data Management Gateway 466
Download a Data Management Gateway to a Workstation or Server on the Corporate Network 466
Installing and Configuring a Data Management Gateway from the Power BI Admin Page 475
The Data Management Gateway Application 477
Reregistering a Data Management Gateway Key with the Data Management Gateway Application 477
Changing Data Management Gateway Parameters 478
Stopping and (Re)Starting a Gateway 480
Deleting a Gateway 480
Data Sources 480
Using a Connection String to Determine Connection Settings 486
Deleting a Data Source 488
Data Refresh 489
Scheduled Data Refresh 489
Ad-Hoc Data Refresh 490
Using Data Sources 491
Shared Queries 491
Sign In to Power Bi from Power Query 491
Sharing Queries 492
Using a Shared Query 494
Searching for Shared Queries 494
Deleting a Shared Query 495
Editing Shared Query Settings 495
OData Feeds 495
My Power BI 496
Power BI Admin 497
Role Management 497
The Data Steward Group 498
Checking System Health 499
Exporting System Health Logs 500
Natural Language Querying 500
Conclusion 502
Chapter 16:Sample Data 503
Downloading the Sample Data 503
The Sample Data 503
Sample Files 504
Sample Database 504
Index 506
| Erscheint lt. Verlag | 24.6.2014 |
|---|---|
| Zusatzinfo | XXVI, 552 p. 418 illus. |
| Verlagsort | Berkeley |
| Sprache | englisch |
| Themenwelt | Informatik ► Software Entwicklung ► User Interfaces (HCI) |
| ISBN-10 | 1-4302-6617-1 / 1430266171 |
| ISBN-13 | 978-1-4302-6617-4 / 9781430266174 |
| Haben Sie eine Frage zum Produkt? |
PDF (Wasserzeichen)Größe: 22,8 MB
DRM: Digitales Wasserzeichen
Dieses eBook enthält ein digitales Wasserzeichen und ist damit für Sie personalisiert. Bei einer missbräuchlichen Weitergabe des eBooks an Dritte ist eine Rückverfolgung an die Quelle möglich.
Dateiformat: PDF (Portable Document Format)
Mit einem festen Seitenlayout eignet sich die PDF besonders für Fachbücher mit Spalten, Tabellen und Abbildungen. Eine PDF kann auf fast allen Geräten angezeigt werden, ist aber für kleine Displays (Smartphone, eReader) nur eingeschränkt geeignet.
Systemvoraussetzungen:
PC/Mac: Mit einem PC oder Mac können Sie dieses eBook lesen. Sie benötigen dafür einen PDF-Viewer - z.B. den Adobe Reader oder Adobe Digital Editions.
eReader: Dieses eBook kann mit (fast) allen eBook-Readern gelesen werden. Mit dem amazon-Kindle ist es aber nicht kompatibel.
Smartphone/Tablet: Egal ob Apple oder Android, dieses eBook können Sie lesen. Sie benötigen dafür einen PDF-Viewer - z.B. die kostenlose Adobe Digital Editions-App.
Zusätzliches Feature: Online Lesen
Dieses eBook können Sie zusätzlich zum Download auch online im Webbrowser lesen.
Buying eBooks from abroad
For tax law reasons we can sell eBooks just within Germany and Switzerland. Regrettably we cannot fulfill eBook-orders from other countries.
aus dem Bereich
