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

"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.