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:
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!