A PowerPivot Conversion Success

I have just completed a major conversion of one of my “traditional” spreadsheet reporting solutions to PowerPivot for a major coal mining company.  It has been one of the most satisfying solutions I have ever done and convinces me even more than before of the importance of PowerPivot to all Excel users.

The Problem

While the older solution relied on data gleaned from SQL Server, the presentation layer relied on standard Excel formulas.  This meant that if there was ever a change, deletion or addition to the levels of the model (i.e. new mine, closed mine, reorganization, etc.) I personally had to make a detailed and frightening change to the core report that impacted levels up and down, right and left throughout the model (new Sub-Sub-Totals, Sub-Totals, Totals and Grand Totals – new variance analyses up and down through the model).  This was a tedious and painful job and one that often cropped up at inconvenient times for me –  always when I was busy working somewhere else in the world.

The second problem was that this business model was just too complex for a standard Pivot Table solution – so that was not an answer.  One thing that always makes me shake my head is how complex businesses can make their business processes and how it is usually legitimate.

PowerPivot on the other hand is capable of multi-table, DAX-driven calculations that can then be dropped into the enhanced PowerPivot Pivot Tables in a way that accomplishes exactly what the original model did – but totally data-driven and needing zero maintenance by me if any of the things I mentioned earlier happen (additions deletions, reorgs, etc.)

What I Learned

I learned (or re-learned) the following very important things in this process:

  • This shit isn’t easy!!  Anyone who says that PowerPivot makes reporting in Excel easy should move beyond Adventureworks and try doing REAL solutions for REAL businesses
  • I could not have done any of this without my knowledge of relational databases in general and SQL Server in particular.
  • “Pre-Aggregation” is critical to a successful PowerPivot solution.  The idea of bringing millions of records up into Excel and then doing all the work there is not only just not practical but simply ridiculous.  Why would you want to bring detailed transactional data into the backend of an analytical solution? For example in my solution I only cared about how much of each type of coal was mined at each mine by day and only for the period I want to analyze.  In this case it shrunk a nearly million row data table of 4 years of truck data into 22,000 rows of aggregated data for the year 2013.  I had all the info I needed and the size was manageable and most importantly the time for refresh is acceptable.  It also means that my solution can live in client-side Excel and does not need to be promoted to SharePoint to succeed – although SharePoint is going to also be part of the solution as well.
  • SQL Server Functions are the most efficient way to return data to your PowerPivot solution.  A straightforward SQL statement like

select * from fnTruckSummary(’01/01/2013′,’12/31/2013′)

is efficient to work with as you move through all the various data sources involved.  I have also found that Functions are significantly faster than Views for reasons I have yet to explain.

  • My code to change the source data dynamically through VBA works as advertised and I am finding that my personal “Best Practices” around this technique work well for me.  For example is created a table on a separate spreadsheet like this:


If I need to feed a value chosen by the user on the spreadsheet through clicking a slicer to choose a particular year of data I use a formula. So for example underlying

spFcst_Plan 2013,F above is this formula =”spFcst_Plan “&CurrYear&”,F”.

Then my code to refresh all these connections is just

    For Each r In Range(“tblConnections[Connection]”)

        Connect r.Value, r.Offset(0, 1).Value, Range(“txtProvider”).Value

    Next r

Sub Connect(ConnName, SQLText, txtProvider)

    With ActiveWorkbook.Connections(ConnName).OLEDBConnection         .CommandText = Array(SQLText)         .Connection = txtProvider         .Refresh     End With    

End Sub

Notice that Range “txtProvider” in that code is a cell reference to a cell in the model where I manage the OLEDB provider for all these links.  This allows me to easily change the Provider for all these links in one place one time.

Once I have refreshed all the data sources I set the value of a cell that is visible to all users of the model to capture the data and time last refreshed

Range(“LastRefresh”).Value = Now()

Then I save the spreadsheet and announce this to the user\


    MsgBox “Refresh Complete … File Has Been Saved With The Current Data”, vbInformation + vbOKOnly, “Data Refresh”

In this way when a user opens this file they may not need to refresh it if, for example, the first user of each day runs this refresh code (driven by a button on the first Worksheet).  This way all other users only need to open and use the file (no one really cares about the changes in the data until the end of a day anyway so today’s data means nothing to them).


I know that there are those who think that my posts are too long (??) but the fact is that I could go much, much further into the details of all this but I won’t anymore then.  Instead I will give you highlights from now on and you gotta figure the rest out for yourself, I guess.

In the end every solution is a snowflake (they’re ALL different) and all I can do to help promote this technology is give you high-end things I have figured out.  I see a lot of folks coming here but very few comments to tell me whether I’m helping or not.  That frustrates me a lot I’m afraid :-(.

I will however keep posting here because I believe that this technology is important to us all and to our clients internal and external.

I also believe that most of those posting about PowerPivot and Excel futures are ignoring the capabilities available to client-side Excel users in a way that risk the acceptance of PowerPivot broadly.

Funny final story …. Whenever I mention to ANY of my clients that my analytical solutions in Excel using PowerPivot can be published to a SharePoint “Gallery” and shared easily by Senior management and others throughout the organization they always say “That’s the LAST thing I want to do !!! I want this available only to those who can get REAL business value out of this !! I can’t spend all my time answering stupid questions from people drawing stupid conclusions from all this !! “  I hear this EVERY time I mention the SharePoint Gallery option…..  Too good – and makes too much sense.  Data distribution has to be controlled as well.


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 Business Intelligence, Excel, Excel 2013, Microsoft Excel 2013, Office 2013, Office Automation, OLAP, PowerPivot, Spreadsheets, VBA. Bookmark the permalink.

12 Responses to A PowerPivot Conversion Success

  1. Dick, I think that many people (including me) greatly appreciate anything you are willing to share with us. Please write as much as you can. Although I am an expert in many Excel-things, there are some things I am not an expert in, so continue to share. One overarching problem is that it is difficult enough for “Excel pros” to become PowerPivot experts using the party line methodology, let alone your fantastic “workaround” process.

    • Dick Moffat says:

      Thanks David .. Much appreciated.

      What got me upset about the comment made about how long my posts are is the fact that such a comment reflects the stupidity of so many “leaders” of the “community” who think they can show off a few tricks they’ve learned by accident and that’ll help people (and make them saints or something)… The fact is that this Powerpivot is a bitch of a thing … But it has to be in order to get the job done.

      But thanx to Microsoft Powerpivot is just a way to sell Office 365 and for their sanctified “gurus” Powerpivot is a way to show how smart they are and book training that teaches people nothing about how to REALLY use the product.

      I have some doubts if it is in fact possible to train anyone on Powerpivot anyway because it’s soooo complicated and the business processes it would be used against are totally unique one from another.

      To really know how to use Powerpivot means you gotta know it at a high level (especially DAX) and then you have to know realational databases amd especially SQL Server at a high level and then you gotta be an expert on Excel itself… That’s a lot !!

      It’s just like saying you can take a 3 day course in SQL and then lnow howbto go put and create an ERP suatem from scratch – times 3 :-).

      It’s just going to take a lot of sweat equity on the part of anyone who wants to deliver a REAL solution using Powerpivot. This is just reality..

      So since no one is going to know all of that there has to be a set of practical best practices laid out and everyone has to work together (spreadheet people, database folks and IT and Microsoft) to create a Powerpivot solution. It ain’t gonna happen the way Microsoft and its “pals” are pushing this…. And trust me … If Powepivot does not fly in the market Microsoft will move on .. Unless they already have actually…

      Dick Moffat Sent from my Cell Phone 1-519-200-7133


  2. Dick, I really like the way you look at Power Pivot. I also think that it has a lot of potential but it is by far not the easy selfservice bi tool as advertised. You need to be in the game for quite a while to get things started in Power Pivot.

    • Dick Moffat says:

      Hey Andy:

      Thanx for that .. It makes me feel better. I feel like the Lone Ranger on this.

      It amazes me how MS keeps missing the boat on their marketing year in and year out. I remember when they decided that “Smart Tags” in Excel 2002 were going to cure cancer and bring peace to the Middle East :-)… I knew it was a crock immediately and watched them push it and push it for more than a year .. It would have been embarassing if anyone gave a shit… But it was a complete waste of time and actually a negative.

      And now after a few years not talking to anyone who really use their software (except their usual sycophants) this is what we get – a great technology with a stupid message and with a new alternative BI “toy” released every few months just to make the confusion worse.

      I suppose I’m supposed to not care because I don’t work for MS but without people like us to actually USE their products what they’re doing is pointless. So just maybe they should listen to us before they go out and screw their message up totally.

      The fact that this repeats over and over year after year from the Office people is beyond frustrating…

      Dick Moffat Sent from my Cell Phone 1-519-200-7133


  3. timrodman says:


    Thanks so much for this post. This is amazing stuff!

    I’ve been using Power Pivot for almost a year now and recently started attending Rob Collie’s Power Pivot user group here in Cleveland, but this is one of the best technical posts on Power Pivot that I have ever read.

    I am in the IT department, but we are aggressively trying to put reporting power back into the hands of the business users by teaching them Power Pivot and connecting to user-friendly Data Warehouse SQL tables that we are creating. Currently we have several published dashboard reports that are based on Power Pivot, but the major drawback is that they have to be manually refreshed by the users. We aren’t about to go down the SharePoint / Power Pivot road just yet since it seems like a complicated path.

    So, we currently have people manually refreshing the data by going into the Power Pivot window every time they want to update the report. Your post answers a question that I have had for a long time about how to do this through VBA. This is great!

    You also introduce something that I had never even thought of: controlling what data gets returned into the Power Pivot module by using a slicer (wow!). This would also allow me to bring data into Power Pivot from a Stored Procedure (wow, wow!).

    I also like your idea to wipe out the Power Pivot model to get the file size down, but I tried it and I’m not sure how do you prevent your Pivot Tables from refreshing against the now empty Power Pivot model. Can you help?

    Anyways, thanks again for the post. In case you’re interested, this is how I wound up here:
    1. I read Rob Collie’s blog regularly and just read a recent article: http://www.powerpivotpro.com/2014/01/lost-in-a-polar-vortex-of-google-spreadsheets/
    2. That article mentioned part 1 and 2 of a discussion that now has part 4: http://datascopic.net/excel-google-spreadsheet-headed-part-4/
    3. Part 4 lead me back to an older post on Rob’s blog: http://www.powerpivotpro.com/2009/12/microsoft-unveils-new-programming-language-xl/
    4. Which lead me to your blog and one of the most recent posts: https://dmoffat.wordpress.com/2014/01/07/a-powerpivot-conversion-success/
    5. Which then lead me to search your blog for the “best practices” phrase that you mention which landed me here

  4. gregkramer1 says:

    Love the long blog posts. Your a real pro and your candid reflections are thought-provoking!

    Having spent the last few months on a Power Pivot project, I would concur that it is powerful stuff and almost nothing about it was easy. Even a last minute salvage from the PowerPivotPro himself was required at the end.

  5. Matt Hughes says:

    It ain’t easy. That’s the profound truth. It bothers me a lot that the leaders of the Power Pivot movement paint a very different picture. It may hurt them in the long run…Excel users that are chasing the mastery of this product beyond the “self-service” BI model may fall off. With each step I’ve taken to learn Power Pivot and DAX I’ve discovered the hidden prerequisite skill sets that are mandatory to go beyond “self-service” or “departmental” BI. I’ve been using Excel/Access for a long time and I really believe Power Pivot is a dream product. I can solution-out my sales reporting problems easily using the “self-service” model. In my opinion, knowing this is simply the first step. Although it’s exciting to find what appears to be a perfect quick-solution, the road to a complete solution requires much additional learning (my Amazon.com book reviews are proof). The flow of books and software from Amazon was significant in order to properly approach a fully-automated solution that would not require human intervention. Your post was great…don’t stop…and post-long whenever you feel like it.

    • Biggus Dickus says:


      Thanx for you thoughts.

      You nailed it. Unfortunately it appears as if Self-Service BI is what they want and I do not believe there is any support for anything beyond that. I too think that is a big mistake because you’re going to end up with nothing but half-finished solutions where the user reaches that “wall” where they need more skills that only a small number of them can possibly have – and without some kind of community of “professionals”to assist them they’ll just give up.

      This is not meant to just be self-serving, it is just the facts as I (and apparently you) seem them).


  6. Cad Lag says:

    You blog is timely. I have been using MS Excel, MS Access, and VBA for years and have recently started on Power Pivot 2013. I like the idea that MS is providing a self service business intelligence solution package. I need some case studies from advanced users like you, because they add to my set of tools. Hopefully, I will learn enough one day to pay it forward. If you are wondering, I appreciate the detail that comes with a long post. At this stage in my learning, more details are better than fewer details. Keep up the good work.

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 )

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