Every information you access is data or even the vice-versa is true. The business world is generating data in leaps and bounds, and there is no limit to it. Plus, in the data-driven world, every action you perform ends up creating a record, and modern business is overwhelmed with data. So what is that one way to manage this humongous amount of data? If you are thinking of getting actionable insights by evaluating the dataset, then you might need a tool for it. Ever heard of what is Power BI ?
Table of Content
Definitely, there are very few people who aren’t introduced to this power tool. Power BI is a SaaS-based data collective business intelligence platform powered by Microsoft. From the inducement of Power BI in 2014, Microsoft has provided regular updates and made it more useful for Power BI customers. Power BI works on converting your raw and scattered data into interactive visualizations at one place that helps users in making business decisions. Hundreds of certified connectors are provided in the Power BI platform to connect data from different systems and even from files.
With this post, our SharePoint Developers have explored some of the most influential aspects of Microsoft Power BI before we start using it. Let’s know deeper about what is Power BI?
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.
To define Power BI – it 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. Business intelligence platform powered with excessive information helps businesses take the right decision at the right time.
The operability of the Power BI site is very simple. It extracts the data from multiple sources outs them together, and it intelligently gets converted into a 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 on cloud. And now if you have a question, what is Power BI, a mobile app or a web application, or how can you use it?
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 or 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 in different platforms.
Let’s see how each one of these Power BI tools work distinctly.
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:
- Connect, transform, and model data.
- Connect multiple sources into one dataset and generate reports.
- 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.
- Choose a color theme from existing templates and also create a new one with your own set of colors.
- Setup rules for row-level security.
- Power query editor and DAX queries to shape your data.
- Performance check for visualizations.
- Support Python.
- Publish reports in Power BI service.
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, lets 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 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, setup 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.
Power BI Connectivity Types
Power BI provides majorly four types of connectivity depending on the data sources connector you are using.
- Composite (Mixed-mode)
These different connectivity types have their benefits and limitations. You should check all types to choose the best suitable options.
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 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.
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 the 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 gets 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 does not supports this till last year. There is restrictions to some transformations in Power Query like change column data types, split columns, remove 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.
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 connection in 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 on data transformation and authoring. Only report level DAX measures are available to add. These measures 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.
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 other 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:
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.
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 load pre-summarize data in Power BI that will reduce its size and after importing in Power BI again, with reduction in 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 that numeric columns with high margin 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 in data reduction.
- Preference for custom columns
Power BI provides facilities to create custom columns in tables. 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 in Power BI will reduce the calculation efforts inside the engine and increase the performance.
- Disable Power Query Load
By default, Power Query 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 date. These fragments of instances 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 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 direct query option and other data can reside on the dataset.
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. It also gives clarity on how eliminating unnecessary storage space using Power BI can make tasks efficient. We hope this was an insightful blog, that shows 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
Shital Patel is VP at TatvaSoft with a high-level of proficiency and technical precision in SharePoint Development. His experience of the last two decades has helped businesses to solve complex challenges resulting in growth and performance of Startups to Fortune 500 companies.
Know more about our SharePoint Development ServicesLearn More
Build your Team
Want to Hire Skilled Developers