A Simple Tutorial on PowerPivot (1)

1. Introduction

Using PowerPivot is pretty easy. In general, four steps are involved. 

• First, you need to get the data to PowerPivot. This is about importing and doing a little preparation on the data. Data can be brought from relational databases, Analysis Service databases, PowerPivot that have been published at SharePoint sites, Excel and Text files, data feeds (ops, something new from SharePoint), through Copy-Paste actions, linked worksheet, etc.

• Second, after taking everything into PowerPivot, you need to enrich the data through various ways, such as filtering some rows, hide/unhide some columns, formatting columns (or change data types), create calculated columns through DAX definitions, and most important of all, defining relationships among different PowerPivot tables.

• Third, with all data ready to use, it is the time to analyze the data.Here is the time to use DAX to create measures and more Excel functionalities to find out interesting results.

• Forth, and the most important of all, create and share your reports.This is about showing and sharing your reports through SharePoint.

This tutorial has four parts where each part introduces one of the above steps.

Let’s start with the first step.  

2. Data Importing and Preparation

To bring data to PowerPivot is similar to bringing data to SQL Server Express. You will have to be able to connect to different data sources. PowerPivot requires database providers and, in some cases, client libraries in order to be able to import data from these sources. In most cases, the database providers and client libraries are not provided by PowerPivot for Excel and must be acquired and installed separately.

To import data, the simplest way is to import using Table Import Wizard at the PowerPivot window. There are two options when importing data using the Table Import Wizard (so you should open PowerPivot window and click “Table Import Wizard”.

1. Select from a list of tables or view

2. Specify a SQL Query. 

If you choose “Select from a list of tables or view”, the following picture shows the result.

The button “Select Related Tables” gives a big help if you know one of the tables (and have selected it” and wants to make sure that all other relevant tables are included.”

For the selected tables, you can use “Preview&Filter” button to decide whether to include or exclude certain columns or rows from the data. The following picture is an example. 

After selecting all the relevant tables and completing relevant filtering options. Click “Finish” button to start the actual importing process. Result of the Importing process looks like the following.

Now, let’s take a look at the second way, i.e., “Specify a SQL Query” when doing the Table Import Wizard. When choosing this option, “Specify a SQL Query”, one can use the “Design” button to go into the Query Designer to specify the SQL Query. The picture below is an example of the SQL query.

 In addition, Table Import Wizard lets you import data from MS Analysis Services (MSAS) and other PowerPivot workbooks. It’s important to note that the result of MSAS query is typically a "cellset" object, which is a multi-dimensional object. But when the data is imported into PowerPivot, it is transformed into a simple table. When you import data from MSAS you won’t have the same interactivity of slicing and dicing as you typically have when using BI client tools, such as Excel connected to an Analysis Services Unified Dimension Model (UDM) cube. Another very interesting feature of PowerPivot is that  PowerPivot can connect to data in a published PowerPivot workbooks at sharepoint sites. This makes the enterprise mash-ups into reality.  It’s worthy to note that you cannot import data from PowerPivot workbooks if they are .xlsx files on a local disk drive.

Besides importing tables, PowerPivot now supports “data feed”. Data feeds is a new type of technology that provides tabular-like data such that users can subscribe to the data in the same way as the RSS feeds in order to receive updated data as the source changes. Data feeds are based on the Open Data Protocol, which uses the Atom Publishing Protocol and its Data Services URI and Payload Extensions.  Data feed can come from SQL Server reporting services, SharePoint List and any generic data feed that supports the OData protocol.

Here is an example when you choose to import “From Data Feeds” in PowerPivot. PowerPivot understood the URL description and import the initial data from the source.

Text Files is another important source of data. Just like importing data in DTS, PowerPivot works with both Delimited text files and fixed-length-field text files. But to work with the fix-length-field files is a little bit tricky. One of the key step here is to define a schema.ini (exact name as this) at the same location as the file.  The contents of the schema.ini file are described in the MSDN topic at  this link

You can also import from Excel files. This is demonstrated below.

There are also other ways to bring data into PowerPivot. First one is to paste data from clipboard (well, you need to copy these tabular data first), as demonstrated below.

Second one is to create ”Linked Excel tables” which keeps the source and target in sync automatically. The following is an example.

And the result of the linked table is shown in the PowerPivot window with a special icon (the one next to the “DimStore” name in the below picture).

 After specifying the import criteria, PowerPivot will start the importing with the progress reports. It is important to note here that although PowerPivot can import data from many diverse data sources. The data types supported in PowerPivot is very limited compared to the big variety of data types at different sources. One of the things to be alerted is when you are importing data under different currency types, PowerPivot will import currency types as general currency types (“general” to the regional format defined by the machine where PowerPivot is running on). This can definitely lead to unexpected results. But you can use the  PowerPivot formatting tools to adjust the columns’ data type, and format after the import is completed.