Experiencing of Microsoft Excel Power Pivot

Excel is always a very handy tool when the target data is relatively smaller in size. It is getting slower when the target data reach a certain size limit.

However, the new Excel Power Pivot add-on is a breakthrough on the bottleneck.

Below I demonstrate how we can load 1.8 million rows of data in the speed of seconds using Excel Power Pivot:

If you have big data file, e.g. HDF5 file you may use Microsoft Power Query to import the file. You may understand more and download Microsoft Power Query from the URL below:\

https://support.office.com/en-us/article/Introduction-to-Microsoft-Power-Query-for-Excel-6E92E2F4-2079-4E1F-BAD5-89F6269CD605

With sufficient RAMs, we have tried up to around 103 millions rows of records to import into and play around in PowerPivot. Try it out the larger data sets from below URLs:

http://labrosa.ee.columbia.edu/millionsong/pages/getting-dataset
http://aws.amazon.com/datasets/Economics?browse=1

For the ease of illustration, we will just continue with the above example and demonstrate some of the basic features of PowerPivot.

Below is a screen of the power pivot after the above transaction flat file load into system:

To build a simple data model, we need to add in the dimensional data and join the relevant fields of different data sets.

Open your dimensional data file in excel, copy and paste into power pivot window:

With both data sets in power pivot, join the related filed and define the relationship:

Do some simple Transformation of data and build the presentation output via Pivot Table:

One of the great advantage of using Microsoft power pivot is that most of the audience of the report are familiar with the user interface. Meanwhile, they still can have a certain degree of flexibility and interaction with the report presented to them without troubling the data analyst to alter the data back end again. They can slice and drill down the data sets they are seeing.