A Beginner’s Guide to Business Intelligence: Access, Clean, Organize, and Present Data with Power BI

Getting started with business intelligence is easy. In this post, I’ll share a few steps to follow to get access to data, start building a data model and create a report in a client application like Power BI.

To do business intelligence, you need to:

Access data
Clean data
Organize data
Present data

Table of Contents

Business Intelligence with Excel

When you think about it, if you have Excel, you can do anything listed above.

Access data

From Data > New Query, you can get data from many places, like CSV or Excel file, from the web, from external services like Salesforce, or from a more structured database like SQL Server.

For example, you can get data from Wikipedia, like the number of internet users in 2017, here : https://en.wikipedia.org/wiki/List_of_countries_by_number_of_Internet_users

Clean Data

The second step is to clean and transform your data. In the table above for instance, we need to change most columns data types to numbers instead of text.

Organize your data

Next, you need to organize your data into a data model. For the sake of the demo, I imported the number of mobile phones by country.

Then I created a third table to list the different countries, and gave each one a unique number, usually called a key. Other tables now refer to countries via a country key. Now our data is ready to create a data model with it.

With Excel, you can create data models, meaning creating relationships between tables. To do this, I added each table to the data model by going to : Power Pivot > Tables > Add to Data Model. Lastly, I created the relationships between these 3 tables. Because the Countries table is supposed to have a unique country name for each row, I created one to many relationship between the countries table and the 2 others.

Present Data

Our model is now ready for analysis. We can create a pivot table, which by default, will use “This Workbook’s Data Model”.

From the PivotTable fields, you can see our 3 tables with fields.

We can now display the number of Internet Users and the number of Mobile Phone for each country quite easily.

More realistic scenario

In a company, each of the 4 operations are slightly more complex.

Access data
Clean data
Organize data
Present data

You get access to internal data usually from structured databases maintained by the IT department, usually stored in SQL Server database. Internal users input data to system sources like CRM, Marketing Automation, Finance Systems, etc. The IT department import only relevant tables for analytics, and as a Business Intelligence Developer, your job is to leverage this data to build applications, reports and dashboards to the business departments.

Usually, Business Intelligence Developers have better tools than Excel for cleaning, organizing and presenting data. Tools like Power BI and Tableau do the job better.

Business Needs

The sales department from Adventure Works want to understand their sales based on the product categories and sub categories. They want to be able to select any product categories and sub categories, and to be able to see the sales trends for the selected values.