POWER BI:
Microsoft
makes Power BI available as part of the Microsoft Business Application
Platform, a somewhat confusing umbrella term that refers to several related
technologies, including Power BI, PowerApps, and (according to some
documentation) Microsoft Flow. You should already have a sense of what Power BI
is about, but you might not be familiar with the other two. Power Apps is a
point-and-click application development platform, and Microsoft Flow is a
workflow and business process management platform.
For
this series, we’re concerned primarily with Power BI, which provides a number
of tools for delivering BI insights through browsers or mobile apps as well as
embedding them within custom applications. In addition to the online service,
Power BI includes Power BI Desktop, the Power BI mobile apps, the Power BI API,
and Power BI Report Server. The rest of the article goes into more detail about
each component.
Power BI Services:
Notice
that the My Workspace section in the left navigation pane is
expanded, showing links to dashboards, reports, workbooks, and datasets. These
four items represent the primary components that go into the Power BI
presentation structure:
Dataset: Collection
of related data that you import or connect to. A dataset is similar to a
database table and can be used in multiple reports, dashboards, and
workspaces. You can retrieve data from files, databases, online services,
or Power BI apps published by other people in your organization.
- Report: One or
more pages of visualizations based on a single dataset. A report can be
associated with only one workspace, but it can be associated with multiple
dashboards within that workspace. You can interact with a report either in
Reading view or Editing view, depending on your granted level of
permissions.
- Dashboard: A
presentation canvas that contains zero or more tiles or widgets. A
dashboard can be associated with only one workspace, but it can display
visualizations from multiple datasets or reports. You can pin an
individual visualization to a tile or pin an entire report to a dashboard.
If you’re a Power BI Pro or Premium subscriber, you can also share
dashboards.
- Workspace: A
container for datasets, reports, and dashboards. The Power BI service
supports two types of workspaces: My Workspace and app
workspaces, which you access through the Workspaces section
in the left navigation pane. My Workspace is a personal
work area provided automatically when you log into the service. Only you
can access this space. An app workspace is used to share and collaborate
on content. You can also use an app workspace to create, publish, and
manage Power BI apps (collections of dashboards and reports).
Microsoft
offers several Power BI subscription plans. At the entry level is the Power BI
Free service. To register, you must use a work email account, not a personal
account such as Gmail. If you try, you’ll receive a polite message denying you
access. In addition, you’re limited 10 GB of storage, and you can use only the basic
features, although these are actually fairly robust. For example, you can
connect to all the supported data sources, clean and prepare the data, and
build and publish reports. You can even embed the reports in public websites.
The
next level up is the Power BI Pro service, which builds on the Free service but
adds such features as sharing, collaboration, auditing, and auto-refresh. The
Pro service also lets users create app workspaces. As with the Free service,
Pro users are limited to 10 GB of storage; however, they can also create app
workspaces that support up to 10 GB of storage each. Microsoft currently offers
of a 60-day free trial of the Pro service.
Microsoft
also offers versions of the Power BI service for US government customers and
European Union customers. The services are separate from the regular commercial
services. Microsoft does not offer a free version of either one. (Contact
Microsoft for more details.)
Power BI Desktop:
Power
BI Desktop is a downloadable application that Microsoft provides for free. The
application is essentially a report-building tool that provides capabilities
similar to the Power BI service, but kicks them up a notch. With Power BI
Desktop, you can build advanced data queries and models, create sophisticated
reports and visualizations, and publish the consolidated report packages to the
Power BI service or Power BI Report Server.
Both
conceptually and physically, Power BI Desktop can be divided into three
categories, or views, for how you interact with data and create reports:
- Report
view: A canvas for building and viewing reports based on the
datasets defined in Data view.
- Data
view: Defined datasets based on data retrieved from one or
more data sources. Data view offers limited
transformation features, with many more capabilities available through
the Query Editor, which opens in a separate window.
- Relationships
view: Identified relationships between the datasets defined
in Data view. When possible, Power BI Desktop identifies
the relationships automatically, but you can also define them manually.
To access any of the three
views, click the applicable button in the navigation pane at the left side of
the Power BI Desktop interface, shown in the following figure. In this
case, Report view is selected, displaying a one-page report
that includes two visualizations, one table and one bar chart
The data for the report comes from the AdventureWorks2017 sample
database, running on a local instance of SQL Server 2017. However, you can
define datasets based on data from a variety sources, including files such as
Excel, CSV, XML, and JSON; databases such as Oracle, Access, DB2, and MySQL;
and online services such as Azure, Salesforce Reports, Google Analytics, and
Facebook.
Power BI Desktop also provides generic connectors for accessing
data not available through the predefined connectors. For example, you can use
an interface type such as ODBC, OLE DB, OData, or REST to connect to a data
source, or you can run an R script and create a dataset based on the results.
Where Power BI Desktop really shines, when compared to the Power
BI service, are in the features available in the Query Editor to shape and
combine data, some of which are shown in the following figure. In this case,
the Sales.vSalesPerson dataset
is open, which is based on a view in the AdventureWorks2017 database with the
same name.
In the Query
Editor, you can rename datasets or columns, filter out columns or
rows, aggregate or pivot data, and shape data in numerous other ways. You can
also combine datasets, even if they come from different sources. In addition,
Power BI Desktop provides the Data Analysis Expressions (DAX) language for
performing more complex transformations.
After you’ve gotten the data in the format you need, you can
use Report view
to create multiple types of visualizations, including bar chats, line charts,
scatter charts, pie charts, treemaps, tables, matrices, and maps. Report view provides
numerous options for configuring and refining the charts so you’re presenting
the data as effectively as possible. In addition, you can import and display
key performance indicators (KPIs) as well as add dynamic reference lines to visualizations
to focus on important insights. Once you have your reports the way you want
them, you can publish them to the Power BI service or to Power BI Report
Server.
There are plenty of other features available to Power BI Desktop
than what I’ve covered here, and most of them are easy to access and
understand. The UI is powerful and intuitive enough to support a wide range of
users, from data stewards to business users to data analysts.
Power BI Mobile Apps:
Microsoft
offers Power BI mobile apps for iOS, Android, and Windows mobile devices. The
apps make it possible to provide specific users with access the Power BI
dashboards, reports, and apps, while taking into account the form factor of the
smaller devices. For example, the following figure shows the Human Resources Sample dashboard
(in landscape mode), as it is rendered by the Power BI app for iPhone.
With a Power BI app, you can connect to either the Power BI
service or to a Power BI Report Server instance. Because you’re dealing with an
app rather than a website, you can view the Power BI content offline. Once
you’re reconnected, Power BI automatically refreshes the data. When you’re
connected via a 3G network, the data is refreshed every 24 hours. When you’re
connected via Wi-Fi, the updates occur every two hours.
When you’re creating Power BI reports, you can optimize them for
mobile devices. This causes Power BI to add features to the reports specific to
mobile usage, such as allowing users to drill down into visualizations. In
addition, you can add slicers to your reports that let users filter the
displayed data. Plus, you can create a QR code for a report and distribute it
to colleagues, who can then scan the code from within their Power BI app to
view the report.
Power BI Report Server:
One of the newer tools in the Power BI arsenal is Power BI
Report Server, an on-premises solution for creating, deploying and managing
Power BI reports. The product is included with a Power BI Premium subscription
to provide customers with a tool for delivering reports from within their own
data centers. Users, in turn, can view the reports via their browsers or Power
BI mobile apps or as email attachments.
If
you choose to install Power BI Report Server, you must use the Report Server
Configuration Manager to specify such settings as the service account, web
service URL, SQL Server database, and web portal URL, as shown in the following
figure. You need to set up your configurations before you can start working
with the actual reports.
The Report Server Configuration Manager is included in the Power
BI Report Server installation, but it is separate from the tools you use to
manage the reports. For report management, you must use the Report Server web
portal, which is enabled after you configure the necessary settings. Through
the web portal, you can access all your reports and KPIs, as well as carry out
such tasks as schedule data updates or subscribe to published reports.
Like the Power BI service, Power BI Report Server
works in conjunction with Power BI Desktop. You can create reports and then
save them to Power BI Report Server. For example, you can save a report such as
the one shown in the following figure to Power BI Report Server
