40,79 €
Mehr erfahren.
- Herausgeber: Packt Publishing
- Kategorie: Wissenschaft und neue Technologien
- Sprache: Englisch
Design, create and manage robust Power BI solutions to gain meaningful business insights
Key Features
- Master all the dashboarding and reporting features of Microsoft Power BI
- Combine data from multiple sources, create stunning visualizations and publish your reports across multiple platforms
- A comprehensive guide with real-world use cases and examples demonstrating how you can get the best out of Microsoft Power BI
Book Description
This book is intended for business intelligence professionals responsible for the design and development of Power BI content as well as managers, architects and administrators who oversee Power BI projects and deployments. The chapters flow from the planning of a Power BI project through the development and distribution of content to the administration of Power BI for an organization.
BI developers will learn how to create sustainable and impactful Power BI datasets, reports, and dashboards. This includes connecting to data sources, shaping and enhancing source data, and developing an analytical data model. Additionally, top report and dashboard design practices are described using features such as Bookmarks and the Power KPI visual.
BI managers will learn how Power BI’s tools work together such as with the On-premises data gateway and how content can be staged and securely distributed via Apps. Additionally, both the Power BI Report Server and Power BI Premium are reviewed.
By the end of this book, you will be confident in creating effective charts, tables, reports or dashboards for any kind of data using the tools and techniques in Microsoft PowerBI.
What you will learn
- Build efficient data retrieval and transformation processes with the Power Query M Language
- Design scalable, user-friendly DirectQuery and Import Data Models
- Develop visually rich, immersive, and interactive reports and dashboards
- Maintain version control and stage deployments across development, test, and production environments
- Manage and monitor the Power BI Service and the On-premises data gateway
- Develop a fully on-premise solution with the Power BI Report Server
- Scale up a Power BI solution via Power BI Premium capacity and migration to Azure Analysis Services or SQL Server Analysis Services
Who this book is for
Business Intelligence professionals and existing Power BI users looking to master Power BI for all their data visualization and dashboarding needs will find this book to be useful. While understanding of the basic BI concepts is required, some exposure to Microsoft Power BI will be helpful.
Brett Powell is the owner of Frontline Analytics, a data and analytics consulting firm and Microsoft Power BI partner. He has worked with Power BI technologies since they were first introduced with the Power Pivot add-in for Excel 2010 and has contributed to the design and delivery of Microsoft BI solutions across retail, manufacturing, finance, and professional services. He is also the author of Microsoft Power BI Cookbook and a regular speaker at Microsoft technology events such as the Power BI World Tour and the Data & BI Summit. He regularly shares technical tips and examples on his blog, Insight Quest, and is a co-organizer of the Boston BI User Group.Sie lesen das E-Book in den Legimi-Apps auf:
Seitenzahl: 773
Veröffentlichungsjahr: 2018
Ähnliche
Mastering Microsoft Power BI
Copyright © 2018 Packt Publishing
All rights reserved. No part of this book may be reproduced, stored in a retrieval system, or transmitted in any form or by any means, without the prior written permission of the publisher, except in the case of brief quotations embedded in critical articles or reviews.
Every effort has been made in the preparation of this book to ensure the accuracy of the information presented. However, the information contained in this book is sold without warranty, either express or implied. Neither the author(s), nor Packt Publishing or its dealers and distributors, will be held liable for any damages caused or alleged to have been caused directly or indirectly by this book.
Packt Publishing has endeavored to provide trademark information about all of the companies and products mentioned in this book by the appropriate use of capitals. However, Packt Publishing cannot guarantee the accuracy of this information.
Commissioning Editor: Amey VarangaonkarAcquisition Editor:Divya PoojariContent Development Editor: Amrita NoronhaTechnical Editor: Sneha HanchateCopy Editors: Safis Editing, Vikrant PhadkayProject Coordinator:Shweta BirwatkarProofreader: Safis EditingIndexer:Aishwarya GangawaneGraphics:Jisha ChirayilProduction Coordinator:Shantanu Zagade
First published: March 2018
Production reference: 1280318
Published by Packt Publishing Ltd. Livery Place 35 Livery Street Birmingham B3 2PB, UK.
ISBN 978-1-78829-723-3
www.packtpub.com
To my mother, Cathy, and my brother, Dustin. I love you both.
Mapt is an online digital library that gives you full access to over 5,000 books and videos, as well as industry leading tools to help you plan your personal development and advance your career. For more information, please visit our website.
Why subscribe?
Spend less time learning and more time coding with practical eBooks and Videos from over 4,000 industry professionals
Improve your learning with Skill Plans built especially for you
Get a free eBook or video every month
Mapt is fully searchable
Copy and paste, print, and bookmark content
PacktPub.com
Did you know that Packt offers eBook versions of every book published, with PDF and ePub files available? You can upgrade to the eBook version at www.PacktPub.com and as a print book customer, you are entitled to a discount on the eBook copy. Get in touch with us at [email protected] for more details.
At www.PacktPub.com, you can also read a collection of free technical articles, sign up for a range of free newsletters, and receive exclusive discounts and offers on Packt books and eBooks.
Contributors
About the author
Brett Powell is the owner of Frontline Analytics, a data and analytics consulting firm and Microsoft Power BI partner. He has worked with Power BI technologies since they were first introduced with the Power Pivot add-in for Excel 2010 and has contributed to the design and delivery of Microsoft BI solutions across retail, manufacturing, finance, and professional services. He is also the author ofMicrosoft Power BI Cookbook and a regular speaker at Microsoft technology events such as the Power BI World Tour and the Data & BI Summit. He regularly shares technical tips and examples on his blog, Insight Quest, and is a co-organizer of the Boston BI User Group.
About the reviewer
Ruben Oliva Ramos is a computer engineer from Tecnologico of León Institute, with a master's degree in computer and electronics systems engineering and networking specialization from the University of Salle Bajio. He has more than 5 years' experience of developing web apps to control and monitor devices connected to Arduino and Raspberry Pi, using web frameworks and cloud services to build IoT applications. He has authored Raspberry Pi 3 Home Automation Projects, Internet of Things Programming with JavaScript, Advanced Analytics with R and Tableau, and SciPy Recipes for Packt.
Packt is searching for authors like you
If you're interested in becoming an author for Packt, please visit authors.packtpub.com and apply today. We have worked with thousands of developers and tech professionals, just like you, to help them share their insight with the global tech community. You can make a general application, apply for a specific hot topic that we are recruiting an author for, or submit your own idea.
Table of Contents
Title Page
Copyright and Credits
Mastering Microsoft Power BI
Dedication
Packt Upsell
Why subscribe?
PacktPub.com
Contributors
About the author
About the reviewer
Packt is searching for authors like you
Preface
Who this book is for
What this book covers
To get the most out of this book
Download the example code files
Download the color images
Conventions used
Get in touch
Reviews
Planning Power BI Projects
Power BI deployment modes
Corporate BI
Self-Service Visualization
Self-Service BI
Choosing a deployment mode
Project discovery and ingestion
Sample Power BI project template 
Sample template – Adventure Works BI
Power BI project roles
Dataset designer
Report authors
Power BI admin
Project role collaboration
Power BI licenses
Power BI license scenarios
Power BI Premium features
Data warehouse bus matrix
Dataset design process
Selecting the business process
Declaring the grain
Identifying the dimensions
Defining the facts
Data profiling
Dataset planning
Data transformations
Import versus DirectQuery
Import mode 
DirectQuery mode
Sample project analysis
Summary
Connecting to Sources and Transforming Data with M
Query design per dataset mode
Import mode dataset queries
DirectQuery dataset queries
Data sources
Authentication
Data source settings
Privacy levels
Power BI as a data source
Power BI Desktop options
Global options
CURRENT FILE options
SQL views
SQL views versus M queries
SQL view examples
Date dimension view
Mark As Date Table
Product Dimension view
Slowly-changing dimensions
M queries
Data Source Parameters
Staging Queries
DirectQuery staging
Fact and dimension queries
Source Reference Only
M query summary
Excel workbook – Annual Sales Plan
Data types
Item access in M
DirectQuery report execution
Bridge Tables Queries
Parameter Tables
Security Tables
Query folding
Partial query folding
M Query examples
Trailing three years filter
Customer history column
Derived column data types
Product dimension integration
R script transformation
M editing tools
Advanced Editor
Visual Studio Code
Visual Studio
Summary
Designing Import and DirectQuery Data Models
Dataset layers
Dataset objectives
Competing objectives
External factors
The Data Model 
The Relationships View
The Data View
The Report View
Fact tables
Fact table columns
Fact column data types
Fact-to-dimension relationships
Dimension tables
Hierarchies
Custom sort
Bridge tables
Parameter tables
Measure groups
Last refreshed date
Measure support logic
Relationships
Uniqueness 
Ambiguity
Single-direction relationships
Direct flights only
Bidirectional relationships
Shared dimensions
Date dimensions 
The CROSSFILTER function
Model metadata
Visibility
Column metadata
Default Summarization
Data format
Data category
Field descriptions
Optimizing performance
Import
Columnar compression
Memory analysis via DMVs
DirectQuery 
Optimized DAX functions
Columnstore and HTAP
Summary
Developing DAX Measures and Security Roles
DAX measures
Filter context
SQL equivalent
Measure evaluation process
Row context
Scalar and table functions
The CALCULATE() function
Related tables
The FILTER() function
DAX variables
Base measures
Measure support expressions
KPI Targets
Current and prior periods
Date intelligence metrics
Current versus prior and growth rates
Rolling periods
Dimension metrics
Missing dimensions
Ranking metrics
Dynamic ranking measures
Security roles
Dynamic row-level security
Performance testing
DAX Studio
Tracing a Power BI dataset via DAX Studio
Summary
Creating and Formatting Power BI Reports
Report planning
Power BI report architecture
Live connections to Power BI datasets
Customizing Live connection reports
Switching source datasets
Visualization best practices
Visualization anti-patterns
Choosing the visual
Tables versus charts
Chart selection
Visual interactions
Edit interactions
What-if parameters
Slicers
Slicer synchronization
Custom slicer parameters
Report filter scopes
Report filter conditions
Report and page filters
Page filter or slicer?
Relative date filtering
Visual-level filtering
Top N visual-level filters
Visualization formatting
Visual-level formatting
Line and column charts
Tooltips
Report page tooltips
Column and line chart conditional formatting
Column chart conditional formatting
Line chart conditional formatting
Table and matrix
Table and matrix conditional formatting
Values as rows
Scatter charts
Map visuals
Bubble map
Filled map
Mobile-optimized reports
Responsive visuals
Report design summary
Summary
Applying Custom Visuals, Animation, and Analytics
Drillthrough report pages
Custom labels and the back button
Multi-column drillthrough
Bookmarks
Selection pane and the Spotlight property
Custom report navigation
View mode
ArcGIS Map visual for Power BI
ArcGIS Maps Plus subscriptions
Waterfall chart breakdown
Analytics pane
Trend Line
Forecast line
Quick Insights 
Explain the increase/decrease
Custom visuals
Adding a custom visual
Power KPI visual
Chiclet Slicer
Impact Bubble Chart
Dot Plot by Maq Software
Animation and data storytelling
Play axis for scatter charts
Pulse Chart
Summary
Designing Power BI Dashboards and Architectures
Dashboards versus reports
Dashboard design
Visual selection
Layout
Navigation pane
Full screen mode
Supporting tiles
Custom date filters
Multi-dashboard architectures
Single-dashboard architecture
Multiple-dashboard architecture
Organizational dashboard architecture
Multiple datasets
Dashboard tiles
Tile details and custom links
Images and text boxes
SQL Server Reporting Services
Excel workbooks
Live report pages
Mobile-optimized dashboards
Summary
Managing Application Workspaces and Content
Application workspaces
Workspace roles and rights
Workspace admins
Workspace members
My Workspace
Staged deployments
Workspace datasets
Power BI REST API
Client application ID
Workspace and content IDs
PowerShell sample scripts
Dashboard data classifications
Version control
OneDrive for Business version history
Source control for M and DAX code
Metadata management
Field descriptions
Creating descriptions
View field descriptions
Metadata reporting
Query field descriptions
Standard metadata reports
Server and database parameters
Querying the DMVs from Power BI
Integrating and enhancing DMV data
Metadata report pages
Summary
Managing the On-Premises Data Gateway
On-premises data gateway planning
Top gateway planning tasks
Determining whether a gateway is needed
Identifying where the gateway should be installed
Defining the gateway infrastructure and hardware requirements
On-premises data gateway versus personal mode
Gateway clusters
Gateway architectures
Gateway security
Gateway configuration
The gateway service account
TCP versus HTTPS mode 
Managing gateway clusters
Gateway administrators
Gateway data sources and users
PowerShell support for gateway clusters
Troubleshooting and monitoring gateways
Restoring, migrating, and taking over a gateway
Gateway log files
Performance Monitor counters
Scheduled data refresh
DirectQuery datasets
Single sign-on to DirectQuery sources via Kerberos
Live connections to Analysis Services models
Azure Analysis Services refresh
Dashboard cache refresh
Summary
Deploying the Power BI Report Server
Planning for the Power BI Report Server
Feature differences with the Power BI service
Parity with SQL Server Reporting Services
Data sources and connectivity options
Hardware and user licensing
Pro licenses for report authors
Alternative and hybrid deployment models
Report Server reference topology
Installation
Hardware and software requirements
Analysis Services Integrated
Retrieve the Report Server product key
Migrating from SQL Server Reporting Services
Configuration
Service Account
Remote Report Server Database
Office Online Server for Excel Workbooks
Upgrade cycles 
Report Server Desktop Application
Running desktop versions side by side
Report Server Web Portal
Scheduled data refresh
Data source authentication
Power BI mobile applications
Report server administration
Securing Power BI report content
Execution logs
Scale Power BI Report Server
Summary
Creating Power BI Apps and Content Distribution
Content distribution methods
Power BI apps
Licensing apps
App deployment process
User permissions
Publishing apps
Installing apps
Apps on Power BI mobile
App updates
Dataset-to-workspace relationship
Self-Service BI workspace
Self-Service content distribution
Risks to Self-Service BI
Sharing dashboards and reports
Sharing scopes
Sharing versus Power BI apps
SharePoint Online embedding
Custom application embedding
Publish to web
Data alerts
Microsoft Flow integration
Email Subscriptions
Analyze in Excel
Power BI Publisher for Excel
Summary
Administering Power BI for an Organization
Data governance for Power BI
Implementing data governance
Azure Active Directory
Azure AD B2B collaboration
Licensing external users
Conditional access policies
Power BI Admin Portal
Usage metrics
Users and Audit logs
Tenant settings
Embed Codes
Organizational Custom visuals
Usage metrics reports
Audit logs
Audit log monitoring solutions
Audit logs solution template
Power BI Premium capacities
Capacity allocation
Create, size, and monitor capacities
Change capacity size
Monitor premium capacities
App workspace assignment
Capacity admins
Summary
Scaling with Premium and Analysis Services
Power BI Premium
Power BI Premium capabilities
Corporate Power BI datasets
Limitation of Corporate BI datasets – Reusability
Premium capacity nodes
Frontend versus backend resources
Power BI Premium capacity allocation
Corporate and Self-Service BI capacity 
Power BI Premium resource utilization
Data model optimizations
Report and visualization optimizations
Premium capacity estimations
Analysis Services
Analysis Services Models versus Power BI Desktop
Scale
Usability
Development and management tools
Azure Analysis Services versus SSAS
SSAS to Azure AS Migration
Provision Azure Analysis Services 
Migration of Power BI Desktop to Analysis Services
Summary
Other Books You May Enjoy
Leave a review - let other readers know what you think
Preface
Microsoft Power BI is a leading business intelligence and analytics platform that supports both self-service data visualization and exploration as well as enterprise BI deployments. Power BI consists of cloud services, mobile applications, a data modeling and report authoring application, and other utilities, including the On-premises data gateway. Additionally, organizations can deploy Power BI reports on-premise via the Power BI Report Server and scale their deployments with Power BI Premium capacity and Analysis Services.
This book provides an end-to-end analysis of Power BI tools and features, from planning a Power BI project to distributing Power BI apps to large groups of users. You'll be familiarized with all the fundamental concepts and see how Power BI datasets, reports, and dashboards can be designed to deliver insights and rich, interactive experiences. You'll also become knowledgeable about management and administration topics such as the allocation of Power BI Premium capacities, Azure Active Directory security groups, conditional access policies, and staged deployments of Power BI content. This book will encourage you to take advantage of these powerful features and follow thoughtful, consistent practices in deploying Power BI for your organization.
Who this book is for
This book is intended for business intelligence professionals responsible for either the development of Power BI solutions or the management and administration of a Power BI deployment. BI developers can use this as a reference guide to features and techniques to enhance their solutions. Likewise, BI managers interested in a broad conceptual understanding, as well as processes and practices to inform their delivery of Power BI, will find this a useful resource. Experience of creating content on Power BI Desktop and sharing content on the Power BI service will be helpful.
What this book covers
Chapter 1, Planning Power BI Projects, discusses alternative deployment modes for Power BI, team and project roles, and licensing. Additionally, an example project template and its corresponding planning and dataset design processes are described.
Chapter 2, Connecting to Sources and Transforming Data with M, depicts the data access layer supporting a Power BI dataset, including data sources and fact and dimension table queries. Concepts of the Power Query M language, such as query folding and parameters, are explained and examples of custom M queries involving conditional and dynamic logic are given.
Chapter 3, Designing Import and DirectQuery Data Models, reviews the components of the data model layer and design techniques in support of usability, performance, and other objectives. These topics include relationship cross-filtering, custom sort orders, hierarchies, and metadata.
Chapter 4, Developing DAX Measures and Security Roles, covers the implementation of analysis expressions reflecting business definitions and common analysis requirements. Primary DAX functions, concepts, and use cases such as date intelligence, row-level security roles, and performance testing are examined.
Chapter 5, Creating and Formatting Power BI Reports, describes a report planning process, data visualization practices, and report design fundamentals, including visual selection and filter scopes. Top report development features, such as slicer visuals, tool tips, and conditional formatting are also reviewed.
Chapter 6, Applying Custom Visuals, Animation, and Analytics, examines powerful interactive and analytical features, including drillthrough report pages, bookmarks, the Analytics pane, ArcGIS Maps, and the waterfall charts. Additionally, examples of custom visuals, such as the Power KPI, and the capabilities of animation to support data storytelling are provided.
Chapter 7, Designing Power BI Dashboards and Architectures, provides guidance on visual selection, layout, and supporting tiles to drive effective dashboards. Alternative multi-dashboard architectures, such as an organizational dashboard architecture, are reviewed, is the configuration of dashboard tiles and mobile optimized dashboards.
Chapter 8, Managing Application Workspaces and Content, features the role and administration of app workspaces in the context of Power BI solutions and staged deployments. Additionally, the Power BI REST API, content management features, and practices are reviewed, including field descriptions and version history.
Chapter 9, Managing the On-Premises Data Gateway, covers top gateway planning considerations, including alternative gateway architectures, workloads, and hardware requirements. Gateway administration processes and tools are described, such as the manage gateways portal, gateway log files, and PowerShell Gateway commands.
Chapter 10, Deploying the Power BI Report Server, contrasts the Power BI Report Server with the Power BI cloud service and provides guidance on deployment topics such as licensing, reference topology, configuration, administration, and upgrade cycles.
Chapter 11, Creating Power BI Apps and Content Distribution, walks through the process of publishing and updating apps for groups of users. Additionally, other common distribution methods are covered, such as the sharing of reports and dashboards, email subscriptions, data-alert-driven emails, and embedding Power BI content in SharePoint Online.
Chapter 12, Administering Power BI for an Organization, highlights data governance for self-service and corporate BI, Azure Active Directory features such as Conditional Access Policies, and the Power BI admin portal. Details are provided about configuring Power BI service tenant settings, managing Power BI Premium capacities, and the tools available to monitor Power BI activities.
Chapter 13, Scaling with Premium and Analysis Services, reviews the capabilities of Power BI Premium and alternative methods for allocating premium capacity. Additionally, Power BI datasets are contrasted with Analysis Services models, Azure Analysis Services is contrasted with SQL Server Analysis Services, and the migration of a Power BI dataset to an Analysis Services model is described.
To get the most out of this book
A Power BI Pro license and access to the Power BI service is necessary to follow many of the topics and examples in this book. The assignment of the Power BI Service Administrator role within the Office 365 admin center, as well as administrative access to an On-premises data gateway, would also be helpful for the second half of this book. It's assumed that readers are familiar with the main user interfaces of Power BI Desktop and have some background in business intelligence or information technology.
The primary data source for the examples in this book was the AdventureWorks data warehouse sample database for SQL Server 2016 CTP3. A SQL Server 2017 Developer Edition database engine instance was used to host the sample database. For the import mode dataset, an Excel workbook stored the sales plan data. For the DirectQuery dataset, the sales plan data was stored in the sample SQL Server database.
The Power BI Desktop files and specific queries and scripts utilized in the book are included in the code bundle. However, the source data and database are not included in the code bundle. Additionally, the database used by the book contains objects not included in the downloadable sample database, such as SQL views for each fact and dimension table. Therefore, even with access to a SQL Server 2017 database engine instance and the sample AdventureWorks data warehouse database, the examples in the book cannot be completely reproduced.
Download the example code files
You can download the example code files for this book from your account at www.packtpub.com. If you purchased this book elsewhere, you can visit www.packtpub.com/support and register to have the files emailed directly to you.
You can download the code files by following these steps:
Log in or register at
www.packtpub.com
.
Select the
SUPPORT
tab.
Click on
Code Downloads & Errata
.
Enter the name of the book in the
Search
box and follow the onscreen instructions.
Once the file is downloaded, please make sure that you unzip or extract the folder using the latest version of:
WinRAR/7-Zip for Windows
Zipeg/iZip/UnRarX for Mac
7-Zip/PeaZip for Linux
The code bundle for the book is also hosted on GitHub at https://github.com/PacktPublishing/Mastering-Microsoft-Power-BI. In case there's an update to the code, it will be updated on the existing GitHub repository.
We also have other code bundles from our rich catalog of books and videos available at https://github.com/PacktPublishing/. Check them out!
Download the color images
We also provide a PDF file that has color images of the screenshots/diagrams used in this book. You can download it here: http://www.packtpub.com/sites/default/files/downloads/MasteringMicrosoftPowerBI_ColorImages.pdf.
Conventions used
There are a number of text conventions used throughout this book.
CodeInText: Indicates code words in text, database table names, folder names, filenames, file extensions, pathnames, dummy URLs, user input, and Twitter handles. Here is an example: "Mount the downloaded WebStorm-10*.dmg disk image file as another disk in your system."
A block of code is set as follows:
SELECTP.ProductKey as 'Product Key', P.ProductAlternateKey as 'Product Alternate Key', P.EnglishProductName AS 'Product Name', ISNULL(S.EnglishProductSubcategoryName, 'Undefined') 'ProductSubcategory'
When we wish to draw your attention to a particular part of a code block, the relevant lines or items are set in bold:
Internet Sales Amount (Import) =SUMX('Internet Sales','Internet Sales'[Order Quantity]*'InternetSales'[Unit Price])Internet Sales Amount (DirectQuery) =SUM('Internet Sales'[Sales Amount])
Bold: Indicates a new term, an important word, or words that you see onscreen. For example, words in menus or dialog boxes appear in the text like this. Here is an example: "Select System info from the Administration panel."
Get in touch
Feedback from our readers is always welcome.
General feedback: Email [email protected] and mention the book title in the subject of your message. If you have questions about any aspect of this book, please email us at [email protected].
Errata: Although we have taken every care to ensure the accuracy of our content, mistakes do happen. If you have found a mistake in this book, we would be grateful if you would report this to us. Please visit www.packtpub.com/submit-errata, selecting your book, clicking on the Errata Submission Form link, and entering the details.
Piracy: If you come across any illegal copies of our works in any form on the Internet, we would be grateful if you would provide us with the location address or website name. Please contact us at [email protected] with a link to the material.
If you are interested in becoming an author: If there is a topic that you have expertise in and you are interested in either writing or contributing to a book, please visit authors.packtpub.com.
Reviews
Please leave a review. Once you have read and used this book, why not leave a review on the site that you purchased it from? Potential readers can then see and use your unbiased opinion to make purchase decisions, we at Packt can understand what you think about our products, and our authors can see your feedback on their book. Thank you!
For more information about Packt, please visit packtpub.com.
Planning Power BI Projects
In this chapter, we will walk through a Power BI project planning process from the perspective of an organization with an on-premises data warehouse and a supporting nightly extract-transform-load (ETL) process but no existing SSAS servers or IT-approved Power BI datasets. The business intelligence team will be responsible for the development of a Power BI dataset, including source queries, relationships, and metrics, in addition to a set of Power BI reports and dashboards.
Almost all business users will consume the reports and dashboards in the Power BI online service and via the Power BI mobile apps, but a few business analysts will also require the ability to author Power BI and Excel reports for their teams based on the new dataset. Power BI Pro licenses and Power BI Premium capacity will be used to support the development, scalability, and distribution requirements of the project.
In this chapter, we will review the following topics:
Power BI deployment modes
Project discovery and ingestion
Power BI project roles
Power BI licenses
Dataset design process
Dataset planning
Import and DirectQuery datasets
Power BI deployment modes
Organizations can choose to deliver and manage their Power BI deployment through IT and standard project workflows or to empower certain business users to take advantage of Self-Service BI capabilities with tools such as Power BI Desktop and Excel. In many scenarios, a combination of IT resources, such as the On-premises data gateway and Power BI Premium capacity, can be combined with the business users' knowledge of requirements and familiarity with data analysis and visualization.
Organizations may also utilize alternative deployment modes per project or with different business teams based on available resources and the needs of the project. The greatest value from Power BI deployments can be obtained when the technical expertise and governance of Corporate BI solutions are combined with the data exploration and analysis features, which can be made available to all users. The scalability and accessibility of Power BI solutions to support thousands of users, including read-only users who have not been assigned Power BI Pro licenses, is made possible by provisioning Power BI Premium capacity, as described in the final three chapters of this book.
Corporate BI
The Corporate BI delivery approach in which the BI team develops and maintains both the Power BI dataset (data model) and the required report visualizations is a common deployment option, particularly for large-scale projects and projects with executive-level sponsors or stakeholders. This is the approach followed in this chapter and throughout this book, as it offers maximum control over top BI objectives, such as version control, scalability, usability, and performance.
However, as per the following Power BI deployment modes diagram, there are other approaches in which business teams own or contribute to the solution:
A Power BI dataset is a semantic data model composed of data source queries, relationships between dimensions and fact tables, and measurement calculations. The Power BI Desktop application can be used to create datasets as well as merely connect to existing datasets to author Power BI reports. The Power BI Desktop shares the same data retrieval and modeling engines as the latest version of SQL Server Analysis Services (SSAS) in tabular mode and Azure Analysis Services, Microsoft's enterprise BI modeling solution.Many BI/IT organizations utilize Analysis Services models as the primary data source for Power BI projects and it's possible to migrate Power BI Desktop files (.pbix) to Analysis Services models, as described in Chapter 13,Scaling with Premium and Analysis Services.
Self-service approaches can benefit both IT and business teams, as they can reduce IT resources, project timelines, and provide the business with a greater level of flexibility as their analytical needs change. Additionally, Power BI projects can be migrated across deployment modes over time as required skills and resources change. However, greater levels of self-service and shared ownership structures can also increase the risk of miscommunication and introduce issues of version control, quality, and consistency.
Self-Service Visualization
In the Self-Service Visualization approach, the dataset is created and maintained by the IT organization's BI team, but certain business users with Power BI Pro licenses create reports and dashboards for consumption by other users. In many scenarios, business analysts are already comfortable with authoring reports in Power BI Desktop (or, optionally, Excel) and can leverage their business knowledge to rapidly develop useful visualizations and insights. Given ownership of the dataset, the BI team can be confident that only curated data sources and standard metric definitions are used in reports and can ensure that the dataset remains available, performant, and updated, or refreshed as per business requirements.
Self-Service BI
In the Self-Service BI approach, the BI organization only contributes essential infrastructure and monitoring, such as the use of an On-premises data gateway and possibly Power Premium capacity to support the solution. Since the business team maintains control of both the dataset and the visualization layer, the business team has maximum flexibility to tailor its own solutions including data source retrieval, transformation, and modeling. This flexibility, however, can be negated by a lack of technical skills (for example, DAX measures) and a lack of technical knowledge such as the relationships between tables in a database. Additionally, business-controlled datasets can introduce version conflicts with corporate semantic models and generally lack the resilience, performance, and scalability of IT-owned datasets.
Choosing a deployment mode
Larger organizations with experience of deploying and managing Power BI often utilize a mix of deployment modes depending on the needs of the project and available resources. For example, a Corporate BI solution with a set of standard IT developed reports and dashboards distributed via a Power BI app may be extended by assigning Power BI Pro licenses to certain business users who have experience or training in Power BI report design. These users could then leverage the existing data model and business definitions maintained by IT to create new reports and dashboards and distribute this content in a separate Power BI app to distinguish ownership.
Another common scenario is a proof-of-concept (POC) or small-scale self-service solution developed by a business user or a team to be transitioned to a formal, IT-owned, and managed solution. Power BI Desktop's rich graphical interfaces at each layer of the application (query editor, data model, and report canvas) make it possible and often easy for users to create useful models and reports with minimal experience and little to no code. It's much more difficult, of course, to deliver consistent insights across business functions (that is, finance, sales, and marketing) and at scale in a secure, governed environment. The IT organization can enhance the quality and analytical value of these assets as well as provide robust governance and administrative controls to ensure that the right data is being accessed by the right people.
The following list of fundamental questions will help guide a deployment mode decision:
Who will own the data model?
Experienced dataset designers and other IT professionals are usually required to support complex data transformations, analytical data modeling, large data sizes, and security rules, such as RLS roles, as described in
Chapter 4
,
Developing DAX Measures and Security Roles
If the required data model is relatively small and simple, or if the requirements are unclear, the business team may be best positioned to create at least the initial iterations of the model
The data model could be created with Analysis Services or Power BI Desktop
Who will own the reports and dashboards?
Experienced Power BI report developers with an understanding of corporate standards and data visualization best practices can deliver a consistent user experience
Business users can be trained on report design and development practices and are well-positioned to manage the visualization layer, given their knowledge of business needs and questions
How will the Power BI content be managed and distributed?
A staged deployment across development, test, and production environments, as described in
Chapter 8
,
Managing Application Workspaces and Content
, helps to ensure that quality, validated content is published. This approach is generally exclusive to Corporate BI projects.
Sufficient Power BI Premium capacity is required to support distribution to Power BI Free users and either large datasets or demanding query workloads.
Self-Service BI content can be assigned to Premium Capacity, but organizations may wish to limit the scale or scope of these projects to ensure that provisioned capacity is being used efficiently.
Project discovery and ingestion
A set of standard questions within a project template form can be used to initiate Power BI projects. Business guidance on these questions informs the BI team of the high-level technical needs of the project and helps to promote a productive project kickoff.
By reviewing the project template, the BI team can ask the project sponsor or relevant subject matter experts (SMEs) targeted questions to better understand the current state and the goals of the project.
Sample Power BI project template
The primary focus of the project-planning template and the overall project planning stage is on the data sources and the scale and structure of the Power BI dataset required. The project sponsor or business users may only have an idea of several reports, dashboards, or metrics needed but, as a Corporate BI project, it's essential to focus on where the project fits within an overall BI architecture and the long-term return on investment (ROI) of the solution. For example, BI teams would look to leverage any existing Power BI datasets or SSAS tabular models applicable to the project and would be sensitive to version-control issues.
Sample template – Adventure Works BI
The template is comprised of two tables. The first table answers the essential who and when questions so that the project can be added to the BI team's backlog. The BI team can use this information to plan their engagements across multiple ongoing and requested Power BI projects and to respond to project stakeholders, such as Vickie Jacobs, VP of Group Sales, in this example:
10/15/2017
Project Sponsor
Vickie Jacobs, VP of Group Sales
Primary Stakeholders
Adventure Works Sales Adventure Works Corp
Power BI Author(s)
Mark Langford, Sales Analytics Manager
The following table is a list of questions that describe the project's requirements and scope. For example, the number of users that will be read-only consumers of Power BI reports and dashboards, and the number of self-service users that will need Power BI Pro licenses to create Power BI content will largely impact the total cost of the project.
Likewise, the amount of historical data to include in the dataset (2 years, 5 years?) can significantly impact performance scalability:
Topic
#
Question
Business Input
Data sources
1
Can you describe the required data? (For example, sales, inventory, shipping).
Internet Sales, Reseller Sales, and the Sales and Margin Plan. We need to analyze total corporate sales, online, and reseller sales, and compare these results to our plan.
Data sources
2
Is all of the data required for your project available in the data warehouse (SQL Server)?
No
Data Sources
3
What other data sources (if any) contain all or part of the required data (for example, Web, Oracle, Excel)?
The Sales and Margin Plan is maintained in Excel.
Security
4
Should certain users be prevented from viewing some or all of the data?
Yes, sales managers and associates should only see data for their sales territory group. VPs of sales, however, should have global access.
Security
5
Does the data contain any PCII or sensitive data?
No, not that I’m aware of
Scale
6
Approximately, how many years of historical data are needed?
3-4
Data refresh
7
How often does the data need to be refreshed?
Daily
Data refresh
8
Is there a need to view data in real time (as it changes)?
No
Distribution
9
Approximately, how many users will need to view reports and dashboards?
200
Distribution
10
Approximately, how many users will need to create reports and dashboards?
3-4
Version control
11
Are there existing reports on the same data? If so, please describe.
Yes, there are daily and weekly sales snapshot reports available on the portal. Additionally, our team builds reports in Excel that compare actuals to plan.
Version Control
12
Is the Power BI solution expected to replace these existing reports?
Yes, we would like to exclusively use Power BI going forward.
A business analyst inside the IT organization can partner with the business on completing the project ingestion template and review the current state to give greater context to the template. Prior to the project kickoff meeting, the business analyst can meet with the BI team members to review the template and any additional findings or considerations.
Power BI project roles
Following the review of the project template and input from the business analyst, members of the Power BI team can directly engage the project sponsor and other key stakeholders to officially engage in the project. These stakeholders include subject matter experts on the data source systems, business team members knowledgeable of the current state of reporting and analytics, and administrative or governance personnel with knowledge of organizational policies, available licenses, and current usage.
New Power BI projects of any significant scale and long-term adoption of Power BI within organizations require Dataset Designers, Report Authors, and a Power BI Admin(s), as illustrated in the following diagram:
Each of the three Power BI project roles and perhaps longer-term roles as part of a business intelligence team entail a distinct set of skills and responsibilities. It can be advantageous in a short-term or POC scenario for a single user to serve as both a dataset designer and a report author. However, the Power BI platform and the multi-faceted nature of Corporate BI deployments is too broad and dynamic for a single BI professional to adequately fulfill both roles. It's therefore recommended that team members either self-select or are assigned distinct roles based on their existing skills and experience and that each member develops advanced and current knowledge relevant to their role. A BI manager and/or a project manager can help facilitate effective communication across roles and between the BI team and other stakeholders, such as project sponsors.
Dataset designer
Power BI report visualizations and dashboard tiles are built on top of datasets, and each Power BI report is associated with a single dataset. Power BI datasets can import data from multiple data sources on a refresh schedule or can be configured to issue queries directly to a single data source to resolve report queries. Datasets are therefore a critical component of Power BI projects and their design has tremendous implications regarding user experience, query performance, source system and Power BI resource utilization, and more.
The dataset designer is responsible for the data access layer of the Power BI dataset, including the authentication to data sources and the M queries used to define the tables of the data model. Additionally, the dataset designer defines the relationships of the model and any required row-level security roles, and develops the DAX measure expressions for use in reports, such as year-to-date (YTD) sales. Given these responsibilities, the dataset designer should regularly communicate with data source owners or SMEs, as well as report authors. For example, the dataset designer needs to be aware of changes to data sources so that data access queries can be revised accordingly and report authors can advise of any additional measures or columns necessary to create new reports. Furthermore, the dataset designer should be aware of the performance and resource utilization of deployed datasets and should work with the Power BI admin on issues such as Power BI Premium capacity.
As per the Power BI team toles diagram, there are usually very few dataset designers in a team while there may be many report authors. This is largely due to the organizational objectives of version control and reusability, which leads to a small number of large datasets. Additionally, robust dataset development requires knowledge of the M and DAX functional programming languages, dimensional modeling practices, and business intelligence. Database experience is also very helpful. If multiple dataset designers are on a team they should look to standardize their development practices so that they can more easily learn and support each other's solutions.
Report authors
Report authors interface directly with the consumers of reports and dashboards or a representative of this group. In a self-service deployment mode or a hybrid project (business and IT), a small number of report authors may themselves work within the business. Above all else, report authors must have a clear understanding of the business questions to be answered and the measures and attributes (columns) needed to visually analyze and answer these questions. The report author should also be knowledgeable of visualization best practices, such as symmetry and minimalism, in addition to any corporate standards for report formatting and layout.
Power BI Desktop provides a rich set of formatting properties and analytical features, giving report authors granular control over the appearance and behavior of visualizations.
Report authors should be very familiar with all standard capabilities, such as conditional formatting, drilldown, drillthrough, and cross-highlighting, as they often lead demonstrations or training sessions. Additionally, report authors should understand the organization's policies on custom visuals available in the MS Office store and the specific use cases for top or popular custom visuals.
Power BI admin
A Power BI admin is focused on the overall deployment of Power BI within an organization in terms of security, governance, and resource utilization. Power BI admins are not involved in the day-to-day activities of specific projects but rather configure and manage settings in Power BI that align with the organization's policies. A Power BI admin, for example, monitors the adoption of Power BI content, identifies any high-risk user activities, and manages any Power BI Premium capacities that have been provisioned. Additionally, Power BI admins use Azure Active Directory security groups within the Power BI admin portal to manage access to various Power BI features, such as sharing Power BI content with external organizations.
Users assigned to the Power BI service administrator role obtain access to the Power BI admin portal and the rights to configure Power BI Tenant settings. For example, in the following image, Anna Sanders is assigned to the Power BI service administrator role within the Office 365 admin center:
The Power BI service administrator role allows Anna to access the Power BI admin portal to enable or disable features, such as exporting data and printing reports and dashboard. BI and IT managers that oversee Power BI deployments are often assigned to this role, as it also provides the ability to manage Power BI Premium capacities and access to standard monitoring and usage reporting. Note that only global administrators of Office 365 can assign users to the Power BI service administrator role.
The Power BI admin should have a clear understanding of the organizational policy on the various tenant settings, such as whether content can be shared with external users. For most tenant settings, the Power BI service administrator can define rules in the Power BI admin portal to include or exclude specific security groups. For example, external sharing can be disabled for the entire organization except for a specific security group of users. Most organizations should assign two or more users to the Power BI service administrator role and ensure these users are trained on the administration features specific to this role. Chapter 12, Administering Power BI for an Organization, contains details on the Power BI admin portal and other administrative topics.
Project role collaboration
Communicating and documenting project role assignments during the planning stage promotes the efficient use of time during the development and operations phases. For organizations committed to the Power BI platform as a component of a longer-term data strategy, the project roles may become full-time positions.
For example, BI developers with experience in DAX and/or SSAS tabular databases may be hired as dataset designers while BI developers with experience in data visualization tools and corporate report development may be hired as report authors:
Name
Project role
Brett Powell
Dataset Designer
Jennifer Lawrence
Report Author
Anna Sanders
Power BI Service Admin
Mark Langford
Report Author
Stacy Loeb
QA Tester
Power BI licenses
Users can be assigned either a Power BI Free or a Power BI Pro license. Power BI licenses (Pro and Free) can be purchased individually in the Office 365 admin center, and a Power Pro license is included with an Office 365 Enterprise E5 subscription. A Power BI Pro license is required to publish content to Power BI app workspaces, consume a Power BI app that's not assigned to Power BI Premium capacity, and utilize other advanced features, as shown in the following table:
Feature
Power BI Free
Power BI Pro
Connect to 70+ data sources
Yes
Yes
Publish to web
Yes
Yes
Peer-to-peer sharing
No
Yes
Export to Excel, CSV, PowerPoint
Yes
Yes
Email subscriptions
No
Yes
App workspaces and apps
No
Yes
Analyze in Excel, Analyze in Power BI Desktop
No
Yes
With Power BI Premium, users with Power BI Free licenses are able to access and view Power BI apps of reports and dashboards that have been assigned to premium capacities. This access includes consuming the content via the Power BI mobile application. Additionally, Power BI Pro users can share dashboards with Power BI Free users if the dashboard is contained in a Premium workspace. Power BI Pro licenses are required for users that create or distribute Power BI content, such as connecting to published datasets from Power BI Desktop or Excel.
In this sample project example, only three or four business users may need Power BI Pro licenses to create and share reports and dashboards. Mark Langford, a data analyst for the sales organization, requires a Pro license to analyze published datasets from Microsoft Excel. Jennifer Lawrence, a Corporate BI developer and report author for this project, requires a Pro license to publish Power BI reports to app workspaces and distribute Power BI apps to users.
The following image from the Office 365 admin center identifies the assignment of a Power BI Pro license to a report author:
As a report author, Jennifer doesn't require any custom role assignment as per the Roles property of the preceding image. If Jennifer becomes responsible for administering Power BI in the future, the Edit option for the Roles property can be used to assign her to the Power BI service administrator role, as described in the Power BI project roles section earlier.
The approximately 200 Adventure Works sales team users who only need to view the content can be assigned Free licenses and consume the published content via Power BI apps associated with Power BI Premium capacity. Organizations can obtain more Power BI Pro licenses and Power BI Premium capacity (virtual cores, RAM) as usage and workloads increase.
The administration and governance of Power BI deployments at scale involve several topics (such as authentication, activity monitoring, and auditing), and Power BI provides features dedicated to simplifying administration.
These topics and features are reviewed in Chapter 12, Administering Power BI for an Organization.
Power BI license scenarios
The optimal mix of Power BI Pro and Power BI Premium licensing in terms of total cost will vary based on the volume of users and the composition of these users between read-only consumers of content versus Self-Service BI users. In relatively small deployments, such as 200 total users, a Power BI Pro license can be assigned to each user regardless of self-service usage and Power BI Premium capacity can be avoided. Be advised, however, that, as per the following Power BI Premium features section, there are other benefits to licensing Power BI Premium capacity that may be necessary for certain deployments, such as larger datasets or more frequent data refreshes.
If an organization consists of 700 total users with 600 read-only users and 100 self-service users (content creators), it's more cost effective to assign Power BI Pro licenses to the 100 self-service users and to provision Power BI Premium capacity to support the other 600 users. Likewise, for a larger organization with 5,000 total users and 4,000 self-service users, the most cost-effective licensing option is to assign Power Pro licenses to the 4,000 self-service users and to license Power BI Premium for the remaining 1,000 users.
See Chapter 12, Administering Power BI for an Organization, and Chapter 13, Scaling with Power BI Premium and SSAS, for additional details on aligning Power BI licenses and resources with the needs of Power BI deployments.
Power BI Premium features
An organization may choose to license Power BI Premium capacities for additional or separate reasons beyond the ability to distribute Power BI content to read-only users without incurring per-user license costs. Significantly, greater detail on Power BI Premium features and deployment considerations is included in Chapter 13, Scaling with Premium and Analysis Services.
The following table identifies several of the top additional benefits and capabilities of Power BI Premium:
Data warehouse bus matrix
The fundamentals of the dataset should be designed so that it can support future BI and analytics projects and other business teams requiring access to the same data. The dataset will be tasked with delivering both accurate and consistent results across teams and use cases as well as providing a familiar and intuitive interface for analysis.
To promote reusability and project communication, a data warehouse bus matrix of business processes and shared dimensions is recommended:
Each row reflects an important and recurring business process, such as the monthly close of the general ledger, and each column represents a business entity, which may relate to one or several of the business processes. The shaded rows (Internet Sales, Reseller Sales, and Sales Plan) identify the business processes that will be implemented as their own star schemas for this project. The business matrix can be developed in collaboration with business stakeholders, such as the corporate finance manager, as well as source system and business intelligence or data warehouse SMEs.
Additional business processes, such as maintaining product inventory levels, could potentially be added to the same Power BI dataset in a future project. Importantly, these future additions could leverage existing dimension tables, such as a Product table, including its source query, column metadata, and any defined hierarchies.
Dataset design process
With the data warehouse bus matrix as a guide, the business intelligence team can work with representatives from the relevant business teams and project sponsors to complete the following four-step dataset design process:
Select the business process.
Declare the grain.
Identify the dimensions.
Define the facts.
Selecting the business process
Ultimately each business process will be represented by a fact table with a star schema of many-to-one relationships to dimensions. In a discovery or requirements gathering process it can be difficult to focus on a single business process in isolation as users regularly analyze multiple business processes simultaneously or need to. Nonetheless, it's essential that the dataset being designed reflects low level business activities (for example, receiving an online sales order) rather than a consolidation or integration of distinct business processes such as a table with both online and reseller sales data:
Confirm that the answer provided to the first question of the project template regarding data sources is accurate:
In this project, the required business processes are
Internet Sales
,
Reseller Sales
,
Annual Sales and Margin Plan
Each of the three business processes corresponds to a fact table to be included in the Power BI dataset
Obtain a high-level understanding of the top business questions each business process will answer:
For example,
"What are total sales relative to the
Annual Sales Plan
and relative to last year?"
In this project,
Internet Sales
and
Reseller Sales
will be combined into overall corporate sales and margin KPIs
Optionally, reference the data warehouse bus matrix of business processes and their related dimensions:
For example, discuss the integration of inventory data and the insights this integration may provide
In many projects, a choice or compromise has to be made given the limited availability of certain business processes and the costs or timelines associated with preparing this data for production use:
Additionally, business processes (fact tables) are the top drivers of the storage and processing costs of the dataset and thus should only be included if necessary.
