Building Data Relationships in PowerPivot

This post is a follow-up to one of my previous posts, titled: “Using PowerPivot’s Copy-Paste Support for Importing Data from Word” and this time, we will be discussing about building data relationships in PowerPivot.

To this end, 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! 🙂

 

Let’s See an Example

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

Building Data Relationships in PowerPivot

 

Building Data Relationships in PowerPivot

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

 

Using the Copy-Paste Feature

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:

Building Data Relationships in 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:


Learn essential SQL Server development tips! Enroll to our Online Course!

Check our online course titled “Essential SQL Server Development Tips for SQL Developers
(special limited-time discount included in link).

Sharpen your SQL Server database programming skills via a large set of tips on T-SQL and database development techniques. The course, among other, features over than 30 live demonstrations!

Essential SQL Server Development Tips for SQL Developers - Online Course
(Lifetime Access, Certificate of Completion, downloadable resources and more!)

Enroll Now with Discount!


Creating the Table Relationships

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:
Building Data Relationships in PowerPivot
That’s it! If we now click on the “Manage Relationships” button we can see that the relationship has been created:
Building Data Relationships in PowerPivot
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:

Building Data Relationships in PowerPivot

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:
Building Data Relationships in PowerPivot
Building Data Relationships in PowerPivot

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.

 

Conclusion

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!

 

Featured Online Courses:

 

Other articles to check on SQLNetHub:

 

 

Recommended Software Tools

Snippets Generator: Create and modify T-SQL snippets for use in SQL Management Studio, fast, easy and efficiently.

Snippets Generator - SQL Snippets Creation Tool

Learn more

 

Dynamic SQL Generator: Convert static T-SQL code to dynamic and vice versa, easily and fast.

Dynamic SQL Generator: Easily convert static SQL Server T-SQL scripts to dynamic and vice versa.

Learn more

 

 

Subscribe to our newsletter and stay up to date!

Subscribe to our YouTube channel (SQLNetHub TV)

Easily generate snippets with Snippets Generator!

Secure your databases using DBA Security Advisor!

Generate dynamic T-SQL scripts with Dynamic SQL Generator!

Check our latest software releases!

Check our eBooks!

 

Rate this article: 1 Star2 Stars3 Stars4 Stars5 Stars (1 votes, average: 5.00 out of 5)

Loading...

Reference: SQLNetHub.com (https://www.sqlnethub.com)

© SQLNetHub