fbpx

How to create and use analytics reports with Power BI

Short story about Power BI

You see all the cool stuff that Power BI has to offer but you don’t really know what it is? Where do you really get started? What all it offers?

It is visualization tool to create stunning reports and dashboards to help you gain insights in your business. And to make business decisions.

In today’s world of fast changing trends and urge to make positive but creative business stories, sometimes it is overwhelming to gather all the data, go through, analyze them and figure out what would be the most optimal business decision for your company.

Power BI is the tool that visualize data you have and helps you to understand them better, but also to notice some trends within your work flows that are hard to see in Excel sheet. After all, human beings are visual creatures. Most of us process information based on what we see. 65 percent of us are visual learners, according to the Social Science Research Network.

There are 3 different pieces:

  1. Power BI Desktop – Free desktop app that offers you the most functionalities. Within just a few clicks you will get a bunch of visualization that will serve your business.
  2. Power BI Service – Cloud service in the Microsoft Cloud offerings. The point of the service is to drive and enable sharing of collaboration. It works as well in and out of your organization. You can have groups of people and share dashboards. Also, with the possibility of giving rights to ones who may or may not see particular visualization.
  3. Power BI Mobile App – It is possible to use all mentioned serviced on your phone, tablet or other devices wherever you are. Furthermore, there are other tools to help you while being mobile, like sending alert or annotation.
Long-Story-About-Power-BI-1-Get-Stared-2-Use-Power-BI-1

Okay, now what? What’s the first step?

Data (Sources and Connectors)

First step is to download Power BI Desktop, upload relevant data and create your first visualization: dashboard or report. With Power BI Desktop, you can connect to data from many different sources.

Data types are organized in the following categories:

  • All
  • File
  • Database
  • Power BI
  • Azure
  • Online Services
  • Other

Each of mentioned data types provides the data connections. For example, the File category: Excel, Text/CSV, XML, JSON, Folder, PDF, SharePoint Folder;

And the Database category: SQL Server Database, Access Database, SQL Server Analysis Services Database, Oracle Database, IBM DB2 Database, IBM Informix database (Beta), IBM Netezza, MySQL Database, PostgreSQL Database, Sybase Database, Teradata, etc.

The Power BI team is continually expanding the data sources available to Power BI Desktop and the Power BI service. For now, there are more than 250.

Query definition

When working in the Query Editor window of Power BI Desktop, there are a handful of commonly used tasks.

The common query tasks are the following:

  • Connect to data
  • Shape and combine data
  • Group rows
  • Pivot columns
  • Create custom columns
  • Query formulas

You can edit the steps that Query Editor generates, and create custom formulas to get precise control over connecting to and shaping your data. Whenever Query Editor performs an action on data, the formula associated with the action is displayed in the Formula Bar.

Data Modeling

Data Modeling is one of the features used to connect multiple data sources in BI tool using a relationship. A relationship defines how data sources are connected with each other and you can create interesting data visualizations on multiple data sources.

With the modeling feature, you can build custom calculations on the existing tables and these columns can be directly presented into Power BI visualizations. This allows businesses to define new metrics and to perform custom calculations for those metrics.

Data Visualization

Visualizations (known as visuals for short) display insights that have been discovered in the data. A Power BI report might have a single page with one visual or it might have pages full of visuals. In the Power BI service, visuals can be pinned from reports to dashboards. There are many different visual types available directly from the Power BI Visualizations pane.

It’s important to make the distinction between report designers and report consumers. If you are the person building or modifying the report, then you are a designer. Designers have edit permissions to the report and its underlying dataset. In Power BI Desktop, this means you can open the dataset in Data view and create visuals in Report view. In Power BI service, this means you can open the data set or report in the report editor in Editing view. If a report or dashboard has been shared with you, you are a report consumer. You’ll be able to view and interact with the report and its visuals but you won’t be able to make as many changes as a designer can.

Consume and share

Next step is to publish that visualization from Desktop to the Cloud – Power BI Service. Of course, if you are worried about your data – there are ways to publish visualization, without publishing your data.

From all these visualizations, one can make dashboard that collects all singular visualizations within the organization to give you visual overview.

After creating and publishing wanted content, it’s time to share it with particular colleagues or groups of them in or outside your organization. And real time collaboration starts.

To be part of this collab, people you share visualizations with can use their private email addresses. Power BI Service is user friendly even for newbies who don’t have IT background, which is important because it really gathers experts from all departments in organisation. This is particularly important for analytics departments that create relevant analytics and reports, and then share them across the company.

Apart from consuming the mentioned materials, one can set different access rights for collaborators. Row-level security (RLS) with Power BI can be used to restrict data access for given users. Filters restrict data access at the row level, and you can define filters within roles. This is a practical tool for larger companies, especially ones that have more departments or operate in multiple countries.

Power BI Desktop is a versatile tool that supports four development modes:

  • Live Connection
  • Import (cached)
  • DirectQuery
  • Mixed

Live Connection is mode to develop a report that directly queries an existing data model. With it, one can exploit existing data assets and allow connecting to the base model or a perspective. Also, measures can be added to the report.

One of the features that will be interesting for some companies is that the user’s identity is passed through to enforce role permissions. A great benefit of Live Connection is definitely automatically dashboard tiles update.

Import mode is the most common mode used to develop models. This mode delivers extremely fast performance thanks to in-memory querying. It also offers design flexibility to modelers, and support for specific Power BI service features (Q&A, Quick Insights, etc.). Because of these strengths, it’s the default mode when creating a new Power BI Desktop solution.

It’s important to understand that imported data is always stored to disk. When queried or refreshed, the data must be fully loaded into memory of the Power BI capacity. Once in memory, Import models can then achieve very fast query results. It’s also important to understand that there’s no concept of an Import model being partially loaded into memory.

DirectQuery mode is an alternative to Import mode. Models developed in DirectQuery mode don’t import data. Instead, they consist only of metadata defining the model structure. When the model is queried, native queries are used to retrieve data from the underlying data source.

There are two main reasons to consider developing a DirectQuery model:

  • When data volumes are too large – even when data reduction methods are applied – to load into a model, or practically refresh
  • When reports and dashboards need to deliver “near real-time” data, beyond what can be achieved within scheduled refresh limits. (Scheduled refresh limits are eight times a day for shared capacity, and 48 times a day for a Premium capacity.)

Mix mode can mix Import and DirectQuery modes, or integrate multiple DirectQuery data sources. Models developed in this mode support configuring the storage mode for each model table. This mode also supports calculated tables (defined with DAX).

The table storage mode can be configured as Import, DirectQuery, or Dual. A table configured as Dual storage mode is both Import and DirectQuery, and this setting allows the Power BI service to determine the most efficient mode to use on a query-by-query basis.

Mix mode strives to deliver the best of Import and DirectQuery modes. When configured appropriately they can combine the high query performance of in-memory models with the ability to retrieve near real-time data from data sources.

For any inquiries about purchasing a Power BI license or any additional questions, feel free to contact us at here

Video: This is Power BI

For more Customer Showcases, see Microsoft Power BI website

Print and export dashboards and reports

Print and export dashboards and reports

Occasionally, you might want to print a report or dashboard for a meeting or for sharing with others. Power BI provides a few ways for you to make these printouts.

Video: Print reports

In the Power BI service, select Export in the top-left side of the service and then select Print this page to open a print dialog box.

Long-Story-About-Power-BI-6-Publish-and-share-3-Print-and-export-dashboards-and-reports-01

Export data from a visual

You can also export the data from any visual in the Power BI service. Select the ellipsis on any visual and then select Export data. You can save to a Microsoft Excel file or a .CSV file.

Long-Story-About-Power-BI-6-Publish-and-share-3-Print-and-export-dashboards-and-reports-02

You can also print or export directly from a report. When you are viewing a report in the Power BI service, select Export > Print to open the print dialog box.

Long-Story-About-Power-BI-6-Publish-and-share-3-Print-and-export-dashboards-and-reports-03

Manually republish and refresh your data

To update reports and datasets that you’ve already published from Power BI Desktop to the Power BI service, select Publish on the Home tab.

When you republish a report in the Power BI service, you’re prompted to confirm that you want to replace the previous dataset and reports.

When you select Replace, the datasets and reports in the Power BI service are overwritten with the new datasets and reports.

For more information, see Export data from visualizations

Introducing Power BI Mobile

Introducing Power BI Mobile

To keep track of your data while you’re on the move, you can use one of Power BI’s touch-friendly mobile applications for iOS, Android, or Windows devices.

Video: Introducing Power BI mobile apps

Sign in to your account by using your Power BI service account information. The first screen displays all the content to which you have access, including reports, dashboards, and groups. The workspace also includes sample dashboards that you can explore for inspiration.

Long-Story-About-Power-BI-6-Publish-and-share-4-Introducing-Power-BI-Mobile-01

Tap any dashboard to open it. Within a dashboard, you can tap a dashboard tile to focus on it in a larger view. Note any insights that you discover by tapping the Annotate button in the top-right corner. The Annotate feature allows you to draw on a focused tile to highlight areas of interest. The annotation tools are along the bottom of the screen.

Long-Story-About-Power-BI-6-Publish-and-share-4-Introducing-Power-BI-Mobile-02

Share your annotated tile by tapping the Share link in the top right-hand corner.

For more information, see What are the Power BI mobile apps?

Create workspaces in Power BI

Create workspaces in Power BI

In this unit, you will create a group that defines a set of users who have access to specific dashboards, reports, and data.

Video: Create workspaces

Workspaces are places to collaborate with colleagues to create and refine collections of dashboards, reports, and paginated reports. There are two types of groups in Power BI:

  • Classic workspaces – groups are based on the groups in Office 365. If you’ve been using Office 365 groups to manage your group’s email, calendar, and documents, then you’ll find that Power BI offers the same features. When you create a group in Power BI, you’re actually creating an Office 365 group.
  • New workspaces – are now the default workspace in Power BI. -Assign workspace roles to user groups: security groups, distribution lists, Office 365 groups, and individuals.
  • Create a workspace in Power BI without creating an Office 365 group.
  • Use more granular workspaces roles for more flexible permissions management in a workspace.
  • The Power BI admin can control who can create workspaces in Power BI

Setting up a new workspace

In Workspace settings in the admin portal, admins can use the Create workspaces (new workspace experience) setting to allow everybody or nobody in an organization to create new workspace experience workspaces. They can also limit creation to members of specific security groups.

Long-Story-About-Power-BI-6-Publish-and-share-5-Create-workspaces-in-Power-BI-01

Setting up a classic workspace

Imagine setting up a new finance group. Start in My Workspace with the dashboards, reports, and datasets that you’ve created or that someone has shared with you.

Expand My Workspace and select Create a group.

Long-Story-About-Power-BI-6-Publish-and-share-5-Create-workspaces-in-Power-BI-02

Give the group a name, for example, Finance. Power BI makes sure that the name doesn’t exist on the domain.

Long-Story-About-Power-BI-6-Publish-and-share-5-Create-workspaces-in-Power-BI-03

Set the privacy level by deciding whether anyone in your organization or only its members can see the contents of the group.

In the Create a group dialog box, type email addresses, security groups, and distribution lists. Select Add to make the set of users members of the group, and then select Save to save the group.

For more information, see Create classic workspaces in Power BI  and Organize work in the new workspaces in Power BI

Build apps

Build apps

In this lesson, you will create an app with artifacts that already exist in Power BI, and then you will share the app with colleagues.

Video: Build apps

Start in the Power BI service. In My Workspace, you will create an app that includes your dashboard, the report underneath, and the dataset. Then, you will share the app with people in your organization so they can reuse the artifacts. In the workspace list view, decide which dashboards and reports you want Included in app.

Select the Publish app button in the upper right to start the process of creating and publishing an app from the workspace.

Long-Story-About-Power-BI-6-Publish-and-share-6-Build-apps-01

On Setup, fill in the name and description to help people find the app. You can set a theme color to personalize it. You can also add a link to a support site.

Choose whether to distribute the app to specific people or to groups, and then give the app a title. Provide a detailed description in the Description box so that people know what your app provides.

Long-Story-About-Power-BI-6-Publish-and-share-6-Build-apps-02

On the bottom of the dialog box, you can upload an image for the app and then select the dashboard to include in the app. When you publish, the app is added to the organization’s content gallery.

For more information, see Publish an app in Power BI

Use apps

Use apps

In this lesson, you will create an instance of an app for everyone in a group.

Video: Use apps

Start in My Workspace, select the group that you previously created, select My Organization to browse your content packs, and then connect to your app.

Long-Story-About-Power-BI-6-Publish-and-share-7-Use-apps-01

Power BI imports the dashboards, reports, and datasets that are in the app.

When you select the dataset, Power BI asks if you want to personalize the app. Create a copy of the app that you can use to make changes to yet keep it disconnected from the published version of the app. By creating a copy, you won’t automatically receive updates if the app creator makes changes to the published version of the app. You can edit the dashboard, the report, and even the dataset if you want.

Long-Story-About-Power-BI-6-Publish-and-share-7-Use-apps-02

Update apps

To edit an app that you created previously and see how other people use your app, start in the Power BI Service in My Workspace. Anytime that you make changes to the dashboard, you’ll get a reminder that you’ve changed something that you’ve shared with others in an app. Power BI will prompt you to update the shared version.

Long-Story-About-Power-BI-6-Publish-and-share-7-Use-apps-03

Return to the settings icon and select View app to see the apps that you’ve already published. When you select Edit, you’ll return to the screen where you can edit the title and description. This time, the screen includes an Update button.

Long-Story-About-Power-BI-6-Publish-and-share-7-Use-apps-04

Power BI processes those changes and publishes the updated app to the app gallery. Anyone who has connected to your app will receive a message that the app has changed, and they will have the option to accept the changes or to keep the older version. As the app owner, you can manage the versions that your colleagues are using.

For more information, see Change your published app

Integrate OneDrive for Business with Power BI

Integrate OneDrive for Business with Power BI

You can use your Power BI and Office365 groups to collaborate and share by using Microsoft OneDrive for Business.

Video: Use data from Microsoft OneDrive

OneDrive for Business is a potential storage location for your Power BI content that provides version history. You can share your files with an Office365 group to enable several people to work on the same Power BI or Excel files.

Long-Story-About-Power-BI-6-Publish-and-share-8-Integrate-OneDrive-for-Business-with-Power-BI-01

To connect to a PBIX (Power BI Desktop) file on OneDrive for Business, sign in to the Power BI service and select Get Data. Under Create new content, select Files, and then select OneDrive – Business. Highlight the file and then select Connect.

Long-Story-About-Power-BI-6-Publish-and-share-8-Integrate-OneDrive-for-Business-with-Power-BI-02

Your content appears on the left-hand side navigation bar. File changes on the OneDrive for Business page will automatically reflect in the Power BI environment and will be recorded in the version history.

For more information, see Connect to files stored in OneDrive for your Power BI app workspace

Publish to web

Publish to web

In this lesson, you’re going to share a Power BI report on a webpage or share it through email. This feature of Power BI is often referred to as Publish to web.

Video: Publish reports to web

In the Power BI service, select the report that you want to share so that it’s displayed on the canvas. Then from the menu, select File > Publish to web. A dialog box will appear, explaining that you’ll receive an embed code that will allow you to include the report on a website or in an email.

Long-Story-About-Power-BI-6-Publish-and-share-9-Publish-to-web-01

When you select Create embed code, Power BI presents another dialog box stating that you’re about to share your data with everyone on the Internet. Verify that sharing publicly is acceptable before moving ahead.

Power BI presents a dialog with two links:

  • A link that you can share in an email, which shows the report as a webpage
  • HTML code (a link plus within an iframe) so that you can embed the report directly into a webpage

For the HTML link, you can choose from predefined sizes for the embedded report, or you can modify the iframe code and customize its size.

You can paste the email link into a browser and see your report as a webpage. You can interact with that webpage just as you would if you were viewing the report in Power BI. The following image shows a Publish to web page when its link was copied directly from that dialog box into a browser.

Long-Story-About-Power-BI-6-Publish-and-share-9-Publish-to-web-02

You can also embed that iframe link into a blog post, website, or Sway.

If you want to delete an embedded code that you created, Power BI can help. In Power BI, select the gear icon in the upper-right corner and then select Manage embed codes.

The Power BI workspace shows the embed codes that you’ve created.

For more information, see Publish to web from Power BI

Ask questions of your data with natural language

Ask questions of your data with natural language

Sometimes, the fastest way to get answers about your data is by asking questions in the Q&A feature of Power BI.

Video: Ask questions in natural language

Note: Currently, Power BI Q&A only supports answering queries that are asked in English; however, a preview is available for Spanish that can be enabled by your Power BI administrator.

Explore Q&A

You can use Q&A to explore your data by using the intuitive, natural language capabilities of Power BI and receive answers in the form of charts and graphs.

Long-Story-About-Power-BI-5-Explore-data-4-Ask-questions-of-your-data-with-natural-language-01

Ask a question

Ask a question about your data in Q&A by using natural language. Natural language refers to the ordinary language that humans use to communicate with one another every day. An example would be, “What are the total units by region?”

Long-Story-About-Power-BI-5-Explore-data-4-Ask-questions-of-your-data-with-natural-language-02

Q&A is available on dashboards and reports in Power BI. Go to the dashboard and place your cursor in the question box to open the Q&A screen.

Long-Story-About-Power-BI-5-Explore-data-4-Ask-questions-of-your-data-with-natural-language-03

If the visuals’ axis labels and values include the words salesaccountmonth, and opportunities, then you can confidently ask questions. For example, “Which account has the highest opportunity” or “Show sales by month as a bar chart.”

Other helpful items are provided on the side of the screen. For each dataset, Q&A shows you keywords and occasionally shows you some sample or suggested questions. Select any of these to add them to the question box.

Another way that Q&A helps you ask questions is with prompts, autocomplete, and visual cues.

Long-Story-About-Power-BI-5-Explore-data-4-Ask-questions-of-your-data-with-natural-language-04

Q&A visuals

Q&A picks the best visual based on the data that is being displayed. For example, numbers might be displayed as a line chart while cities are more likely to be displayed as a map.

You can also tell Q&A which visual to use by adding it to your question. Q&A will prompt you with a list of workable visual types. By using the previous example, you could ask, “What are the total units by region by pie chart?”

Long-Story-About-Power-BI-5-Explore-data-4-Ask-questions-of-your-data-with-natural-language-05

For more information, see Create a visual with Power BI Q&A

Create custom Q&A suggestions

Create custom Q&A suggestions

With Power BI, you can add your own suggested questions for others who use the natural language query box.

Video: Adding custom questions

Users will see your suggested questions when they ask a question.

Long-Story-About-Power-BI-5-Explore-data-5-Create-custom-Q-A-suggestions-01

To add your own questions, select the ellipsis next to the dashboard that you want to use. Select Settings from the menu. You can completely disable the Q&A search input box from the Dashboards section of the Settings page.

Long-Story-About-Power-BI-5-Explore-data-5-Create-custom-Q-A-suggestions-02

To add questions, select the Datasets section. All datasets that are associated with the dashboard are displayed. Select the dataset that is associated with your dashboard from the list, select Featured Q&A questions, and then select the Add a question link. Enter your question or prompt into the input box and then select Apply.

Long-Story-About-Power-BI-5-Explore-data-5-Create-custom-Q-A-suggestions-03

When anyone selects the search input box, they’ll see your suggested entries at the top of the prompt list. Custom questions are a valuable way to get dashboard users to think about the type of data that is available and how to best use it.

For more information, see Create featured questions for Power BI Q&A

"The purpose of a business is to create a customer who creates customers" — Shiv Singh

Our reference list:

Liked our service? Start a project with us.