By Dave Kester, Terence Chow, and Lawrence Hii

Dave Kester	Terence Chow	Lawrence Hii

Even though technology has increased the capacity to create large data sets, the human brain is limited in how much data it can comprehend. Also, the more data created, the more challenging it is to create controls around the data. Because of this dilemma, our company hosts periodic round table discussions for actuaries in our community (Des Moines). The goal is to share best practices and to get a pulse on the latest needs. Recent topics include:

• risks for using Excel in production work,
• common challenges that arise with control and governance, and
• PowerPivot.

PowerPivot certainly has received the most interest.

Thus, the motivation for this article is to extend PowerPivot sharing to the Technology Section of the actuarial community.

Why use PowerPivot? These are two common reasons:

1. PowerPivot makes it easy to understand the data and, thus make decisions,and
2. PowerPivot has industrial strength controls to protect the data.

PowerPivot is new to most actuaries. So the goal for this article is to outline the basic features, benefits, and capabilities of PowerPivot.

Before Powerpivot

Actuaries have several approaches for working with their data. Two common approaches are:

1. Import data from databases and/or external files into Excel and use Excel’s analytical tools, or
2. Keep the data in the database and write queries to perform the analysis.

On one hand, database security is lost by using Excel to slice and dice the data. Clearly, Excel is a great tool for the job. However, manually copying or importing data into Excel presents a risk that inadvertent and undetected changes may occur to the data. This is because there are no controls to prevent changes to the imported data. This is especially unnerving considering companies rely on the accuracy of their data for business decisions and external communication. Without strict controls, once the data leaves the database, there is no longer a single source of truth.

On the other hand, flexibility and familiarity of Excel is lost by using special queries to reproduce Excel functionality. Not only does this approach require more technical skills, it is less intuitive. The purpose of queries is for the user to understand large data sets. The technical aspect of writing complicated queries in a database can hinder this process. Granted, analyzing the data using a database is more secure, but at the expense of not leveraging Excel’s flexibility.

After Powerpivot

What is PowerPivot?
How does PowerPivot solve this problem? First, review the basics. PowerPivot is an extension to Excel 2010 or later that offers powerful data analysis tools. More importantly, it is a tool that combines the benefits of using Excel as a data analysis tool and storing data in a database. PowerPivot allows data analysis in Excel while keeping the data secure in a database where it belongs. In other words, PowerPivot may not appear new because it is an extension of Excel. PowerPivot provides additional functionality within the familiarity of Excel’s user interface.

Massive Data Sets
At its core, PowerPivot is a beefed-up version of Excel’s regular pivot table tool. Unlike Excel’s regular pivot table, PowerPivot eliminates the need to copy and paste data into an Excel workbook. PowerPivot reads the data directly from the database. However, it does not modify the source data. PowerPivot has controls preventing changes to the source data. Thus, PowerPivot preserves the single source of truth of the data.

Another great feature of PowerPivot is that it eliminates Excel’s row limit. The only limitation for data size is the computer’s capacity. It should be noted that the computer’s limited capacity could be an issue.

Data from Multiple Sources
Actuaries often need to load data sets from external sources such as text files. Sure, Excel can import data from different data sources. The problem is creating relationships between data sources. Without PowerPivot, combining multiple tables into one table requires VLOOKUPs before creating pivot tables. VLOOKUPs significantly reduce performance. PowerPivot completely revolutionizes this process. Once the data is loaded, relationships are created between tables within PowerPivot similar to relationships created in a database. Using Excel terminology, PowerPivot allows creating pivot tables from data in Sheet1, Sheet2, and Sheet3 without using VLOOKUP to combine the data.

Performance and Speed
PowerPivot excels at compressing data. Data stored in PowerPivot is saved to an .xlsx file. This is the same file format used for other Excel workbook files. However, PowerPivot creates a smaller file. The larger the data set, the better the compression. In addition, data stored in PowerPivot consumes less RAM than the same data stored in worksheets. This gives PowerPivot the capability to process enormous quantities of data in seconds. By connecting PowerPivot to a database, the Excel workbook is updated simply by clicking the refresh button. The refresh button copies data to your local machine. This improves efficiency and enables you to access the data offline. However, it still has the control of not allowing changes to the data source.

Understanding the Data
Excel’s current pivot tables are intuitive. A new feature that improves pivot tables that is also included in PowerPivot is slicers. Slicers filter data based on user selection. Slicers are graphical, which makes them easy to use and understand. For example, assume the goal is to filter data where the State is IA. Without slicers, pivot tables require unchecking all values in the State field except IA. PowerPivot automatically creates buttons for each state available. Choosing IA is accomplished simply by clicking the IA button. If the goal is to select the first 10 states, click the first state and drag the next nine states. It is that easy. If the states are not adjacent, press the Ctrl key to select multiple states. Refer to the screenshot below. Product, Cohort Name, and VarName are examples of slicers.

View large image

Let’s face it. Dates drive a lot of actuarial calculations. Yes—dates are supported in Excel. But, organizing dates requires coding. For example, organizing data by months or quarters requires functions. PowerPivot simplifies this process using a timeline slicer. A timeline is a special slicer designed to filter by dates. PowerPivot automatically displays the timeline in years, quarters, months, or even days. In the example below, the timeline is displayed in quarters. Notice the 12 quarters from 2011Q1 to 2013Q4 are selected. No coding is required to organize data by quarters. This is truly an actuarial dream.

Dynamic Formula
Ever create a formula next to a pivot table but wished it changed as the pivot table changes? With PowerPivot, calculated fields allow creating formulas within the pivot table. Because the fields are part of the pivot table, they automatically adjust as the layout of the pivot table changes. Sure, it is possible to write formulas outside of the pivot table. But, the formulas must be rewritten if the pivot table changes.

In summary, slicers and calculated fields allow actuaries to convert ordinary Excel workbooks into powerful applications. Also, the maintenance for PowerPivot spreadsheets is reduced because calculated fields are incorporated into the pivot table. Once the workbook is designed, the main task for the actuary is to click the refresh button and analyze the results. The slicers and pivot table are self-maintaining.

Does Powerpivot Require Purchasing New Software?

Not likely. PowerPivot is available for download as a free add-on for Excel 2010. PowerPivot is standard with Excel 2013 (standalone edition or part of Office Professional Plus).

All data and pivot table definitions in PowerPivot are saved to an .xlsx file. Thus, sharing files with others is similar to sharing current Excel workbooks. If the other user has PowerPivot installed, they can open and use the workbook. If they have access to the database, they can refresh the data.

View large image

However, if they do not have PowerPivot installed, options still exist. Options include publishing to a SharePoint server or using Office 365. With SharePoint, the recipients can render the workbook on their browsers and analyze the data as if working within PowerPivot. Installing and using SharePoint is not a simple process and generally requires IT support. Office 365 also has its limitations, but does provide options.

Will This Require Staff Training?

Yes. The amount of training depends on the user. Most pivot table experts will quickly adjust to PowerPivot. The beauty of PowerPivot is that it is designed for business users, not technicians.

Summary

Even though PowerPivot is relatively new, our company has already benefitted from its features. However, we have only scratched the surface. We believe PowerPivot will be widely used by actuaries for many years. It should also improve with future releases. We welcome feedback and encourage an ongoing discussion. Please contact us with any questions you have about PowerPivot or to share your experience.

To learn more about PowerPivot, we created a video you may access here. Also, the table below summarizes how PowerPivot can improve upon using Excel, pivot tables, or a database exclusively.

David Kester, FSA, MAAA, is the co-founder and president of SALT Solutions, an actuarial consulting company in Des Moines, Iowa, and CoachingActuaries.com, a site that provides online practice tools for students preparing for actuarial exams. Dave can be contacted at dave@saltsolutions.com.

Terence Chow is an Actuarial Assistant at SALT Solutions and CoachingActuaries.com. Terence can be contacted at tchow@coachingactuaries.com.

Lawrence Hii is part of the actuarial supporting staff at SALT Solutions and CoachingActuaries.com. Lawrence can be contacted at lawrence@coachingactuaries.com.