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.
We have unrivalled expertise in successful C/ETRM and TMS implementations, and digital transformation projects, as well as defining market solutions that have become system industry standards. We have a proven track record of successful deliveries at greenfield projects and at existing clients.
Copyright 2022 KWA Analytics