Building a Simple BI Solution in Excel 2013, Part 1

nsurprisingly there’s been a lot of interest in Office 2013 since it was announced yesterday, and I’m certainly very excited by all of the new BI features in it. Luckily I wasn’t working today so I had the chance to have a proper play with it, and I thought it would be useful to walk through the process of building a simple BI solution in Excel 2013 to show off some of the new features.

Let’s start with a blank workbook:


Some people don’t like the new look for Office, but I quite like it. You can even set custom backgrounds: you may just be able to see some wispy clouds in the top right-hand corner of the screenshot above. But anyway, to business. To import some data, I first need to go to the Data tab as normal:


As you can see there are some new options available here, and I’m going to go to the Windows Azure Marketplace. Actually, I’m going to cheat a little and just say that I’m going to import the UK weather forecast from 12:00am today (July 17th) along with some related tables as described in this blog post. The UI for the import wizard is plain but functional:


Interestingly, the data is always imported at the end of the wizard even if I check the ‘Only Create Connection’ option on the last step of the wizard.

Once the data from all the tables has been imported, I need to specify some relationships. I can either do this by clicking on the Relationships button in the Data tab or (better still) going to the PowerPivot window and using the Diagram View. Now as I said yesterday, xVelocity in-memory database is now integrated into Excel but I still need to use the PowerPivot addin in some cases; The PowerPivot addin comes bundled with Excel 2013 but isn’t enabled by default, so in order to use it I first need to enable it; the steps to do this are detailed here. I can then click on the Manage button in the PowerPivot tab to open the PowerPivot window:


And then build some relationships between my tables in Diagram View, which can be reached by clicking on the small Diagram button in the very bottom right hand corner of the PowerPivot window. Once in the Diagram View, building relationships is simply a matter of dragging one column onto another:


I can also build hierarchies here; Duncan Sutcliffe shows how to do this here, and also how to use hierarchies in Power View which is something new. I’ve created a hierarchy going from Region to Weather Station.

With that done I can of course build a PivotTable. The first thing I’ve noticed is that there isn’t now a separate type of PivotTable for PowerPivot, which is kind of a shame because this means that you don’t seem to be able to create Slicers in the Field List any more, and have to go up to the Ribbon. It’s good for consistency though I suppose. Measure aggregation and number formats are all handled in the Value Field Settings dialog:


There’s a new type of Slicer available too for dates called the TimeLine which Raphael has a lot of good detail on here.

Here’s what the new Quick Explore option, which provides some options on where to drill to and for charts to create, looks like when I hover over a Region on Rows:


Really, though, the thing to do is to create a Power View sheet. This can be accomplished by going to the Insert tab and clicking the Power View button:


This is where things get exciting. Power View in Excel now supports maps and because my weather data contains the latitude and longitude of all of the weather stations in the UK it’s very easy to plot each weather station on a map and visualise the temperature and predicted weather for each station. To do this I just need to drop the Region Name column onto the Filters selection (choosing a region reduces the number of Weather Stations displayed down to a manageable number for the purposes of this demo), then drop the Latitude and Longitude columns onto the main canvas, turn the resulting table into a map, and then set the size of the markers to show temperature and the colour to show the type of weather:


I’ll be speaking at SQL South West this week so what’s the weather like down there at the moment?


From this I can see that today there’s fog in the Scilly Islands (shown by the green marker in the bottom left hand corner) and that it’s very cold and foggy in Liscombe (shown by the small red marker in the middle of the map). This dashboard was ridiculously easy to create, by the way, and I am already completely in love with Excel 2013 simply because of Power View – it’s a killer feature in my opinion.

At this point we’ve got a working self-service BI solution, made very quickly in Excel 2013 and it’s something that every competent Excel user would be able to achieve.

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

19 responses

    • Power View only supports PowerPivot and SSAS Tabular data sources. You have to remember that in Excel 2013, though, all data you import into Excel via the Data tab now goes into PowerPivot.

      • Unfortunately, this is not a universal solution. In this case, Simple users will not be able to make Power View reports from Multidimensional SSAS 🙁
        But I sincerely hope that all the MDX scenarios will be translated into the Tabular Model (for migration tasks)

      • Yes, but at some point soon users of SSAS 2012 Multidimensional will be able to query it in DAX, which means that Power View will work on Multidimensional.

  1. Pingback: Building a Simple BI Solution in Excel 2013, Part 2 « 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. “You have to remember that in Excel 2013, though, all data you import into Excel via the Data tab now goes into PowerPivot.”

    I imported data from SQL Server. I imported data via Query. In both cases, I was presented with a list of import options – PowerPivot was not one of the options. I have no idea why we have different experiences, or why PowerPivot isn’t even listed as an option (yes, PowerPivot is installed and working fine).

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

  6. Pingback: Power View in Excel 2013 Part One: Pie Charts « Thomas.I Microsoft BI & Analytics

  7. Pingback: Power View Map in Excel 2013 Preview | Ms SQL Girl

  8. Hello Chris, not sure if this is the right place to post.

    I am working on a powerview requirement for the Maps and one of the requirements is to utilize the “Color” option provided in the Maps report. However, it seems that I can only use Dimension Attributes in the Color field, I am unable to use Measures to drive the color field. Do you know if this is a limitation?

  9. I have SQL 2012 standard edition in my local. I have developed SSAS & deployed in local. I have been asked to develop power view report in excel 2013 using this SSAS. But when I tried to do in Excel 2013 professional Plus, I am getting below error:
    Power view couldn’t load the model or data source because the data source type is not supported.

    Does power view is supported in business edition of SQL or it requires Business/Enterprise edition of SQL server? Please do help me. Thanks in advance

    • There was wrong in question-
      Does power view is supported in Standard edition of SQL or it requires Business/Enterprise edition of SQL server? Please do help me.

    • Are you using SSAS Multidimensional or SSAS Tabular? Power View in Excel (as opposed to Power View the standalone tool) can only connect to SSAS Tabular models right now, and if you have SQL Server 2012 Standard Edition you will only be able to use SSAS Multidimensional.

Leave a Reply to Colin Banfield Cancel reply

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

%d bloggers like this: