In this post let’s have a look on PowerPivot add-on for Excel 2010-2013. Be aware, PowerPivot is not available for Office 2013 (365) Home Premium, so it was preferable for me to stand on Office 2010 rather than migrate to new version of the product.
So, what PowerPivot is and what innovations it brings to us. In few words – it gives the possibility to work with not huge data from different data sources directly in Excel, without warehouse database and analysis services. For me, sounds really great. And I will show you on one example from my personal practice.
I will speak about my private Excel file, to which I upload on monthly basis my financial operations (incomes and outcomes) and then perform some analysis. So, before PowerPivot the process was rather uncomfortable and included too much manual work. In order to optimize my work I have had to create some application or macros in Excel, or move data to Analysis Services. But I did not want to do any of these cases for my private activities, so I was continuing doing the following:
- For each line I have to calculate Year and Month,
- Enter currency rate (if the operation in the line was not in rubles (my country currency)),
- Calculate total operation amount in rubles.
The part of the sheet can be found below. I’ve marked with green color manually filled and calculated columns.
Also, I have to feel all data for balance report manually, as it contains some calculations and categories that were not presented in source data. The simplified result was:
The same work I was doing for the other report, which indicates on what projects money on key accounts were designed to. Also Excel helps me with several pivot tables for detailed analysis.
To sum up, I was spending about 30-40 minutes each month to prepare all data and finish all required analysis. As this work was done in my private time, I was not always attentive, made some mistakes, missed some data, etc.
Now let’s go to PowerPivot and how it has changed the described below process. The main idea is: I was able to create database scheme right in Excel, without any external tools.
First I’ve created the following additional tables on other Excel sheets:
- Calendar table (Date),
- Currency rate table (Rate),
- Hierarchy for balance report (Categories).
And then I linked these tables to my source data:
Everything, that I’ve describe below was done in MS Excel without using any other tools or programs. It was really very flexible and easy. To add table to PowerPivot you just have to click one button:
And to create links between tables you just have to click button and select tables and fields:
In the next post I will finish PowerPivot theme, showing some usage of DAX language (real analysis service language inside Excel) and the final view of my financial analysis process.