Level: Beginners
One of my regular blog readers (and graduate of my live training course) Phil Cross asked me last week if I knew of anywhere to find a comprehensive guide for Self Service BI users of when to use Excel vs Power BI to author new data models. I thought this would make a good blog post, so here goes.
Power Pivot Data Model Authoring Options
There are 4 broad options you have when deciding to build a new data model using the Power technologies from Microsoft. These are:
- Power Pivot for Excel
- Power BI Desktop, stand alone with no intention to publish to PowerBI.com
- Power BI Desktop to author, then publish to PowerBI.com
- SQL Server Analysis Services Tabular
There are other options including direct query to a SQL DB however I am not covering those other options here. I am not going to cover SSAS tabular here either as this is an article about the self service options. The rest of this article outlines the reasons you may choose one of the first 3 options over the other 2. The reasons are not mutually exclusive and hence there can be multiple options to solve specific business requirements.
Reasons You May Want to Use Power Pivot for Excel
- You are not willing to put your data into the cloud, for whatever reason.
- Your IT department has locked you out of PowerBI.com and you can’t convince them to let you use it.
- people that you need to share with don’t have access to the data in the cloud.
- You need standalone Excel reports that contain the data model. Eg “Analyze in Excel” is not an option for whatever reason.
- You already own SharePoint Enterprise Edition and want to use that as your report distribution server (read my article here about that).
Note: There are lots of other reasons you may want to use Excel over Power BI, however for these other reasons it is most likely possible to keep the all the benefits by authoring your data model in Power BI, publishing it to the Power BI Service and then use “Analyze in Excel” to build your reports in Excel. As a result all of these next reasons are not really reasons at all (in my view), because you can have your cake and eat it too.
- You love Excel and just prefer to use it.
- You need the fully featured and granular charting capabilities of Excel that currently cannot be achieved in Power BI.
- You have some “non-data model” calculations that form part of your reports that are best handled in Excel.
- You need to use the cellular capabilities of Excel to do calculation so that can’t be done in the model for what ever reason
- you want to leverage the cellular layout of Excel to produce grids of data (not handled that well in Power BI), particularly for multi page lists.
Note: this Analyze in Excel approach will only work if everyone that needs to interact with the spreadsheet has access to the data on Power BI
Some Reasons The Excel Approach May Not Work for You
- Power Pivot for Excel is still unstable at times and that can be frustrating.
- Maybe you don’t have a version of Excel with Power Pivot.
- Maybe you only have 32 bit Office and this can’t handle your data models, plus you can’t get 64 bit.
- Distributing large Excel workbooks (eg several hundred MBs in size) is an issue.
Reasons You May Want to use Power BI Desktop (no Publish to the Cloud)
Power BI Desktop is a free, standalone tool that can be downloaded and used by anyone regardless if they use PowerBI.com. It therefore follows that anyone can use Power BI Desktop as an Excel replacement using the “old world” distribution methods traditionally used for Excel (eg email, distribute via file server, SharePoint etc). Reasons you may want to use this approach include:
- Better, more stable and latest version of Power Pivot and Power Query technologies
- Better UI
- Bi-directional cross filtering
- Leverages the latest visualisation technology delivered as part of Power BI
- You don’t have a version of Excel that includes Power Pivot.
- It’s free.
- You only have 32 bit Office but need 64 bit data models.
Some Reasons This Approach May Not Work for You
- Every person that wants to consume the reports this way must download and install Power BI Desktop.
- Some users may be scared away by the Power BI Desktop user interface (it is a bit more complex than PowerBI.com).
- If the workbooks are large (as data models can be) then pushing these large files around to users may be an issue.
Reasons You May Want to Use Power BI + Cloud
Some of the benefits come with the free version and some only come with the Professional version.
Benefits With the Free Version
- Better, more stable and latest version of Power Pivot and Power Query technologies
- Leverages the latest visualisation technology delivered as part of Power BI
- A great range of modern visualisations
- Open source framework (anyone can develop and share new visuals)
- HTML5 responsive design (works on any browser and mobile devices)
- Mobile app integration (it works on cross platform mobiles ‘out of the box’)
- It’s free.
- You only have 32 bit Office but need 64 bit data models.
- You want to share via the Cloud
- You want to leverage Dashboards to
- Consolidate data from different sources to a single screen
- leverage Q&A for natural language queries
- You want to leverage Quick Insights machine learning analytics.
- You want to create reports and visuals using Excel and the Power BI “Analyze in Excel” is a viable approach to connect to the data.
- This approach is very powerful. You can build and maintain a single data model in Power BI Desktop, publish it to PowerBI.com and then build as many “thin” Excel workbooks as you like using “Analyze in Excel”. These new thin workbooks have the additional benefits of being very small (ie thin) and also only users that have approved access to PowerBI.com can refresh the data (note that any data previously refreshed will be visible to anyone). There is no need to have a copy of the data model inside each Excel Workbook, you can simply connect to the cloud.
- and then there is the local PC version of what I describe above. I’m not sure this is scalable, but it certainly works. Read about that’s here.
Additional Benefits in the Power BI Pro Version
In addition to the benefits that come with the free version are the following pro version benefits.
- Group Workspaces and content packs for granular sharing and control of access
- Row Level Security
- Automatic refresh against the data sources using a Gateway
- Email subscriptions
Some Reasons This Approach May Not Work for You
- You don’t want to pay. Note, you can make the product affordable by getting the right balance of free and Pro users in your company.
- You don’t want to put your data in the cloud.
- IT wont let you install Power BI Desktop
- IT won’t let you use PowerBI.com
- You don’t like learning new software and just want to stick with what you know best.
Final Considerations
The last important point is that you can migrate an Excel Data Model to Power BI, but you cannot migrate a Power BI Data Model to Excel. So if in doubt, do it in Excel first. You may suffer pain with the modelling but at least you can migrate to Power BI Desktop if you change your mind.
Hello there,
I have a question about resolution issue I’m having when working with Power BI.
I have my laptop connected to network and so are additional 2 monitors.
Power BI report on my laptop looks fine, but as soon as I drag it to monitor (I have two of them also connected) then the image becomes blurry. Can anyone please advise what could be the reason and whether I need to adjust the image quality so that it can be clearly visible on monitor?
Thanks
It’s not you, it’s power BI. This is a very big issue and I haven’t seen any improvement over time. Power BI is very bad re-adjusting to different resolution monitors. I see that most often when changing between one monitor resolution when connected to a desktop and then a different monitor resolution when going mobile.
It seems it was worse since Sept 2019. There is a bug logged here https://community.powerbi.com/t5/Issues/September-2019-Update-Blurry-on-2nd-Monitor-Screen/idi-p/789103
Hi,
An example of the additional technology is when using maps. E.g. In Excel, the cities are not supported. PowerBI connects to Bing so you can bring in city analysis. Excel filled maps do not have that function unless you mess about with the api (do something complicated).
Hi Matt,
I’ve found that when initially working with different data sets from different parts of the organisation when I’m not clear what is what and where there are issues creating the queries in Power Query in excel is good as I can use the worksheet to quickly filter and use formulas to cross check values etc. In Power BI it’s actually quite difficult to quickly navigate around a dataset and look at it ‘in the raw’ as it were. Once I have things mostly ok then I can import the queries into Power BI and go from there.
As a new user I can’t say this is a genuine reason but thought it worth mentioning, perhaps once I understand the toolset better my way of working will change
Very good point Jonathan. Actually if you use Analyze in Excel you can have both. Also there is my local host workbook that allows you to do that locally with power bi desktop running. Also consider learning a bit of dax query, so you can create test summary tables using “new table” in power query.
Hi Matt, what still confuses me is how the synchronizing between the various BI tools is taking place in regard to new features in the DAX engine. It seems that Power BI desktop always is the forerunner regarding this however how (and when) can you make sure that you can use these new features in Power BI service and Excel. It makes planning ahead a little bit difficult.
Do you know how the process and rules regarding this is Matt?
My opinion is that there are no rules here. Sometimes MS seems to release something to the service first, sometimes it seems to come to Desktop first. As a general rule, if it is in preview then you can’t be sure it will work in both places. I know it is not ideal, but I think that is the life of rapidly changing software in the cloud.
Thanks Matt, I appreciate your objectivity !!!
“Note, you can make the product affordable by getting the right balance of free and Pro users in your company”
can you please clarify what do you mean, my understanding you can’t mix PRO and free, since last month Microsoft “fixed” that bug, practically, either you have all free users or all pro users.
maybe we need to have a discussion about the cost, PowerBI is very competitive if you have small numbers of users, but if you have a lot of people just reading the reports, then Excel is the undisputed winner.
I was not aware of any changes that prevent a mix of pro and free users. Do you have a link that references this so I can look I to it?
http://community.powerbi.com/t5/Service/Fixes-to-Power-BI-Pro-License-Required-Dialogs/td-p/113391
Thanks for the link. But this doesn’t say (that I can see) that you must have all pro or all free. So unless I have missed something, it should be possible to load a dataset to myWorkspace and share it with free users as a dashboard.
you are right , what I mean “In practice” you end up with this situation, the fact that you can not share reports from the PRO users to the free users, it means either everyone should be pro or everyone stick with the free, mixing both does not really work. ( I hope to be proven wrong)
let’s say you build a reports and share it with 100 users, now you go into vacation, someone needs to update the reports, he can’t simply use the group workspace and keep updating the same reports because all the users need to be pro, the solution send a new dashboard with a new links to all users and ask them them to not use the previous dashboard.
don’t get me wrong, I love PowerBI, but i think the cost is a big factor when someone consider PowerBI.com specially in the case where Most of the users are just viewers, the fact that group workspace is a pro feature is a big let down.
My views is MS. needs to be paid for the likely hundreds of millions of dollars it has spent on the product. I agree that pricing for large deployments can be prohibitive. A medium sized company with 2,000 staff would have to pay $240,000 per year – hardly cheap. But this is what drives my view about blending pro and free licences. Using the 80:20 rule, 400 users would only cost $48,000 per year and that is very competitive with the likes of Tableau. I doubt the hassle of sharing from an individual account is worth paying $196,000 per year to fix. Another approach is to build Analyze in Excel static reports and then refresh and distribute to static users via a tool like http://xbi.com.au/update
Who knows, maybe there will be an alternative way for limited sharing possibly without interactivity in the future.
So if you have secure data there is no way to publish reports that people can see on their phones? Bummer.
If you want to use the Mobile App, the data must be accessible via PowerBI.com. This can be via a Gateway to your on premise SSAS Tabular server if you like. But I guess it depends how your company feels about that
Here’s the thing I struggle with when it comes to transitioning over to fully from Excel Power Pivot to PBI, and maybe I’m doing something wrong:
When it comes to writing and testing / diagnosing my DAX code – I find Excel PP superior. For instance, I can write DAX, and interact with my data set by filtering to see how it impacts the DAX measure, effectively see the results of my measure immediately upon hitting ENTER. In PBI, I can’t filter down the data set and watch how my measure changes.
Has anyone else had this challenge when it comes to writing DAX? Am I missing something?
I agree. I hate having to go back into the query editor to filter the table to see what happens. I actually don’t do what you are talking about (apply filters and look at the measures). I always do this in a test visual – a Pivot Tsble in Excel or something similar in PowerBI. But I miss not being able to apply filters to inspect he data
This is great, Matt. But there’s another dimension to this whole “best way to get information out” article that I’m still confused about somewhat.
I’m an Excel guy, who’s just started to dabble with PowerPivot and PowerQuery. In the past I’ve dabbled with a bit of SQL via SQL Server Management Server, to bring through the data I want into Excel. But being a purely Excel guy, I’ve never learned much more about SQL Server other than how to write TQSL queries to get the data I need into a PivotTable.
I’ve often heard other MS SQL Server terms such as SSRS, SSAS, and SSIS bandied around by IT types, but they have never really meant anything much to me, and so have always gone over my head. I’m just an Excel guy, after all.
Now I’m starting to think about how PowerBI can help me share data and insights more widely in the team that I’ve just joined. At the same time, someone else in the team is building models in this mysterious SSAS thing, and someone else is looking at how to share insights from these models using this (to me) mysterious SSRS thing. For both of them, this is really the first time for them linking these two mysterious things together.
So I come along talking about this thing called PowerBI that can be used to easily share data and insights. And they say “Hmm…sounds awfully like this SSRS/SSAS/SSIS thing that we’re learning and building as we go. Compared to SSRS/SSAS/SSIS, what exactly *is* PowerBI?”
And I don’t know how to answer that. Consequently, we’re all a little bit confused about the relative strengths and weaknesses of PowerBI vs the SSRS/SSAS/SSRS trinity. Or whether it is a case of ‘vs’ at all. Is there any wisdom you can impart on this?
Yes, this article talks to people that have already discovered Power Pivot and Power BI, and helps them decided which to use for different scenarios. You are part of the “yet to understand why” group. Take a read of this and let me know if this answers some of your questions. If not, then let me know where the gaps are https://exceleratorbi.com.au/who-needs-power-pivot-power-query-and-power-bi-anyway/
Good link. I understand what DAX and model sharing bring to the party. I’m sold on the advantages of using DAX and PQ to supercharge what I already do in Excel/VBA/TSQL. But there’s something about that benefit 5 of the “Who can benefit from PowerBI” part of that link that I’m mulling over. That excellent point says that PowerBi will add value to people that “Can’t get the (timely) support they need from their IT department using traditional Enterprise BI tools.” But in our case, we effectively *are* IT. Or rather, we are “Shadow IT”: My boss previously got soooo frustrated with IT that he went and hired a whole bunch of data geeks (me included) and then purchased us any tool he thought we might need in order to get, model and share data, including things like Data Liberator (to get stuff more easily out of SAP), SQL Server, and (at my prompting) now PowerBI.
We can probably happily build our reports just as easily in the ‘trinity’ or PowerBI if we want to…but because noone in the team knows both products that well, we’re wondering if one will serve us better than the other, or whether say PowerBI will allow us to share data that sits in SSAS better than perhaps SSRS will. We don’t yet know, because we’re not experts in either of these.
Our customers aren’t really people that would be interested in learning about DAX etc…they just want reports that they can paramatise. And so we can probably happily service their needs in either PowerBI or the SQL stack. And we can happily do our data modelling and analysis in either stack too. For some reason I find myself gravitating towards PowerBI, but that might be purely because I see it as less threatening for an Excel guy like me to get to grips with. My colleague looks at the DAX stuff I’m writing and tells me that I’d love SSAS, because to her it looks like SSAS and SSRS in combination do pretty much the same thing as PowerBI do. But again, neither of us is completely over either tool, let alone both of them.
SSAS comes in two flavors, I assume you are referencing SSAS Tabular, which is pretty much the result of the DAX language developed for PowerPivot (I’m not clear on chicken vs egg, but they use the same language). SSRS 2016 will soon have PowerBI rolled into its suite (I believe they are planning to roll it out mid-year 2017, more to come from the June Data Insights summit I’m sure). So long story short, Power BI will come in three flavors, PowerBI Desktop, PowerBI.com and PowerBI via SSRS, at least that is how I understand it. So the good news is, if you have a team of people running with SSMS, SSAS, SSRS and you are wondering where the heck PowerBI fits in, it will soon be a sub-bullet under SSRS and you will be able to create data models and visualizations via the SSRS portal, assuming you are working with SSRS 2016.
I agree with Erich. The data modelling technologies in Power BI, Power Pivot for Excel, SSAS Tabular are the same. But the first 2 are much more accessible because you don’t need a server or server tools and skills
I wrote about the new SSRS preview here https://exceleratorbi.com.au/power-bi-premise-not-cloud/
I’m a little surprised that Power BI Desktop is used as a standalone solution and distributed among peers as such. Is that a common thread among analysts in all their variations?
If it is I begin to understand many of the feature requests and questions on blog sites etc.
I don’t know if it is common or not, but it does reflect a solution to problems some users have (that they can’t control). If they are not allowed to have 64 bit Excel and not allowed to use the cloud, then it is an option.
Arh I see, that’s make sense. Thanks.
In your intro you said you were going to cover the 4 choices but then you only covered 3 of them in your article. You never discussed Analysis Services tabular (on premises or the AMAZING Azure flavor).
In my view the natural progression is from Excel, to power bi desktop and then to Azure Analysis Services (and you can link your data to Powerbi.com and/or excel.) The only true downside is the Power Query functions are not available to load data to your data model ( although it is available in the on premises CTP version currently and so it should make it to the Azure version soon)
I don’t think there is not enough awareness yet of how incredible the Azure tabular option is. Would you please consider doing an in depth article on Azure Analysis Services?
Mmmm, so I did – sorry about that. I never intended to cover SSAS here but I clearly didn’t say that. I have changed it now, but still haven’t covered SSAS. I see Azure and SSAS as an enterprise option and it is not something I have spent any time looking at, hence it’s not something I can cover with any authority (for now anyway)
Matt
I am curious why you did not cover the use of PowerBI desktop as a local SSAS server, it works very well, and as a matter of fact, I used your template for one of reports, and the client was very very impressed.
as one of the users who can not use PowerBI.com , I have my Model in PowerBI desktop and use Excel for reporting, obviously I am very excited about SSAS Vnext, as i can finally have a supported solution.
I considered it, but I felt this was more of a hack than a viable mainstream option. Maybe I will mention it briefly.
I think adoption towards Power BI will increase substantially once Microsoft allows you to publish Power BI Reports on prem without having to use SSRS
Do you mean somehow publish internally without the new preview version of SSRS that supports Power BI! Microsoft is investing heavily in making Power BI available via SSRS, and I don’t see them taking a different approach anytime soon. https://exceleratorbi.com.au/power-bi-premise-not-cloud/
Very timely. I was going to write up a similar item for our internal audience. Do you mind if I use what you put together (giving all due credit of course and pointing to your blog)?
Of course, feel free to distribute widely.
Matt,
My love for Excel seems to run deep but I have heard from different sides that Power BI is the way to go. And your pointing out my personal experience; Power BI has a different Look and Feel then good old Excel. I am going to start on Power BI soon . small off course 😉
I do have one remark/question; when a report has a hierarchy* I seem to be lost in Power BI (missing an overview) while I love Excel at that point. It is easy to zoom in and out while maintaining a overview where I am in the hierarchy. Can you give a hint how to work on that in Power BI?
* I actually work a lot with hierarchies somehow; for GL statements but also for an overview of projects and underlying subprojects where a project is my ‘key dimension’.
The “Pivot table” experience has been lacking in Power BI for sure. Is that what you mean? Have you looked at the new Matrix preview? https://powerpivotpro.com/2017/03/two-great-new-power-bi-features/
Hi Matt thanks for the blog
I wonder if you would agree that another reason (you did not mention) to prefer Excel PP over PBI would be:
If you are more interested in building complicated Grid Reports rather than graphical dashboards, than Excel Power Pivot is superior to PBI?
You are right, I should have added that, and pivot tables for that matter (I will add these). However I will add them in the “not really reasons” as you can do here with Analyze in Excel.
is it possible to use Excel formula (like Index, Array and many other) or automation or formula&automation in Power BI
No, this is not possible. But dax is a very powerful language and perhaps a better question is “can I get the results I need using Power BI”? It is highly likely that what you need can be solved in different ways.
Really helpful blog article Matt.
If you’re a perfectionist, you may want to watch that bullet formatting under “Additional Benefits in the Power BI Pro Version” – formatting fine under “Reasons You May Want to Use Power BI + Cloud”.
My experience is gradually moving through the chain from Power Excel to PBI.
It may not be optimal for development, but has worked well for end users as the transitions were gradual. I gradually moved over the old excel reports to Power Pivot. Users did not have to adopt a new tool and despite the changes under the hood, to them it was just excel, but better. Then moving to PBI was a smaller step for the end users, it just your normal (Power) excel reports, but better. And once everything was in Power Excel is was much easier to obtain funding for PBI as I had working models in desktop.
So, although the end goal might be PBI, if you’re unable to get start there, there are benefits to the smooth pathway up to the summit.
I like your point Graham. The user needs and comfort is very important.
Excellent blog, Matt!
It answers the questions of those contemplating between Excel and Power BI.
Thanks
Ramana
Thanks Matt
Well explained and good understanding.
Thanks for laying out the options/consideration Matt, appreciate it.
I think the final para under Power BI + Cloud seems to be the most important in the article, in that if PowerBI.com is available to you, developing in PowerBI Desktop and publishing allows all the benefits or the three options, especially when utilizing Analyze in Excel (if data table or certain charting is needed).
Thanks again for taking the time to share you thoughts, appreciate it.
Cheers