Why Power BI Desktop and Not Excel Power Pivot?

As regular readers of my blog know, I spend a time working with Power BI. Since it was released Friday, July 24, 2015, it started me thinking about all of the people who have been spending time on the previous version which, I affectionately thought of as the 4 Powers in Excel plus web. Well since the Office 365 web version is going away on December 31, 2015, I thought of the many ways you could use the new Power BI with the Excel.

Power BI on the Web is a Full Featured Application

One thing to keep in mind is that the web component of Power BI is very powerful. If you are not interested in mashing data so much as reporting on data from one source, be that SQL Server or Sales Force, select the needed data into the Dataset section of Power BI, and start working on creating visualization reports and pinning sections of those reports to a dashboard. Please note, if you create a visualization on the web there is not currently a way to download it to the desktop, meaning reports created on the web, stay on the web and you cannot download them. There is no reason to use Power BI Desktop at all. That being said, the web version is a tool not the tool, so there are times when you may need to work with either Power BI Desktop or Excel and load those models into Power BI on the web.

When do I want to use Excel for my Data Modeling and Not Power BI?

If you have created a working data mashup in Excel Power Pivot, it makes sense to load that to the web directly and use it as is. It is not possible to bring in an Excel Power Pivot model into Power Pivot Desktop, because it doesn’t read the data in Power Pivot as a data source. If you have spreadsheets containing data it will read those in, but if you have a Power Pivot model which doesn’t use any linked tables, it won’t find the data. However, if the same excel spreadsheet is loaded to the Web Version of Power BI, the data model created will be found. If you have a model created, select Add Data in the Web version to add the model and use it to generate visualizations. Power View does not have all of the modeling types which Power BI now has, so if Tree Maps or Funnel charts should be displayed either web or the desktop version of Power BI will do it. If you need to update your model in Excel, make sure that you store the model within One Drive so that the Power Pivot updates you create will be automatically migrated to the web.

When to use Power BI Desktop?

If you have anything but Excel 2013, use Power BI Desktop. Power BI Desktop provides visualizations PowerBIDesktopvery similar to Power View, which are not available outside of Excel 2013. If you would like to create a new data mashup, start by using Power BI Desktop, because it has some new modeling features which you might want to take advantage of such as Many-to-Many and Cross Filter Direction. It handles role playing dimensions the exact same way that Excel Power Pivot does, meaning you can only have one active relationship at a time. The features you need for data modeling are all their, just moved around a bit, such as using DAX to create columns or measures. Power BI Desktops allows you to create data mashups from multiple data sources as well, allowing data to be modeled from as many sources as the model will tie together.

Favorite Power BI Features

After using the previous version of Power BI, I figured I would list the features I most like in the new version, which are other reasons I would give for switching to it from Excel.

  • Colors – Getting to pick whatever color I want for my charts is wonderful. I was so getting tired of blue.
  • Data Refresh – Getting the automatic Data Refresh to work in Office 365 was complicated. The new application for refreshing Personal Gateway is wonderful as it is so easy to use. The only thing I don’t like about it is the name as I don’t see why creating a connection to a server be Personal?
  • New Visualizations – Options are great and I love having more to select from.
  • Lack of dependency on Excel Versions – It can take a while, especially in large companies to get everyone upgraded to the latest version of Excel. Now that process doesn’t keep good visualizations from happening.
  • Price – At $9.99 a month or free if you don’t mind manually refreshing your data and don’t have much data, the cost decrease helps win the argument to move to it.
  • Separation from SharePoint – the underlying administration on the web version with SharePoint in Office 365 added a layer of complexity which thankfully has been removed.

I have a lot of other thoughts on Power BI, but this is getting pretty long, so I will save my thoughts for another posting. Since it doesn’t cost anything to get started, I highly encourage everyone to start playing around with Power BI and let me know what you think.

 

Yours Always

Ginger Grant

Data aficionado et SQL Raconteur

5 comments on “Why Power BI Desktop and Not Excel Power Pivot?

  1. Amir Netz

    Good analysis Ginger. Thanks for sharing you thoughts!

  2. DungAnh

    i’ve worked a lot with Power BI in Excel, and honnestly, it sucks : i have a simple data source, a little complex star schema, around 30 DAX formulas (simple & complexity) , data refreshing when user open the file..
    => It often cause a bug and then you have to close all excel files on your computer (i use 64 bits – Power BI on 32 bits is “hard”)

    Since i decided to move on by using Power BI Desktop i’m totally satisfy. No more bugs, simple to use ( in Excel we have a lots of menu, tab and options my god), concentrate on “Business Intelligence”, easy to publish, refresh data, sharing …

    1. Ginger Grant

      DungAnh —

      I understand your frustration with Power Pivot in excel as I have had problems with it too. The big reasons I still model with it is I want to be able to migrate to tabular and DAX date calculations. When I can migrate Power BI Desktop to a tabular model and mark a table in Power BI desktop as a date table, I’ll stop using Excel. Until then, I don’t think I can stop using it. I appreciate your perspective though as there may be a lot of people who don’t need these features and will do what you do, and create models in Power BI Desktop, which does appear to be more stable.

      Thanks so much for offering your perspective as I know you speak for a lot of people.

      Regards,

      Ginger

  3. michaelyangcma

    Hi Ginger,

    Thanks for the informative post. I am a new power bi user and wonder will the html links I share with other people be automatically refreshed as well or is that just a snapshot of the report at a given moment?

    Michael

    1. Ginger Grant

      Michael —
      Great Question. In answer to your question, the data in the Power BI report will be updated automatically if your data source is also cloud based, such as an Azure DB. If you have other data sources, you will need to use a gateway and configure the updates.

      Regards,

      Ginger

Leave a Reply