Reply To: Microsoft Excel Business Intelligence tools

Vikas Jain

Below is what forms Microsoft Excel BI stack.

Power Query – This is ETL( Extraction and transformation ) tool. This is used to pull from multiple source like csv, internet, DB etc. and this data can be transformed/filtered and can be shown in excel or can be fed to PowerPivot.

Power Pivot – Power Pivot is like excel database. It allows to create data model within excel and also compresses the data( the standard compression rate is 1/10). Also different tables with in the data model can be related by keys and hence the pivot table which is finally generated is can pull up fields from multiple table. The output of Power Pivot can be in a pivot table or power view.

Power View – Power view is the reporting layer for management level reports, Static text can be included, pictures can be included, graphs charts etc. Source of Power view can be Power Pivot or even Tabular model ( our DB).

Power MAP – Advanced reporting layer for geographical maps and also video option available. The reporting can be done on a 3D interactive globe

Something to note that, It’s not that only according to the filter in pivots/reports the data will be pulled from source. The data will always be there within excel even if it’s not used in the final reporting layer( pivot table or power view). And this can cause memory issue and error messages “Excel cannot complete the current action with available resources” if the data held becomes too large.

There is an option of putting Power Pivot on sharepoint. The data handling capability is more than Excel but less than DB.

Download PDF version

This field is for validation purposes and should be left unchanged.