Welcome to the final post in my blog series concerning Microsoft Exam 70-778, where I hope to provide a revision tool for those planning to take the exam or a learning aid for those looking to increase their Power BI knowledge. Last weeks post discussed a range of topics about access and security when using Power BI as your Business Intelligence solution. We now move onto the final exam focus area – Configure apps and apps workspaces – which tests candidates against the following subjects:

Create and configure an app workspace; publish an app; update a published app; package dashboards and reports as apps

To follow through any of the examples described in this post, you should make sure that you have access to a Power BI Professional subscription. Free sixty day trials are available for this service if required.

Creating Workspaces

We provided a brief overview of the salient concepts behind a Workspace in last weeks post, chiefly from the perspective of the personal Workspaces that each user has access to from within Power BI Online. As mentioned as part of this discussion, additional workspaces can be created on top of this, to allow for a more logical grouping of Power BI content to become accessible within an organisation. There are a few ways to create a Workspace as part of the “current” experience (more on this later). Workspaces currently take advantage of Office 365 Groups in the background, so creating one of these within the Office 365 Admin Portal will automatically cause a Workspace with the same name to appear within Power BI Online. It is also possible to create all of this from within the Power BI Online interface, by using the Create app workspace button:

You have a couple of options that can be specified when creating a Workspace, as illustrated in the screenshot below:

  • To control how content within a Workspace can be interacted with, you can define whether its membership is Public – Anyone can see what’s inside or Private – Only approved members can see what’s inside. By “anyone”, this refers to any user account that has been provisioned on your Office 365/Azure Active Directory Tenant. You should ensure that the most appropriate option is chosen when the Workspace is created, as it cannot be changed once defined.
  • Permissions for all members of a Workspace are definable as either write access (Members can edit Power BI content) or read-only accessĀ  (Members can only view Power BI content) to anything created within a Workspace.
  • If you are fortunate enough to have access to dedicated Power BI Premium capacity, then you can also choose to assign your Workspace to this. Doing this will ensure that the Workspace can benefit from the increased CPU/Memory capacity within your available node(s).

Once created, a Workspace becomes accessible from within the Power BI Online interface and also as a choosable location to publish your .pbix files to from within Power BI Desktop:

It is also possible to add the following components, either from other Workspaces in Power BI or via upload, from within Power BI Online:

As alluded to earlier, the “current” Workspace experience is being overhauled currently, chiefly meaning that there will no longer be a need to create an Office 365 Group in the background. You can find out more about this new experience by reading the Microsoft Docs article on the very subject which, although still in preview, will eventually be something you need to be aware of for the exam.

Publishing Workspaces as an App

To distribute Power BI content to a broader audience, either internally or externally, you can publish all material that has been deployed out to a Workspace as an App. Taking this extra step can help you to simplify any ongoing management of a Workspace as, instead of having to grant access to individual users or Security Groups, you can alternatively create a self-service experience for users across the business, enabling them to install and interact with the Power BI content that is most relevant for their role.

You can create an App from within a Workspace by selecting the Publish app option at the top right of the window:

From here, you must define several different settings across the three tabs listed:

  • Details – Allows you to provide a description for the app and also set the Background color that displays to end users, based on a predefined list (i.e. no option to specify a unique hex colour value):
  • Content – Let’s you determine the default landing page presented to users when they first load the App. In most cases, it is best to choose a Dashboard, although it is possible to use a Report or have no landing page at all for the App (in this scenario, the user will see a list of all the content included within the App instead). It is also possible to review all of the content that will be published out within the App on this tab:
  • Access – Here, you can define who within the organisation can install the App – all users within your Office 365/Azure Active Directory tenant (Entire organization) or a specified list of Users or Security Groups (Specific individuals or group). There is no option to define more granular permissions (e.g. modify the contents within an App), which makes sense given the intended business requirement Apps serve. In most cases, you will probably leave the Entire organization option selected:

With all settings defined, you can then click the Finish button to get your App published out. Depending on the size of your datasets and Reports, this may take a while, as the below confirmation step advises:

After successfully publishing your App, you are presented with a URL link to it and also have the option to navigate straight to it so that you can verify its contents:

Apps, once installed, can also be accessed from the Apps button on the left-hand pane within Power BI Online:

From an end-users perspective, they would then interact with the newly published App by either:

  • Accessing and installing the app using the URL generated in the previous example.
  • Navigating to the My organization tab within AppSource and pressing the Get it now button under the corresponding App:

Apps can be updated with new or modified content at any time, without the need for users to install them again. All you would need to do is update the corresponding Workspace and then select the Update app button at the top left of the Workspace window:

The process of re-publishing the App is then identical to when you first publish it out.

ISV developers who have put together a range of Power BI content that integrates with their solution can also publish Apps to the public AppSource store, allowing any Power BI user the world over to install and utilise the content contained within. The process involved here involves several hoops and is not something you need to worry about for the exam.

Key Takeaways

  • Workspaces act as a container for the various components that form a Power BI Reporting solution. Within a Workspace, you will find all of the Dashboards, Reports, Workbooks and Datasets that developers have published content to. Each User has a Workspace created for them in Power BI when they first access the service. It is also possible to create additional Workspaces, either through the Power BI Online interface or by creating an Office 365 Group. A new experience for creating Workspaces is currently in preview which, once released, would negate the need for each Workspace to have an associated Office 365 Group.
  • When creating a Workspace, you can define various settings such as the type of access each user has (read-only or ability to modify its content), its members and whether it requires assignment to a Power BI Premium node. It is not possible to change the access type for a Workspace after creation, but you can freely change its name or modify its membership at any time.
  • The contents of a Workspace can be published as an App, enabling you to expose your solution to a broader audience within or outside your organisation. Once published, users navigate to the Power BI AppSource store for their tenant, which lists all Apps available for installation. Once installed, they will then become visible from within the Apps area of the application. You can update content within an App at any time by republishing its corresponding Workspace. It is also possible to define individual properties within an App, such as its description, access rights and landing page. To install and use Apps, the user in question must have a Power BI Professional license.

We’ve come to the end of the series (at last!) and covered a LOT of content relating to Power BI. I’ll be doing a roundup post next week, where I’ll try and group together all of the posts into the single location, alongside some general exam preperation tips. If you’ve been following the series, thank you for reading and I hope that it has proved to be a useful learning tool šŸ™‚

Welcome to the twelfth and penultimate post in my blog series concerning Microsoft Exam 70-778, where I hope to provide a revision tool for those planning to take the exam or a learning aid for those looking to increase their Power BI knowledge. We’re on the home stretch now and, after reviewing last week the various options available to publish Power BI Reports both online and on-premise, we now take a deep dive into some vital security concepts as part of the Configure security for dashboards, reports and apps theme, which covers the following skill areas:

Create a security group by using the Admin Portal; configure access to dashboards and app workspaces; configure the export and sharing setting of the tenant; configure Row-Level Security

Before exploring these topics further, however, it is essential to outline a concept that this series has continually skated around – Power BI Workspaces.

Workspace Overview

We’ve seen so far in the series how it is possible to deploy Power BI Desktop Reports into Power BI Online. As part of this process, you must define a Workspace where your Reports and Datasets will appear after publishing. There are three types of Workspaces:

  • My Workspace – Each user, by default, will have a personal Workspace, which cannot be deleted or changed.
  • Office 365 Group Workspace
  • App Workspace

Workspaces are, for the most part, a logical grouping of all the components that you need to start building out a BI solution. They are worked with from within Power BI Online only (meaning that they do not exist as part of Power BI Report Server) and can be interacted with from the left-hand pane within Power BI Online:

As indicated above, each user’s Workspace can contain:

  • Dashboards – These are created within the Power BI service, as we saw a fortnight ago.
  • Reports – These are built out in Power BI Desktop or uploaded directly into Power BI Online from a .pbix file.
  • Workbooks – These will show a list of Excel workbooks that have been uploaded into Power BI, allowing you to leverage an existing solution built out using Excel PivotTables/PivotCharts almost immediately through Power BI. For this exam, it is not something you need to worry about necessarily, but be aware this topic does crop up within Exam 70-779.
  • Datasets – Contains a list of all data models uploaded/created for Power BI Reports or Workbooks.

It is possible to share out Dashboards and Reports to other Users/Security Groups, and we will see how this can be done with the example later on in this post. One consideration to bear in mind is that, when sharing Reports, this does not share out any Dashboards that reference it. Content shared to you will become visible within the Shared with me tab on Power BI Online:

Next week’s post will go into further detail on how to create and manage Workspaces, and how to handle access to App Workspaces.

Office 365 Security Groups

Those who have experience administrating on-premise Active Directory (AD) domains will have full familiarity with the concept of Security Groups – logical containers of Users that can then be assigned file/folder privileges, group policy objects and access to other principals on the domain. Given that Power BI uses Azure Active Directory (AAD) as its identity provider, the same kind of concepts come into play when you start to determine how to manage access to Power BI Dashboards, Reports and Datasets to specific groups of Users. Office 365 Security Groups are virtually identical to their on-premise AD equivalent; the primary difference being is that Administrators must create them from within the Office Microsoft 365 Admin Center. It is also possible to add them through Microsoft Azure as well, so your choice here really comes down to preference. In either case, you must ensure that you have the relevant administrator privileges on your AAD tenant to create and manage them. Once created and defined with your required list of Users, they then become available as a shareable object within Power BI Online.

In the example towards the end of this post, we will walk through how to create a Security Group and how this can then be used to share out a Dashboard.

Managing Export and Sharing Settings

With the introduction of GDPR last year, data privacy concerns remain a paramount concern for organisations. These concerns can often come into conflict with new functionality that technology solutions can offer us such as, for example, the ability to export a Power BI Report as a PowerPoint presentation. To help with these considerations and in line with Microsoft’s overall commitments from a GDPR standpoint, Power BI Online provides several options that allow you to granularly define various actions that Users can and cannot do, such as using custom visuals in reports, accessing audit/usage information and the ability to use preview features, such as dataflows. The list of settings most relevant to data sharing can be found under the Export and sharing settings section on the Admin Portal -> Tenant settings area of Power BI Online:

Each of the listed features can be enabled or disabled globally on the Office 365 tenant. Alternatively, by utilising Security Groups, you can grant or curtail specific functionality to a group/department within an organisation. You have no option to specify individual User access as part of this, so it becomes a requirement to have your required Security Groups defined within Office 365 before you can start working with this feature.

Row-Level Security

Granting global allow/deny privileges at a Report level may not be sufficient for specific business requirements. It could be, for example, that a single Sales report is in place for both junior and senior sales professionals, and there is a need to only present data that is most relevant to their role. Or, for example, there is a need to show data that has the most relevance for an individual’s particular geographic region. In these situations and, to avoid a scenario where you would have to define separate queries to segregate this data appropriately, Row-Level Security (or RLS) becomes a significant asset. It allows you to set Roles linked to DAX expressions, which tell Power BI which data to show to a particular group of Users.

There are two steps involved as part of implementing RLS. First, you must create a Role that defines the list of privileges for one or multiple Users. This step can be achieved by navigating to the Modeling tab within Power BI Desktop and selecting the Manage Roles button, which will open the appropriate dialog window:

Next, you must define a DAX Expression for each table that requires filtering as part of the Role. These can be set up for as many Tables as you like, but the critical thing to remember is that the DAX Expression must conform to a TRUE/FALSE equality check. The example below – whether it will either be TRUE or FALSE that the TotalSales value on a row will be greater than or equal to 500 – meets this requirement:

With the Role defined, it is then possible to test it locally from within Power BI Desktop by using the View as Roles button to select your corresponding Role:

With everything built out and working with Power BI Desktop, the second step is to publish your Report to Power BI Online and then assign the Role to Users or a Security Group by navigating to the Dataset in question:

It is also possible to use the Test as role feature within Power BI Online, which behaves identically to its Desktop client equivalent:

To help leverage additional functionality out of RLS, Microsoft provides the following two DAX functions:

  • USERNAME() – Returns the domain name of the User accessing the Report. For Desktop Reports, this will typically be in the format <Domain>\<User Name>; when viewing the Report online, the value rendered instead will either be the user’s email address or onmicrosoft.com account name.
  • USERPRINCIPALNAME() – Returns the User Principal Name (UPN) of the User accessing the Report. The UPN will almost always be the user’s email address or, in some cases for Power BI Online, their onmicrosoft.com user account name.

By using these functions in tandem with IF DAX constructs, you have the additional capability to restrict access to specific data, based on user account names. All in all, RLS is a powerful feature to have at your disposal but, as highlighted in last week’s post, you should be aware of its limitations. RLS is incompatible when there is a need to Publish to web a report and the feature is also not available if you are querying a SQL Server Analysis Services data source via a live connection.

Example: Sharing a Power BI Dashboard with a Security Group

The steps that follow will show how to create an Office 365 Security Group and then share out a Dashboard to it from within Power BI. To complete the steps outlined, you should ensure that you are assigned either the Global administrator or User management administrator role in Office 365 and that your user account has a Power BI Professional license:

  1. Navigate to the Admin Center within Office 365, expand the Groups tab on the left-hand pane and select Groups:
  2. The main window should refresh, displaying the list of Groups setup on the AAD tenant. Select the Add a group button:
  3. Define the settings as indicated in the below screenshot, making sure that the Type selected is Security, and press Add:
  4. You will then receive confirmation that the Security Group was added successfully to your tenant:
  5. With the main Groups window, select the new Security Group and then click the Edit button on the right-hand details pane:
  6. Then, select the Add members button to add in the required list of Users:
  1. Press Save to commit any changes:
  2. Navigate back to Power BI Online and to the Dashboard that needs sharing. Select the Share button at the top right of the screen:
  3. Within the Share dashboard pane, begin typing in the name of the Security Group created in the previous steps. Power BI will automatically detect and auto-complete the name of the group for you. Before pressing the Share button, you can also include a custom message to recipients that will be sent via an email and also toggle whether they will also be able to Share the dashboard themselves. A URL link generates at this point as well, allowing you to copy/paste this into an email, IM message etc.:
  4. Once the Dashboard is Shared, you can then navigate to the Access tab to review the list of Users/Security Groups that have access to your Dashboard. It is also possible to modify their access levels or remove access entirely by clicking on the ellipses button next to each Name:

Key Takeaways

  • Workspaces act as a container for the various components that form a Power BI Reporting solution. Within a Workspace, you will find all of the Dashboards, Reports, Workbooks and Datasets that developers have published content to. Each User has a Workspace created for them in Power BI when they first access the service. Additional Workspaces can be added through Office 365 Groups or by installing a Power BI App from AppSource. Dashboards and Reports created within your a Users Workspace are shareable to other Users, provided that your account has a Power BI Professional license assigned to it.
  • To help manage permissions to Dashboards/Reports in a more efficient manner, Administrators can create Security Groups on the same Office 365 Tenant where Power BI Online resides. These can contain multiple groups of Users, allowing administrators to minimise the amount of effort involved in managing Dashboard/Report access. Most crucially, this will also enable Users that do not have an Exchange Online mailbox to access Dashboards/Reports when they are shared out in this manner.
  • Administrators have a whole host of options available to them within the Tenant settings area of the Admin Portal. These include, but are not limited to:
    • Export and Sharing Settings
    • Enable/Disable Content Sharing
    • Enable/Disable Publish To Web
    • Enable/Disable Export Reports as PowerPoint Presentations
    • Enable/Disable Print Dashboards and Reports
    • Content Pack and App Settings
    • Integration Settings
    • Custom Visuals Settings
    • R Visuals Settings
    • Audit and Usage Settings
    • Dashboard Settings
    • Developer Settings
  • All of these settings can be enabled for a specific security group, the entire organisation (excepting specific security groups) or allowed for particular security groups, excluding all others in the organisation.
  • Row-Level Security (RLS) allows report developers to restrict data, based on Roles. Row-level DAX evaluation formulas are used to achieve this, which filters the data that is returned, depending on a TRUE/FALSE logic test. To utilise the feature, you must define both the Roles and DAX formulas for each query within your data model. Then, after deploying your Report to Power BI Online, you then assign Users or Security Groups to the Role(s) created within Power BI Desktop. It is possible to view the effect of a Role at any time, within Power BI Desktop or Online, via the View As Role functionality. With the wide-array of DAX formulas available, including specific ones that return the details for the current user accessing a Report, it is possible to define very granular filtering within a Power BI report, to suit particular security or access models.

Putting some thought into Power BI’s security and access components early on when developing your solution will allow you to best take advantage of features such as RLS, which then benefit further when utilised alongside the other functionality described this week. The final post in the series next week will provide a more detailed description of Workspaces and how these can be used to create Apps for both internal and external consumption.

Welcome to the eleventh post in my blog series concerning Microsoft Exam 70-778, where I hope to provide a revision tool for those planning to take the exam or a learning aid for those looking to increase their Power BI knowledge. In last week’s post, we covered a broad spectrum of topics ranging from Dashboards through to integrating on-premise data sources within Power BI Online. Dashboards are just one means of consuming published Power BI Reports, with a few additional options also available to help us include Power BI content within existing websites, intranet or on-premise deployments. TheĀ Publish and embed reportsĀ exam topic covers this, focusing on the following skill areas:

Publish to web; publish to Microsoft SharePoint; publish reports to a Power BI Report Server

Let’s dive into each of these specific areas of functionality and how to utilise them most effectively.

Publish to Web

The ability to publish Power BI content to any location on the web can be incredibly beneficial. Users can access Power BI information without needing to log into Power BI Online or even, necessarily, be licensed, allowing you to quickly include Reports as part of an existing website, blog or application. Although there is some trade-off from a functionality perspective, you can expect to do most of the things you’d expect a Power BI Report to do as standard. Keep in mind though; it’s only possible to publish Reports to the web, not Dashboards or Datasets. To do this, you navigate to the relevant Report within Power BI Online and select the File -> Publish to web option:

At this stage, note that you will be presented with additional dialog box prompts, indicated below, which emphasise two critical points to remember regarding the Publish to web option:

  • ANYONE on the web can access your Report once published, and there is also a high likelihood that crawler bots, including search engines, will also index and include your Report as part of search results. To ensure that only users within your organisation can access your report, consider using the Embed option as opposed to Publish to Web, which is available for users assigned a Power BI Professional license.
  • If you wish to embed reports for internal users to access, then this is technically not allowed under the Power BI Free license. To use this functionality in a compliant manner, you must ensure that all internal users accessing the report are assigned a Power BI Professional license.

Once sorting the various T&C’s, a URL and embed code is generated for you to use. A handy feature for less-technical users is the ability to modify the size of the IFrame, based on the options defined below:

You can then embed the IFrame snippet within any HTML page. For example, the below snippet would load a basic HTML document, with the Report fully rendered for access:

<html>
<p><b>iFrame Test</b><p>
<iframe width="933" height="700" src="https://app.powerbi.com/view?r=aBjFVit61EihycCDLLtDAlMJeVQICkMIwvEFSbgosnAFFjy4kctLkRzqVxB3xZHfsNMO4GDDbzxGSbLfycHKUSEytFTe16sULAe61SsXzPdA2hhGWiTx7YTwf4o6" frameborder="0" allowFullScreen="true"></iframe>
</html>

The rendered Report behaves as you would expect any other Power BI Report to work. You can navigate between pages, move your cursor over visualizations, drill up/down through them, view their tooltips and even apply filters. However, you are unable to see or export any underlying data or export the Report as a PowerPoint document/PDF. You also cannot Publish to web a report that:

  • Has R visuals.
  • Uses Row-level security.
  • Has an on-premise SQL Server Analysis Services Tabular data source.
  • Is shared with you.

Putting aside the paramount data privacy and licensing concerns associated with this feature, it does nevertheless present a nice way of sharing out Power BI content for external users to access.

Publish to SharePoint

Intranet sites represent a potential deployment option for Power BI content, and the Publish to SharePoint Online option helps to accommodate this. Available for Power BI Professional subscriptions and online SharePoint deployments, this feature works similar to Publish to web and can be found nestled just next to this option on theĀ FileĀ tab:

Once selected, Power BI will then generate a URL link, as indicated below:

Your next step will then be to navigate to the SharePoint page that you wish to embed the Report onto and adding the appropriate control onto the page:

And then populate the proper details on the right-hand pane when it appears:

Note that you have a few options here to help customise how the Report renders. You can specify a default page, the page ratio and also toggle whether the Navigation or Filter panes are displayed.

An important thing to remember when working with this feature is to ensure that all SharePoint users who need to access the Report have been granted the relevant access permissions within Power BI; merely adding the Report into SharePoint will not automatically do this for you, and users will instead get an error message. The quickest way to grant these privileges is to ensure that all users are part of the Workspace/Office 365 Group where the Report resides. Alternatively, you can also specifically Share the Report out to all required users, but this could prove cumbersome to manage for larger deployments.

Publish to Power BI Report Server

Organisations that are not quite ready to start using Power BI within the cloud-based offering can still potentially benefit from all the goodness that the solution has to offer. If you have an active SQL Server Enterprise license agreement with Software Assurance or a Power BI Premium subscription, then Microsoft allows you to download a fully licensed copy of Power BI Report Server. This application is, in effect, an on-premise version of Power BI Online, provideing sufficient feature parity alongside some nice little extras thrown in. From a technical standpoint, the solution is a retooled version of SQL Server Reporting Services (SSRS); and, as a consequence, you have the full capability to deploy out SSRS reports onto Power BI Report Server. Functionality like this may represent a significant boon, allowing organisations to leverage their existing reporting solutions while enabling them to start developing new reports using Power BI as well. Microsoft also provides a Developer edition of the application, which is really neat. šŸ™‚

Publishing a Report to Power BI Report Server has some significant differences when compared to the Online version. Because the solution does not benefit from the same release cadence as the online offering, you must use a separate Power BI Desktop application when developing for Power BI Report Server. The latest release from January 2019 (at the time of writing this) is available for download, but will not necessarily have the same monthly release cycles compared with the “normal” Power BI Desktop application. As such, be aware that you may encounter issues working with Reports developed between both Desktop applications and, the general recommendation would be to ensure these are developed separately wherever possible.

When getting a Report deployed out to Power BI Report Server, you must somewhat counter-intuitively navigate into the File -> Save as area of the application, where the appropriate option becomes visible:

Then, when prompted, populate the URL field with the Web Portal URL value (derived from the Report Server Configuration Manager application):

Next, specify the name of the folder where the Report will be saved. In the example below, there are no folders set up, so the Report will be deployed out to the default, root folder on the instance:

It may then take some time for the Report to publish out successfully…

…at which point, you can then take a look at how the Report looks within the application. As the screenshot below indicates, the experience here is virtually identical when compared to Power BI Online:

If you ever need to make changes to your Report, you would have to revert to Power BI Desktop, make the appropriate changes and then repeat the steps above. Unlike Power BI Online, there is no option to modify reports from within a web browser. A small trade-off but, as we can see, Power BI Report Server does provide a complete Power BI experience that is tailorable to an organisation’s specific infrastructure/hosting requirements.

Key Takeaways

  • The Publish to web option allows for non-licensed, external users to view a Power BI Report in its entirety. A URL and IFrame embed code can be generated for this at any time within the portal and then dropped into virtually any website. Although you will lose some functionality when deploying a Report out in this manner, you can expect that users will be able to perform most types of interactions with visualizations, Report pages and other components, as if they were accessing the Report through Power BI Online. In some cases, you may be unable to use the Publish to web option if your Report uses certain kinds of features, such as R Visuals or row-level security. You must also take into account any privacy or data protection concerns, as Reports deployed out in this manner will be publically accessible; where this is an issue, the Embed option is available as a secure alternative.
  • There are three steps involved if you wish to add a Report to SharePoint. First, you must generate the unique SharePoint embed URL within Power BI. Secondly, you then need to add on the dedicated control for this feature on your target SharePoint page and configure the relevant display options. Finally, you then need to ensure that all SharePoint users have been granted access to the Report, either at a Workspace level (recommended option) or by having the Report shared with them. By implication, in this scenario, all SharePoint users would have to have at least a Power BI Professional license to take full advantage of this functionality.
  • Publishing a Report to Power BI Report Server is mostly the same as if you were to do the same with the online version of the product. Instead of selecting a Workspace to add the Report to you, specify the name of the Report Server folder where the Report will reside. From a development standpoint, the dedicated Power BI Desktop for Power BI Report Server must be used and may differ in functionality from the “normal” version of the tool. There is also no option to edit a report from within Power BI Report Server like you can through the online version.

We saw previously how Power BI Embedded presents a means of integrating Power BI visualizations as part of a bespoke application, but the options offered in this post today do represent a potentially quicker and simpler alternative to achieving the same ends. In this series penultimate post next week, we will dive deeper into the various security options available as part of Power BI, to help ensure that both reports – and any underlying data – can be “hardened” to suit a variety of different scenarios.

Welcome to my tenth post in a blog series aimed to provide a revision tool for Microsoft Exam 70-778, and for those looking to increase their expertise in Power BI. In last week’s post, we explored the possibilities developers have to leverage Power BI within their applications and how the Power BI API relates to all this. As we now get into the home stretch, this weeks post combines two exam areas into one. The first topic, all concerning how to Access on-premises data with Power BI, covers the following skills areas:

Connect to a data source by using a data gateway; publish reports to the Power BI service from Power BI Desktop; edit Power BI Service reports by using Power BI desktop.

Then, we’ll look at how to Configure a dashboard and do the following with them:

Add text and images; filter dashboards; dashboard settings; customize the URL and title; enable natural language queries

Power BI Gateway

A typical obstacle that can prevent an organisation from wholly adopting a cloud solution is the need to retain existing on-premise systems. A myriad of potential concerns may be involved here, such as regulatory or contractual arrangements. As a Software as a Service (SaaS) offering, Power BI is no different in this regard, thereby presenting potential obstacles for its adoption. The online element of the solution is designed to interact with data sources that are “in-cloud”, such as Azure SQL databases or Dynamics 365 Customer Engagement. If, for example, you need to access a data warehousing solution residing within an on-premise IBM DB2 instance, this is not necessarily something that can be “opened up” for access.

To address these concerns in a streamlined manner, Microsoft makes available the On-Premise Gateway application, which provides an easy to install and configure way of interacting with on-premise data sources. Built initially with Power BI in mind, the solution is now extended to support by the various services that make up the Power Platform, such as Microsoft Flow and PowerApps. The list of supported on-premise data sources is extensive, covering well-known systems such as:

  • SQL Server
  • Active Directory
  • SharePoint
  • MySQL
  • IBM DB2

A general recommendation when installing the gateway is that the target machine should remain consistently online and have the proper access to all of your on-premise data sources.

To get up and running with the on-premise gateway, you must first download and install the corresponding client from Power BI online, by clicking on theĀ Download button in the top right area of the application (the icon that has a downward arrow and a line):

Clicking this will navigate you to the dedicated information page for the Gateway (which is accessible via a direct link too), where the very obviously placed button will let you download the application:

Once downloaded and installed, some additional configuration is then required:

  • You must specify the type of gateway to deploy – either one that uses the Recommended configuration or one configured for Personal Mode. Recommended will be the one that you generally need to go for, with Personal Mode only being useful for testing purposes or when developing on a local machine.
  • You will need to sign into the Office 365 tenant that contains the Power BI Subscription for your new gateway, making sure that the account in question has sufficient privileges to register one. Once successfully signed in, you can then chose to deploy a new gateway or a replacement one:
  • Next, you will need to define a name for your gateway and also a recovery key. Make sure this is noted down, as you will require it if the gateway requires modification in future. The application should also determine the most appropriate Azure region to associate itself with, but this can be adjusted if needed. Keep in mind though that doing so may make it incompatible with certain services:

With everything configured correctly, you can then confirm the status of the gateway by navigating to the appropriate tab on the application:

Its successful creation can then be verified by going into Power BI Online, where it should be visible in the Manage gateways settings area:

Once implemented, you can then press the ADD DATA SOURCE button to start adding your on-premise connections. The screenshot below shows an example of how to configure access to the WideWorldImporters sample database on an on-premise SQL Server instance:

The gateway is an essential tool to have within your arsenal if you are attempting to leverage the benefits of Power BI Online more quickly, but find yourself restrained by existing, on-premise data sources; once deployed, it straightforwardly allows you to have the best of both worlds.

Publishing Reports

Although it is perfectly acceptable for users to work and interact with their Reports from within the Power BI Desktop application, Reports that typically require more general consumption or utilisation by non-technical audiences will require deploying out to either a) Power BI Online or b) a Power BI Report Server instance. The steps involved in the second one are a little different (and require access to a separate Power BI Desktop application), but for the first route, it is merely just a case of clicking on the Publish icon on the main application ribbon once your Report is ready:

If you have access to several Workspaces within Power BI, you must first specify which workspace the Report will appear in after publishing:

Depending on the size of your Report, this may take some time to upload, but a helpful dialog box will keep you informed on progress:

Once published, you will be provided with a hyperlink that opens the Report within your web browser, where both a new Report and corresponding Dataset will exist:

Once deployed, a Report can be modified in one of three ways:

  • By updating the existing Report within Power BI Desktop and then re-publishing your changes; all current Reports/Datasets will then refresh accordingly.
  • If the original .pbix file is unavailable on your local machine, then you can go to File -> Download report (Preview)Ā to get a copy of the file:
  • This can then be modified and re-published in the manner already described.
  • From directly within the Browser. This route provides a similar experience to Power BI Desktop but optimised for browser use. I would not generally recommend altering reports in this manner, especially if your Reports are subject to strict version control policies.

Working with Dashboards

Power BI Reports are similar to books in many ways, in that they cater towards more detailed analysis and precise drill-down capability. For situations where executive or senior level individuals require an at a glance view of the information that is most relevant for their needs, a Dashboard represents the optimal choice to include the data that interests them the most, while still allowing them to drill-down further if required. They also afford additional functionality that assists from a presentation standpoint, by supporting the ability to include images, hyperlinks, videos and even custom streaming datasets.

It is not possible to create Dashboards from within Power BI Desktop. Instead, you must generate them from a Workspace within Power BI online by selecting the + Create button at the top right of the screen, choosing the correspondingĀ Dashboard option and by finally providing an (ideally descriptive) name for it:

You have many different options available when working with a Dashboard, either from an administrative or end-user perspective and these are all accessible from the Dashboard ribbon, shown below and explained in the bullet points that follow:

  • Add tile: Adds various tiles onto your Dashboard, such as web content, videos, custom streaming data sources, images or text boxes.
  • Comments: Displays and lets you add comments to the dashboard, which can be viewed by all other users who have access to the Dashboard.
  • View related: Shows a list of all Reports and Datasets that are associated with the Dashboard.
  • Set as featured: When selected, Power BI will always display this Dashboard first when you login.
  • Favorite: Adds the Dashboard to your favourites list.
  • Subscribe: Allows you to configure email alerts whenever a refresh of data occurs on the dashboard, which will include the Dashboard content and a link to access it. Multiple subscriptions can be set up in this manner. This feature is only available if you have a Power BI Professional subscription.
  • Share: Allows you to share data with other users within or outside your organisation. Once shared, you can then define the access level that these users have – Read or Read and reshare. Access can be revoked at any time. This feature is only available if you have a Power BI Professional subscription.
  • Web view: Lets you toggle between either a Web View or Phone View of the Dashboard, allowing you to verify how the Dashboard renders on different device types.
  • Dashboard theme: Enables you to change the current Theme for the Dashboard. New themes can also be defined using the Custom Theme designer or by uploading a JSON file. If you have already designed your own custom Report theme file, as discussed earlier in this series, then this can be uploaded here too.
  • Duplicate dashboard: Creates a copy of the Dashboard within the current workspace, using a name you specify.
  • Print Dashboard: Lets you print the currently displayed Dashboard to a physical printer or PDF.
  • Refresh dashboard tiles: Forcibly updates all dashboard tiles to return the latest available data.
  • Performance inspector: This will display some KPI type recommendations relating to your Dashboard, advising on elements such as network latency, choice of tiles and other information which is useful when fine-tuning Dashboard performance.
  • Settings: Lets you modify settings for the Dashboard, such as Q&A capabilities, whether Comments are allowed or the default behaviour when moving tiles around. The Dashboard can also be renamed here.

As mentioned already, accessing the + Add tile button on the ribbon shows all the options available for adding content onto your Dashboard:

The next few sections will primarily focus on the three most used options – visualizations from a report, text boxes and images.

Visualizations

Any Report visualization can be pinned to a Dashboard, provided that they exist in a report within the same Workspace. To do this, navigate to the visualization in question and click on the pin icon in its top right corner. You’ll be asked to specify what theme is used for the visualization and also which Dashboard to add it to. Once decided and pinned, you can then navigate to your dashboard to see how this looks:

If there is a requirement to filter a visualization first before pinning it to a Dashboard, then you must do this within the Report, as there is no option to filter visualizations after they are embedded as a tile. A great alternative to get around this is discussed on the PowerDAX website, which involves the use of slicers.

Text Boxes

This tile type should be reasonably self-explanatory, but it is worth highlighting the additional options available here:

  • Titles/subtitles can be specified and, optionally, displayed.
  • Text can be rendered using rich-text editor capabilities.
  • It is possible to add a hyperlink to an external link, another Dashboard or a Report page, that redirects users accordingly after clicking the tile.

Images

The list of available properties when configuring an image tile is mostly the same as text boxes. The picture you wish to display must derive from a Web URL; there is no option to upload an image file. A good candidate for hosting any image could include a publically available OneDrive folder or an Azure Blob Storage location:

Rearranging Dashboard Content

It is possible to drag, drop, shorten and widen tiles on a Dashboard at any time. The two screenshots below show how it is possible to do this to suit any potential layout requirement you may have:

Key Takeaways

  • The Power BI On-Premise Gateway provides a streamlined route to working with non-cloud data sources within Power BI, Microsoft Flow and PowerApps. As a lightweight and easy-to-configure client application, it supports a wide variety of data sources, making them accessible as if they were in the cloud. Once set up, corresponding Data Sources are then made available for configuration and for leveraging as part of any Power BI Dataset.
  • Reports can be published into Power BI Online, meaning that they become accessible online and to a broader group of users, without requiring access to Power BI Desktop. Reports need deploying into a Workspace, which can be created manually or derived from an Office 365 Group. Each Report contains a corresponding Dataset, where all queries defined within Power BI Desktop exist.
  • Reports that already exist on Power BI Online can be updated by just publishing a new version of the Report from Power BI Desktop. It is also possible to modify Reports from directly within the browser and by downloading a copy of the .pbix Report file as well, which can then be altered and re-published.
  • Dashboards provide a means of grouping together various content as tiles, designed for at-a-glance analysis and optimal end-user experience.
  • The list of content that can be pinned to a Dashboard includes:
    • Visualizations
    • Web content
    • Images
    • Text boxes
    • Videos
    • Custom streaming data
  • Pinned content can be re-arranged on Dashboard via drag and drop functionality. It is also possible to resize tiles to any given height/width.
  • Within the settings of a Dashboard, it is possible to enable/disable features such as natural language queries (Q&A’s) and Notes.
  • Some features of a Dashboard are only available if you have a Power BI Professional subscription, such as sharing and email subscriptions.

We’ve covered a lot in today’s post and jumped around two distinct functionality areas within Power BI, both of which have arguable importance in their own rights. Next weeks post will hopefully be a lot easier to digest, as we evaluate the options available to publish Power BI Reports to a variety of locations, such as for public access or within SharePoint.

Welcome to post number nine in my series designed to provide a revision tool for Microsoft Exam 70-778, and for those looking to increase their expertise in Power BI. The topics we have covered so far in the series have all involved Power BI Desktop primarily, and we now move away from this as we evaluate how to Manage Custom Reporting Solutions with Power BI. This focus area for the exam measures the following skill areas:

Configure and access Microsoft Power BI Embedded; enable developers to create and edit reports through custom applications; enable developers to embed reports in applications; use the Power BI API to push data into a Power BI dataset; enable developers to create custom visual.

Despite being, at first glance, a very technically focused area, it is not necessarily a requirement for the exam to know how to work with these features in-depth. However, what this post will try to do is fully explain what Power BI Embedded is (and how it can be tailored accordingly), the capabilities and benefits of the Power BI API and, finally, what options you have available to build custom visualizations, that are then available for use across any Power BI Report.

Power BI Embedded

A potential limitation of using Power BI as your Business Intelligence solution is that you must access your reporting solution through one of the two interfaces, depending on how you have licensed the product:

For strictly organisational only access, this is all fine and dandy; but if you desire to grant external users access to your reports, it would be necessary to open up a door into a critical component of your IT infrastructure, often in tandem with any other systems your solution may contain. For example, if you have developed a support portal for your customers to submit cases with and wish to provide them with a range of Power BI visualizations, you would need to grant and deploy access to two, separate application instances – your support portal and Power BI Online/Report Server. This can lead to a jarring end-user experience and severely limit your capabilities in providing a unified, bespoke and branded solution.

Power BI Embedded seeks to address this scenario by providing application developers the means to embed Power BI reports and visualizations directly within their existing applications. The experience this offers is seamless, to the extent that end-users will not even need to know that you are using Power BI at all. Consequently, this potentially allows you to look exceptionally gifted when you begin to deploy engaging and highly interactive visualizations into your application quickly. As an Azure-based service with a pricing mechanism to suit, you only need to suitably estimate your potential compute capacities, deploy your capacity and any corresponding reports and then build out the appropriate link to your Power BI content within your application.

To get started with using Power BI Embedded, you need to make sure you have the following at your disposal:

To get a feel for the capabilities on offer as part of this offering, you can go to the Power BI Embedded Playground, made available courtesy of Microsoft. This tool allows you to test how the various Power BI Embedded components render themselves, tweak around with their appearance and generate working code samples that are usable within your application. The screenshot below shows an example of how a single Report visual would look when embedding it into a bespoke application:

As the screenshot indicates, there is no loss in core functionality when consuming Power BI in this manner. You can hover over individual areas to gain insights into your data; you can drill-down through the data; data is sortable in the conventional manner; and, finally, you can view the underlying data in the visualization or even export it out into an Excel/CSV document. Also, you have extensive options available that can be used to modify how a visual, report etc. is rendered on your application page, allowing you to ensure that all rendering completes most optimally for your application.

All in all, Power BI Embedded represents a significant boon for application developers, enabling them to very quickly leverage the extensive reporting capabilities Power BIĀ  provides, all of which is cloud-based, highly scalable and minutely tailorable. It is important to highlight that all of this goodness comes with a potentially high cost, namely, that of requiring a sufficiently proficient application developer (preferably .NET focused) to join all of the various bits together. But, if you are already in the position where you have developed an extensive range of Power BI reports for consumption by your customer base, Power BI Embedded is the natural progression point in turning your solution into a real piece of intellectual property.

The Power BI API

If you are finding your feet with Power BI Embedded and need to look at carrying out more complex actions against Power BI content that is pulling through from a workspace, then the API is an area that you will need to gain familiarity in working with too. Microsoft exposes a whole range of functionality as part of the Power BI API, that can assist in a wide variety of tasks – such as automation, deployment and allowing any bespoke application to further leverage benefits out of their Power BI embedded solution. Some examples of the types of things you can do with the API include:

The API requires that you authenticate against the Power BI service, using a corresponding Application Registration on Azure Active Directory, which defines the list of privileges that can be carried out. This component can be straightforwardly created using the wizard provided by Microsoft, and a full tutorial is also available on how to generate an access token from your newly created Application Registration. The key thing as part of all of this is to ensure that your Application Registration is scoped for only the permissions you require (these can be changed in future if needed) and not to grant all permissions needlessly.

Because the API is a REST endpoint, there are a variety of programming languages or tools that you can use from an interaction standpoint. PowerShell is an especially good candidate for this and, in the snippet below, you can see how this can be used to modify the User Name and Password for a SQL Server data source deployed onto Power BI Online:

#Make the request to patch the Data Source with the updated credentials

$sqluser = "MyUserName"
$sqlPass = "P@ssw0rd!"

$patchURI = "https://api.powerbi.com/v1.0/myorg/gateways/cfafbeb1-8037-4d0c-896e-a46fb27ff229/datasources/1e8176ec-b01c-4a34-afad-e001ce1a28dd/"
$person = @{
    credentialType='Basic'
    basicCredentials=@{
        username=$sqluser
        password=$sqlpass
        }
}
$personJSON = $person | ConvertTo-Json
$request3 = Invoke-RestMethod -Uri $patchURI -Headers $authHeader -Method PATCH -Verbose -Body $personJSON

This example deliberately excludes some of the previous steps needed to authenticate with Power BI and is, therefore, provided for strictly illustrative purposes only.

Creating Custom Visuals

Developers have access to primarily two options when it comes to building out bespoke visualizations, which are then includable in a Power BI Online, Embedded and Report Server report:

Last week’s post discussed this topic in more detail from an exam standpoint which, in a nutshell, only requires you to have a general awareness of the options available here; no need to start extensively learning a new programming language, unless you really want to šŸ™‚

Key Takeaways

  • Power BI Embedded is an Azure hosted offering that allows you add Power BI Report content into bespoke applications. This deployment option can be incredibly useful if you wish to make available your Power BI solution to users outside of your organisation or if you have an existing, bespoke application system that can benefit from utilising Power BI content. An Azure subscription is required to begin working with Power BI Embedded and you are billed based on node size, not individual user licenses. All Power BI content requires publishing to the online service before its contents become available for Power BI Embedded to access. Report developers will, therefore, need granting a Power BI Professional license to carry out these activities.
  • The Power BI API grants access to developers to perform automation or administrative actions programmatically against the Power BI Online service. Utilising a REST API, developers can determine the optimal programming language of choice to interact with the API, allowing them to streamline the deployment of Reports or Dashboards to the Power BI service or leverage additional functionality when utilising Power BI Embedded. The API can also cater to specific data load requirements, although more complex needs in this area would require addressing via alternate means (SSIS, Azure Data Factory etc.)
  • Developers can add their own bespoke visualizations to a Power BI Report by either developing them using Node.js or using the R language. The first of these options facilitate a more streamlined deployment mechanism and allows developers to add their visualizations to AppSource, whereas the second option may be more useful for complex visualization types with an analytical or statistical function.

Compared to the other exam topics, a general awareness of these concepts is more than likely sufficient from a learning perspective and is (arguably) useful knowledge in any event, as it allows you to understand how developers can further extend a Power BI solution to suit a particular business need. In next weeks post, we will move into the final subject area for the exam, as the focus shifts towards how to work with Power BI outside of the Desktop application and the various tools available to integrate on-premise data sources into Power BI Online.