Excel Database Business Intelligence

In a simple and easy-to-understand format, this article provides the necessary steps to creating a Power Pivot Data Model in Microsoft Excel 2013, as well as instruction on how to generate a complex report in Excel using a Power Pivot table.

Related: Understanding Power Pivot and Power View in Microsoft Excel 2013

If you are using Excel 2013, Power Pivot is installed by default. If not, you can install it by going to:

Files → Options → Add-ins (This is a tab on the left panel of the screen.)

In the Manage dropdown list, select COM Add-Ins and press Go.

After pressing the Go button, a new screen will open in front of you. Select Microsoft Office Power Pivot for Excel 2013. This will add a new ribbon tab in your Excel ribbon menu as Power Pivot.

Sample Database

Use the provided sample database script available at the top of the page via the "Download the Code" icon. All you have to do is copy the script and paste it into the new query window of your SQL Server Management Studio (SSMS) and select Execute.

Overview

In this exercise, you will be developing a Power Pivot model by importing SQL Server data into Excel. We will create hierarchies, calculated columns, Key Performance Indicators (KPIs), and add related columns on the basis of DAX language.

The Excel document will look like this:

Step 1: Load Data

In this step, you'll load data from SQL Server database (DWtest) into Excel 2013 file. To do this, perform the following steps:

1. Create a new Excel 2013 Workbook.

2. Go to the Power Pivot ribbon tab and select the Manage menu item. This will open a new Power Pivot for Excel window. In this screen, we will design our data model.

3. On the Home ribbon tab, select From Database and then select From SQL Server. This will start the table import wizard. On the first screen, you'll specify the connection parameters (Server Name, Log On to Server and Database Name) with your SQL Server Instance on which you have deployed the Sample Database. I have selected '.\sql2012' as my Server. (The peridod "." indicates that the server is my localhost. sql2012 is my instance name where my database DWTest is deployed.)