Chris Webb's BI Blog

Analysis Services, MDX, PowerPivot, DAX and anything BI-related

Building a Simple BI Solution in Excel 2013, Part 2

with 22 comments

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:

image

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

image

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:

image

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:

image

image

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

image

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:

iPad1

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

iPad2

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!

Written by Chris Webb

July 18, 2012 at 4:04 pm

22 Responses

Subscribe to comments with RSS.

  1. [...] Part 2, you’ll see how you can share this solution with your co-workers in the Office Web App. Share [...]

  2. [...] Building a Simple BI Solution in Excel 2013, Part 2 [...]

  3. [...] the meantime I usually count on amazing guys like Chris to keep me posted on the news. :) (amazing posts by the way- go read them if you are into Excel 2013 BI! [...]

  4. [...] I’ll try to cover them all in the next few days … Chris Webb has already published two posts on the first three topics here and here. [...]

  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. [...] follow up posts that provide a solid walk-through of creating a BI solution in Excel 2013 (part 1, part 2) with links to others in the SQL community that have been blogging about Excel 2013 from a [...]

  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.

    Kumar

    August 13, 2012 at 6:42 pm

    • 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.

      Chris

      Chris Webb

      August 13, 2012 at 10:15 pm

      • 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.

        kumar

        August 14, 2012 at 3:34 am

      • Exactly

        Chris Webb

        August 14, 2012 at 3:19 pm

  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

    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.

    Kumar

    August 14, 2012 at 5:27 pm

    • 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.

      Chris Webb

      August 14, 2012 at 9:10 pm

      • Thanks for answering everything Chris, It helps me a lot.

        Kumar

        August 14, 2012 at 9:18 pm

  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

    Dave White

    September 2, 2012 at 9:50 am

  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

    Dave White

    September 2, 2012 at 9:57 am

    • 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.

      Chris

      Chris Webb

      September 6, 2012 at 2:04 pm

      • Hi Dave,

        I’ve now had it confirmed that the connection name can’t be changed. Sorry…

        Chris

        Chris Webb

        September 7, 2012 at 10:13 am

  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!

    Dave White

    September 9, 2012 at 1:35 pm

  12. [...] then uploaded) then this functionality should be sufficient. This is the kind of scenario I showed here, and I think a lot of customers with no existing BI will still be impressed with it; obviously [...]

  13. [...] Power View kann angeblich auch in der Office 2013 Preview-Version von Office 365 über die dortige Web App betrieben werden. Zur Quelle dieser Information … [...]

  14. […] Power View kann angeblich auch in der Office 2013 Preview-Version von Office 365 über die dortige Web App betrieben werden. Zur Quelle dieser Information … […]


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 3,240 other followers

%d bloggers like this: