Visualizing Data with Power BI

Visualizing Data with Power BI

Have you ever wondered how Power BI visuals could make your business operations and data processing more effective? Let us track back a little bit.

The 3 Components

In the previous installments, we reviewed two of the three components of Power BI desktop.

The first component allows you to connect to a multitude of data sources to import your data into Power BI. Common data sources include a database connection or an Excel file. You can also connect to multiple data sources. In addition to that, you can import data from more than one source into a single PBI solution.

The second component is the data model. Once the data is imported into Power BI, we can construct the foundation for what our reports will be built on. The relationships formed in the data model are what allows us to pivot and slice data to view it from new and insightful angles. They let us view the data from a high-level view or drill down into the granular level and examine the details, and they’re the levers being pulled beneath the canvas when report building.

The third component is the report builder. When it comes to visualizing data, Power BI offers a full suite of chart types, metrics, and filters in which to view our data. Within one Power BI solution, you can build one report or a whole collection of reports, and within each report, you can include one or many Power BI visuals. But what’s the purpose of visualizing data and why should we take the time to build a report in Power BI in the first place?

Seeing is Believing Through Power BI Visuals

Although Power BI is a lightweight, easy-to-use, and powerful tool, it still takes time and resources to construct a Power BI solution. You have to import data, design a data model, and build a report. But these steps are an investment, and Power BI is a tool that will pay off in terms of saving time and providing value.

When looking at a table of words and numbers, you may have a trained eye to spot trends or key indicators. But the human eye is much more efficient at extracting information when seeing an actual line or columns stacked against each other. Power BI can present your data in a variety of ways to help you make sense of your business and quickly identify the important data points or trends. Let’s dive into the report builder and examine a sample report.

Pivoting to Power BI

Purposefully, Microsoft modeled Power BI’s report builder to be similar to Excel pivot tables. Whether you’re an analyst, a manager, or an executive, chances are you’re more familiar with Excel than Power BI, so we should be able to jump right into Power BI’s report builder with a minimal learning curve.

In this article, we will continue to use the Retail Analysis data in which we built a data model in the previous article Analyzing Data with Power BI. Let’s start with a blank report.

The report view, similar to an Excel pivot table, has a section on the left to visualize our data and a Fields pane on the right to add or remove data points from our visual. You’ll also notice there are a few extra tabs, one called Filters and the other called Visualizations.

To begin visualizing our data, we’ll start by navigating the Fields pane. We’ll drag-and-drop in This Year Sales by Store Number Name. Instantly, Power BI creates a column chart for us.

Focus by Slicing

Unlike traditional canned reports, Power BI reports are interactive. One way to interact with reports is Slicers. Slicers allow you to focus on a specific part of a chart and temporarily hide the other parts you’re not interested in. For example, let’s navigate to the Fields pane and drag-and-drop the District Manager field onto the report builder. Then, under the Visualizations pane, we’ll select the Slicer option. We know the District Manager field has been converted into a Slicer by the checkboxes appearing next to each manager’s name.

In our data model, we built a relationship between the District table and Store table to define one district manager can oversee many stores. Power BI’s report builder uses this relationship to slice our report. Let’s look at only Brad Sutton’s stores by selecting the checkbox next to his name.

As you can see, the report instantly changes to only show Brad Sutton’s stores. By simply unselecting the checkbox next to his name, the report will revert to its full view of all district managers.

Feel the Power of Power BI Visuals

Column charts are just one type of visual available in Power BI. Line charts, scatter plots, and donut charts are a small sample of the different ways to view your data. You can even write custom code in languages like Python should you have a need that isn’t available out of the box.

With Power BI, you get the ability to visualize your data in a clean and modern design that is easy on the eyes, so you can quickly gain insights from your business—even the ones you didn’t know were there.


Analyzing Data with Power BI

Analyzing Data with Power BI

Before we get to the finished product of an interactive report or dashboard, we first need to connect to our raw data source or sources. In addition to that, we need to build a robust data model through analyzing data with Power BI Desktop.

In short, the data model is the foundation of a Power BI report. When configured correctly, a data model is what allows us to slice data at any angle. It drill down from the high-level overview to the granular level data and gives us the ultimate flexibility to view our data.

From there, we can analyze our data and perform any calculations necessary to convert our raw data into real, valuable business outputs. If you recall from our previous article (What is Power BI?), Power BI Desktop is a free application that is compatible with Windows.

It has three components: a data connector, a data modeler, and a report builder. Let’s start with the data connector.

Connecting to Your Data

Power BI Desktop lets you connect to a variety of data sources. These include raw files, databases, Azure, and a plethora of others. You can connect to multiple sources at one time, but for this exercise, we’ll use the Retail Analysis sample Power BI workbook available for free download from Microsoft.

Once the data is loaded, we can view our entire dataset. This dataset has five tables: Sales, District, Item, Store, and Time. The Sales table is the largest with almost one million rows.

Power BI Desktop is equipped to handle a sizable amount of data, but for very large datasets, or Big Data datasets, it may be best to use DirectQuery instead of importing all of your data into Power BI Desktop. This method queries the data from its data source and only pulls in the query results at the time of its execution. We’ll examine DirectQuery in-depth in a future installment.

As we further examine our dataset, we can make changes if necessary. For example, we can clean up the dataset by deleting the columns that we don’t need for this report. Note: this does not impact the original data source. Whatever changes we make in Power BI will not affect the original Excel spreadsheet, including the creation of new columns and formatting existing ones. For our sample, we have converted the Sum_Regular_Sales_Dollars column from number to a currency format. After we’ve completed our data cleansing and transformation tasks, we’re ready to create our data model.

The Data Model

Data modeling requires a working knowledge of database and data warehousing techniques, but once you get a hold of the basics, Power BI Desktop provides a simple and intuitive visual interface that makes data modeling a breeze. The Retail Analysis dataset has one fact table, or the dependent variable outputs that will appear on a chart’s y-axis, called Sales and four dimension tables, the independent variables that will appear on a chart’s x-axis.

The Sales table is the central component of our data model. The Store, Item, and Time tables feed into the Sales table, and the District table feeds into the Store table, which indirectly feeds into the Sales table. These table relationships can be created by using the drag-and-drop interface or by utilizing the Manage Relationships tab.

Also, we can modify the properties for each table and each field. Options include renaming the field, changing the data type or format, or giving a description of the field. Below we can see the Buyer field is selected within the Item table.

Power BI data models are meant to be revised and tinkered with. It may take many iterations to nail down your analysis and built the optimal report. You can switch back and forth between your data tables and data model. Updating the data tables will update your model, so plan accordingly. In your data model, you can add or delete facts and dimensions, or show or hide certain fields to keep your report clean. You can start small with a few tables from one data source then add more as you go along. Power BI Desktop offers the ultimate combination of flexibility and functionality.

See the Results of Analyzing Data with Power BI

The data model is the iceberg beneath the surface. It’s the foundation for insight. The data model uses the relationships you created between facts and dimensions to slice and pivot the data and present it in new and different ways. Power BI’s clean and interactive reports help you spot trends, key data points, and new insights. We’ll cover Power BI’s visualizations in the next installment.


What is Power BI?

What is Power BI?

The business intelligence, or BI, space is a crowded market that’s why most people are also confused about what is Power BI. In line with that, there are many viable solutions that can meet an organization’s needs. Each solution represents its vendor’s unique approach to BI. Some vendors focus on smaller businesses while others focus on large enterprises.

Moreover, some are adept at the data organization and manipulation side, and others shine on the reporting end. According to a highly regarded research and advisory firm, Forrester, Microsoft’s PBI platform is the number one solution for BI.

In addition to that, Forrester releases quarterly assessments called the Forrester WaveTM that analyze various industries and rank their top players. On July 29, 2019, Forrester released its Enterprise BI Platforms (Vendor-Managed) Forrester Wave, and determined Microsoft Power BI to have the strongest offering in the market and the largest market presence.

Other notable platforms included (in no particular order): TIBCO Software, Salesforce, Oracle, Sisense, and Tableau Software. So what is PowerBI, and what makes it the market leader in BI?

In Microsoft’s own words, PBI is, “a business analytics solution that lets you visualize your data and share insights across your organization or embed them in your app or website.” Essentially, Power BI is a collection of products and services bundled into one platform. Its product offerings cater to businesses from small to enterprise and technical and non-technical users. Let’s start with the core product: Power BI Desktop.

Power BI Desktop

Power BI Desktop is a free application from Microsoft. It’s made for a single user––particularly an analyst with some technical proficiency––to build a report from scratch. It has three components.

The first is to connect to your data, which includes the ability to pull from a variety of sources such as an Azure SQL database or an Excel spreadsheet. The next component is for data modeling. Organize, structure, and transform your connected data to prepare it for the final component––visualization.

This is the report builder that automatically links to your data model. Use the drag and drop interface to create a simple, single report or a sophisticated dashboard. Once you’re satisfied with the report, you’re ready to share it with the rest of your team or organization with Power BI Report Server.

PBI Report Server

PBI Report Server is a paid service that let’s Power BI users publish and distribute their reports. On the server, you can organize reports and manage who has access to each report. It also lets you share reports with others outside of your organization as well. You can give controlled web access and distribute your reports across mobile devices, so they can be viewed on the go.

It can be upgraded to PBI Premium for big enterprises that require large deployments and resource-intensive workloads.

PBI Mobile

Power BI Mobile is a free native mobile app available for Android and iOS, including the Apple Watch. Once a report is created and published with PBI Desktop, you can view it on your mobile device, so you can stay up to date on your business from anywhere.

Further, it has the functionality to annotate reports with your touch-enabled screen and share your new insights with your team. You can also configure the app to receive push notifications for personal data alerts.

Power BI Embedded

With PBI Embedded, you can seamlessly integrate Power BI into your custom applications. It is a complete solution that gives software developers the tools to take any PBI report and embed it anywhere into your application.

This way, you can leverage the work you’ve already done with PBI and create customer-facing reports and dashboards without having to build and maintain them from scratch.

The Market Leader

By dividing its platform into distinct products, Microsoft Power BI makes it simple and easy for a new user to quickly test the waters with Power BI Desktop while providing the full bevy of features, functionality, and resources through its other products to dedicated and power heavy users.

It has become the top market leader for BI solutions and is continuing to grow and strengthen its platform. The next time you consider a BI vendor, make sure Microsoft is at the top of your list.


What is Business Intelligence?

What is Business Intelligence?

February 19, 2020

In today’s competitive climate, informed decision making by business intelligence at all levels of the organization is a key component to a business’s success and longevity.

How It Used to Be

In the past, a typical organization detached its IT department from the rest of the business. Because of this siloed approach, business intelligence served the organization through an order-and-delivery approach.

For example, the business would place a request to the IT department for a set of reports. Now the IT department would produce the reports, and then deliver them back to the business. This allowed the business to receive reliable reporting on a scheduled basis.

Typically, the reports were identical, so the business knew exactly how to interpret their data and could make confident decisions. But the rigid, time-delayed order-and-delivery approach has many constraints.

The canned reports only provided a limited view of the business. Few members of the organization had access to their data. Then, businesses began to see the advantage of integrating technology into their workflows instead of isolating it.

In each department and at every level, they wanted customized reports tailored to their specific processes. These were then delivered on an ad-hoc basis, sometimes on-demand. And they wanted to be able to view their data in real-time, or as close to it as possible. With so many requests of so much variety, the IT department became overwhelmed and couldn’t fulfill all the orders.

They didn’t have the subject matter expertise of the business to produce these customized reports in an accurate and meaningful away. When they did, the business wanted to tweak and adjust the reports based on their analysis, which led to more orders.

The lack of proper communication and integration from both sides of the house led to frustration and inefficiency. Out of necessity, new approaches and new technologies were born.

Business Intelligence Today

Now, businesses are turning to fully integrated IT and business solutions that meet the high demands of flexible, sophisticated analysis in a timely fashion. These solutions put the organization’s data at the fingertips of its analysts, senior-level, and management-level employees.

In addition to the traditional canned reports, they can do ad-hoc analysis to create single-use reports for unique situations that might not happen again. And they allow for self-service, which lets those without a technical background to be able to structure and analyze an organization’s data with minimal training. Further, business intelligence solutions are incorporating advanced analytics onto their platform.

The Cutting Edge

The next evolution of business intelligence is called augmented business intelligence, and it has arrived. These solutions are utilizing artificial intelligence and machine learning to automate the business intelligence flow and perform advanced analysis on the data to find deep insights. Microsoft’s Power BI is on the cutting edge of this new technology.

It’s is a suite of business intelligence tools and services that helps businesses make fast, data-driven informed decisions. For businesses that are seeking to gain a competitive advantage, Power BI may be one solution to help guide them to future success. We’ll examine Power BI in our next installment.