Getting started with Power Pivot

Microsoft is boosting Power BI these days. In particular Power Pivot is a very interesting feature usable for creating nice reports in Excel.

In this article I will describe how to setup a basic report created with Power Pivot with data from a SQL database (of Dynamics NAV 2013R2). We are going to create a report with Sales Revenue per Customer where we can filter on a Salesperson or Country.

In order to be able to use Power Pivot you need to install Excel 2010 or 2013 with PowerPivot for Excel 2010 (in 2013 no download neseccary). Power Pivot comes standard with Microsoft Office 2013 and Office 365. You have to activate it in settings.

Data Model

We start with configuring our data, which is the resource for any  report. That’s done in Power Pivot by clicking “Manage”. Here we are able to select the data source. In this case we use “From SQL Server”.

Select the SQL Server and Database.

Choose the option on how to import data. In this case I choose to import data from a list of tables. Using a query is a bit more difficult. I’ll write another blog about that.

We select the tables Customer and Cust_Ledger Entry. The table Customer will give us al customer related data such as name and location and the table Cust_Ledger Entry contains the Sales Revenue figures per Customer.

If we finish now, both tables will be imported in Power Pivot. That can be quite a lot of (unnecessary) information. So it might be useful to only import certain fields, which we can select by “Preview & Filter”.

I selected the following fields, for Customer:

• Name
• Adress
• SalesPerson
• Country/Region

For Cust_Ledger Entry:

• Customer No.
• Posting Date
• Doctype
• Document No.
• Sales LCY

We can finish now, the data will be imported in Excel.

Now we need to make a relationship between the two tables. That we can do in the Diagram View (1) How is the data related from one table to the other? In this case we want to show the Sales Revenue in relation to the Customer. The link between the two tables is the field Customer No. and No. By selecting and swiping from table 1 to table 2 a connection is made (2).

This is a graphic way of connecting tables. We can also manage relationships manually, that is possible with the function “Manage Relationships”. You can find it in the navigate bar by Design.

As you can see the relationship we just made is listed (3) and the connected fields are Customer No. and No.

Building the Report

Now we have the dataset and relationships ready we can start visualizing the data. You can choose which way you prefer. A PivotTable, PivotChart or whatever you want. Let’s start with a PivotTable (we can add the chart afterwards anyway).

The first thing you will notice is that you have multiple tables to select your fields from.

Let’s build a small report where we want to show the Sales Revenue per Customer and where we can filter on Country and Sales Person.

Add Name to Rows and Sales LCY to Values. For the Salesperson and Country we create slicers.

E voila, we have a report. We have insight on our total Sales Revenue, per Customer, per Salesperson and per Country for the CRONUS Demo Company from Dynamics NAV.

You can see that Salesperson KS has more Sales than HD. When selecting a Salesperson the Country lists and Cusomter lists are both filtered.

From here there are a lot of things we can do and create different kinds of reports from the data. For example showing data on a geographical map with Power View, which I will describe in another blogpost.