SQL is fast to answer questions on data; it is quite useful to pull quick insights. When you need to do calculations on the results and present in special formats, Excel is the tool you are looking for.
Excel is among the most powerful tools for bespoke reporting and analysis. But it has some limitations: there is a limit to the data you can load and analyse at one shot, around a million lines. Even if your data is much less than a million lines when you work on complicated reports such as the balance sheet ageing report, Excel gets slower and slower.
The world has been changing towards digitalisation, and data is now coming in massive amounts. Data analysis is evolved into a science in its own right and so the tools used for analysing data. MS Excel caught up with this evolution with Power Pivot.
Power Pivot was first presented in 2010, and it has been evolving into a much practical and more robust tool over the last decade. Power Pivot implements the features of database software such as database tables and relationships, adds its calculation engine with a language designed explicitly for analysis over Power Pivot, the Data Analysis Expressions (DAX), and reports analysis results with pivot tables and visualisations such as dashboards.
We will start our analysis with loading Lead Merchants Sales Database tables to Power Pivot and set relationships between these tables. Then we will answer our questions with the analysis of the sales database with Pivot Tables, and we will prepare the same dashboard we developed in classic Excel.
For most of us, the very point of using Excel is preparing bespoke reports for our business needs. Power Pivot limits us performing analysis with pivot tables. We can gather any information over the data we are analysing, but we can not put the results in our desired format with pivot tables. Also performing calculations with classic Excel methods and functions are not practical with pivot tables.
So as a final step, we will prepare the flat file we previously have arranged with SQL to use in classic Excel, but this time with Power Pivot.
You can download the Power Pivot workbook used for our analysis from the link below. If you want to follow the workshop performing the step-by-step instructions yourself, you can find the Lead Merchants Database tables here.
Now let’s start our analysis. We will start with the Power Pivot, then the Dashboard and we will also prepare the Flat File we have previously made in SQL to work on classic Excel.