Last updated on March 9, 2021
Excel is an excellent tool for analyzing data. An analyst can easily connect to and import data, perform analyses, and achieve results quickly. Export to Excel is still one of the most used features of any Business Intelligence tool on the market. The demand for “self-service BI” resulted in a lot of imported data being stored in overly large Excel files. This posed several problems. IT administrators had to deal with storage requirements. Analysts were restricted by the amount of data they could work with, and the proliferation of these “spreadmarts” storing potentially sensitive data created a governance nightmare.
A little history
Power Pivot was created to provide a self-service BI tool that solved these problems. Initially released as an add-in for Excel 2010, it contained a new analytical engine that would soon be introduced to SQL Server Analysis Services as well. Its columnar compression meant that millions of rows of data could be analyzed in Excel and would not require massive amounts of space to store. Data in Power Pivot is read-only and refreshable – ensuring integrity. It allowed analysts to set up their own analytical data sets and analyze them using a familiar looking language (DAX), and visual reporting canvas (PowerView) all from within Excel.
The original version of Power BI brought PowerPivot to Office 365 through Excel before Power BI’s relaunch gave it its own consumption interface (the service) and design client (Power BI Desktop). Both the PowerPivot engine, and Power Query were incorporated into the service and Power BI Desktop, while the Silverlight based Power View was replaced with a more web friendly reporting canvas.
Excel support
Throughout all these changes, Excel has continued to be well supported in the Power BI service. Analyze in Excel allows an analyst to connect to a deployed Power BI dataset (built with Power BI Desktop) and analyze it using pivot tables, charts, etc. Recent “connect to dataset” features have made this even simpler. Organizational Data Types allow Excel data to be decorated with related data in Power BI.
Excel workbooks containing Power Pivot models have always been supported by the service. These models can even be refreshed on a regular basis. If the source data resides on premises, it can even be refreshed through the on-premises data gateway. This all because the data engine in Power BI is essentially Power Pivot.
It’s that word “essentially” that causes a problem.
Datasets that are created and stored within Excel workbooks are functional but can only be accessed by that workbook. Contrast this with a dataset created by Power BI Desktop, which can be accessed by other interactive (pbix) reports, paginated reports, and as mentioned above, by Excel itself. The XMLA endpoint also allows these reports to be accessed by a myriad of third part products. None of this is true for datasets created and stored in Excel.
So why would anyone continue to create models in Excel. The reason has been until now that although Excel can connect to Power BI datasets to perform analysis, those connected workbooks would not be updated when the source dataset changes. This meant that those analysts that really care about Excel needed to work with the Excel created models. This changed recently with an announcement at Microsoft Ignite Spring 2021. In the session Drive a data Culture with Power BI: Vision, Strategy and Roadmap it was announced that very soon, Excel files connected to Power BI datasets will be automatically updated. This removes the last technical reason to continue to use Power Pivot in Excel.
Tooling
Building a dataset with Power BI Desktop is fundamentally the same as building one with Excel. The two core languages and engines (M with Power Query, and DAX with Power Pivot) are equivalent between the two products. The only difference is that the engine versions found in Excel tend to lag those found in Power BI Desktop and the Power BI service itself. I’d argue that the interfaces for performing these transforms, and building the models are far superior in Power BI Desktop. not to mention the third-party add-in capability.
In this “new world” of Excel data analysis, Datasets will be created by using Power BI Desktop, deployed to the service, and then Excel will connect to them to provide deep analysis. These workbooks can then be published to the Power BI service alongside and other interactive or paginated reports for use by analysts. With this new capability, Excel truly resumes its place as a full-fledged first-class citizen in the Power BI space.
What to use when
With this change, the decision of what tool to use can be based completely on its suitability to task, and not on technical limitations. There are distinct types of reports, and different sorts of users. The choice of what to use when can now be based completely on these factors. The common element among them all is the dataset.
With respect to report usage, typical usage can be seen below.
| Tool | Used by | Purpose |
|---|---|---|
| Power BI Service | Report consumers | Consuming all types of reports: interactive, paginated and Excel |
| Excel Online | Report consumers | Consuming Excel reports from SharePoint, Teams, or the Power BI service |
| Power BI Desktop | Model builders Interactive report designers | Building Power BI dataset Building interactive reports |
| Power BI Report Builder | Paginated report designers | Building paginated reports |
| Excel | Analysts | Building Excel reports Analyzing Power BI datasets |
Making the move
Moving away from Power Pivot won’t require any new services or infrastructure, and existing reports and models don’t need to be converted. They will continue to work and be supported for the foreseeable future. Microsoft has neither said not indicated that Power Pivot in Excel is going anywhere. However, by building your new datasets in Power BI Desktop, you will be better positioned moving forward.
If you do want to migrate some or all your existing Excel based Power Pivot datasets, it’s a simple matter of importing the Excel file into Power BI Desktop. This is completely different than connecting to an Excel file as a data source. From the File menu in Power BI Desktop, select Import, then select Power Query, Power Pivot, Power View. You will then select the Excel file that contains your dataset.
Power BI will then import all your Power Query queries, your Power Pivot dataset, and if you have any it will convert PowerView reports to the Power BI report types. The new report can then replace your existing Excel file. Once deployed to the Power BI service, other Excel files can connect to it if so desired.
Building your datasets with Power BI Desktop allows you to take advantage of a rich set of services, across a broad range of products, including Excel. Building them in Excel locks you into an Excel only scenario. If you already use Power BI, then there’s really no reason to continue to build Power Pivot datasets in Excel.
Totally agree John. I would add one more difference between PP and PBI. Not only does PP lag in futures, it is relatively buggy when compared to Desktop. Despite a lot of focus to remediate the issues, there are still bugs that remain. I don’t see this changing and I don’t see much future for PP 🔮
Well, Excel does neither rely on (often to be paid) 3rd-party tools nor does it depend on the (slow) cloud.
It has always been able to connect to OLAP cubes, but also offers programmatic access (VBA) to both, Power Pivot and Power Query, enabling deep integration into the other Office products. The ability to create back-linked tables is just another advantage.
As soon as the one-time purchase of Office products ends Power Pivot can and should be updated. Power BI these days – as a superset of Analysis Services – does not fit into every office anymore. Power Pivot is the true self-service product. Microsoft should stop neglecting it.
I think Frank makes a good point.
Power BI is a great tool, and I use it personally a lot. But I as a person generally don’t have access to a Power BI Service (in my case I do but that’s not the point).
It is likely the same case for many SMEs out there: No need for Power BI Service, but very occasional model creation and use can be useful.
Or perhaps they’re risk-averse and want to avoid having data in a cloud-based service, but a few people need to collaborate on a single model / file strictly within the company intranet.
For an enterprise, this article is definitely relevant. Yet Power BI’s relative dependance on the (cost-tolerable) cloud as opposed to Excel / Power Pivot’s independence makes the trade-off in a fair amount of cases not obvious.
Thanks fo your view. And after I read this article I have a question. Now when I can analyze data by Excel from Power BI Dataset, Why (maybe When) use SSAS (Tabular) instead of Power BI Dataset ?
I’m having a hard time uploading large Excel files into Power BI. Are there settings I can adjust?
John, I’m curious if/how the situation has changed with the advent of Fabric (or other changes to Excel and/or Power BI since 2021).