Building Data Relationships in PowerPivot

This post is a follow-up to the previous post titled: “Using PowerPivot’s Copy-Paste Support for Importing Data from Word”.

In this post we will see how we can import data from Microsoft Word using PowerPivot’s copy-paste feature, create relationships on the data and perform aggregations that is BI! :)

For example, consider the two following tables which exist in a sample Word document:

 
 
 
 

 
 
 
 
 
 

The business query is to evaluate the sales activity for all the salesmen and decide possible adjustments to their commissions.

To do this in PowerPivot, we are going to use the copy-paste feature, create a relationship for the two tables’ data, and visually build our business query using the user-friendly interface and tools of PowerPivot.

The first step is to import the data from Word right into Excel using PowerPivot. So first, we have to launch Excel 2010 and then start the PowerPivot environment:









Then, we copy the first table from the Word document into clipboard and we click on the “To New Table” button.

After previewing the data and clicking on the “OK” button, we see that the first table’s data are imported into PowerPivot:

Similarly, the next step is to import the data of the second table into PowerPivot as well. To this end, we copy the second table into clipboard and in the PowerPivot dialog we click again on the “To New Table” button.

After the preview, we can see that the second table’s data are imported into PowerPivot:

Now it’s time to create the relationship between the two tables! We click on the “Table” menu, then on the “Create Relationship” button, and we set the following relationship between the two tables:

That’s it! If we now click on the “Manage Relationships” button we can see that the relationship has been created:

Now, back to the “Home” menu, we click on the “PivotTable” button and we select a PivotTable chart preset, for this example I selected the “Single PivotChart”. We select to import the PivotChart into a new worksheet and we have our data ready for processing:

For answering the business query we can check out the total sales amount per salesman and also see the employment date of each salesman. Then, based on the sales amount of each salesman, we can propose possible adjustments to the commissions. For this purpose, I created the two following charts:

Based on the sales activity of each salesman and on the results of the aggregations as illustrated in the two above charts, we derive to the conclusion that Salesman 3 should get the larger commission rate, then Salesman 1 followed by Salesman 2 and last Salesman 4 who has the less sales activity.


This post provided a simple example on how we can import data right into Excel using PowerPivot’s copy-paste feature, create relationships on the data and perform BI with ease by using the powerful engine of SQL Server PowerPivot.

I hope you found this post useful!
Read more on this article...

Using PowerPivot’s Copy-Paste Support for Importing Data from Word

One of the great features of SQL Server PowerPivot is that it supports importing data directly from the clipboard. This is quite handy in cases you want to import and analyze data fast.


An example of using the above feature is importing data from Microsoft Word. Let’s say you have a Word document containing the following table:











The scenario here is to import the data from Word and perform a simple analysis.

The first step is to select the entire table and copy its contents to the clipboard.


Then you have to launch Excel 2010 and open the PowerPivot window:

 
 
 
 
 

Click on “To New Table” and you will be presented with a Paste Preview dialog in which you will be able to see the contents of the clipboard and how they are “translated” by the PowerPivot engine:

At the bottom of the dialog you can choose to use the first row as column names and that’s what I did in this example.

You then click on “OK” and you are presented with the data as imported into PowerPivot:

 
 
 
 
 
 
 
 
 

Next, by clicking on the “PivotTable” button you are presented with various options regarding what type(s) of pivot tables you can create:
 

For this example I chose to use a “Single PivotChart”.

Then, the PowerPivot dialog closes and the Excel worksheet is presented containing the pivot table:

You can then easily perform aggregations using the tools provided by PowerPivot:

 

Well, I guess that’s it for today! I hope you found this post useful!

Another post on copy-paste and PowerPivot is coming where we will see how we can copy-paste two tables from Word and build relationships on the data.
Read more on this article...