How to Start Using Power BI
Feb 5, 2019
Have you been hearing that Power BI is the best and easiest way to analyze and visualize your company’s data?
Are you interested in learning how Power BI can benefit your organization?
Keep reading to find out how to start using Power BI.
What is Power BI?
Power BI is Microsoft’s latest reporting tool for building impressive visual reports that give insights and meaning to your data. Similar to SQL Server Reporting Services (SSRS), Power BI can create reports for your on-premises data or data sitting in the cloud. However, Power BI’s advantage over SSRS is the interactive data visualizations that you can create with just a few clicks.
Power BI Desktop vs. Power BI Service
Power BI comes in two options: Power BI Desktop and Power BI Service. Power BI Desktop is a free download tool from Microsoft that allows you to share your Power BI Reports with anyone in your organization if they, too, have Power BI Desktop. Power BI Service is Cloud-Based and does not require you to download any software to your computer. However, you must have an Office 365 login to use the Power BI App. Most people start with Power BI Desktop to create initial data connections and reports and then publish their Power BI datasets and reports to the Power BI Service to share their reports and create dashboards. Microsoft has also introduced Power BI Server for people and organizations that are not ready to move their data and reports to the cloud.
Create Your First Power BI Visuals
To get started, let’s focus on using Power BI Desktop to connect to an on-premise Data Warehouse. Then, we’ll show how to create powerful visuals. Finally, we’ll look at how to drill down into those visuals and show insights into your data.
Connect to an on-premise Data Warehouse using Power BI Desktop
First, I’ll start by opening Power BI Desktop. Next, I’ll sign into my Power BI Service account, so I can easily publish my reports to the Power BI Service. You must have an Office 365 account to sign into the Power BI Service. *Note that you do not have to have an Office 365 account to use Power BI Desktop.
Next, I’ll click the “Get Data” button inside Power BI Desktop:
After clicking “Get Data,” you’ll see many options for connecting to data that you want to analyze in Power BI, such as:
- Excel files
- Text / CSV Files
- Databases, such as SQL Server, SQL Server Analysis Services, and Oracle
- Azure data sources
- Many other options, including online services and even SharePoint!
In this example, I’m going to connect to a SQL Server instance I have running on-premises in my organization and then connect to the AdventureWorksDW sample database. This database is a free download from Microsoft and contains Dimension and Fact tables that all relate to bicycles and bicycle-related sales for the fictional company named Adventure Works.
Once you select Database, then SQL Server database, and click Connect, you’ll see this screen:
You’ll need to provide the instance name of your SQL Server database along with the Database name, and then click “OK.”
Notice two options here:
- “Import” connects to the data, compresses and caches the dataset into Power BI Desktop, and then closes the connection; you will use this option most of the time.
- “Direct Query” keeps a live connection to the data and is good for very large data sets.
Once you click OK, you will be prompted for your logon credentials the first time you connect. After connecting to that database, Power BI will save your connection string for future reports and datasets. Talk to your SQL Server Administrator to find out which credentials to use for your connection string.
Now, you should be connected to your SQL Server database.
How to Create Visuals and Gain Insight using Power BI
Once you’ve logged into your SQL Server database successfully, you will see the following screen which lists each of the tables from the database. Typically, I choose my Fact table first and then click “Related Tables” to see which tables have a direct Primary Key / Foreign Key relationship to the Fact table, as you can see here:
Notice two options for bringing data into Power BI:
- “Load” pulls in all columns and rows from each table selected on the left-hand side.
- “Edit” opens Power Query. Power Query allows you to choose which rows and columns you’d like to bring into your data set.
Once you click “Load”, you’ll see Power BI loading the data. Power BI compresses and caches all data from the tables you selected to make the dataset smaller and more efficient for use in Power BI reports.
Once Power BI is finished loading the data, you’ll see each of the tables and their respective fields on the far right-hand side of the Power BI Desktop interface:
This is where the real fun starts! You can now begin choosing fields from your data set to see Power BI begin building visualizations. Typically, I begin my Power BI reports by choosing a measure from the Fact Table and then building the report from there. In this first example, I chose the “SalesAmount” field from the FactResellerSales table and used the default visualization of “Clustered Column Chart.”
Next, I chose another data point, “SalesTerritoryCountry” from the DimSalesTerritory table. The report now shows Sales Amount by Sales Territory Country. I also resized the report and changed the formatting of the report by using the Format options under the “Paint Roller” icon:
Once I add in a second column, in this case the SalesTerritoryRegion column from the DimSalesTerritory table, and adjust the formatting, my report now looks like this:
Here is an image of the report zoomed in so you can see the details a little better:
As you can see, the visual report shows a breakdown of Adventure Works’ Sales Amounts by Country and also by Sales Territory Region. From this visual, I can see the countries with the highest and lowest sales. I can also see which region inside the United States has the highest and lowest sales. Note that only the US has multiple sales territory regions. You could use this visual to set sales goals for the coming year or set up focused incentives to sales reps in the territories whose sales you would like to see increase in the next quarter.
To change the visual you want, simply choose another visual from the Visualizations section as you see here:
This is just one example of creating a beautiful and meaningful report with Power BI Desktop. Next, try changing the formatting to suit your customer’s needs and use different visuals to give insights to your data.
Gaining More Insight From Power BI
As you can see, you can easily start using Power BI today. But I have just scratched the surface of what Power BI can do for your organization. You can also publish your reports to the Power BI Service, connect data from disparate data sources, and create dashboards. You can use Cortana’s English language query tool to gain further insights into your data. Power BI’s built in tools make it easy to share your data with internal colleagues and external customers. Discover all of these capabilities and more by taking a class with a Microsoft Certified Trainer.
Webucator, a Microsoft Certified Partner, offers MOC 20778: Analyzing Data with Power BI training. This 3-day course will help your company start using Power BI. The training covers everything listed here and more. View the Power BI Course details here.
Blog Post Author: Shelley Vinson Helfer
Shelley Vinson Helfer is a Microsoft Certified Trainer and consultant in SQL Server, SharePoint, and .Net. She specializes in SQL Server BI (Power BI, PowerPivot for Excel, PowerView, Tabular Data Services) and SSRS, SSIS, and SSAS. She is experienced in SQL Database and Azure VMs.