Power View external data refresh (kind of!)

I recently subscribed to Office 365 Pro plus to learn about Power View and the associated Share Point Online benefits of publishing self service BI dashboards.

Dashboard

Power View reports can be accessed via Excel Services on-line which makes for easy publishing of dynamic dashboards.   For a company with SharePoint Enterprise on premise you already have access to Power View, and with SQL Server you have access to the in memory analysis services model.  All you need is Share Point and SQL Server support.

Many companies on the Microsoft BI platform are not always SharePoint Enterprise customers.  These customers can find it difficult to make the business case for Enterprise if they will only use the BI features.

With Share Point on-line can we benefit from the BI publishing features by subscribing to the service?  If this is possible we could side step the problem mentioned above.

I quickly realised that with the current release of Share Point on-line only internal (in the Excel workbook) data models can be updated.  It’s not directly possible to update models that are linked to on premise databases. This is very limiting as the majority of data still resides in on premise databases.  I do wonder how Microsoft think sometimes.

One workaround to this is to download the workbook to the local file system.  Open in the Excel client application, refresh the data and then upload back to the Share Point document library.  Of course with a stack of files this is the last thing you want to be doing.

What if we could automate this?

Using Visual Studio it’s possible to drive Excel with a short piece of code.

When coming up with this solution I quickly hit a wall as I’m no Share Point expert.    I wondered if I connected the document library in Share Point to a drive letter on local explorer then could I side step the complexity of Share Point.

Did it work?  I’m very happy to say yes.

Here’s the complete code I used to write a simple refresh utility :

using System;
using System.IO;
using Microsoft.Office.Interop.Excel;

namespace ExcelRefresh
{
    class Program
    {
        static void Main(string[] args)
        {

           string filePath = args[0];
           Update(filePath);

        }

private static void Update(string excelfile)
        {
            Console.WriteLine("Refresh is starting...");
            Application excel = new Application();

            try
            {

                Workbook wb = excel.Workbooks.Open(excelfile);
                Console.WriteLine("File open.");
                Console.WriteLine("Refresh starting.");
                wb.RefreshAll();
                Console.WriteLine("Refresh complete.");
                Console.WriteLine("Saving the workbook to Office365.");
                wb.Save();
                Console.WriteLine("Operation Complete.");
                wb.Close();
            }

            finally
            {
                excel.Quit();

            }
        }

    }

}

When this app is built you just need to pass in a path to the Excel file you want refreshed.

c:\ExcelRefresh z:\\SalesByModel.xlsx

Notice I escaped the \.   Don’t forget to map the document library to a local drive letter before running.

I must give thanks to Jamie Tarquino who blogged about this here.

Also thanks to Chris Webb & Jen Stirrup for bringing Power View and Share Point Online to my attention.  Seriously, if you ever need top class support for Microsoft BI – you have to look these folks up.  They are prolific speakers/knowledge sharers at the top of their community.

You can find a copy of the app I built here.  It wouldn’t be too much of a stretch to add this to task scheduler that runs according to the times you set.

This process enables you to use the on-line Share Point service which doesn’t require lots of IT investment.  You can then publish self service reports and dashboards to your stakeholders.   One issue with this is that the data model resides in the Excel workbook. To limit this risk you can prevent the workbook from being downloaded by your users simply by changing the document permissions.

If this proves successful you may find you’ve built the support to deploy Share Point on premise, and thus benefit from tighter data integration directly with SQL Server.  If not, what have you risked?  A couple of subscriptions to Office and a bit of your time.

What do you think about this approach?  As I mentioned above, it’s a workaround and not without problems but it may just help you overcome one of the challenges of deploying any BI system and that is getting the business support.

Lee

About Lee Hawthorn

Business Intelligence Analyst & Chartered Management Accountant
This entry was posted in Power View and tagged . Bookmark the permalink.

9 Responses to Power View external data refresh (kind of!)

  1. Chris Webb says:

    Nice post Lee! I’m sure by now you’ll have seen the Pwer BI announcement and the fact that it supports external data refresh; something else I picked up on last week is that if you use Project Online as a data source it is possible to make your report refresh in Sharepoint Online with a bit of hackery: http://office.microsoft.com/en-gb/office365-project-online-help/grant-reporting-access-in-project-online-HA104021109.aspx

  2. I read about Power BI after posting this. It’s very interesting. I’m not surprised they bundled the products like that. It’s not ideal as there will be fragmentation in the office suites. We already see this with Power Pivot. Hopefully we’ll see this eventually sorted.

    When everyone is on Office365 this problem will go away but this will take a good while.

    Reading the ZDnet article (http://www.zdnet.com/microsoft-announces-power-bi-for-office-365-7000017746/) I don’t see anywhere that says a local db/tabular source will refresh a 365 SharePoint Online model. I expect this but do you have an official public source that indicates this? I’m sure your customers have different types of data integration such as cloud to cloud, on premise to cloud etc,. We see Microsoft are behind this hybrid approach to the cloud at the OS level. Let’s hope the hybrid features filter through to Power BI.

    I can see why Microsoft might restrict Share Point Online as they may think the move will eat away at their traditional SharePoint on premise licences. Of course, many customers of 365 will never be SP Enterprise customers so in this sense moving all in to the cloud could be the best way forward for all.

    The Projects Excel refresh is interesting. Does this make you think there is some over hackery to refresh power pivot/data explorer?

  3. Lee Hawthorn says:

    Thanks Chris

    I like the sound of the Data Gateway. Time to get a fatter Internet pipe.

    Lee

  4. Great post! I went through the process of getting started Office365 with PowerBI –> http://www.bloomconsultingbi.com/2013/10/up-and-running-microsoft-powerbi-msbi.html However, after creating the gateway + data source + creating a local Excel file with the OData connection + posting to Documents online, I was not able to refresh the data source in Web Excel. Except if I brought down the latest Excel 2013 version + clicking refresh, then it refreshed correctly. Does the refresh work from the Web Excel 2013 version? Thanks again for your time! JB

  5. Brent Alderton says:

    Any update on this? I am after the same thing whereby the excel spreadsheet that will be used to show the data analysis via Power BI gets its information from other spreadsheets which reside on Sharepoint Online as well. Surely there must be a way for refreshes to be enabled especially where those source spreadsheets also are on Sharepoint.

Leave a Reply