Post by Rob Collie

SalesForce Data Into Power Pivot / Power BI?  YES!

This is what it looks like when Microsoft Does Something Epically Awesome…
…But Dark Clouds Loom – Read on to Learn Why

We LOVE This!

Seriously, we do.  It’s AMAZING.  Multiple of our clients are going to jump all over this.  It’s going to change their culture – AGAIN.

If Power Query can connect to it, that lets you pull SalesForce data directly into Power Pivot.  No more export, save, import/paste/etc.

SalesForce Data Into Power Pivot / Power View? YES!

Power Query Can Import SalesForce Data Directly Into Power Pivot, Which Means You Can Then Visualize in Power View, For Instance

So, the primary point of today’s post is to make you aware of this new capability, and tell you how to get it.

How to Get It

  1. Make sure you have the latest version of Power Query downloaded and installed.
  2. Download and install the additional “connector” for SalesForce:  32-bit here or alternatively 64-bit here.

Optional:  Read the Official MS Blog Post, Watch Their Video

image

Click to Visit the Power BI Team’s Blog

The Problem:  No Auto Refresh Support (Except Power BI Online)

Seriously, Power Query is our favorite thing from Microsoft other than Power Pivot (and Excel) itself.  It’s a game changer.  And this new connectivity to SalesForce solves a massively widespread problem.

But Power Query has an Achilles Heel:  it only supports scheduled autorefresh in Power BI Online.  Microsoft has not released a server version of Power Query that you can install in your “on premises” (or even private cloud) environment.

That’s right:  if you are running your own Power Pivot for SharePoint (or Tabular SSAS) server, workbooks/models that utilize Power Query are a DEAD END for Auto Refresh.

That’s a pretty powerful DIS-incentive against using Power Query.  Most of our clients are either already utilizing AutoRefresh on the server, or planning to do so in the short term, and as a result, we caution them against using Power Query, period.

Which is a TERRIBLE, TERRIBLE SHAME.  Power Query is stinking amazing.  It’s heartbreaking every time we have to recommend against using it.

So, A Poison Pill for Microsoft Revenues?

Power Query Won't Refresh on the Server, Except in Power BI Online

We wonder whether Microsoft has adequately considered another likely consequence of all this.  This might turn out to be an expensive mistake that costs Microsoft a lot of licensing revenue.

Consider that this is how a Power Pivot “infection’’ typically proceeds:

  1. An Excel Pro gets ahold of Power Pivot and builds something amazing.
  2. They show that around the office, and everyone is blown away.
  3. They are then asked to produce MORE amazing stuff.
  4. A few others get in on the act and also start producing amazing insights.
  5. But now there is a sharing problem.  How do I get these awesome insights onto everyone’s desktops and devices?  It’s a very difficult problem – lots of software installs, file size issues, etc.
  6. The organization then realizes that it’s time for a server.  A YouTube for workbooks, if you will.

See where this is headed?

SalesForce Drives PQ Adoption.  PQ Adoption Drives You into a Wall.

This new SalesForce capability is brilliant – it will probably drive a massive uptick in usage of Power Query.

As a result, thousands of organizations will develop portfolios of models/workbooks that depend on Power Query.  They will become ENTRENCHED in Power Query, in other words.

That entrenchment will happen, in most cases, PRIOR to the realization of “a server would be nice.”

And one of the big selling points of that server will be effectively eliminated.  No autorefresh will translate into “no sale.”

You could argue that this will therefore drive Power BI Online adoption, but I don’t buy it.  Power BI Online is either a good fit for your org or it isn’t, and so far we are finding few orgs where it’s a good fit.  (Over time, Power BI Online will hopefully evolve and become a good fit for an ever-increasing percentage of the population).

Blessing in Disguise?

I’m actually hoping that I’m right about this being a big problem for Microsoft.  Runaway Power Query adoption would force MS to actually fill this ridiculous gap, and release a PQ server for on-prem and private cloud deployments.

PQ is just too damn awesome for it to remain pigeonholed like it is today.  The MS beast has many competing priorities, and it’s often hard for those priorities to get the attention they need until there’s a crisis.

I think they may now, in fact, be sowing the seeds of precisely that crisis.

So please, everyone, go install and start using the SalesForce import capability today Smile

For more on Power BI Online and the Power BI Family of products, see What is Power BI?

  Subscribe to PowerPivotPro!
X

Subscribe

Rob Collie

One of the founding engineers behind Power Pivot during his 14-year career at Microsoft, and creator of the world’s first cloud Power Pivot service, Rob is one of the foremost authorities on self-service business intelligence and next-generation spreadsheet technology. 

This Post Has 23 Comments

  1. Not ideal but you may be able to refresh the Excel with Power Query via SSIS. The process would be the business copy the Excel with PQ in a network shared folder. The SSIS scheduled will pick up all the Excel files inside the folder and do the refresh and save. The SSIS can upload the Excel file to SharePoint if necessary. Also refer to http://southbaydba.com/2013/09/10/part-5-power-query-api-refreshing-data-indeed/, there is some other ways. But yes, none of them are ideal and we should have a easy to use server to do the refresh jobs.

  2. Rob, I agree that Power Query is powerful, and that there should be an easy way to use it as a data source for SSAS (SQL Server Analysis Services) Tabular. I have moved several Power Pivots to Tabular, but don’t see my organization paying the Power BI tax for many users.

  3. I feel your pain Rob, and this is something i realised a very long time ago. My feeling is that is a deliberate move from Microsoft to force *cough* guide users to the cloud. Im guessing there is some resistance to baking this functionality into SharePoint internally as it means updates to both SQL Server and SharePoint components and new functionality is generally not released as part of a CU or Service Pack. There have been some exceptions but its generally around version compatibility e.g. SharePoint 2010 supporting the latest Power View or PowerView supporting SSAS mutlidimensional models

  4. I am encountering a limit of 2,000 rows utilizing ‘From Salesforce Reports’. The report I am connecting to in SF has 6,331 rows. I know there is a display limit in SF of 2,000 rows, but is there a workaround to connect Powerquery to the full report of 6,331 rows?

    1. No idea personally, since we don’t use SalesForce here at PowerPivotPro HQ (or at least, not yet).

      But I’ve asked the friendly folks at MS to maybe drop in and answer. Let’s cross our fingers 🙂

        1. Response from Microsoft Power Query Team:

          Thanks for reporting the issue. Unfortunately this is a known limitation of the Salesforce API. Their website has the same limitation like you pointed out. Salesforce need to update their API for us achieve what you want.

          One co-worker suggested you can work around this by rebuilding the report using the Salesforce Objects connector, which would allow Power Query to access all the rows.

  5. There is always an option to Refresh a Power Query Connection on file Open via “Connection Properties” and then Refresh the corresponding Power Pivot Model on fine open via VBA

    When do you want to see the latest Data /Report ? – When you open the file – So whats the point spending money on a “Server” ?

  6. Not sure how our IT is doing it in detail but we have a daily scheduled load of saleforece data into our datawarehouse. PowerPivot takes the data from DWH using the Sharepoint scheduled refresh function. So far this works great for us.

    1. I’ve done this previously. There are quite a few commercial connectors to be able to connect to SF. You can easily use any data integration tool such as SSIS to schedule to pull the data from SF to the data warehouse. However, this is IT solution and business users have very limited control.

      Power Query gives the user full control but you cannot auto refresh without Power BI cloud subscription. I guess a scheduled “ETL Refresh as a Services” as mentioned in my first reply would be able to solve the issue in a ugly way. But it is free if your company already has SSIS invested.

  7. Let’s see, it’s been about 4 months… does anyone know if the Power Query 2,000 record limitation “From SFDC Report” has been solved for other than connecting directly to “SFDC Objects”?

    Or if it is something that will be solved for? If so, is there an ETA?

    Or will the only way be… to just connect directly to the “SFDC Objects” or create a data mart from SFDC raw data?

    Thanks!

  8. Currently i believe there is no workaround. I had to tailor my report in SFDC to reduce the number of rows. This is very very annoying as any big company could easily go over the 2K row limit…

  9. I installed the latest Power Query add in and wanted to also install the Salesforce Extension, but link no longer works. Did they take away this preview installation? Did they move it into Power Bi instead?

      1. Thanks Tim. There was another update ( PowerQuery_2.23.4035.242 (64-bit) [en-us] ) – but still no SFDC extension.
        Initially there was a separate install file for it, but they took it down. ( Salesforce_Extension_Preview_For_PowerQuery_2_16_3822_242 (64-bit) [en-us]_msi (1).47dz4p6 ).
        Do you have Office ProPlus? My Excel Version is V15.0.4719.1002

  10. Is there a way to use this amazing tool if my company has a SSO (single sign-on) in place? We have a private domain but it doesn’t work for this tool. It just logs in in a separate webpage and that is useless.

    Please help!

        1. Hi there,
          Sorry for confusion. Yes, I meant Power Query. It looks impressive in the video but I cannot find an alternative or fix-up for my problem with SSO feature.

        2. I get “Power Update” and “Power Query” mixed up all the time. Too many “Power” names to keep track of 🙂

          My comment on “Trouble Looms Ahead” was specific to the fact that you couldn’t auto-refresh Power Query files at the time this article was written. You now can with Power Update.

          As for you SSO problem, I personally haven’t encountered it, I’m curious and have some questions:
          1. What data source are you trying to connect to?
          2. What option are you choosing from the Power Query menu?
          3. Are you using Active Directory SSO?

          1. 1. Well, I am trying to use Salesforce connection.
            2. Since my company uses SSO the only way I see it to connect is to use the private domain we have. However, this is where either I or Power Query fails because eventually, it’s just opening my Salesforce in a separate webpage. Without any option to actually validate my credentials.
            3. I am not sure about this. Sorry, I am new at this. 🙂

          2. Ah, I see. Have you seen this post yet?

            https://social.technet.microsoft.com/Forums/en-US/a4265fa1-2f01-4c4f-b4ce-1e21b9862109/unable-to-connect-power-query-to-salesforce-through-custom-domain?forum=powerquery

            Check the last comment which I think might be what you’re looking for. If not, just reply to that discussion, letting them know that you have the same issue. Then you’ll get a notification when there’s an update, just like when you comment on this blog.

Leave a Comment or Question