What are the benefits of using Powerpivot table over a regular PivotTable?

After a successful implementation of a data warehouse, the obvious next step is to provide a reporting solution or a delivery layer so business users can use the data for further reporting/analytics. A traditional approach is for IT to build a reporting solution comprised of managed reports and views, and deliver them through the company’s portal. This approach is often called a Full Service delivery method, because IT does most of the work.

In the Full Service approach, an analyst works with the business to gather requirements and deliver those to the development team. In most cases, time is spent going back and forth on requirements between business and the development team. By the time the report is delivered, requirements may have changed or the business wants to add more filters or change the layout and again the cycle is repeated. This inefficiency can be a result of front end limitations, data structure limitations, unknown requirements, or the difficulty of creating ad-hoc reports. Business users are heavily dependent on IT in this traditional approach, and business users tend to avoid using the sophisticated BI tools due to the learning curve involved. Hence, although the data is available in the data warehouse, it may end up not being used as desired.

Self-Service BI overcomes most of these hurdles. The major advantage is a greater user independence from IT to access required data. Self-Service BI can be easy to learn, with less training required. Self-Service BI improves a user’s ability to analyze and report on data, and enables the business to focus more on viewing the data and making informed decisions or creating quick proof of concepts (POCs). However, keep in mind that IT needs to build a data layer to make Self-Service BI a success.

Today many enterprises implement business intelligence solutions with self-service BI as the goal.  Organizations need to develop a long-term strategy which will lead to a reduced dependency on IT to access the required data — some self-service tools can help achieve this by building small POCs. There are few good self-service BI tools in the marketplace that can help users to analyze data with minimum IT involvement. One of these tools, PowerPivot, provides seamless transition to users of Excel.

Top Reasons to Use PowerPivot

• Take advantage of familiar Excel tools and features
• Process large amounts of data in seconds leveraging in-memory compression
• Load data sets from virtually any source
• Combine data from different data sources, like Oracle and SQL Server
• Interactively explore and analyze data without expert’s help
• Use powerful new analytical capabilities such as Data Analysis Expressions (DAX)

More PowerPivot Benefits

Since PowerPivot is combined with Excel, it provides some obvious benefits. They are:

• No Additional cost if you have Office 2010, Power Pivot, and SharePoint 2010
• New data analysis capabilities
  • Combine and relate data from heterogeneous sources
  • Larger data sets with in-memory compression Tabular Model
  • Slicers
  • Dashboards, Charts and Pivot Tables
• Quick learning, less training required
• Do it yourself, power to users – less dependency on IT
• Improves user’s ability to analyze and report on data
• Quick POCs

PowerPivot Drawbacks

As with any product, PowerPivot comes with some drawbacks, which include:

• Many-to-many relationships in the data require special handling / workaround
• 32-bit Excel may run out of memory on the large data sets
• Prone to corruption… save your work often and have backup versions. Corruption is more prominent with large data and you may need to revert to a backup copy or rebuild PowerPivots.

What Else can we do with PowerPivot?

• PowerPivot file can be shared on SharePoint 2010
• PowerPivot can interface with Report Builder 3.0
• You can Import PowerPivot to SQL 2012 Tabular Model
• Use as a base to design/develop UDM Things to keep in mind while using PowerPivot

• Takes the burden off of IT
• Speed up the analysis and hence development of reports
• Data to people aka Power to People to analyze data
• Creates the risk of chaotic reporting within an organization
• Taxes the system as number of users increase

• Creates the risk of chaotic reporting within an organization
• Taxes the system as number of users increase

IT departments should work with business units to determine key metrics and an agreed-upon vocabulary, establish consistent processes for creating reports and publishing, and to define and implement policies for data access, security, privacy.

Wondering if PowerPivot is right for you, or want to learn more about Self-Service BI? Learn more about our Data and Analytics practice!

Have 15 minutes? This webinar can give you some great tips on understanding PowerPivot in Excel 2013.

What are the benefits of using Powerpivot table over a regular pivot table?

Why power pivot is better than a normal graph?

What is difference between pivot table and regular table?

What are the advantages and disadvantages of pivot table?