PowerPivot in Excel 2013 Is A Break-Through For All Excel Users

FYI – I, Dick Moffat, have been building spreadsheets for others professionally for data analysis since 1985.  Seriously .  I started using by using VisiCal in 1983.

The Data-Centric Spreadsheet

For some strange reason when I first saw Lotus 1-2-3 I saw more of a way to analyze records-based data than the standard way a person has used a spreadsheet.  That is not to say that I haven’t done many, many “traditional” cross-tab or completely “free-form” spreadsheets to satisfy the perceived requirements of clients over the last thirty years but I have ALWAYS been drawn to the capability of spreadsheets to analyze “data” structured in rows and columns (records and fields).  I have no idea why.

Even in the late 80s I was using add-ins in 1-2-3 to query external data sources, most notably Informix databases and Dbase files.  In 1993-94 I built a tool in Excel 5 for a major New York bank where I used an OCX to query a Sybase OLAP server, returning the data back into Excel and then allowing the user to drill up and down based on the rows and fields returned, creating a pivot table connecting to external OLP data in effect by manipulating the results using VBA.  This was used with great success for 2 years but unfortunately a merger led to the group I was working with disappearing (the story of my career) and the tool (called CitiLens to give a small hint) died because the new people wanted to do new things – of course.

Unfortunately this gig was so far ahead of its time that the skills I developed there simply died on the vine too. I moved back to traditional spreadsheets with the then new Microsoft Access behind them as a source and became a fair-to-middling Microsoft Access developer over the next decade or two.

But I have jumped on every small incremental improvement brought to Excel with regards to data integration over the last 23 years and have had great success quietly developing query and analysis tools for many clients worldwide.  Sadly I was never able to interest Microsoft in what I was accomplishing, so I just soldiered on doing cool data-centric work for those clients I could find that “got” what I was able to do.

Recent projects have included daily reporting from multiple SQL Servers for a Major Coal Mining Company in West Virginia reporting daily for the last 4 years on the previous day’s production at 20+ mines, comparing to Budget, Forecast by Day, MTD, YTD and consolidating across the reporting areas and across the product line.  I have built a reporting and management tool for a major Canadian Bank’s U.S. ops integrating corporate data on client history and analytical facts.  I am part way through a project for a major communication company in the Netherlands that uses PowerPivot with SQL Server and SQL Server Linked Servers to provide querying and analysis of network data across multiple locations in Europe with data in SQL Server, MySQL, Oracle and Access.

Hello PowerPivot !!

About two years ago, out of the blue, Microsoft quietly shipped a new product, an Add-In for Excel, called PowerPivot for Excel (PP) that allowed several things that had never existed in a Microsoft Desktop product before:

1. It used a new technology to effectively compress data imported into it in such a way that you could import 10’s and apparently 100’s of millions of data records into a single Excel file (!!>>) and see an extraordinary compression ratio that led to Excel files that are still huge but that are much smaller than one would expect and that effectively eliminated the upper limits of data imports for most potential users of Excel.

2. It included the core OLAP technology of SQL Server Analysis Services (SSAS) into these new Excel files, thus turning them instantly into what MS calls DIY Business Intelligence or “BI for All” and other such cool marketing phrases. Thusly inside these files a user had the ability to analyze the data just like using Cubes in SSAS.  Very cool indeed !

3. In addition it provided a set of internal functions that allowed to developer/user to add significant value to the analysis using a new type of BI function set that in effect emulates to capability of MDX in SSAS.   The DAX functions open up incredible capabilities to add value to the data available and ultimately change the way Excel is used for data analysis eliminating the need for traditional Vlookups and even traditional SUMS and Counts and Averages in-situ in the worksheets but rather PP can do ALL the work for you and drop the results in a structured, formatted way in the model ready to go.

4. It would provide a way to track KPIs as well.

  • 5. In addition Microsoft has provided a capability within SharePoint to host these wonderful PowerPivot Excel files in such a way that all the data being accumulated can be promoted up to a SharePoint Server and be provided to all users with access to Corporate SharePoint using a browser with very slick and interactive web-only analytical web pages.  In effect making the PP Excel files the source for a potentially huge internal data warehouse dedicated exclusively to PowerPivot.  Very, very cool. All starting with a blank Excel file sitting on a user’s desktop!

Hello Excel 2013 !!

With the release of Office 2013, PowerPivot has been integrated more tightly into Excel and PowerPivot itself has been evolved to include things like Hierarchies (something I will be talking about soon), a more advanced UI for managing relationships and new capabilities for DAX.  They have also integrated the PowerPivot data store as the primary store for all data objects within Excel itself . They have also integrated PowerPivot function into the Object Model thus giving some degree of programmability to your PowerPivot integration in your models (I will be discussing this here soon).

So Microsoft has shipped what is basically a desktop product that can replace all those dangerously free-form, user-built, and supported Excel files out there and replace them with disciplined, data-driven models that can then be transformed into major corporate Web-Based reporting Portals for all consumers in the Corporation high and low.   They also believe that that today’s Power-User Analyst in today’s corporations can do this all themselves.  Great idea – right? 

Absolutely right!    But with a little help maybe?

Opportunity For Everyone

Over the last 20+ years Microsoft Excel’s sequential management groups have believed in the Data-Centric spreadsheet all along and I’ve just ridden the wave (basically Microsoft has confirmed what I have been promoting quietly for the last 30 years) … But until now it has been a very small wave without a lot of genuine effort on their part to spread the word.

Still, despite all the competing technologies that have tried to beat Excel over the head from extinct company after extinct company, in the end people want their shit to end up in Excel ! And they want to PULL that data in rather than rely on someone else to PUSH it out to them. Makes perfect sense to me.  So PowerPivot is the perfect answer for all the millions of users around the world not even realizing that this is what they really need and really want.

The key thing I take from this is that, if properly designed, a PowerPivot Excel Workbook should be able to work day after day, week after week, month after month and year after year producing reports that reflect your businesses’ activities as they grow or shrink, change and evolve.  

By being driven exclusively by tables of data fed from (hopefully) properly structured, transactional corporate data a PowerPivot Excel file should be able to reliably produce the real reports that real managers really need to really run their business – not just do good demo ….  This is HUGE progress …  That is IF Microsoft can sell it and IF companies accept that the paradigm-shift PowerPivot would involve in their internal use of Excel and their internal reporting is worth all the effort – or if it’s even possible (?).

In the near future I will endeavor to use my years of experience to help the PowerPivot / Excel / Data story with blog posts here.  I promise to help people understand all the stages that MUST be successfully completed to implement a PowerPivot “Solution” including data acquisition, transformation, pre-aggregation, star schema design and implementation and important techniques I use to keep the data “fresh” and current in a typical, real corporate environment.  I will also discuss techniques for actual Excel Model design that integrates this new technology.  Frankly we all have to think differently about our spreadsheet design from now on.

I am also of course available to consult anywhere in the world remotely or on-site on Corporate Reporting, PowerPivot, Data integration, and Excel broadly.

Thanks guys !!

Thanks so much to Rob Collie and Bill Jelen (Mr. Excel) for their efforts promoting PowerPivot for Excel.  I am one of your disciples and I hope nothing but the best for their efforts for them and for all Excel users and I will help you any way I can.

About Biggus Dickus

Dick is a consultant in London, ON Canada who specializes in Microsoft Excel and Microsoft Office Development.
This entry was posted in Uncategorized. Bookmark the permalink.

6 Responses to PowerPivot in Excel 2013 Is A Break-Through For All Excel Users

  1. Giorgio Rovelli says:

    Are there any examples of Excel PowerPivot applied to extract and report data from SAP?

    • Biggus Dickus says:

      I have accessed SAP tables before from SQL Server no problems. SAP uses standard database backends (SQL Server, Oracle, etc.) so connecting to it is pretty simple BUT …

      1. It is usually very tough to get rights to directly connect to the SAP database. SAP DBA’s are notoriously tight-assed and really don’t want you doing that. Frankly they want to do it for you – smacks of job insecurity most times. They really hate the idea of an external application tapping their database and using up cycles on their server. It is NEVER easy to get at SAP data directly.

      2. Then if you do get in I have found the structure of the tables in there are optimized fro the program and it is not always easy to figure out what is where (a common problem in all databases designed by others I find 😉 ).

      3. To me it is best if the organization sets up a data warehouse to capture the SAP data into separate from the core application… but that becomes a major pain very quickly.

      Hope a little truth helps. I know that’s not what you wanted to hear ;-).

      Let me know if you know a better way anyone.


  2. Giorgio Rovelli says:

    Thanks Dick, unfortunately I’ve experienced the same uphill struggle when it comes to interfacing Excel or Access with SAP. Can the DAX functions be called from Access?

  3. sam says:


    SAP is a front end to a Database (Oracle, SQLServer etc), Like Access being a front end to the JET Database

    So if you can pull Data from Oracle, SQLServer etc in to Excel then you can pull data from SAP to excel directly bypassing the ABAP layer.

    I have built a Resource planning tool for one of my Client that pulls and pushes data from Excel directly to SAP (ORACLE DB)
    While this was not a challenge technically – Some one very high up the ladder from the business side had to talk to some else very high up the ladder on the IT side and after what exchanging some pleasantries :-), the access (and the Password) to the tables was made available to certain people who had to use the application.

  4. Giorgio Rovelli says:

    Well, I can see an example of integrating SAP and Excel by looking at Figure 1 on http://msdn.microsoft.com/en-us/magazine/cc337889.aspx but wonder whether things have got even better since then

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s