Microsoft Excel & PowerPivot

/ BI and Reporting Tools

Microsoft Excel is a software program produced by Microsoft Corp. that allow users to organize, format and calculate data with formulas using a spreadsheet system. This software as part of the Microsoft office suite and is compatible with other applications in the office suite.

Excel is a commercial spreadsheet application produced and distributed by Microsoft for Microsoft Windows and Mac OS X. It features the ability to perform basic calculations, use graphing tools, create pivot tables and and create macro programming language.

Excel has the same basic features as every spreadsheet, which use a collection of cells arranged into rows and columns to organize data manipulation. They also display data as charts, histograms and line graphs.

Excel permits users to section data so as to view various factors from a different perspective. Visual Basic is used for applications in Excel, allowing users to create variety of complex numerical methods. Programmers are given an option to code directly using the Visual Basic Editor, including Windows for writing code, debugging and code module organization.

Microsoft Excel PowerPivot

PowerPivot is a free add-in to the 2010 version of the spreadsheet application Microsoft Excel. In Excel 2013, PowerPivot only available for certain versions of Office. It extends the capabilities of the PivotTable data summarisation and cross-tabulation feature by introducing the ability to import data from multiple sources. As such, PowerPivot falls under Microsoft’s Business Intelligence offering, complementing it with its self-service, in-memory capabilities.

Prior to the release of PowerPivot, Microsoft relied heavily on SQL Server Analysis Services as the engine for its Business Intelligence suite. PowerPivot complements the SQL Server core BI components under the vision of one Business Intelligence Semantic Model (BISM), which aims to integrate on-disk multidimensional analytics previously known as Unified Dimensional Model, or UDM, with a more flexible, in-memory “tabular” model.

As a self-service BI product PowerPivot is intended to allow users with no specialised BI or analytics training to develop data models and calculations, sharing them either directly or through SharePoint document libraries.

  • Warehousing analytics and Business Intelligence within Excel – A local Analysis Services VertiPaq engine that compresses and loads data and makes it available to data visualization objects, such as PivotTables, in a worksheet. Sort and filter are sped up by performing operations with a local Analysis Services VertiPaq processor;
  • Support for a variety of datasources – Provides the foundation to load and combine source data from any location for mass data analysis on the desktop, including relational databases, multidimensional sources, cloud services, data feeds, Excel files, text files, and data from the Web;
  • SharePoint Integration – Enables users to share data models and analysis with the solutions in SharePoint; users can configure refresh cycles to ensure the data remains current automatically;
  • PowerPivot allows creating portable and reusable data; data stays inside the workbook. Users do not have to manage external data connections. The user has a single workbook (.xlsx) file that contains embedded data extracted and processed by an internal processor, but rendered exclusively through Excel;
  • When users publish, move, copy, or share a workbook, all the data is transported inclusively; saved data is stored inside the Excel workbook;
  • Once the data is loaded the user can work in disconnected mode – no further connectivity is required to any server;
  • The maximum file size of a PowerPivot workbook is 2 GB on disk and 4 GB in memory. File compression reduces the amount of disk space required to save a workbook with typical ratios of 1:10 disk-to-memory or greater;
  • Support for Data Analysis eXpressions, which allow users to perform analytical calculations. DAX includes functions dealing with time intelligence, data aggregation and statistical calculations in general.

Share

Last updated: 21.07.2022

Our Recommendations

Our Memberships

Our Certification

Wojskowe Centrum Normalizacji Jakości I KodyfikacjiTÜV NORDTÜV RHEINLAND

Our Partnerships

Competencies