Analysis on the Cheap

I often remind participants in my seminars that every accountant I’ve ever met shares a common favorite four letter word.

Seriously.

That word is, of course, FREE.

With that in mind, I’ve run across some tools over the last few months that I haven’t blogged about, and wanted to pass these along to you all.  The tools are available for the low, low price of….. wait for it…. FREE.

Analyzing and manipulating large data sets has long been the job of accountants, auditors, and analysts, and the classic tools for these tasks like CaseWare IDEA, ACL, and others have always been very expensive and required a week or so of training so that you can be proficient with them.  With the general availability of quad core processors, workstations with 8 GB+ of RAM, fast 1 TB hard disks, end users no longer need to wait on someone to create a report for them.  Get the right tools, get access to the data, and get to work. 

Basic Tools: Microsoft Query and Excel PivotTables

Although they aren’t technically “free” since you need to purchase Microsoft Office to get them, Microsoft offers a couple of tools which are just as useful to analysts as an adjustable wrench (a Crescent wrench) and a claw hammer are to those doing home repair.

• Microsoft Query is a tool for selecting, joining, sorting, filtering, and extracting data from databases.  Query is one of those “helper” applications which is hidden from most users – I have only executed MS Query from within the Data tab of the Excel 2007/2010 Ribbon (Data, Get External Data Group, From Other Sources, Microsoft Query), but it is invaluable for entry level work with databases, and is compatible with any ODBC compliant database which you can use on your PC, including MS Access, MS SQL, and many, many other formats.  Queries can be created, edited, saved, and executed from a simple menu structure, and a wizard makes the hard task of writing SQL statements into child’s play.
• Excel Pivot Tables make it possible to summarize large data sets into interactive tables.  Although a full discussion of Pivot Tables would take all day (I actually own entire volumes written on Pivot Tables, and teach TWO half day classes on them (1) (2) through K2 Enterprises), suffice it to say that Pivot Tables will change your life, and do just about anything you want except make your teeth whiter and make you more attractive to members of the opposite sex.  Seriously – they’re that good.

 

ActiveData For Office

My friends over at InformationActive.com have a couple of nice products which meet the needs of accountants, engineers, and other professionals who need to perform sophisticated analysis on large data sets.  While my favorite one, ActiveData for Excel, is still a pay application, there is a version of this powerful tool called ActiveData for Office, which is now available for the low, low, price of FREE.  ActiveData for Office (also referred to as “ActiveData for SQL”) uses standard ODBC connections to talk to large databases, and will perform routine calculations like verifying the accuracy of an A/R aging report based on the dates in an open item listing, stratifying a sample, and evaluating sample results.  I’ve been somewhat confused by the move to give away this product, as I think it’s more powerful than the Excel tool – but pricing decisions are above my pay grade.  For more information, visit InformationActive.com’s page on ActiveData for Office.

ActiveData for Office is a FREE data analysis tool which is invaluable for Accountants and other information professionals who need to slice and dice large data sets.

 

PowerPivot for Office 2010

With the release of Microsoft Office 2010, our friends at Microsoft have come to the table with one of the best tools for summarizing huge (100K+ records) data sets.  While the tool does require that you become an early adopter of Office 2010, the price is definitely right, and the tool has been used to analyze data sets with hundreds of millions of records.  PowerPivot (free from www.powerpivot.com) is a self-service business intelligence tool designed to let end users create their own business intelligence (BI) solutions which combine data from disparate sources using an Excel add-in.  For seasoned BI professionals, PowerPivot creates pre-summarized tables which are periodically updated and can be queried similar to cubes for almost instant calculations on data sets with millions of records.

While you can do many of the same things using MS Access, update queries, dummy databases, ODBC connections, and pivot tables, PowerPIvot is both more powerful (smarter) and easier to use (better looking) than the alternatives, and it’s free.

If you haven’t tried it, PowerPivot is available from www.powerpivot.com.

Comments

[Jeff Huckaby]

Brian - enjoy your blog. On PowerPivot, having used it for over 9+ months, the tool is a lot more powerful than anything you can do with Access. You can easily import many large data sets (hundreds of millions rows of data) from a wide variety of data sources: oracle, sql server, teradata, .txt files, excel spreadsheets, etc. You can easily create data "mash-ups" that would normally take IT weeks to complete, in minutes/hours. The key component of PowerPivot is its ability to store the cube "in-memory" for very fast analysis. In addition, PowerPivot can compress data at a factor of 50 to 1. Informative dashboards can be created using the existing PivotTable functionality, and calcultions (called measures) can be created using PowerPivot new "DAX" language, using built-in time intelligence, such as MTD, YTD, and period over period analysis. Sharing the analysis takes a click of the button to share to the corporate intranet via SharePoint 2010.