Chris Webb's BI Blog

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

Building a Simple BI Solution in Excel 2013, Part 1

with 19 comments

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:

image

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:

image

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:

image

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:

image

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:

image

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:

image

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:

image

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:

image

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:

image

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

image

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.

Written by Chris Webb

July 17, 2012 at 11:33 pm

19 Responses

Subscribe to comments with RSS.

  1. Does Power View reports use only PowerPivot data source & Tabular or not?

    Voyager_lan

    July 18, 2012 at 8:48 am

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

      Chris Webb

      July 18, 2012 at 10:27 am

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

        Voyager_lan

        July 19, 2012 at 11:53 am

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

        Chris Webb

        July 19, 2012 at 11:55 am

  2. Chris, which bit version of Office 365 did you tried?

    milang

    July 18, 2012 at 11:29 am

  3. [...] part 1 of this series I showed how to build a BI dashboard with UK weather data in Excel 2013. What I’m [...]

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

  5. [...] 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! [...]

  6. “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).

    Colin Banfield

    July 19, 2012 at 6:57 pm

  7. [...] in the next few days … Chris Webb has already published two posts on the first three topics here and [...]

  8. [...] integration of Power View into Excel 2013 is the biggest news for the BI-functionality in Excel. Chris Webb has already blogged about the map functionality in Power View  Excel sheets. I will describe a [...]

  9. [...] Building a Simple BI Solution in Excel 2013, Part 1 by Chris Webb [...]

  10. 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?

    Omar Sultan

    March 31, 2014 at 1:34 pm

    • Hi Omar, I don’t know – I think you’re right, this is probably a limitation of the current version.

      Chris Webb

      March 31, 2014 at 1:45 pm

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

    Amith

    July 28, 2014 at 7:00 am

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

      Amith

      July 28, 2014 at 9:25 am

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

      Chris Webb

      July 28, 2014 at 3:07 pm


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,146 other followers

%d bloggers like this: