skip to Main Content

power pivot to power bi

A Power BI Technique Mined from the Power Pivot Archives

Below, you will find one of our all-time favorite Power BI techniques. In the ten (yes ten) years we’ve been operating this site, we’ve written over 1,000 articles on Power BI.  Hard to imagine - even for us - but true.

Years ago, we first wrote up this technique in the context of Power Pivot – Power BI’s cousin and predecessor, which we like to call “Power BI in Excel.” 

Since the two products share the same brains (DAX and M), this technique is more relevant today than ever. Everything below is 100% legit for Power BI – the same data model “shape,” the same DAX, etc. – but now you can use it to power up your Power BI visuals, and not just PivotTables.  Enjoy! Smile

A few of us are having a really interesting discussion on Twitter right now that I thought could use a different forum.

“PowerPivot – bad for BI developers and consultants because it removes some need for their services, or good because it opens more doors?”

Here are some of the posts, I mean, tweets:

  • Vidasmatelis_normal VidasM: Preparing key points to talk at #sqlpass FoB lunch.Topic #PowerPivot impact on DW projects.More/less work for us?I welcome any suggestions.
  • Rd_headshot_cropped2_normal andrewbrust: @VidasM I think #PowerPivot will give rise to projects that would otherwise never have happened.
  • Rd_headshot_cropped2_normal andrewbrust: @VidasM And if a published #PowerPivot solution becomes popular, then it’s a candiadte for a more conventional implementation
  • `wp_q52cf6_posts`.`post_title` powerpivotpro: @VidasM I think more work for SSAS dev. In my exp, iterating on reqs =90% of project.Now u’ll do many short “upsize” conversions from PPwkbk
  • `wp_q52cf6_posts`.`post_title` powerpivotpro: @andrewbrust Agree with all your pts. #powerpivot good 4 BI pro. If BI were less crucial, 1 could squeeze other. But crucial feeds crucial:)
  • 110483183_4_yu59_normal Kjonge: @VidasM my idea is analysts creating adhoc and reports for single users with #powerpivot but enterprise reports run on SSAS and SSRS
  • Vidasmatelis_normal VidasM: @powerpivotpro , @andrewbrust – But – I can count projects where #PowerPivot would have been “Good enough”. So I can quantify my loss.
  • Vidasmatelis_normal VidasM: @powerpivotpro , @andrewbrust – But I just have to guess how much I’ll gain (maybe). That is why I worry. And again-I just love #PowerPivot
  • Vidasmatelis_normal VidasM: I also see risk – companies building “Data dumps” instead of proper data warehouse, as it is faster and #PowerPivot can be used for reports
  • 110483183_4_yu59_normal Kjonge: @VidasM i still see some #powerpivot issues like security that will prevent it from being used as real enterprise wide reporting
  • Would love this conversation to continue, both the four of us above, as well as others.  Please post your thoughts as comments!

    Rob Collie

    Rob Collie

    One of the original engineering leaders behind Power BI and Power Pivot during his 14-year career at Microsoft, Rob Collie founded a consulting company in 2013 that is 100% devoted to “the new way forward” made possible by Power BI and its related technologies. Since 2013, PowerPivotPro has rapidly grown to become the leading firm in the industry, pioneering an agile, results-first methodology never before seen in the Business Intelligence space. A sought-after public speaker and author of the #1-selling Power BI book, Rob and his team would like to help you revolutionize your business and your career.

    This Post Has 27 Comments
    1. OK, that is great. On twitter 140 character quite often is not enough to express what you think.

      So – clearly PowerPivot will change how BI Projects will run. Let’s try to guess impact:

      Small companies – they will start doing BI. But they never had any money to do BI before, so I don’t see impact from this on SSAS developers. Maybe we will be able to get occasioanal small contract from them?

      Medium companies – they will do more BI. They will try to use PowerPivot as much as possible. They will build “Data dumps” with just data extracts from different systems and let users to build their reports. Impact – I am guessing less work for SSAS developers.

      Big companies/enterpriese – they will do proper PowerPivot and sharepoint setup and I agree that at the very end there could be more opportunities for SSAS developers to migrate “popular” powerpivot projects to SSAS.

      Net effect – I am not sure still.

      But that is just mine opinion.

    2. OK, here’s my > 140 char followup as well. At one time I would have agreed with you, that this looked scary.

      Few years back, I was meeting with a vendor or consultant (I forget who). A solid MS partner. And I was feeling a little guilty that whatever it was we were building, it was basically moving into this partner’s market in a big way. I mentioned that in passing.

      The partner didn’t hesitate. He went to the whiteboard and drew a circle, the inside of which represented the part of the market that MS “owned.” Around the circle’s circumference, he indicated that this was where partners “attached,” and did their work.

      Then he enlarged the circle. Yes, that swallowed some things that the partners were doing. But the circumference got bigger, too. He was quite adamant that no matter what MS did, it would always be expanding partner opportunities.

      Since then I’ve tested his theory multiple times as I evaluated new situations. Every time, he has proven to be right. And I’ve changed my view forever.

      My advice to all BI pros pondering this same question: don’t play wait-and-see. Start developing new theories now – more about the opportunities that it creates, and less about what might shrink. If you think something is going away, and you’re wrong, well, you still know how to do that thing, and thus you’re still well-positioned, so why spend cycles pondering it now? The new opportunities – that’s where you insulate yourself from reduction in certain segments of your business, and also where you can even grow your business.

      So develop those theories on new opportunities. Start testing them out with customers. Some will resonate, some won’t. That’s fine. The ones that stick will be fun and profitable.

    3. Another question for you Vidas. You mention “data dumps” reducing the need for proper data warehouses. Do you mean data feeds? Or text files? Both?

      And I wonder – how hard will it be to maintain data quality if that’s the direction customers go? PowerPivot does still depend, to a large extent, on being fed clean data. So if DW efforts start to slack in favor of feeds, how do you see data quality being impacted, and how will that impact PowerPivot solutions?

      Bear in mind that I don’t have much DW experience at all. So for instance if 90% of DW work is spent on planning and implementing data quality, then I predict a seismic shift to data dumps won’t happen, or if it does, customers will quickly realize their mistake. If 10% is spend on data quality, then maybe you are right.


    4. Someone told me that in Chinese the word “Change” is written the same way as “Opportunity”

      Anyway – PowerPivot = big change. And smart people know have to find the big opportunities in times of big changes.

      Who would be the ones to lead enterprises through the deployment of PowerPivot? Who will be the ones to develop the best practices, the added-value IT tools, the migration processes from Self-Service BI apps to Corporate apps?


      You. If you are smart.

      Nuff said.

      1. BTW – I am not sure about the chinese words for “Change” and “Opportunity”.

        I might be wrong. I might be making it up. If it is not so, then they better change the Chinese language then have me admit an error.

    5. My one real concern here is tha the PowerPivot client has no developer API. That disappoints me, not just because I come from a dev background and like everything to be programmable (which I do) but also because I see potential partner opportunities to add value on top of PP, but can’t do that (client-side) without the API.

      I hope the next release of the PP client is part of Excel (natively, and not as an add-in) so that we can use VSTO to build kick-ass value-added PP solutions on the client and our current SSAS skills to add value on the server.

      1. Hi Andrew, i agree with you, i would like to build my own applications on the PowerPivot VertiPaq engine (think a custom ASP .Net PowerPivot ..) .

        But i understand from a MS point of view they want to sell Excel licences.

    6. Patience guys. The API will be there.

      We are just crunched on time to ship V1.0 of PowerPivot (remember – just 18 months from SQL 2008). But we have the full intention of exposing the API in full.

    7. Hi, In no way I am afraid of this change. I am just trying to better understand what opportunities I’ll have and how this will change my projects.

      Current Microsoft message about PowerPivot is:
      – PowerPivot is for Excel power users.
      – PowerPivot will create more projects for SSAS developers.

      I believe this message would be more accurate:
      – PowerPivot is for BI Developers and for Excel power users. At the very least BI Developers will build Proof of Concept projects, most likely also final BI applications
      – BI developers who work with SSAS and PowerPivot most likely will have more work

      About data dumps – that is just a term of relational database where you “dump” data from different sources. Building DW databases is expensive project. So companies will extract data from different sources and put them in some relational database and will allow users to use that as source. There will be no surrogate keys, no changing dimensions, no dimension tables/fact tables, etc. But with PowerPivot users will be able to do some cleansing and still build reports. That is just my prediction, nobody knows what will hapen for sure :-).

    8. […] to the great discussion some of us had on Twitter and the follow up on the PowerPivotPro blog we now know a PowerPivot API will be available in the […]

    9. Ok here’s how i see the BI Pros work in the following years
      – Sell Enterprise BI with SharePoint and PowerPivot
      – Let the Business do ad hoc BI with PowerPivot

      – At the same time build the enterprise DWH with default reports, cubes, PerfPoint to support the primary operation (like sales by salesperson and product in a store) with Single Version of the Truth
      – Support the DWH as source for PowerPivot

      – Discover new business needs by monitoring the PowerPivot applications
      – incorporate this need in the traditional DWH with reports and deploy it enterprise wide

      This is the ultimate “Agile/Scrum” datawarehouse making the iterations very short and costs low (the ROI) because you are sure this is what they want and know where to get the data.

      But what i find hard is how to sell to the business why Single version or enterprise BI has to be done when a report had become much used or very important. I understand we BI Pros understand the need but will the business? Especially when it will cost them money to build or extend the DWH/Cubes/Reports. How do you guys see this?

      1. Ok i think i answered my own question on the drive home. I think we can sell the step from self service to traditional BI like this: traditional BI enables enterprise dashboards, KPI’s and reports (perf point services, SSRS on OLAP) even using role based security (on OLAP)

        1. Kasper, I agree with your observations, as usual. Do you think that just general IT fear about leaving mission-critical apps in power users’ hands will also drive “upsizing” of PowerPivot solutions?

        2. I hope not, and i don’t believe it will. But the mindset has of the IT has to be transformed to let them know they still are in control! They manage the shared powerpivot location, they see what users use ..

    10. I don’t see it as a bad thing if they want to absorb and upsize a fraction of the PowerPivot reports/models. That would be a happy result in my view. I wasn’t clear about that – I don’t mean that they will want to absrob them all.

    11. I think that it will initially bring more attention to msft BI. But shortly thereafter be abandon as a “fad”. Once users start linking to text files, excel files, SSRS reports, and the models start breaking (because of changes), most IT shops will put an end to it. And that point IT will say “let’s do it right” and we will be back to creating data mart’s and well designed cubes.

      Remember the “UDM”? No one even talks about that anymore, but that was supposed to revolutionize the BI space…..

      I think in the short term in will hurt BI consulting firms, (because companies will believe that they can do this themselves with a client tool and wizards) but in the long term it will have no effect.

      1. Naturally, marketing depts get their hands on something and their job is to sell it. No two products will ever have the same impact on the marketplace, but if all you do is listen to the marketing, they both will sound the same in a way, because, hey, both are gonna change the world completely right? 🙂

        I’m not your run of the mill tech enthusiast, I’m not a marketer, and, frankly, I’ve never been much of a Microsoft “patriot.” I can run off a list of MS products that I hate that’s longer than the list that I love (with a whole bunch of products in the “like” bucket). Starting a blog like this is a very unusual step for me.

        So, I’m happy to hear your opinions – I will censor spam and abusive comments, but not comments like yours. If we can keep it friendly and fact-based, I am happy to continue.

        So let’s try this: Excel is not going away. The kinds of problems you mention – users linking to stuff, reports breaking, etc. – is already taking place today. PowerPivot does nothing to *increase* that sort of activity. What PowerPivot does do is take some of that activity and move it to a more controlled environment. The Excel users are incented to adopt it because it provides them a lot of benefit, but in the process they are guided to do things a more reliable, transparent way, that reduces outages.

        And no one is saying PowerPivot replaces traditional BI, or the need for data marts, etc. Imagine that Excel activity today, and an important chunk of it becoming more accurate and reliable. Centralized BI activities very much remain.

        For my writeup of this, check out the What is PowerPivot link in the site header.


    12. Don’t get me wrong. I think PowerPivot will be a very slick tool. But I don’t see it being used “by the masses”. MSFT used to preach “one version of the truth” and “BI for the masses” and it seems that now they are investing a lot of time and money into a tool which only a few people in each company will use and will not provide one version of the truth. Just seems a little confusing in that msft doesn’t have a real robust set of client side tools “for the masses”.

      But I’m sure that PowerPivot will find a niche where departments don’t have IT support and this tool will allow them to “do it themselves”.

    13. PowerPivot works by compressing columns of data that are narrow in their range values. For example millions of customers names can be crompressed very since variation in names relatively small. Gemini will not work quickly for data that is unique for evey item, like a book title. Data warehouses will still be needed, PowerPivot won’t be appropriate for all solutions. The same fundamental considerations will still apply and effect how information is sused like how much data is there, how complicated is data model & calculations, what is the data and what do you want to do with it.

      1. Excellent points Shaun. It is a very good best practice to trim long and unique text fields from PowerPivot when using large record sets.

        I’d also like to point out, though, that PowerPivot is not aimed at being a replacement for Data Warehouses, at all. It very much is meant to work in conjunction with, and be fed by, well-designed data warehouses.

        PowerPivot DOES let you “mash up” DW data with non-DW data, and that’s a core strength of the product. But I don’t think it’s meant to replace DW’s in any way.

    14. In our BI implementation (which uses the complete BI stack sans SharePoint) we are analyzing hundreds of millions–>well into billions of rows of data across various dimensions. We’ll likely move to PDW (Madison) in the next year for scale. We are hopeful that like SQL Server’s scale-out improvements, SSAS will too scale-out to handle much bigger data volumes.

      PowerPivot will likely be a success with the avg corporate worker who never dreamed of working with 100+ million rows of data locally and so quickly. Yet the universe of data is growing so rapidly and so large, that only large engines will be able to handle it. As good as PowerPivot may be, we could never use it to analyze anything more than a small slice of our data.

      RFID and other sensor readings, second or millisecond based analysis, financial transactional data, rich statistical and data mining sets, huge text and other semi-structured data, all require the ability to process large data.

      My prediction is that SSAS will migrate in this direction- to help us make sense, in a rapid fashion, of the information stored within all of this new and expanding data in our world.

    15. I started the movement for Business Intelligence at my last company. The original solution was excel based…I essentially wrote an Oracle dashboard / UI in VBA/VBScript….fun times lol anyways…it paved the way for a more conventional solution (to reaffirm Andrew’s suggestion)

    16. I would add, however, that BI is all about data simplification and accessibility – it seems that as out of the box solutions get increasingly better and cheaper – DBAs will also have to be knowledgeable of BI concepts…on the other hand, most BI people I know are pretty well certified to be a DBAs (and much more than that). At the same time, I think BI professionals will become more pressured to learn more about more advanced techniques that may commonly be associated more so with AI such as unsupervised learning algorithms for anomaly detection and what have you.

    17. PowerPivot is boon for BI reporting folks, but it can become monster when not used in a controlled manner. The purpose of data warehouse is to provide enterprise wide users ‘only one version of the truth’. Imagine various users creating their own spreadmarts, with no proper process to validate the data quality, reporting metrics, information dissemination and the like.

      In a mature organization, data warehouse still should remain the ‘nucleus of reporting’. Power Pivot should be used by ‘power users’ as a client tool to create dashboards, reports and should be deployed to Sharepoint for information sharing. Indiscriminate use of PowerPivot by , roots and branches, will cause confusion, waste resources and time.

    Leave a Reply

    Your email address will not be published. Required fields are marked *