MS-Excel : Power pivot
Today I explored the features of power pivot in MS-Excel and amazed with its wonderful capabilities. Even though at first glance it looks like the replica of its predecessor, power pivot has plenty of benefits attached to it. In this post I would like to show case the reason behind the powerful performance by power pivot, its installation and also small demo to understand the creation of basic pivot.
Power pivot engine is known as Vertipag which is based on the in memory calculation. It can hold the entire data from the selected tables of the database which may go beyond millions of rows. Advantage of holding the entire data in memory is to avoide the expensive disk I/O which is the main reason for slow performance.
Power pivot stores the data in a compressed mode to reduce the disk storage space. This we can understand it very clearly by zipping the MS-Excel power pivot file and find that there is no difference in size before and after the zipping.
Another interesting feature is column store using which it can save huge disk space. In case of row store some columns are repeated for each row but in the case of column store, each column stores the unique records. Please go through the below link to get more details about column store.
http://db.csail.mit.edu/projects/cstore/abadi-sigmod08.pdf
Power pivot does not ship with MS-Excel by default and you can download and install the same from the below link.
http://office.microsoft.com/en-us/excel/download-powerpivot-HA101959985.aspx
There is slight difference from the ordinary pivot and power pivot in fetching the records. Power pivot has the ability to create the relationship between the tables from which it fetches the data and it has seperate window to have connection and storage.
Sample
Once you install power pivot, new ribbon comes up in the menu bar of MS-Excel as shown in Fig 1.
Click on the ribbon which opens the below window.
click on the Power pivot window which opens another window to create the connection with the data sources and load the data for consumption.
Select the AdventureworksDW database by choosing the option From database and select the required tabels. For this demo I have selected DimCustomer, DimDate, DimProduct and factInternetSales.
Now we can see data from these tables are stored in seperate tab of power pivot window and also loaded into the system RAM.
we can edit the relationship between these tables by choosing manage relationship option.
Now switch back to MS-Excel mode by click on the excel icon at the right top of the window.
Data preparation is over and can utilize this in pivot table and charts. Here important catch is that unlike previous version, slicers are included in the property of the power pivot.
Power pivot engine is known as Vertipag which is based on the in memory calculation. It can hold the entire data from the selected tables of the database which may go beyond millions of rows. Advantage of holding the entire data in memory is to avoide the expensive disk I/O which is the main reason for slow performance.
Power pivot stores the data in a compressed mode to reduce the disk storage space. This we can understand it very clearly by zipping the MS-Excel power pivot file and find that there is no difference in size before and after the zipping.
Another interesting feature is column store using which it can save huge disk space. In case of row store some columns are repeated for each row but in the case of column store, each column stores the unique records. Please go through the below link to get more details about column store.
http://db.csail.mit.edu/projects/cstore/abadi-sigmod08.pdf
Power pivot does not ship with MS-Excel by default and you can download and install the same from the below link.
http://office.microsoft.com/en-us/excel/download-powerpivot-HA101959985.aspx
There is slight difference from the ordinary pivot and power pivot in fetching the records. Power pivot has the ability to create the relationship between the tables from which it fetches the data and it has seperate window to have connection and storage.
Sample
Once you install power pivot, new ribbon comes up in the menu bar of MS-Excel as shown in Fig 1.
 |
| Fig 1 |
Click on the ribbon which opens the below window.
 |
| Fig 2 |
click on the Power pivot window which opens another window to create the connection with the data sources and load the data for consumption.
 |
| Fig 3 |
Select the AdventureworksDW database by choosing the option From database and select the required tabels. For this demo I have selected DimCustomer, DimDate, DimProduct and factInternetSales.
 |
| Fig 4 |
Now we can see data from these tables are stored in seperate tab of power pivot window and also loaded into the system RAM.
we can edit the relationship between these tables by choosing manage relationship option.
Now switch back to MS-Excel mode by click on the excel icon at the right top of the window.
Data preparation is over and can utilize this in pivot table and charts. Here important catch is that unlike previous version, slicers are included in the property of the power pivot.
Comments
Post a Comment