“ Powerpivot is the best piece of software to come out of Microsoft in 20 years. ”
Microsoft Office 365 and stand alone business and professional versions of Excel include a suite of related BI tools such as Power Query, Power View, Power Map and the key component Powerpivot. Collectively they are referred to as “self service BI” because they enable traditional Excel users who lack a specialized computer programming background to perform multidimensional analytics on large complex datasets. Because there isn’t a need for direct IT or SQL professional involvement, BI application development is accomplished in a much more agile and cost effective way. Powerpivot is an add-in to Excel that dramatically extends the limits of traditional versions by allowing you to link to nearly limitless quantities of data. Applications may reference multiple sources within the same model. The data generated from your legacy system can be combined with almost any other source. An example would be to combine budget data modeled on Excel with financial results residing in the data warehouse. GPS data collected from devices monitoring mobile equipment or fleet assets can be merged and appended to operational planning models. Time keeping devices can be integrated with cost accounting systems, human resource or product pricing models. Web based climate statistics or cloud service market information can be mashed up with CRM datasets. Data models are developed using DAX, & Power Query “M” code which are formula languages designed to be familiar to users of advanced Excel formulas like VLOOKUP. Powerpivot applications, once programmed, are kept up to date automatically by command or on preset schedule. Cut-and-paste update or manual input is a thing of the past. A well designed model can be shared with and used by any person with access to the web. There is no need for Excel expertise or a locally installed version of the program when shared using the Power BI tool.
The following video illustrates many capabilities and features of Powerpivot. The data model itself is based on a fictional entity engaged in farming. It operates from multiple locations, is involved with growing, packing and shipping and owns the equipment it uses. The data from the Excel 2010 Bay Farming model was queried and modified using Powerpivot & Power Query. It is made interactive with Slicers that allow the user to drill down to detailed business segments and custom reporting periods. The workbook also includes several Power View worksheet visualizations and a Power Map geospatial “tour”. It’s a free download that is a great learning tool.