book

Mastering Microsoft Power BI – Second Edition - Second Edition

by Greg Deckler, Brett Powell

June 2022

Beginner to intermediate

712 pages

18h 41m

English

Packt Publishing

Read now

Unlock full access

Who this book is forWhat this book coversTo get the most out of this bookGet in touch
Power BI deployment modesCorporate BISelf-Service VisualizationSelf-Service BIChoosing a deployment modeProject discovery and ingestionSample Power BI project template Sample template – Adventure Works BIPower BI project rolesDataset designerReport authorsPower BI administratorProject role collaborationPower BI licensesShared capacityFreeProDedicated capacityPremiumPremium Per UserEmbeddedPower BI license scenariosDataset designData warehouse bus matrixDataset design processSelect the business processDeclare the grainIdentify the dimensionsDefine the factsData profilingData profiling with Power BI DesktopDataset planningData transformationsImport, DirectQuery, Live, and Composite datasetsImport mode DirectQuery/Live modeComposite modeSample project analysisSummary
Query foldingPartial query foldingQuery design per dataset modeImport mode dataset queriesDirectQuery dataset queriesDirectQuery report executionComposite datasetsTable storage modesData sourcesAuthenticationData source settingsPrivacy levelsPower BI as a data sourcePower BI Desktop optionsGlobal optionsCURRENT FILE optionsSQL viewsSQL views versus M queriesSQL view examplesDate dimension viewMark as date tableProduct dimension viewSlowly changing dimensionsSummary
Types of Power Query M queriesOrganizing queriesData source parametersStaging queriesDirectQuery stagingFact and dimension queriesSource reference onlyExcel workbook – Annual Sales PlanParameter tables queriesSecurity table queriesCustom function queriesCreating Power Query M queriesNumeric data typesItem access in MPower Query M query examplesTrailing three years filterCombining queriesIncremental refresh for fact tablesCustomer history columnProduct dimension integrationR and Python script transformationDataflowsPower BI Premium dataflow featuresPower Query M editing toolsAdvanced EditorVisual Studio CodeVisual StudioSummary
Dataset layersPower BI as a superset of Azure Analysis ServicesDataset objectivesCompeting objectivesExternal factorsThe Data Model The Model viewThe Data viewThe Report viewFact tablesFact table columnsFact column data typesFact-to-dimension relationshipsDimension tablesHierarchiesCustom sortParameter tablesMeasure groupsLast refreshed dateMeasure support logicDisplay foldersRelationshipsUniqueness AmbiguitySingle-direction relationshipsDirect flights onlyBidirectional relationshipsShared dimensionsDate dimensions The CROSSFILTER functionModel metadataVisibilityColumn and measure metadataDefault summarizationData formatData categoryDescriptionOptimizing data model performanceImportQuery cachingColumnar compressionMemory analysis via DMVs and the VertiPaq AnalyzerDirectQuery Columnstore and HTAPAutomatic aggregationsCompositeAggregation tablesSummary
DAX measure basicsFilter contextSQL equivalentMeasure evaluation processRow contextScalar and table functionsRelated tablesThe CALCULATE() functionThe FILTER() functionDAX variablesBase measuresMeasure support expressionsKPI targetsCurrent and prior periodsDate intelligence metricsCurrent versus prior and growth ratesRolling periodsCalculation groupsDimension metricsMissing dimensionsRanking metricsDynamic ranking measuresSecurity rolesDynamic row-level securityPerformance testingPerformance analyzerDAX StudioSummary
Report planning processIdentify the audienceDefine the business questions to answerConfirm that the dataset supports the business questionsDetermine interactivityDefine access and distributionSketch the report layoutReport architecture diagramVisualization best practicesChoosing the right visualTables and matrices versus chartsChart selectionVisualization anti-patternsVisual interactionsEditing interactionsDrillthrough report pagesCustom labels and the back buttonMulti-column drillthroughReport filter scopesReport filter conditionsReport and page filtersRelative date filteringVisual-level filteringTop N visual-level filtersBookmarksSelection pane and the Spotlight propertyCustom report navigationView modeLive connections to Power BI datasetsCustomizing Live connection reportsSwitching Live source datasetsSwitching between import mode and Live mode datasetsReport design summarySummary
The Visualizations paneSlicersSlicer synchronizationCustom slicer parametersWhat-if parametersPage filter or slicer?Single-value visualsThe Card visualThe KPI visualGauge visualMap visualsBubble mapFilled mapWaterfall chartPower Platform visualsPower Apps for Power BIPower Automate for Power BIPremium visualsScorecardPaginated reportsElementsFormatting visualizationsTooltipsReport page tooltipsColumn and line chartsColumn and line chart conditional formattingTable and matrix visualsCustom format stringsTable and matrix conditional formattingSparklinesValues as rowsScatter chartsSummary
AI visualsKey influencersDecomposition treeQ&ASmart narrativeR and Python visualsR visualPython visualArcGIS Maps for Power BICustom visualsAdding a custom visualAnimation and data storytellingPlay axis for Scatter chartsPulse chartAnalytics paneTrend lineForecastingQuick insights/AnalyzeExplain the increase/decreaseMobile-optimized report pagesSummary
Dashboards versus reportsDashboard designVisual selectionLayoutNavigation paneFullscreen modeSupporting tilesDashboard architecturesSingle-dashboard architectureMultiple-dashboard architectureOrganizational dashboard architectureMultiple datasetsDashboard tilesTile details and custom linksReal-time data tilesDashboard themesPaginated reportsExcel workbooksLive report pagesMobile-optimized dashboardsSummary

WorkspacesWorkspace roles and rightsViewer roleContributor roleMember roleAdmin roleDatasets across workspacesMy workspaceStaged deploymentsWorkspace datasetsPower BI REST API and PowerShell modulePower BI REST APIPower BI PowerShell moduleWorkspace and content identifiersPowerShell sample scriptsPower BI deployment pipelinesContent sensitivity and protectionInformation protectionData loss preventionVersion controlOneDrive for BusinessSource control for M and DAX codeMSHGQMMetadata managementField descriptionsCreating descriptionsView field descriptionsMetadata MechanicMetadata reportingStandard metadata reportsServer and database parametersQuerying the DMVs from Power BIIntegrating and enhancing DMV dataMetadata report pagesSummary
On-premises data gateway planningTop gateway planning tasksDetermining whether a gateway is neededIdentifying where the gateway should be installedDefining the gateway infrastructure and hardware requirementsDefining gateway roles and permissionsPlanning for recovery keysStandard versus personal modeGateway conceptsGateway clustersGateway architecturesGateway securityGateway installation and configurationThe gateway service accountTCP versus HTTPS mode ConnectorsRecovery KeysManaging gateway clustersGateway administratorsGateway data sources and usersPowerShell support for gateway clustersTroubleshooting and monitoring gatewaysRestoring, migrating, and taking over a gatewayGateway diagnosticsGateway monitoring reportsData refreshScheduled data refreshDirectQuery datasetsLive connections to Analysis Services modelsDashboard cache refreshSummary
Paginated reports in the Power BI servicePlanning paginated reportsBuilding and publishing paginated reportsIdentifying and interacting with paginated reportsPrinting, exporting, subscribing, and sharingMigrating reports to the Power BI serviceInventoryAssessPlanMigrateUser Acceptance Testing and final deploymentPlanning the Power BI Report Server (PBRS)Feature differences with the Power BI serviceParity with SQL Server Reporting ServicesData sources and connectivity optionsHardware and user licensingPro licenses for report authorsAlternative and hybrid deployment modelsPBRS reference topologyScale PBRSInstalling and upgrading PBRSRetrieve the PBRS product keyUpgrade cycles PBRS client applicationsRunning desktop versions side by sidePower BI mobile applicationsSummary
Content distribution methodsPower BI appsLicensing appsApp deployment processUser permissions and securityPublishing appsInstalling appsApp updatesApps on Power BI mobileSharing contentSharing scopesSharing versus Power BI appsEmbeddingLicensing embeddingPublish to webSecure URL embeddingMicrosoft 365 appsTeamsSharePoint OnlineCustom applicationsData alertsPower Automate integrationEmail subscriptionsAnalyze in ExcelSelf-service BI workspacesSelf-service content distributionRisks of self-service BISummary
Power BI administrator roleData governance for Power BIImplementing data governanceAzure Active DirectoryAAD B2B collaborationLicensing external usersConditional access policiesPower BI admin portalTenant settingsUsage metricsUsers and Audit logsPremium Per UserCapacity settingsEmbed codesOrganizational visualsAzure connectionsWorkspacesCustom brandingProtection metricsFeatured contentUsage metrics reportsAudit logsAudit log monitoring solutionThe Power BI REST API for adminsSummary
Power BI PremiumPower BI Premium capabilitiesPremium capacity nodesFrontend versus backend resourcesPremium capacity estimationsPremium capacity administration and allocationCapacity allocationCorporate and Self-Service BI capacity Create, size, and monitor capacitiesChanging capacity sizeMonitoring Premium capacitiesWorkspace assignmentPremium capacity resource optimizationData model optimizationsReport and visualization optimizationsWorkloadsLife cycle management with PremiumALM Toolkit deploymentDataset management with SSMSBacking up Premium capacitiesSummary

Overview

Dive deep into Microsoft Power BI with the second edition of 'Mastering Microsoft Power BI'. This comprehensive book equips you with the skills to transform business data into actionable insights using Power BI's latest features and techniques. From efficient data retrieval and transformation processes to creating interactive dashboards that tell impactful data stories, you will learn actionable knowledge every step of the way.

What this Book will help me do

Learn to master data collection and modeling using the Power Query M language
Gain expertise in designing DirectQuery, import, and composite data models
Understand how to create advanced analytics reports using DAX and Power BI visuals
Learn to manage the Power BI environment as an administrator with Premium capacity
Develop insightful, scalable, and visually impactful dashboards and reports

Author(s)

Greg Deckler, a seasoned Power BI expert and solution architect, and None Powell, an experienced BI consultant and data visualization specialist, bring their extensive practical knowledge to this book. Together, they share their real-world expertise and proven techniques applying Power BI's diverse capabilities.

Who is it for?

This book is ideal for business intelligence professionals and intermediate Power BI users. If you're looking to master data visualization, prepare insightful dashboards, and explore Power BI's full potential, this is for you. Basic understanding of BI concepts and familiarity with Power BI will ensure you get the most value.