Microsoft Power BI Cookbook: Creating Business Intelligence Solutions of Analytical Data Models, Reports, and Dashboards (English Edition)

Description

Key FeaturesFrom connecting to your data sources to developing and deploying immersive, mobile-ready dashboards and visualizations, this book covers it allOver 90 hands-on, technical recipes, tips, and use cases from across the Power BI platform including the Power BI Service and Mobile ApplicationsProven development techniques and guidance for implementing custom solutions with DAX and M languagesBook Description Microsoft Power BI is a business intelligence and analytics platform consisting of applications and services designed to provide coherent, visual and interactive insights of data. This book will provide thorough, technical examples of using all primary Power BI tools and features as well as demonstrate high impact end-to-end solutions that leverage and integrate these technologies and services. Get familiar with Power BI development tools and services, go deep into the data connectivity and transformation, modeling, visualization and analytical capabilities of Power BI, and see Power BI’s functional programming languages of DAX and M come alive to deliver powerful solutions to address common, challenging scenarios in business intelligence. This book will excite and empower you to get more out of Power BI via detailed recipes, advanced design and development tips, and guidance on enhancing existing Power BI projects. What you will learnCleanse, stage, and integrate your data sources with Power BIAbstract data complexities and provide users with intuitive, self-service BI capabilitiesBuild business logic and analysis into your solutions via the DAX programming language and dynamic, dashboard-ready calculationsTake advantage of the analytics and predictive capabilities of Power BIMake your solutions more dynamic and user specific and/or defined including use cases of parameters, functions, and row level securityUnderstand the differences and implications of DirectQuery, Live Connections, and Import-Mode Power BI datasets and how to deploy content to the Power BI Service and schedule refreshesIntegrate other Microsoft data tools such as Excel and SQL Server Reporting Services into your Power BI solutionAbout the Author Brett Powell is the owner of and business intelligence consultant at Frontline Analytics LLC, a data and analytics research and consulting firm and Microsoft Power BI partner. He has worked with Power BI technologies since they were first introduced as the SQL Server 2008R2 PowerPivot add-in for Excel 2010. He has contributed to the design and development of Microsoft and Power BI solutions of diverse scale and complexity across the retail, manufacturing, financial, and services industries. Brett regularly blogs and shares technical papers regarding the latest MSBI and Power BI features and development techniques and patterns at Insight Quest. He is also an organizer of the Boston BI User Group. Table of ContentsConfiguring Your Power BI Development ToolsAccessing and Retrieving DataBuilding a Power BI Data ModelAuthoring Power BI ReportsCreating Power BI DashboardsGetting Serious with Date IntelligenceParameterizing Your Power BI SolutionsImplementing Dynamic User-Based Visibility in Power BIApplying Advanced Analytics and Custom VisualsDeveloping Solutions for System Monitoring and AdministrationEnhancing and Optimizing Existing Power BI SolutionsDeploying and Distributing Power BI ContentIntegrating Power BI with other Applications Cover Title Page Copyright Credits Foreword About the Author About the Reviewers www.PacktPub.com Customer Feedback Table of Contents Preface Chapter 1: Configuring Power BI Development Tools Introduction Configuring Power BI Desktop options and settings Getting ready How to do it... Installing and running Power BI Desktop Configuring Power BI Desktop options How it works... There's more... See also Power BI's advantages over Excel Power BI Security and Data Source Privacy Installing the On-Premises Data Gateway Getting ready Hardware and network configuration How to do it... Installation of on-premises gateway How it works... Gateway recovery key There's more... See also... Installing Power BI Publisher for Excel How to do it... Installation of Power BI Publisher for Excel There's more... Installing and Configuring DAX Studio How to do it... Installation of DAX Studio Configuration of DAX Studio How it works... There's more... Guy in a Cube video channel Chapter 2: Accessing and Retrieving Data Introduction Viewing and analyzing M functions Getting ready How to do it... Formula Bar Advanced Editor window How it works... Query folding M query structure Lazy evaluation There's more... Partial query folding Limitations of query folding See also... M language references Establishing and managing connections to data sources Getting ready How to do it... Isolate data sources from individual queries Query groups Manage source credentials and privacy levels How it works... Data Source settings Data source privacy settings There's more... See also Building source queries for DirectQuery models Getting ready How to do it... Applying M transformations with DirectQuery models How it works... There's more... DirectQuery project candidates DirectQuery performance See also Importing data to Power BI Desktop models How to do it... Denormalize a dimension Provide automatic sorting How it works... There's more... One GB dataset limit and Power BI Premium See also Applying multiple filtering conditions Getting ready How to do it... Query filter example steps How it works... There's more... Filtering via the Query Editor interface See also Choosing columns and column names How to do it... Identify expensive columns Select columns Rename columns How it works... Column memory usage There's more... Fact table column eliminations Column orders See also Transforming and cleansing source data Getting ready How to do it... Remove duplicates Update a column through a join There's more... See also Creating custom and conditional columns How to do it... Create a dynamic banding attribute Create a formatted name column Comparing the current and previous rows How it works... Conditional expression syntax Case sensitivity Conditional expression evaluation Query folding of custom columns There's more... Add column from example Conditional columns interface DAX calculated columns Error handling and comments Integrating multiple queries Getting ready How to do it... Consolidate files Self-joining querying How it works... Nested join versus flat join Append multiple files There's more... Combine binaries Staging queries versus inline queries See also Choosing column data types How to do it... Remove automatic type detection steps Align relationship column data types Add numeric columns from text columns Use fixed decimal number for precision How it works... Automatic data type detection Numeric data types Power BI Desktop automatic time intelligence There's more... Data type impacts Date with locale Percentage data type See also Visualizing the M library How to do it... How it works... There's more... Chapter 3: Building a Power BI Data Model Introduction Designing a multi fact data model Getting ready Setting business expectations How to do it... Four-step dimensional design process Data warehouse and implementation bus matrix Choose the dataset storage mode - Import or DirectQuery In-Memory mode DirectQuery mode How it works... DAX formula and storage engine There's more... Project ingestion questions Power BI delivery approaches See also Implementing a multi fact data model How to do it... SQL view layer M queries in Power BI Desktop Create model relationships Author DAX measures Configure model metadata There's more... Shared views Handling one-to-many and many-to-many relationships Getting ready How to do it... Single, bidirectional, and CROSSFILTER() Single direction relationships Bidirectional relationship CROSSFILTER() Measure Many-to-many relationships Bidirectional cross-filtering for many-to-many How it works... Ambiguous relationships CROSSFILTER() There's more... DirectQuery supported See also Assigning data formatting and categories How to do it... Data formats Data category How it works... There's more... Model level settings See also Configuring Default Summarization and sorting How to do it... Sort By Column DAX Year-Month sorting DAX Ranking Sort Default Summarization How it works... Default Summarization There's more... Quick measures See also Setting the visibility of columns and tables How to do it... Isolate measures from tables How it works... Measure home tables There's more... Hiding hierarchy columns Group visibility Row level security visibility Visibility features from SSAS Embedding business definitions into DAX measures Getting ready How to do it... Sales and cost metrics Margin and count metrics Secondary relationships How it works... Date relationships There's more... Measure definitions Measure names and additional measures See also Enriching a model with analysis expressions How to do it... Pricing analysis Geometric mean at all grains How it works... Pricing analysis Building analytics into data models with DAX How to do it... Cross-selling opportunities Accessories but not bike customers Bike only customers Active verus inactive customers Actual versus budget model and measures How it works... Filter Context Functions There's more... SUMMARIZECOLUMNS() Integrating math and statistical analysis via DAX How to do it... Correlation coefficient Goodness-of-Fit test statistic How it works... Correlation coefficient syntax Goodness-of-Fit logic and syntax Supporting virtual table relationships How to do it... Segmentation example Summary to detail example Actual versus plan How it works... Year and month selected Virtual relationship functions There's more... Multiple dimensions  Alternatives to virtual relationships See also Creating browsable model hierarchies and groups How to do it... Create hierarchy columns with DAX Implement a hierarchy Create and manage a group How it works... DAX parent and child functions Include other grouping option Model scoped features There's more... DAX calculated columns as rare exceptions Natural hierarchies versus unnatural hierarchies Grouping dates and numbers DirectQuery models supported See also Chapter 4: Authoring Power BI Reports Introduction Building rich and intuitive Power BI reports Getting ready Stakeholder Matrix How to do it... Report planning and design process Report Design Example European Sales and Margin Report Page European country sales and margin report page How it works... European sales report design There's more... Power BI report design checklist Custom visuals Published Power BI datasets as data sources See also Creating table and matrix visuals How to do it... Table visual exceptions Identifying blanks in tables Matrix visual hierarchies How it works... Matrix visual navigation There's more... URL and mail to email support Percent of total formatting Measures on matrix rows Data bar conditional formatting Utilizing graphical visualization types Getting ready Choosing visual types How to do it... Waterfall chart for variance analysis Line chart with conditional formatting Shape map visualization How it works... Shape map Enhancing exploration of reports Getting ready Drillthrough report page requirements Enable Cortana integration and Q&A How to do it... Create featured Q&A questions Parameterized Q&A report Cortana integration Drillthrough Report Pages Report themes How it works... Report theme JSON files There's more... Conversational BI - mobile support for Q&A See also Integrating card visualizations Getting ready How to do it... KPI visual Multi-row card There's more... Gauge visualizations Controlling interactive filtering between visuals How to do it... Visual interaction control How it works... Current year Measures Associating slicers with report pages How to do it... Configure dimension slicers Horizontal slicers Customize a date slicer Relative date filters How it works... Date slicer There's more... Text search Numeric range slicers Applying filters at different scopes How to do it... Report and page level filters Visual level filter - top N How it works... DAX queries from report, page, and visual Filters There's more... Advanced report and page level filters Formatting reports for publication How to do it... Visual alignment and distribution Shapes as backgrounds and groups There's more... Snap objects to grid and keyboard shortcuts Textbox with email link Format painter See also Designing mobile report layouts Getting ready Plan for mobile consumption How to do it... Phone layout - Europe report page Phone layout - United Kingdom report page How it works... There's more... Slicers and drill-down on mobile devices Mobile-optimized dashboards See also Chapter 5: Creating Power BI Dashboards Introduction Building a Power BI dashboard How to do it... Dashboard design process Dashboard development process Constructing an enterprise dashboard How to do it... Dashboard design process How it works... Dual KPI custom visual Supporting tiles Developing dynamic dashboard metrics How to do it... Dynamic date columns KPI target measures How it works... Target measure - trailing 6 months Preparing datasets and reports for Q & A natural language queries Getting ready Determine use cases and feasibility How to do it... Prepare a model for Q & A Model metadata Model design Apply synonyms Analyze Q & a use cases Apply synonyms Publish the dataset Embedding analytical context into visualizations How to do it... Design the visual Create the visual How it works... Color saturation rule Tooltip measures There's more... Exposing what matters - top N and percentage of total visualizations How to do it... Top 25 resellers with below -3% margin Last year's top 50 products with below -10% growth How it works... Prior year rank measure Visualizing performance relative to targets with KPIs and gauges How to do it... Create the visuals Grouping KPIs Publish KPIs to dashboard How it works... Current month filter Time intelligence measures Leveraging Power BI reports in Power BI dashboards How to do it... Define live page requirements Create and publish to the dashboard Refine dashboard layout How it works... Live page slicers Deploying content from Excel and SSRS to Power BI Getting ready How to do it... Publish and pin excel objects Pin SSRS report items Adding data alerts and email notifications to dashboards How to do it... Configure data alert Automate email notification How it works... Chapter 6: Getting Serious with Date Intelligence Introduction Building a complete date dimension table Getting ready How to do it... Date dimension design Required date dimension columns Date dimension planning and design Add date intelligence columns via SQL How it works... Date intelligence columns Loading the date dimension There's more... Role playing date dimensions Surrogate key date conversion Prepping the date dimension via the Query Editor How to do it... Date dimension M Query Add the date intelligence column via join How it works... Date dimension M query DirectQuery support Authoring date intelligence metrics across granularities Getting ready How to do it... Current time period measures Prior time period measures Dynamic prior period measure How it works... Current and prior time period measures Developing advanced date intelligence metrics How to do it... Count of days without sales Dynamic Prior Year-to-Date How it works... Dynamic prior period intelligence Simplifying date intelligence with DAX queries and calculated tables How to do it... Role playing date dimensions via calculated tables Date table logic query How it works... Date table logic query Adding a metric placeholder dimension How to do it... Metric placeholder dimension query Measure group table Chapter 7: Parameterizing Power BI Solutions Introduction Creating dynamic and portable Power BI reports Getting ready How to do it... Single and multiple URL parameters Dynamic embedded URLs There's more... Dashboards with custom URLs See also Filtering queries with parameters Getting ready How to do it... Trailing days query parameter filter Multi-parameter query filters How it works... Query folding of parameter value filters There's more... Power BI Service support Preserving report metadata with Power BI templates Getting ready How to do it... Template parameters Export template Converting static queries into dynamic functions How to do it... There's more... Local resource usage Parameterizing your data sources Getting ready How to do it... SQL Server database Excel filename and path Stored procedure input parameters Generating a list of parameter values via queries How to do it... Dynamic date parameter query Product subcategories parameter query There's more... DirectQuery support Capturing user selections with parameter tables How to do it... Sales plan growth scenarios There's more... Scenario specific measures Building a forecasting process with What if analysis capabilities Getting ready How to do it... Forecast variables from Excel Power BI Desktop forecast model Source connection and unpivoted forecast tables Apply the forecast to historical values Allocate the forecast according to the dimension variable inputs Create relationships, measures, and forecast visuals Test and deploy forecasting tool How it works... Chapter 8: Implementing Dynamic User-Based Visibility in Power BI Introduction Capturing the current user context of Power BI content Getting ready How to do it... How it works... Power BI authentication There's more... USERNAME() versus USERPRINCIPALNAME() See also Defining RLS roles and filtering expressions Getting ready How to do it... United States online Bike Sales Role Europe reseller sales - mountain and touring Deploy security roles to Power BI How it works... Filter transfer via relationships There's more... Managing security Dynamic columns and central permissions table Designing dynamic security models in Power BI Getting ready How to do it... There's more... Performance impact Building dynamic security in DirectQuery data models Getting ready How to do it... How it works... Dynamic security via relationship filter propagation There's more... Bidirectional security relationships Displaying the current filter context in Power BI reports How to do it... Dimension values selected Dimension values remaining How it works... FILTERS() and CONCATENATEX() Avoiding manual user clicks with user-based filtering logic Getting ready How to do it... How it works... There's more... Personal filters feature coming to Power BI apps Chapter 9: Applying Advanced Analytics and Custom Visuals Introduction Incorporating advanced analytics into Power BI reports How to do it... Clustered column chart Line chart How it works... Analytics pane measures There's more... Analytics pane limitations See also Enriching Power BI content with custom visuals and quick insights Getting ready How to do it... Bullet chart custom visual Scoped quick insights How it works... There's more... Quick insights in Power BI Desktop Quick insights on published datasets Creating geospatial mapping visualizations with ArcGIS maps for Power BI Getting ready How to do it... Single field address Customer clustering Map There's more... ArcGIS map field wells Conditional formatting logic See also Configuring custom KPI and slicer visuals Getting ready How to do it... Dual KPI - headcount and labor expense Chiclet Slicer - Sales Territory Country There's more... Chiclet slicer custom visual Building animation and story telling capabilities Getting ready How to do it... Scatter chart with play axis ArcGIS map timeline Pulse chart custom visual There's more... Bookmarks Play axis custom visual Storytelling custom visuals Embedding statistical analyses into your model Getting ready How to do it... Regression table and measures Residuals table and measures Regression report How it works... Statistical formulas DAX calculated tables See also Creating and managing Power BI groupings and bins How to do it... First purchase date grouping Days since last purchase grouping Detecting and analyzing clusters Getting ready How to do it... Create clusters Analyze the clusters How it works... RFM - recency, frequency, monetary Clustering algorithm and limits There's more... R clustering custom visuals Scatter chart-based clustering Forecasting and visualizing future results Getting ready How to do it... Monthly forecast via date hierarchy Weekly sales forecast analysis How it works... Exponential smoothing Dynamic week status column There's more... Forecast requirements Using R functions and scripts to create visuals within Power BI Getting ready How to do it... Base graphics histogram ggplot2 histogram How it works... Automatic duplicate removal Filter context There's more... See also Chapter 10: Developing Solutions for System Monitoring and Administration Introduction Creating a centralized IT monitoring solution with Power BI Getting ready How to do it... How it works... Wait Stats and instance configuration data source setup There's more... Query Store integration DirectQuery real-time monitoring datasets See also Constructing a monitoring visualization and analysis layer Getting ready How to do it... How it works... Relative date filtering There's more... Top 10 slowest queries via Query Store See also Importing and visualizing dynamic management view (DMV) data of SSAS and Power BI data models How to do it... How it works... Memory structures See also Increasing SQL Server DBA productivity with Power BI Getting ready How to do it... How it works... Query Store See also Providing documentation of Power BI and SSAS data models to BI and business teams Getting ready How to do it... How it works... There's more... Power BI documentation reports via Excel SQL Server Analysis Services (SSAS) Metadata Analyzing performance monitor counters of the Microsoft on-premises data gateway and SSAS tabular databases Getting ready How to do it... SSAS tabular memory reporting On-premises data gateway counters How it works... SSAS tabular memory limits On-premises data gateway workloads There's more... High availability and load balancing for the on-premises data gateway Reduce network latency via Azure ExpressRoute and Azure Analysis Services See also Analyzing Extended Events trace data with Power BI Getting ready How to do it... How it works... Self-service Extended Events analysis There's more... SQL Server Profiler versus Extended Events Additional event session integration See also Visualizing log file data from SQL Server Agent jobs and from Office 365 audit searches Getting ready How to do it... Power BI Audit Log Integration SQL Server Agent log integration How it works... PowerShell search for Power BI audit log SQL Server agent tables There's more... Power BI usage reporting See also Chapter 11: Enhancing and Optimizing Existing Power BI Solutions Introduction Enhancing the scalability and usability of a data model Getting ready How to do it... Identify expensive columns and quick wins Normalize large dimensions Sort imported fact tables How it works... Columnar database Run-length encoding (RLE) compression via Order By Segment elimination There's more... Minimize loaded and refreshed queries Revising DAX measures to improve performance Getting ready How to do it... Improper use of FILTER() Optimizing OR condition measures How it works... DAX query engine - formula and storage There's more... DAX variables for performance DAX as a query language Pushing query processing back to source systems Getting ready How to do it... Query folding analysis process Query folding redesign How it works... Query folding factors Native SQL queries There's more... Parallel loading of tables Improving folded queries Strengthening data import and integration processes How to do it... Data source consolidation Error handling, comments, and variable names Handling missing fields How it works... MissingField.UseNull See also Isolating and documenting DAX expressions Getting ready How to do it... Reseller Margin % with variables Variable table filters How it works... Reseller Margin % with variables There's more... DAX Formatter in DAX Studio Chapter 12: Deploying and Distributing Power BI Content Introduction Preparing a content creation and collaboration environment in Power BI How to do it... Evaluate and plan for Power BI deployment Set up a Power BI service deployment How it works... Premium capacity nodes - frontend cores and backend cores There's more... Scaling up and scaling out with Power BI Premium See also Managing migration of Power BI content between development, testing, and production environments Getting ready How to do it... Staged deployment overview Development environment Production environment How it works... Automated report lifecycle - clone and rebind report APIs OneDrive for business synchronization Version restore in OneDrive for business See also Sharing Power BI dashboards with colleagues Getting ready How to do it... How it works... Managing shared dashboards There's more... Analyze shared content from Excel Sharing dashboards from Power BI mobile apps Configuring Power BI app workspaces Getting ready How to do it... How it works... App workspaces and apps App workspaces replace group workspaces There's more... Power BI premium capacity admins See also Configuring refresh schedules and DirectQuery connections with the on-premises data gateway Getting ready How to do it... Scheduled refresh for import mode dataset Configure data sources for the on-premises data gateway Schedule a refresh DirectQuery dataset Configure data sources for the on-premises data gateway Configure the DirectQuery dataset How it works... Dataset refreshes Dashboard and report cache refreshes There's more... Refresh limits: Power BI premium versus shared capacity Trigger refreshes via data refresh APIs in the Power BI Service See also Creating and managing Power BI apps Getting ready How to do it... Publishing an app Distributing and installing the app How it works... App workspaces to apps There's more... Apps replacing content packs Building email subscriptions into Power BI deployments Getting ready Determine feasibility - recipient, distribution method, and content How to do it... Create dashboard and report subscriptions Manage subscriptions There's more... See also Publishing Power BI reports to the public internet Getting ready How to do it... How it works... Publish to web report cache There's more... Embed in SharePoint online See also Enabling the mobile BI experience How to do it... Enhance basic mobile exploration and collaboration Enable advanced mobile BI experiences How it works... Responsive visualizations There's more... Apple watch synchronization SSRS 2016 on-premises via Power BI mobile apps Filters on phone reports See also Chapter 13: Integrating Power BI with Other Applications Introduction Integrating Excel and SSRS objects into Power BI Solutions Getting ready How to do it... SSRS Excel There's more... SSRS and Excel use cases SSRS Microsoft Excel Migrating a Power Pivot for Excel Data Model to Power BI Getting ready How to do it... How it works... Excel items imported There's more... Export or upload to Power BI from Excel 2016 Upload Excel Workbook to Power BI Export Excel Workbook to Power BI Accessing and analyzing Power BI datasets from Excel Getting ready How to do it... Cube formulas DAX query to Power BI How it works... Cube Formulas DAX query data connection There's more... Sharing and distribution limitations New Excel visual types table requirement Building Power BI reports into PowerPoint presentations Getting ready How to do it... Prepare a report for PowerPoint Export report to PowerPoint How it works... High resolution images and textboxes There's more... Embed Power BI tiles in MS Office See also Migrating a Power BI Data Model to SSAS Tabular Getting ready How to do it... How it works... Azure analysis services pricing and performance There's more... Direct import to SQL server data tools See also Accessing MS Azure hosted services such as Azure Analysis Services from Power BI Getting ready How to do it... How it works... Report level measures for live connections to SSAS Client libraries for Azure Analysis Services There's more... Power BI premium DirectQuery and SSAS live connection query limits See also Using Power BI with Microsoft Flow and PowerApps Getting ready How to do it... Streaming Power BI dataset via MS Flow How it works... Microsoft Flow There's more... Write capabilities and MS Flow premium PowerApps Studio and mobile applications See also Index