Building a Simple BI Solution in Excel 2013, Part 2

In part 1 of this series I showed how to build a BI dashboard with UK weather data in Excel 2013. What I’m going to do now is show, very quickly, how this dashboard can be shared with other people via a web browser.

Before I do that, though, an aside: I found a bug in the preview version of Excel 2013 which means that the dashboard I built in the previous post won’t work with what I’m going to describe here. It’s related to how I imported the data – in my last post I imported data from the Azure DataMarket from the Data tab, and what I’ve had to do for this post is import data from inside the PowerPivot window instead. In a way it’s a good thing I did this because the UI for importing data from the Azure DataMarket in PowerPivot is much better than what I showed before (although it too is quite buggy at the moment). No more fumbling around for account keys and OData queries in the way that Rob Collie complains about here, it’s all handled from within the wizard. All you need to do is pick a data set:


And then check the tables you want and optionally apply filters to certain columns that support it:


Anyway, with my workbook rebuilt, the next thing I’m going to do is save it. There’s actually a lot more to saving in Office 2013 than you might expect, and certainly lots of different options for places to save stuff too, so I recommend you read this post to get a feeling of what’s possible. I’ve signed up for the Office 365 Professional Plus Preview which means I have access to all kinds of cloud-based services including Sharepoint in the cloud, so I can save my workbook up to Sharepoint:


I can then go to my Sharepoint site on another computer and view and interact with my dashboard using Office Web Apps in the browser:



Yes, that’s my Power View dashboard in a web browser – and I can change filters and it all works! Here’s my PivotTable:


I can not only drill down, but I can even change what’s on rows and columns by dragging and dropping fields in the Field list in the usual way. It’s a bit slow and (again) a bit buggy at the moment, but by RTM I can see this being a big selling point: anyone with Office 365 will have in place everything they need not only for BI on the desktop, but for sharing their reports over the web (though data refresh may be a problem here). I’m very impressed, and it’s good to see such a strong answer to the question I asked here last year.

Finally, the question I know you’re all dying to ask. Does this work on an iPad? Well, yes and no. The PivotTable works properly and there’s no loss of interactivity, although I’d say that the touch interface doesn’t work all that smoothly yet:


Unfortunately the Power View sheet doesn’t work at all – no Silverlight!


Oh well, maybe that was too much to ask for… this is clearly not the Microsoft mobile BI solution that was promised at PASS last year. That’s not to say it isn’t useful though – there’s still some good stuff you can do with PivotTables and regular Excel sheets. I’m still very happy with what’s been delivered so far!

22 responses

  1. Pingback: Building a Simple BI Solution in Excel 2013, Part 1 « Chris Webb's BI Blog

  2. Pingback: Building a Simple BI Solution in Excel 2013 (Part 1 & 2) « Another Word For It

  3. Pingback: Excel 2013 Business Intelligence New Features –Deep Dive – I « Rui Quintino Blog

  4. Pingback: [Office 2013] New Office aka Office 2013 has been unveiled by Microsoft « Raphael On Microsoft BI

  5. Really good article Chris. I wondered if you have ever encountered the same error I’m getting at the moment when trying to browse an Excel Power View tab after uploading to a SharePoint Online (Office 365 Enterprise preview) document library:

    “External Data Refresh Failed. We were unable to refresh one or more data connections in this workbook. The following connections failed to refresh. ThisWorkbookDataModel.”

  6. Pingback: Robert Frost and Pie Charts |

  7. Hello Chris thanks for the post, I have some questions. After building powerview reports using Excel2013 can we save those reports on the local machine rather than Exporting to SharePoint, If so what are the different formats available for Exporting the PowerView report OR should SharePoint has to be in the picture when ever we refer to PowerPivew in oneway ot other.

    • Hi Kumar,

      I’m a bit confused about what you’re asking here. When you build Power View reports in Excel they are meant to be consumed and viewed inside Excel; they’re a new type of worksheet in Excel. I don’t even think it’s possible to export to Sharepoint, and certainly Sharepoint doesn’t need to be in the picture at all.


      • Thanks for the reply Chris, Actually we are looking for the an Self Service BI Products so my company is considering Power View as an potential product but at present we need to have SharePoint 2010 in order use Power View. So what I am hearing from you is that, if the users are satisfied with the Excel versions of the reports we don’t need to purchase SharePoint 2010 or 2013.

  8. Thanks for the Info Chris, I am really sorry if I am confusing with the following questions. Does PowerView on Excel 2013 uses only specific Datamodels, the reason I am asking this question is when I tried to connect to an existing cube which is of SSAS 2008 STD (edition and version), by using PowerPivot I can slice and dice and by the way I have to admit PowerPivot is awesome in Excel2013 and when I tried to launch an Power View report, I am getting a new work sheeet with no data in it. On the Other hand when directly connect to the same cube and as the Excel is providing me which Template I can choose, I clicked on open with Powerview it throws the following error

    Sorry, Power View couldn’t load the model or data source “CUBENAME” because the data source type is not supported. Please try connecting to a supported data model.

    Where as when I connected with an existing connection from SSAS Tabular cube everything works great.

    Correct me if I am wrong, We cannot build an PowerView report off from SSAS Multi Dimensional Cube or If it is possible does it have a restriction on which edition and version of SQLServer we are using Or is it a bug because PowerPivot is an Symantic layer where we can combine multiple data sources and PowerView should not care about the sources as longas the data and relationships are right with the existing data on the workbook.

    • At the moment Power View in Excel and Sharepoint only works with Tabular models or PowerPivot, because it uses DAX queries in the background. At some point in the future SSAS Multidimensional will also support DAX queries and then Power View will work with it, but there’s no official release date for this at the moment.

  9. Hi Chris
    Thanks for sharing this very useful first look at BI in Excel 2013.
    I really rate your contributions.

    Could you answer this question, please –
    In Excel 2013 the need to explicitly define certain Measures looks to have gone away, which makes working with OLAP Formulas a heck of a lot easier.
    But the ability to rename the PowerPivot Data Connection seems to have gone away also, and all CUBE functions now seem to require the ubiquitous “thisworkbookdatamodel”. This is a tad frustrating!
    Have you been able to bypass this or substitute a shorter more specific name for the connection?

    Is this name established in some config.file somewhere or other metadata that can be edited?

    Thanks, Chris

  10. Sorry, Chris, I meant to add ..

    Does the use of “thisworkbookdatamodel” suggest that it is now possible to point to other workbooks data models, and if so how?

    I could imagine situations where it would be useful to use Workbooks to load one or more In Memory Databases then to refer to those databases from other Workbooks.

    Thanks, Chris

    • Hi Dave,

      I haven’t been able to rename the connection, sorry – I’ll ask if it’s possible though. I’ll also ask about connecting to other workbooks’ data models but I’m pretty sure that’s not going to be possible.


  11. Thanks for checking this out, Chris. It’s a bit naff that the connection can’t be renamed, but at least we now know!

  12. Pingback: Office 2013, Office 365 Editions and BI Features « Chris Webb's BI Blog

  13. Pingback: Das neue Excel 2013 | Teil 3 Power View | Linearis Blog

  14. Pingback: Das neue Excel 2013 | Teil 3 Power View | Linearis :: BI für die Fachabteilung

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: