skip to Main Content

by Matt Allington Many businesses (dare I say most) are yet to get started with Power Pivot and Self Service BI.  Often the first person in an organisation that discovers Power Pivot doesn’t know how to navigate the political and cross functional challenges within the organisation to make it all possible.  The tragedy of this are the missed opportunities – lost time is lost money.  There are huge benefits that can flow to businesses if they could just get this happening. There are benefits for IT and the overall business, including lower IT costs, faster speed to value realisation and indeed green field analyses that would not have even been contemplated as feasible without a Self Service approach and a tool like Power Pivot. Start Line

How to get started?!

So what is the relevance of all this?  Well I recently caught up with SQL MVP Grant Paisley (from Angry Koala) for lunch where we discussed these challenges. I first met Grant when I was Director for BI at Coca- Cola Asia Pacific. Martin Kirby (my Microsoft Account Manager at the time) brought Grant in to see me and talk about the new Power BI tools that Microsoft had been developing. In many ways, this was one of the first days of the rest of my life.  Rob recently suggested that Grant and I get together again and see what opportunities exist to collaborate. Well as it turns out Grant had been doing a lot of thinking indeed. As we chatted over lunch Grant talked about some really exciting concepts that had not occurred to me in the past. You see Grant has a few more Kilometres under his belt on this topic than me (for my US buddies, the relative proportion of Miles under our belts is still the same!). Grant comes from a SQL background and Grant (and his company) are experts at dealing with big scale, enterprise strength BI solutions in significant companies.  Grant is an expert at helping IT do what it does best – delivering consistent and reliable solutions to the users. I on the other hand have a lot of real world experience with business problems, customer pain, using Excel as a business tool and generally living the life of a user. What Grant has highlighted is that there is an opportunity to help companies with both SQL expertise and Self Service BI/Excel expertise at the same time.

Talking with both IT & Business is a big opportunity

Both IT and Business users all want the same thing – they want users/customers to get what they need with the minimum cost, effort and disturbance. And yet there has been a life long struggle between business and IT people, with each trying to translate their own needs to the other party. With the introduction of Power Pivot for Excel, business users now have the potential to solve many of their own BI reporting needs without the need to run to IT every 5 minutes to make a change to a report (or worse still make a change to the data model).  Well more correctly, business users are able to solve their own problems PROVIDED they can access the data they need, ideally from the source. Database locked away   But even if the user can access the data directly, the data is often in a format that is not ideal for Power Pivot.  Rob often talks about finding a “SQL friend” to help you shape to the data you need for your Excel based Power Pivot reports.  This is a great strategy because unfortunately the shape and format of data that is used in relational databases is usually sub-optimal for use in Power Pivot Data Models.  Of course you could (and should) use Power Query to help you shape your data, and this will get you going and even keep you going.  However nothing is as good as working with IT to get a direct and consistent access to the data that you need in the exact format you need.  So getting help from someone with access to the underlying database and knowledge of how to write views in SQL can be invaluable for a Power Pivot Excel user.

pp rdbm data model sml

Typical structure of tables in a data model when sourcing direct from a relational DB

PP Data Model sml

A much better table structure for PowerPivot – achievable when working with your IT department.


Two bows are better than one!

There is a big opportunity here to divide and conquer, and engage with both IT and the business at the same time with a targeted message that makes sense for the audience. Target

IT talking to IT

In my experience IT departments can be quite suspicious of users that want to get access to the database.  I guess IT departments are paid to be suspicious and cautious – after all it is the job of IT to make business data highly available and consistently correct.  And that’s where a professional SQL company like Angry Koala comes in.  Grant and others like him are used to engaging with IT departments and also understand the Power Pivot story.  SQL BI professionals have the best opportunity to explain the Power Pivot vision to an IT department, and help the IT pros understand the benefits of helping the business users get direct access to the required data views to build their reports.

Business talking to Business

On the other hand, business people will get the best benefit from talking to people that talk their own language.  Generally speaking, business people know A LOT MORE about Excel than IT folk, and that should be no surprise given it is the business people that are the professional users of Excel.  So by having an Excel/Power Pivot/DAX expert talk directly to the business people about how to leverage Power Pivot will help business users buy into the vision.

Then bring it all together with synergistic results

So by focusing on both of these areas at the same time, it is possible to communicate the value proposition to businesses as a whole in a way that would otherwise not be possible.  And when the message is delivered correctly, there will be benefits that come in the form of lower IT opex and capex costs over time, faster speed to value realisation for the business, and value realisation in areas that would not have been contemplated as even practicable without such an approach.

And the opportunity is available to all

So this is a model that Grant and I are now focused on, but of course it is available to all people that have the vision and relevant skills to bring the parties together.  In many ways, one of the biggest costs to businesses is the cost of doing nothing, so there is no time like the present to bring senior IT and business people together to create the spark to realise the benefits.  I encourage people that are stuck at the starting line to reach out and connect to the people that have the relevant skills to help you get this party started! I would love to hear about the challenges you are facing in your organisation to get this thing happening.  What are your challenges?

Matt Allington is a professional Self Service BI expert, Power Pivot consultant, trainer and Blogger based in Sydney Australia.

Matt Allington

Matt Allington is a Microsoft MVP specalising in Power Pivot, Power BI and Power Query Consulting and Training based in Sydney Australia. Visit Matt's blog here.

This Post Has 17 Comments
  1. Love this article. I’ll be borrowing quotes from it to continue move to pivot solutions in our organization. Once people lose the fog of Excel and move into pivot structures, they become hungry for more.

  2. hi, I’m someone with an Excel Pivot and now Powerpivot background who also has had a lot of exposure to SQL/T-SQL with Access and SQL Server. I must admit that until recently I was swimming a little in the aphabet soup of SSMS. SSAS. SSIS, SSRS, DWH, ETL, MDX. etc etc. I have now had time to learn more about these and their roles, uses and relations with one another are now much clearer to me.

    I am a qualified accounting professional (CIMA), however, I am wishing to make a slight change in direction and follow a BI path. I’ve always been more interested in the data and ways to use and re-use it than I have been in say, the intricacies of the tax system (and boy do we have them in Anglo countries, but that’s another subject), relationships with auditors or the politics of the workplace. I’ve been the Excel guy wherever I’ve been. So I think this is the way for me.

    PowerPIVOT looks like what I’ve been waiting for. I worked with it a lot in my last job and was aware of DAX and suspected that it held answers to some of my problems and roadblocks but just didn’t have time to really find out what it was, nor did I understand then, clearly, what measures are and why they are different to items in calculated columns. I’ve now read and followed Rob’s DAX book, and get that DAX and PowerPIVOT makes possible many many things that were never possible with flat sourced Pivots. I can now see that PowerPIVOT/DAX removes much of the wall between the db, formal data and the flexibility that business end users have always enjoyed about spreadsheets and which remain the reason they keep choosing them (not always correctly, I’ve been guilty there too) as their favourite tool.

    So I have started looking for work in London. Finance jobs barely mention technical skills other than XL and perhaps whatever accounting package the organisation happens to use. I get few hits of Finance with Business Intelligence (though lots separately). Meanwhile, IT boards, where the Business Intelligence jobs are, have lots of SSMS/SSAS/SSRS, MDX and SQL jobs but hardly a mention of DAX or PowerPIVOT.

    So, I’ve learnt some MDX and learnt what SSIS/SSAS/SSRS are and how they fit together and played around with them (I’ll play some more yet) so far as you can on a home PC with the evaluation versions.

    So, sadly, it looks to me that if I’m going to get a BI job it’s going to be on the basis of my enthusiasm and knowledge of the multi-dimensional model rather than my more hands-on production experience of Excel and PowerPIVOT/DAX.

    It seems to me that PowerPIVOT has a long way to go in the UK.

  3. I’ve been struggling for the past two years two get my organization on bored with PowerPivot and PowerBI. I’m currently fighting through getting PowerPivot enabled on SharePoint. As a SharePoint organization we should absolutely be using Power Bi tools.

    As this article mentions my BIGGEST obstacle has been to get IT to give me direct access to data. I am not an IT person. I am an analyst with knowledge of SQL, and report writing, and of course excel. How can I help them understand my need to connect to the data? What are their concerns here and how can I allegiate them?

    1. Indeed these are the questions! I know it can be very frustrating. As I let my sales experience kick in, I guess a good place to start is to understand the specific objections IT may have with letting you access the data directly. Getting a straight answer may not be that simple, but basically you need to know what the problem is before you can address it. One thing IT HATES is people writing queries over the data. If you do it wrong, then you can bring the systems down, and that is not good for anyone. But that is where budding up with a SQL professional can help. Get them to write the general queries that give you access to the data you need in a standardised shape, and then you can just extract from there on demand (if they let you of course). It is not about whether you can do it yourself or not, it is about appeasing those that are charged with protecting the organisation. Also you need to help IT understand how it will benefit them.

      One approach I think is useful is to produce great Power Pivot reports yourself, extracting the data using what ever methods you do have available. Then start using these reports in your meetings, presentations etc. Sooner or later someone will say “wow, how can we share this…” etc, and then that leads to “well I’m glad you asked, because we do have SharePoint and if only we could….

      1. A lot of great advice here! Thank you so much! I think my biggest obstacle is putting aside my frustrations and needs and thinking about how to address the concerns that the others have. I might have to read your post before work everyday.

      2. You make some interesting points here Matt. Producing great looking PP reports is a great way to start spreading the word and this is exactly what I am doing – and it is starting to show results. Also, host them on Sharepoint if possible – much better then passing Excel files around. Even without the full server side Power BI stack, it is possible to host PP reports on Sharepoint and I have found this to be a great way to make reports available to others, which has been very well received.

        I am also an IT person – BI Manager, but can’t help feeling I must be in the minority, as it is my firm belief that business users should have access to company data to enable them to do their own thing, rather than having to wait on over-stretched IT resources to do it for them at some stage in the distant future.

        I manage an SAP BW data warehouse and in the absence of direct access to SAP BW from PP (hopefully this is on the MS radar) I have proposed making data available to users via regular extracts that they can then pick up and use as needed. I think it important with an approach like this to also offer ongoing support and guidance to users so that they don’t feel they have been thrown in at the deep end and left on their own to sink or swim.

        Without these extracts, users can extract the data themselves by running SAP BW queries; the problem with this is that these queries are written with specific reporting requirements in mind and so don’t always offer the most appropriate data structure and/or content. With the proposed extracts, users are able to do precisely what you have documented, which is to access data in a standardised shape and then extract and use from there as needed.

        The really great thing is that with access to corporate data in this way, it can then very easily be merged with other (non SAP BW) data, giving end users a whole new world of possibilities.

        A great article….thanks!

  4. Great post! I also wonder what you think of this one: how would I address concerns that PowerPivot skills are not widespread and if my company were to make the switch and lose me, would anyone else be willing/able to maintain it? I’ve thought of using the survey Rob did showing interest in starting a pp usergroup, but it didn’t help my case for my area. And before I finished this I already went back to look at old posts and found the Modern Excel group and joined. Maybe I need to come out of the closet and publicly admit my PP enthusiasm and I’ll find some locals.
    Thanks, Kerry

    1. This is another good point. Companies don’t like a single point of failure – and nor they should. I think the right answer to manage the risk is to broaden the approach/appeal to many people on the organisation. I guess that is limited by “how many” you have, but if you have an IT department I guess there must be a few people.

      And I would definitely focus on the upsides first (to create the demand) and then focus on how to manage the downsides once everyone agrees the solution it is worth pursuing. So the upsides are those I covered earlier – lower costs, speed to value, analyses that would never be considered without such an approach etc. Find an opportunity in your business and plug the hole with Power Pivot. Someone will surely notice! If the benefits are clear and the business wants to get more of that, then it will be much easier to get people on board with finding a way to manage the downsides.

      I think the trick is to get people interested in the benefits first. After all, that is why we are all reading Rob’s blog on a regular basis, right?! Because we get it – we just need to get the others to see it too.

  5. My recent challenge is the end user – despite the capabilities of PowerPivot, they would like to have data dumped out, in a format that they are used to.

    So each week, they would prefer to have an updated regular Excel file with the updated raw data in one sheet/tab and the summarized weekly numbers in other sheets/tabs ;(

    But I could do so much more… if I could set it up in PP! There could be “the week’s numbers” – plus any week’s numbers, week/month/quarter trends, actuals versus estimates, etc., etc.

    It’s new, it’s different… sometimes people are not quite ready for a change.

  6. Could you maybe build some great looking reports in PP to highlight the advantages of going the PP route? This may then help your users to overcome their fear of the unknown.

    1. Yes, that has worked in some cases – and I’ve converted some people by doing just that!

      However, in this case, the end user does not want something different – just keep it the same.

      Which actually creates more work, I think 😉 …To replicate a manual process into an automated process to create dumps and summaries to match the existing formats versus aggregate data once, import into PP and go from there!

      Even some experienced Excel users are slow to adopt the new tool – I’ve provided exposure to the tool, examples, had training sessions, set up workbooks with data and still they are not quite comfortable because it is something new.

      Some people see it and get it; some see it, think it’s neat but are not quite sure and resort back to old ways; and some see it and don’t want it.

  7. Matt…
    Great post and as your post and others have echo’d getting access to the data is a challenge. I’ve finally got access to day old data and will move to 15 minute old data soon. Your comments about IT pro’s and queries is exactly the brick wall I was running into.
    I came from the big SAP BI tools and I’m blown away daily at what can be accomplished. It used to drive me crazy how long I would have to wait for the BI expert to get a report 50% correct.
    The crazy phenomenon for me is the lack of understanding of just how powerful PowerPivot is. I get the sense when I speak about it, that because Microsoft offers it for free that it cannot be powerful.

    1. Hey Mike, sounds like you have made some great progress. Sounds like the next step for you is to dazzle the rest of the business with how productive and effective you are using these tools. We know Power Pivot is a revolution in slow motion. There is nothing like someone running out way in front of everyone else to attract attention and get the tongues wagging. Good Luck.

    2. Actually PQ is not free – you must have either an o365 subscription, Office Professional Edition or Excel standalone. Whichever way you go, you will pay more than the minimum to get Excel or Office.

      1. I have a non-O365 version of Office 2010 and have installed the free PQ download and it works fine. I only have the O365 version of Office 2013 so I can’t comment if the PQ download works with the non-O365 version of Office or not, however my expectation is that it will work. Mike where did you find out it only works with the versions you mentioned?

        1. Apologies for the confusion – I meant PP not PQ. I realize you can also get the old version of PP to run on the old version of Excel, but personally I wouldn’t recommend either. I was more trying to help Mike H convince others of the value of PP.

Leave a Comment or Question