Creating Report in Power BI Using Microsoft Access Database

Introduction

According to the MSDN, Power Pivot can be defined as:

PowerPivot is an add-in for Microsoft Excel 2010/13 that enables you to import millions of rows of data from multiple data sources into a single Excel workbook, create relationships between heterogeneous data, create calculated columns and measures using formulas, build PivotTables and PivotCharts and then further analyze the data so that you can make timely business decisions without requiring IT assistance.

Power Pivot in Microsoft Excel 2013 is a powerful tool that we can use to perform powerful data analysis in Microsoft Excel 2013. To start using the Power Pivot tool for Excel we need to enable the tool. By default, this tool is not enabled in Microsoft Excel 2013. We can enable this tool by performing the following procedure.

• Go to File -> Options -> Add-Ins.
  
  
• In the bottom of the Excel Options screen we will be able to see the Manage option. Select COM Add-ins from the manage option and click on Go.
  
  
• Now check the Microsoft Office Power Pivot Excel 2013 box and then click on Ok.
  
  

• Now the preceding ribbon has a Power Pivot tab as shown below:

  
  

If you are using Microsoft Office 2010 SP1, these types of tools were not installed previously in Excel, so you need to download it from the link provided below.

PowerPivot for Microsoft Excel 2010

Now let's start the procedure.

Step 1

Open Microsoft Excel 2013 and create a blank workbook.

Step 2

Click on the PowerPivot tab and click on Manage.

After clicking the Manage tab, a new window will open as shown below.

Step 3

When we select Get External Data option we will be able to see four different sub-options named as From Database, From Data Service, From Other Sources and Existing Connections. We will select From Database option that is further divided into four further parts named as From SQL Server, From Access, From Analysis Services or Power Pivot options. Now we will select From Access option as we are working with Microsoft Access Database.

Step 4

After selecting Access database, a Table Import Wizard will appear where we will select the Database Name and can check for the Test Connection and if we receive the test connection succeeded message we will click on the Next button.

Step 5

After clicking on the Next button, a new window will appear asking "How to Import the Data" where we can import our data in two ways.

• Importing all the data from database tables.
• With a query.

We will select the first option, selecting from a list of tables and click on the Next button.

Step 6

Our next step is to select the tables and views from our database that we want to insert into the Power Pivot for Excel windows and click on Finish.

The import operation may take a couple of minutes depending upon the size of your table. We will be able to see the Success message when the import operations are successfully installed.

As we can see at last, we have completed the data preparation operation (by importing 7 tables) and it also throws the message to check the details. Let us now check the details.

The details shows us the relationship between the tables.

Press OK and click on close.

Step 7

Now we are ready with our table data.

As we can see, our table is now in a Data View mode. We can also see our table in a Diagram View by selecting the Diagram View option from the top-right hand side.

After clicking on the Diagram View option we will be able to see our table data in a Diagram View manner.

We can see that the tables are in a relationship with each other and are joined using arrows. If we move our mouse to any one of the arrows then we will be able to see that the two tables are joined to each other with a common column as shown below.

Here both the tables, Books and Orderlines, are joined together with a single column isbn.

We can also create our own relationship between the tables by clicking the design option and and clicking on the Create Relationship button. A new window will open asking the Table name and Column names and after that click on Create.

Step 8

Our next step is to create the report using the imported table data. So now let's start it.

Go to the Home ribbon tab and go to the PivotTable menu and select the Chart and Table (Horizontal) option. After selecting the Chart and Table (Horizontal) option we will receive a confirmation box from Excel asking whether you want to create the chart in a new worksheet or you want to continue with the existing worksheet. Let us choose the second option and click on the OK button.

Step 9

After clicking on the OK button the following window will appear in the Microsoft Excel. Here we can build either a PivotChart using fields from the PivotChart Field List or a PivotTable report.

Step 10

Creating a PivotChart

Let's create a PivotChart by clicking on the plus (+) icon near the PivotChart area and a new window PivotChart Fields will appear at the right hand side as shown below:

We will now select the fields from the PivotChart Fields for the chart and table that we want to display.

In the preceding picture we have selected isbn and publication_year from the Books table from the right hand side that is further converted to the PivotChart in the left hand side. Here the chart is displaying the publication year of the books based on their ISBN number.

Let us see one more example of PivotChart. In the following image we are adding the four columns customer_numb, customer_first_name, customer_last_name and customer_phone from the customer table that is displayed via PivotChart.

Step 11

Creating a PivotTable

The PivotTable can be created by clicking on the area of the PivotTable. A new window PivotTable Fields will appear as shown below:

Now select the fields from the PivotTable Fields that we want to display as shown below:

Here we are displaying the details of books based on their ISBN. Each ISBN is listed with their author_name, title, publisher_name, publication_year and retail_price as shown.

Summary

Using this article we are able to undersand how to create a report by importing the data from a Microsoft Access database using the Power Pivot tool of Power BI.