Mastering Microsoft Power BI

2018·14.71 MB·English

by  Brett Powell

Title Page Page: 2
Copyright and Credits Page: 2
Mastering Microsoft Power BI Page: 4
Dedication Page: 5
Packt Upsell Page: 6
Why subscribe? Page: 7
PacktPub.com Page: 8
Contributors Page: 9
About the author Page: 10
About the reviewer Page: 11
Packt is searching for authors like you Page: 12
Preface Page: 17
Who this book is for Page: 18
What this book covers Page: 19
To get the most out of this book Page: 20
Download the example code files Page: 21
Download the color images Page: 22
Conventions used Page: 23
Get in touch Page: 24
Reviews Page: 25
Planning Power BI Projects Page: 26
Power BI deployment modes Page: 27
Corporate BI Page: 28
Self-Service Visualization Page: 29
Self-Service BI Page: 30
Choosing a deployment mode Page: 31
Project discovery and ingestion Page: 32
Sample Power BI project template Page: 33
Sample template – Adventure Works BI Page: 34
Power BI project roles Page: 35
Dataset designer Page: 36
Report authors Page: 37
Power BI admin Page: 38
Project role collaboration Page: 39
Power BI licenses Page: 40
Power BI license scenarios Page: 41
Power BI Premium features Page: 42
Data warehouse bus matrix Page: 43
Dataset design process Page: 44
Selecting the business process Page: 45
Declaring the grain Page: 46
Identifying the dimensions Page: 47
Defining the facts Page: 48
Data profiling Page: 49
Dataset planning Page: 50
Data transformations Page: 51
Import versus DirectQuery Page: 52
Import mode Page: 53
DirectQuery mode Page: 54
Sample project analysis Page: 55
Summary Page: 56
Connecting to Sources and Transforming Data with M Page: 57
Query design per dataset mode Page: 58
Import mode dataset queries Page: 59
DirectQuery dataset queries Page: 60
Data sources Page: 61
Authentication Page: 62
Data source settings Page: 63
Privacy levels Page: 64
Power BI as a data source Page: 65
Power BI Desktop options Page: 66
Global options Page: 67
CURRENT FILE options Page: 68
SQL views Page: 69
SQL views versus M queries Page: 70
SQL view examples Page: 71
Date dimension view Page: 72
Mark As Date Table Page: 73
Product Dimension view Page: 74
Slowly-changing dimensions Page: 75
M queries Page: 76
Data Source Parameters Page: 77
Staging Queries Page: 78
DirectQuery staging Page: 79
Fact and dimension queries Page: 80
Source Reference Only Page: 81
M query summary Page: 82
Excel workbook – Annual Sales Plan Page: 83
Data types Page: 84
Item access in M Page: 85
DirectQuery report execution Page: 86
Bridge Tables Queries Page: 87
Parameter Tables Page: 88
Security Tables Page: 89
Query folding Page: 90
Partial query folding Page: 91
M Query examples Page: 92
Trailing three years filter Page: 93
Customer history column Page: 94
Derived column data types Page: 95
Product dimension integration Page: 96
R script transformation Page: 97
M editing tools Page: 98
Advanced Editor Page: 99
Visual Studio Code Page: 100
Visual Studio Page: 101
Summary Page: 102
Designing Import and DirectQuery Data Models Page: 103
Dataset layers Page: 104
Dataset objectives Page: 105
Competing objectives Page: 106
External factors Page: 107
The Data Model Page: 108
The Relationships View Page: 109
The Data View Page: 110
The Report View Page: 111
Fact tables Page: 112
Fact table columns Page: 113
Fact column data types Page: 114
Fact-to-dimension relationships Page: 115
Dimension tables Page: 116
Hierarchies Page: 117
Custom sort Page: 118
Bridge tables Page: 119
Parameter tables Page: 120
Measure groups Page: 121
Last refreshed date Page: 122
Measure support logic Page: 123
Relationships Page: 124
Uniqueness Page: 125
Ambiguity Page: 126
Single-direction relationships Page: 127
Direct flights only Page: 128
Bidirectional relationships Page: 129
Shared dimensions Page: 130
Date dimensions Page: 131
The CROSSFILTER function Page: 132
Model metadata Page: 133
Visibility Page: 134
Column metadata Page: 135
Default Summarization Page: 136
Data format Page: 137
Data category Page: 138
Field descriptions Page: 139
Optimizing performance Page: 140
Import Page: 141
Columnar compression Page: 142
Memory analysis via DMVs Page: 143
DirectQuery Page: 144
Optimized DAX functions Page: 145
Columnstore and HTAP Page: 146
Summary Page: 147
Developing DAX Measures and Security Roles Page: 148
DAX measures Page: 149
Filter context Page: 150
SQL equivalent Page: 151
Measure evaluation process Page: 152
Row context Page: 153
Scalar and table functions Page: 154
The CALCULATE() function Page: 155
Related tables Page: 156
The FILTER() function Page: 157
DAX variables Page: 158
Base measures Page: 159
Measure support expressions Page: 160
KPI Targets Page: 161
Current and prior periods Page: 162
Date intelligence metrics Page: 163
Current versus prior and growth rates Page: 164
Rolling periods Page: 165
Dimension metrics Page: 166
Missing dimensions Page: 167
Ranking metrics Page: 168
Dynamic ranking measures Page: 169
Security roles Page: 170
Dynamic row-level security Page: 171
Performance testing Page: 172
DAX Studio Page: 173
Tracing a Power BI dataset via DAX Studio Page: 174
Summary Page: 175
Creating and Formatting Power BI Reports Page: 176
Report planning Page: 177
Power BI report architecture Page: 178
Live connections to Power BI datasets Page: 179
Customizing Live connection reports Page: 180
Switching source datasets Page: 181
Visualization best practices Page: 182
Visualization anti-patterns Page: 183
Choosing the visual Page: 184
Tables versus charts Page: 185
Chart selection Page: 186
Visual interactions Page: 187
Edit interactions Page: 188
What-if parameters Page: 189
Slicers Page: 190
Slicer synchronization Page: 191
Custom slicer parameters Page: 192
Report filter scopes Page: 193
Report filter conditions Page: 194
Report and page filters Page: 195
Page filter or slicer? Page: 196
Relative date filtering Page: 197
Visual-level filtering Page: 198
Top N visual-level filters Page: 199
Visualization formatting Page: 200
Visual-level formatting Page: 201
Line and column charts Page: 202
Tooltips Page: 203
Report page tooltips Page: 204
Column and line chart conditional formatting Page: 205
Column chart conditional formatting Page: 206
Line chart conditional formatting Page: 207
Table and matrix Page: 208
Table and matrix conditional formatting Page: 209
Values as rows Page: 210
Scatter charts Page: 211
Map visuals Page: 212
Bubble map Page: 213
Filled map Page: 214
Mobile-optimized reports Page: 215
Responsive visuals Page: 216
Report design summary Page: 217
Summary Page: 218
Applying Custom Visuals, Animation, and Analytics Page: 219
Drillthrough report pages Page: 220
Custom labels and the back button Page: 221
Multi-column drillthrough Page: 222
Bookmarks Page: 223
Selection pane and the Spotlight property Page: 224
Custom report navigation Page: 225
View mode Page: 226
ArcGIS Map visual for Power BI Page: 227
ArcGIS Maps Plus subscriptions Page: 228
Waterfall chart breakdown Page: 229
Analytics pane Page: 230
Trend Line Page: 231
Forecast line Page: 232
Quick Insights Page: 233
Explain the increase/decrease Page: 234
Custom visuals Page: 235
Adding a custom visual Page: 236
Power KPI visual Page: 237
Chiclet Slicer Page: 238
Impact Bubble Chart Page: 239
Dot Plot by Maq Software Page: 240
Animation and data storytelling Page: 241
Play axis for scatter charts Page: 242
Pulse Chart Page: 243
Summary Page: 244
Designing Power BI Dashboards and Architectures Page: 245
Dashboards versus reports Page: 246
Dashboard design Page: 247
Visual selection Page: 248
Layout Page: 249
Navigation pane Page: 250
Full screen mode Page: 251
Supporting tiles Page: 252
Custom date filters Page: 253
Multi-dashboard architectures Page: 254
Single-dashboard architecture Page: 255
Multiple-dashboard architecture Page: 256
Organizational dashboard architecture Page: 257
Multiple datasets Page: 258
Dashboard tiles Page: 259
Tile details and custom links Page: 260
Images and text boxes Page: 261
SQL Server Reporting Services Page: 262
Excel workbooks Page: 263
Live report pages Page: 264
Mobile-optimized dashboards Page: 265
Summary Page: 266
Managing Application Workspaces and Content Page: 267
Application workspaces Page: 268
Workspace roles and rights Page: 269
Workspace admins Page: 270
Workspace members Page: 271
My Workspace Page: 272
Staged deployments Page: 273
Workspace datasets Page: 274
Power BI REST API Page: 275
Client application ID Page: 276
Workspace and content IDs Page: 277
PowerShell sample scripts Page: 278
Dashboard data classifications Page: 279
Version control Page: 280
OneDrive for Business version history Page: 281
Source control for M and DAX code Page: 282
Metadata management Page: 283
Field descriptions Page: 284
Creating descriptions Page: 285
View field descriptions Page: 286
Metadata reporting Page: 287
Query field descriptions Page: 288
Standard metadata reports Page: 289
Server and database parameters Page: 290
Querying the DMVs from Power BI Page: 291
Integrating and enhancing DMV data Page: 292
Metadata report pages Page: 293
Summary Page: 294
Managing the On-Premises Data Gateway Page: 295
On-premises data gateway planning Page: 296
Top gateway planning tasks Page: 297
Determining whether a gateway is needed Page: 298
Identifying where the gateway should be installed Page: 299
Defining the gateway infrastructure and hardware requirements Page: 300
On-premises data gateway versus personal mode Page: 301
Gateway clusters Page: 302
Gateway architectures Page: 303
Gateway security Page: 304
Gateway configuration Page: 305
The gateway service account Page: 306
TCP versus HTTPS mode Page: 307
Managing gateway clusters Page: 308
Gateway administrators Page: 309
Gateway data sources and users Page: 310
PowerShell support for gateway clusters Page: 311
Troubleshooting and monitoring gateways Page: 312
Restoring, migrating, and taking over a gateway Page: 313
Gateway log files Page: 314
Performance Monitor counters Page: 315
Scheduled data refresh Page: 316
DirectQuery datasets Page: 317
Single sign-on to DirectQuery sources via Kerberos Page: 318
Live connections to Analysis Services models Page: 319
Azure Analysis Services refresh Page: 320
Dashboard cache refresh Page: 321
Summary Page: 322
Deploying the Power BI Report Server Page: 323
Planning for the Power BI Report Server Page: 324
Feature differences with the Power BI service Page: 325
Parity with SQL Server Reporting Services Page: 326
Data sources and connectivity options Page: 327
Hardware and user licensing Page: 328
Pro licenses for report authors Page: 329
Alternative and hybrid deployment models Page: 330
Report Server reference topology Page: 331
Installation Page: 332
Hardware and software requirements Page: 333
Analysis Services Integrated Page: 334
Retrieve the Report Server product key Page: 335
Migrating from SQL Server Reporting Services Page: 336
Configuration Page: 337
Service Account Page: 338
Remote Report Server Database Page: 339
Office Online Server for Excel Workbooks Page: 340
Upgrade cycles Page: 341
Report Server Desktop Application Page: 342
Running desktop versions side by side Page: 343
Report Server Web Portal Page: 344
Scheduled data refresh Page: 345
Data source authentication Page: 346
Power BI mobile applications Page: 347
Report server administration Page: 348
Securing Power BI report content Page: 349
Execution logs Page: 350
Scale Power BI Report Server Page: 351
Summary Page: 352
Creating Power BI Apps and Content Distribution Page: 353
Content distribution methods Page: 354
Power BI apps Page: 355
Licensing apps Page: 356
App deployment process Page: 357
User permissions Page: 358
Publishing apps Page: 359
Installing apps Page: 360
Apps on Power BI mobile Page: 361
App updates Page: 362
Dataset-to-workspace relationship Page: 363
Self-Service BI workspace Page: 364
Self-Service content distribution Page: 365
Risks to Self-Service BI Page: 366
Sharing dashboards and reports Page: 367
Sharing scopes Page: 368
Sharing versus Power BI apps Page: 369
SharePoint Online embedding Page: 370
Custom application embedding Page: 371
Publish to web Page: 372
Data alerts Page: 373
Microsoft Flow integration Page: 374
Email Subscriptions Page: 375
Analyze in Excel Page: 376
Power BI Publisher for Excel Page: 377
Summary Page: 378
Administering Power BI for an Organization Page: 379
Data governance for Power BI Page: 380
Implementing data governance Page: 381
Azure Active Directory Page: 382
Azure AD B2B collaboration Page: 383
Licensing external users Page: 384
Conditional access policies Page: 385
Power BI Admin Portal Page: 386
Usage metrics Page: 387
Users and Audit logs Page: 388
Tenant settings Page: 389
Embed Codes Page: 390
Organizational Custom visuals Page: 391
Usage metrics reports Page: 392
Audit logs Page: 393
Audit log monitoring solutions Page: 394
Audit logs solution template Page: 395
Power BI Premium capacities Page: 396
Capacity allocation Page: 397
Create, size, and monitor capacities Page: 398
Change capacity size Page: 399
Monitor premium capacities Page: 400
App workspace assignment Page: 401
Capacity admins Page: 402
Summary Page: 403
Scaling with Premium and Analysis Services Page: 404
Power BI Premium Page: 405
Power BI Premium capabilities Page: 406
Corporate Power BI datasets Page: 407
Limitation of Corporate BI datasets – Reusability Page: 408
Premium capacity nodes Page: 409
Frontend versus backend resources Page: 410
Power BI Premium capacity allocation Page: 411
Corporate and Self-Service BI capacity Page: 412
Power BI Premium resource utilization Page: 413
Data model optimizations Page: 414
Report and visualization optimizations Page: 415
Premium capacity estimations Page: 416
Analysis Services Page: 417
Analysis Services Models versus Power BI Desktop Page: 418
Scale Page: 419
Usability Page: 420
Development and management tools Page: 421
Azure Analysis Services versus SSAS Page: 422
SSAS to Azure AS Migration Page: 423
Provision Azure Analysis Services Page: 424
Migration of Power BI Desktop to Analysis Services Page: 425
Summary Page: 426
Other Books You May Enjoy Page: 427
Leave a review - let other readers know what you think Page: 428