What is Power BI?

1. Introduction to Power BI

Often what businesses assume is Power BI is just a business intelligence tool. Well, that’s just partly correct because it is much more than that. Microsoft Power BI is actually a data visualization tool that converts data from varied sources into visually interesting and interactive dashboards. 

Define Power BI – is an intelligent business intelligence tool that can be used for cloud-based apps or other organizations to collate, manage and analyze data from different sources in a convenient way. A business intelligence platform powered with excessive information helps businesses make the right decision at the right time.

The operability of the Power BI site is very simple. It extracts the data from multiple sources puts them together and intelligently gets converted into visually compelling data. This data can be used to make informed data-driven decisions. It consumes information on Power BI reports in the form of graphs, charts, snapshots, and other multimedia formats. 

If we were to define some specific sources of Power BI then it would include countless data sources like Excel spreadsheet files, word docs databases, and other information that’s available on-premise data sources or in the cloud. 

The answer is- Power BI is all of this. It is a Windows-based desktop application also called Power BI desktop, and it is also an online SaaS-based app that can be conveniently accessed from all locations also called Power BI service. It is also versioned as a mobile app for Windows, iOS, or Android-based phones or tablets. These all different versions of Power BI like SaaS, Desktop, and mobile Power BI apps are used on different platforms. 

Let’s see how each one of these Power BI tools works distinctly.

2. Power BI Desktop

Microsoft Power BI Desktop is a free data visualization tool that you install on Windows computers to create reports and visualizations for yourself. It supports different databases and systems to extract business data. It is an extremely handy tool for data scientists and developers to transform your information into meaningful visualizations. Power BI Desktop also offers data warehouse capabilities along with data discovery and preparation. You can prepare reports using this tool but sharing of information is not possible within the tool.

Power BI Users can:

1. Connect, transform, and model data.
2. Connect multiple sources into one dataset and generate reports.
3. A vast variety of visualizations are provided to add to your Power BI report. Additionally, you can install custom visualizations from the market or develop customized Power BI visuals for yourself.
4. Choose a color theme from existing templates and also create a new one with your own set of colors.
5. Setup rules for row-level security.
6. Power query editor and DAX queries to shape your data.
7. Performance check for visualizations.
8. Support Python.
9. Publish reports in Power BI service.

3. Power BI Service

Microsoft Power BI is a cloud service platform that allows users to create interactive dashboards and share the developed reports. It supports light report editing and has a prime focus on the collaboration of teams and organizations. Most of the features of the Power BI desktop are supported in Power BI services too. Power BI service tools, let users share their own reports and among other users who can access these reports and Power BI dashboard using a Power BI website or Power BI mobile apps for Windows, Android, and Apple.

Power BI service has below extensive features to explore:

• Share dashboards and reports with other Power BI users.
• Users can subscribe and set alerts on reports and the Power BI dashboard.
• Setup permissions – who can view and edit your reports.
• Manage row-level security.
• Create different workspaces.
• Create Power BI apps to securely share dashboards and reports of business intelligence to other users in teams.
• Analyze data in excel.
• Securely share reports in other systems via embed API.
• Schedule data refresh and set up on-premise gateways for legacy data.

Few things you could not achieve using only Power BI Service and hence you need Power BI Desktop:

• Add/edit rules for row-level security.
• Create calculated columns.
• Advanced query editor.
• Python and DAX support.
• Data transformation and modeling.

4. Power BI Report Server

Power BI Report Server is known for its on-premises services that it offers as a report server with a web portal that enables the users to display and manage reports and KPIs. Power BI report server comes with paginated reports, Power BI reports KPIs, and mobile reports. This makes the end-user of any business site access reports in various different ways like getting an email of it in their inbox or viewing them on a mobile device or web browser.

Power BI Report Server offers some extensive features like –

• Invert and continuous axis sorting.
• CALCULATE filter
• Smart guides for object alignment.
• CROSSFILTER to support different relationships
• Visual Zoom Slider
• ArcGIS to support Power BI

5. Power BI Mobile

Power BI Mobile enables the users to stay connected to their business data from anywhere and at any time. This means that mobile BI is just a touch away. It enables every business owner and its employees to monitor the business from the phone and help them access on-premises data that is stored in the data cloud or SQL server. Power BI Mobile applications offer a 360-degree view of all the information.

Power BI Mobile offers some extensive features like –

• Flexible and secure mobile access
• Push notifications for data alerts
• Easily annotate reports with a single touch
• Reports for mobile users with a live dashboard 

6. Power BI embedded

Power BI embedded analytics is something that enables the users to embed their Power BI content like dashboards, reports, and tiles, in a website or web application. It helps in offering compelling data experiences to the end-users of your business and enables them to act as per the insights they get from the data. It also offers exceptional customer-facing reports, analytics, and dashboards in the business app. Besides this, Power BI Embedded is helpful in reducing the developer resources as it automates everything from app monitoring to deployment of analytics.

Power BI embedded offers some extensive features like – 

• It offers hourly services without any usage limit.
• It is a cost-effective solution for businesses that want to have powerful business intelligence.
• Helps in merging its capabilities with Power BI Viewer.
• Facilitates implementation of data governance aspects.

7. Power BI Benefits

Power BI is Secure

Power BI comes with various security features that help business owners to protect important and sensitive data. It also enables businesses to meet security and compliance standards. For instance, with the use of Microsoft’s Cloud App Security feature, Power BI offers sophisticated analytics to combat cyber threats. Besides this, sensitivity labels of Power BI make it very easy for admins to alert customers about what data is sensitive. 

Power BI Offers Business Intelligence for All

Power BI is a platform that empowers different types of organizations to create data-driven cultures. This means that the business decisions are made as per the data or information companies have. It results in organizations accomplishing difficult tasks with the help of business intelligence assets. Basically, Power BI enables companies to create an effect where all the employees of the firm can make decisions according to trustworthy and real-time data.

Power BI Easily Connects With Data Sources

Power BI enables the connection of myriad data sources and this includes everything from file data sources such as CSV and Excel to database sources like Snowflake and Oracle database to online data sources such as Adobe Analytics and Salesforce.

Power BI is Improving Everyday

With each passing day, Microsoft is pouring money and time to improve Power BI and this shows its dedication to making it the best data analytics platform in the world. Every now and then new features are added to this tool and the existing ones are improved and tweaked. 

Power BI has Artificial Intelligence Capabilities

Power BI comes with knowledge of artificial intelligence that enables the users to get valuable information, data, and reporting. Besides, it also provides three powerful AI visualisations that are useful to software developers when they need to dive deep into important data and generate insights.

8. Power BI Features

Datasets Filtration

Dataset means the set of data that are created to gather data from different data sources. The developers use datasets to create different kinds of visualisations. The dataset can be created by gathering data from a single source such as an Excel workbook. Then one can filter the datasets and can create smaller subsets that hold important information. In this case, Power BI offers a wide range of in-build connectors like Oracle, Facebook, Excel, Salesforce, SQL database, and more to the users who can easily use these sources and create datasets.

Flexible Tiles

Tile means a single block that comes with Power BI dashboard visualization. Tiles are generally used to separate each informative visualization and this provides a clearer view of data. These tiles are adjustable and can be placed anywhere in the Power BI dashboard according to the user’s convenience.

Informative Reports

In Power BI, reports are a combination of different types of visualization on dashboards that are relevant to specific business topics. A report displays a structured presentation of the business data and also reveals insights from it. This helps the users to easily understand the graph of the business and it can also be shared with other users or employees of the firm.

9. Components of Power BI

Power Query

Power Query is a component for data transformation. It enables the developers to find, connect, and combine the data sources to meet the required needs.  Business analysts use this to transform, integrate and enhance big data into Power BI web service.

Power View

Power View is available in SharePoint, Excel, SQL Server, and Power BI. This technology helps in creating interactive graphs, charts, maps, and more. 

Power Pivot

Power Pivot is a component that follows a data modeling technique to help users develop data models. It uses Data Analysis Expression (DAX) language for modeling both simple and complex data.

Power BI Desktop

Power BI Desktop is a tool for Power Pivot, Power Query, and Power View. It enables us to have all information under one system.

Power Map

Power Map is used for Power BI and Excel. It is a 3-D data visualization tool that allows the users to map the business data and plot millions of rows to visualize data on Bing Maps.

10. Power BI Connectivity Types

Power BI provides majorly four types of connectivity depending on the data sources connector you are using.

1. Import
2. DirectQuery
3. LiveConnection
4. Composite (Mixed-mode)

These different connectivity types have their benefits and limitations. You should check all types to choose the best suitable options.

Import

Import is the most important yet common feature of Power BI where almost all data sources can be used. It is extremely important for businesses to understand all the data that is to be imported and can be stored in a PBIX file. Storing data within the file and in-memory increases the performance for retrieving, querying, and loading reports and thus makes this connectivity type the fastest among all types. If the tables are large then this application is not applicable. Since the performance is dependent on the memory and machine processor, you will feel slowness during development while using the Power BI desktop tool on a local computer with a large amount of data.

Import connectivity type provides you to use the full capabilities of the Power BI desktop tool. DAX functions are fully supported by this. Also, it stores all data in memory. Power BI lets you have the benefit of using the full capacity of datasets as permitted by licenses of Microsoft Power BI.

When you are using Import type, you will have Reporting, Data, and Modelling – 3 option tabs displayed in the Power BI Desktop tool.

DirectQuery

The next in line is the Direct query. As the name suggests, it directly fires queries of data from the source. You cannot store any data in the Power BI report file in the Import connectivity type. DirectQuery connectivity type is available only with relational database sources. The Power BI allows the storage of only the metadata of the source i.e.: table name, field names, relationships, etc in the Power BI file, except for actual data. This gives a major benefit when working with large data tables to achieve 1 GB restrictions of dataset size.

When interacting with Power BI reports, it requests data as per the applied filters into the data sources using the details stored in the file. Also, when data often get updates, you will get nearly real-time data with this connection type. Performance may decrease since the firing of queries is in real-time and no data is available in the file. But there are several techniques provided to minimize the queries to the source.

Since the data storage is not in the Power BI file, it has certain limitations in the capabilities of the Power BI desktop. Many DAX operations did not support this till last year. There are restrictions to some transformations in Power Query like changing column data types, splitting columns, removing duplicates, etc. Also, the returning of data in DirectQuery has limitations to 1 million rows, unless you have a Power BI premium license.

When you are using the DirectQuery type, you will have Reporting and Modelling – 2 option tabs displayed in the Power BI Desktop tool.

LiveConnection

You cannot store data in the Power BI file for the LiveConnection connectivity type. All the data is queried using LiveConnection in the existing analysis Services model to interact with reports. You can only use Azure Analysis Service, SQL Server Analysis Service, and Power BI datasets in Power BI services within this connection type. Since these sources are analytical services, performance for querying is much better than DirectQuery. Generally, we use Power BI for live connections in an enterprise deployment.

As the data is not stored in the Power BI file, the majority of people get confused between DirectQuery and LiveConnection types, but both are so much different and cannot be used on behalf of each other.  Since Analytical Services works on data, you will not have much freedom in data transformation and authoring. Only report-level DAX measures are available to add. These measures are in the Power BI file but cannot make changes in the Analysis Service model data.

You will have all reporting capabilities when you use LiveConnection and thus only the Reports option tab is in display mode in the Power BI desktop tool.

Composite (Mixed-mode)

In the past, Developers couldn’t create connections to multiple sources using Import and DirectQuery in a single Power BI report. But now it’s possible using the Composite connection type. You can include one table from the SQL server using DirectQuery type and another as an Import connection type in the same report. This way you can include a small amount of data in the Power BI file and connect large tables with DirectQuery type.

You have seen the variations in connection types to connect data sources in Power BI. This variation gives you the power to accommodate different types and sizes of data sources at your ease.

Let’s see licensing info for Power BI cost, luckily they have only 2 variations in that:

11. Licensing Information

Power BI service has 2 licenses: Power BI Pro license – to get started with and Power BI Premium license – advanced data analytics, big data support, and dedicated cloud compute.

Read More about Power BI + Google Analytics = Power Analytics

Have a look at the below comparisons before choosing licenses for your use:

Features	Power BI Pro	Power BI Premium
Pricing	10 $ per month per user	4995 $ instance per month
Included with office 365 Enterprise E5	Yes	No
Dedicated cloud compute and storage resources	No	Yes
On-premise reporting through Power BI report server	No	Yes
Compute Processing environment	Shared	Dedicated
Content deployment in multiple regions	No	Yes
Incremental data refresh	Yes	Yes
Data refreshes per day	8	48
Allocate compute resources	No	Yes
Monitor performance for compute resources	No	Yes
Maximum size of individual dataset	1 GB	10 GB
Maximum storage	10 GB Per User	100 GB
Data security encryption	Yes	Yes

You may notice the supported dataset size that Power BI supports in the above table. Perhaps you feel that Power BI is not useful when you have large data to serve. But this will surprise you to know that Microsoft uses some data compression techniques called VertiPaq storage engine to minimize the size of data after importing.

VertiPaq storage engine compresses the data and reduces the size up to 10x smaller. Although there is no specific equation that defines it . It depends on how you have managed the architecture of your raw data. Usually, we see a compression in data from 10 GB of source data and made of 1 GB in size in Power BI reports.

Despite the VertiPaq storage engine compressing the data, it is important that you only load the required data in Power BI to minimize the data source size. Because the gradual data refreshes in the report will increase the data size. Finally, this will directly or indirectly have an effect on the performance of reports and visualization.

12. Data Reduction Techniques

There are 8 different ideas as per the suggestion of leading company  Microsoft to reduce the data size

• Remove unnecessary columns
  Microsoft recommends that you include only those columns in the model that are mandatory for reports. Your requirements may change over time, but you should be aware that data modeling and including new columns in models is easy.
• Remove unnecessary rows
  Microsoft recommends that you include only a few rows in the model that are mandatory in reports. By careful observations, you can set filters and allow only required rows in reports, which reduces the report size and also increases report performance. For example, you only need current year sales data for your report then instead of including all year sales data, you can filter and include only this year’s sales data rows in reports.
• Group by and summarize
  These charts and visualizations need summarized data, and SharePoint developers normally pull all data first in Microsoft Power BI and then shape them accordingly. Instead, you can pre-summarize data in Power BI that will reduce its size, and after importing to Power BI again, with a reduction in the dataset. You can shrink the dataset size which is one of the effective ways to eliminate multiple rows and columns.
• Optimize Column data types
  VertiPaq storage engine has different methods for each column for compression. There is compression seen in numeric columns with high margins and perhaps decreases the size of the dataset. So, it’s advisable to check and set proper data types for columns in the table after importing the data. For example, if you have one column “Lead Number” with alpha-numeric values, like “L00001, L00002, L00003…”. Power BI will detect this as a text column due to alpha-numeric values. Since the prefix of the numbers is already fixed in this column, you can remove the prefix and the column converts into number type. For the large tables, this minor change will give a huge effect on data compression and thus data reduction.
• Preference for custom columns
  Power BI provides facilities to create custom columns in tables. The VertiPaq storage engine stores custom columns just like Power Query sourced columns. These columns are less effective during data reduction and take more time during data refreshes each time. Also, it’s advisable to add custom columns via Power Query editor instead of using direct Dax queries on models because Dax query custom columns are built once all Power query tables are refreshed and it increases the refresh time. However, if you create these calculated columns in the SQL server or any other systems before importing into Power BI will reduce the calculation efforts inside the engine and increase the performance.
• Disable Power Query Load
  By default, Power Query is enabled in Power BI that fires a report to integrate data between different systems. But this has to be performed within the same report. To avoid the loading of the query, you can disable it in the query editor as shown in the below image



• Disable auto date/time
  Power BI Desktop has one option called “Auto Date/Time” which creates new data columns for storing data. These fragments of instances are displayed in the year, month, and day format for better filter options available. But when we use this for large tables in reports, these new columns increase the size of the dataset. You can disable this option for date-time columns on which you don’t need such filters.
• Switch to Mixed mode
  If you want to determine the storage of each table, the Power BI desktop application can help. It enables you to easily bifurcate and create space in the storage. It suggests using composite connectivity – mixed mode to get the data. Either you can import data in the Power BI dataset, or you can use direct queries in the source system. This will help you arrange data as per your need. The direct query option is very useful when there are large size tables and data needs constant updates. This mixed mode will be helpful for summarized data. It will help you tap on the direct query option and other data can reside on the dataset.

13. Final words

Our modern business world is continuously plunging in data. And as we know that every move we take, builds a new record of data. A Power BI is a Business Intelligence magic tool that benefits users in understanding how data works and makes business processes more productive. It also gives clarity on how eliminating unnecessary storage space using Power BI can make tasks efficient. We hope this is an insightful blog, that shows a true and practical sense of what is Power BI and how to use Power BI to manage your data strategically.

More Related Blog Post
BI Tools – Microsoft Power BI vs. Google Data Studio