Chris Webb's BI Blog

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

What Are The Big Changes In Excel 2013 For BI?

with 26 comments

As you may already have read, the first public preview for Office 2013 is now available and there’s lots of new BI functionality in there – see here for an overview. Here’s a quick summary of what the two really important changes are:

  • PowerPivot has been integrated into Excel, kind of. This means that the xVelocity (aka Vertipaq) engine is now native to Excel, and you can do all the basic PowerPivot stuff like loading vast amounts of data from multiple data sources and querying it via PivotTables directly in Excel, without installing any extra addins. PowerPivot does still exist as an optional extra however: you need it if you want to use the more advanced functionality that exists in PowerPivot today, such as filtering data before import, using diagram view, defining hierarchies and perspectives and so on.
  • Power View has also been integrated into Excel: Power View reports become a new type of sheet inside a workbook, and you can use it against data held in the integrated xVelocity/PowerPivot engine; I’m not clear yet whether it will work on a SSAS 2012 Tabular model (and at some point a SSAS Multidimensional model, once support for DAX on Multidimensional models arrives) but I hope it does. No more need to moan about Power View being tied to Sharepoint!

There are a whole bunch of other BI-related changes in Excel which I’ll try to summarise in another post soon (stuff like the suggestions for charts and PivotTables, flash fill, timeline slicer). However I think that the two changes above represent a master-stroke on the part of Microsoft: they make Excel 2013 a serious contender in the self-service BI tool stakes. Certainly, other vendors will be quick to point out the features they have and that Excel doesn’t, and dedicated BI vendors will always be able to add new features faster and more frequently than Excel, but that’s not the point. It won’t happen overnight but at some point every company will upgrade to Office 2013 and when they do, all users will have a BI tool on their desktops which is vastly more capable than Excel today and will be good enough for the majority of BI scenarios – which means that the need to even look at third party tools will disappear.

UPDATE A few clarifications:

  • The PowerPivot addin, while still an addin, comes bundled with Excel – there’s no separate download
  • As the comments below show, and I can confirm, Power View does work with SSAS 2012 Tabular models

I’ll be posting more details throughout the day on Twitter as I play with the new preview, and will post something more substantial here later

Written by Chris Webb

July 16, 2012 at 9:12 pm

Posted in BI, Excel, PowerPivot

26 Responses

Subscribe to comments with RSS.

  1. Chris…were you able to get PowerPivot working?

    Frank Kearney

    July 16, 2012 at 9:47 pm

  2. ok..what you have to do is go to FILE–>OPTIONS–>Add-Ins. At the bottom hit the drop down next to Manage: . Then select COM Add-Ins and click GO. From there you can enable PowerPivot. You have to shut Excel down and reopen.

    A funny thing is that it picks up your prior install of the Office 2010 PowerPivot add-in. So in Excel 2013 you will see two PowerPivot tabs. The second one is the new one.

    Also, PowerView will not connect to any outside source; only internal sources (i.e. data in a tab, PowerPivot) This is because PowerView in SharePoint is the “server” version of PowerView whereas PowerView in Excel 2013 is the local end-user version…

    Frank Kearney

    July 16, 2012 at 10:06 pm

  3. Frank, thanks for your solution to enable Power Pivot.

    Power View works for me with external data from a tabular mode SSAS instance (multidimensional cubes still not possible to connect to). How to do: DATA -> From Other Sources -> From Analysis Services, chose your tabular mode instance and when Excel askes you about what to do with the data, you take the Power View option.

    Robert Zaufall

    July 16, 2012 at 10:27 pm

  4. Integrated means (to me) programmable. Do these features have the ability to be manipulated like any other functionality of Excel with VBA?

    David Hager

    July 17, 2012 at 1:30 am

    • I’m not quite sure. Since xVelocity is now the engine behind all data import in Excel, when I tried to record a macro that seemed to work. Changes made in the PowerPivot tab weren’t recorded though. I may be missing something though.

      Chris Webb

      July 17, 2012 at 12:24 pm

  5. [...] ·         What Are The Big Changes In Excel 2013 For BI? [...]

  6. Apparently, Excel 2013 now has the “Model Object”. See: http://msdn.microsoft.com/en-us/library/office/ff194068(v=office.15). However, no online documentation yet.

    David Hager

    July 17, 2012 at 12:40 pm

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

  8. “I’m not quite sure. Since xVelocity is now the engine behind all data import in Excel,”

    I don’t see how this can be possible. I can import data into a standard Excel table, so what would xVelocity be doing in this case? Also, I can import into a standard Excel PivotTable…but can’t use any DAX functions…which then must require the PowerPivot add-in.

    Colin Banfield

    July 19, 2012 at 7:19 pm

    • Hmm, I think there’s more going on here than I understood when I said that. It seems like in some cases when you import data it goes directly into the worksheet, in others it goes into the native xVelocity store, which is (I gather) called the Excel Model. However what is the case is that xVelocity is now built into Excel and the PowerPivot addin is only necessary for defining relationships and managing measures – PowerPivot no longer stores the data itself.

      I’ll try to find out what happens when – from what I can see, when you import a single table from SQL Server it doesn’t go into the Excel Model, but when you import multiple tables from the Data tab that does go into the Excel Model and you do see it in the PowerPivot window subsequently.

      Chris Webb

      July 19, 2012 at 11:24 pm

      • Chris,

        What’s actually happening is the following:
        1) If you choose to import the data into one or more tables, then xVelocity isn’t involved.
        2) If you select a single table to import into a PivotTable, there is an option to make it part of the data model. If you choose not to make it part of the data model, then xVelocity isn’t involved. It’s the same standard PivotTable that we’ve created in the past on import. Therefore, you can add calculated fields and calculated items from the PivotTable tools Analyze tab, but you can’t create DAX measures. On the other hand, if you choose to make the PivotTable part of the data model, the PivotTable becomes an xVelocity table – you can’t add calculated fields and calculated items from the Analyze menu, but you can create DAX measures, as one would expect.
        3) If you select more than one table to import into a PivotTable, xVelocity is immediately invoked. The option to make the PivotTable a part of the data model is greyed out. This must be so because the standard PivotTable can be created from only a single table.

        You’re correct that xVelocity is divorced from PowerPivot, and built into Excel. Also, relationships can be created without PowerPivot (from the Relationships button in the Data tab). Without PowerPivot, you’re quite limited though. You can’t create DAX measures, mark a table as date, specify sort columns, create hierarchies, perspectives, and all of the other stuff you can do through the PowerPivot window. PowerPivot is now simply a UI to administer the data model.

        I might do a post on this topic because I think that even experienced Excel users will be confused by this new data model thing.

        Colin Banfield

        July 20, 2012 at 7:01 am

      • Yes, it would be good if you could write this up – thanks!

        Chris Webb

        July 20, 2012 at 9:19 am

  9. Any chance we can use other Excel functions like XIRR in DAX Measures?

    Greg

    July 20, 2012 at 11:55 am

    • Not sure, I’ll check.

      Chris Webb

      July 22, 2012 at 4:10 pm

      • Vidas Matelis claims to DAX function to calculate XIRR, but he has never posted it.

        David Hager

        July 22, 2012 at 7:34 pm

      • Actually, Vidas has posted it here: http://portfolioslicer.com/portfolio-slicer/technical-info/calculated-measures

        However he’s run into a performance issue with PowerPivot which won’t be fixed until the next CU of 2012, so I guess that’s why he hasn’t publicised it more.

        Chris Webb

        July 26, 2012 at 6:19 pm

      • Thanks!

        David Hager

        July 26, 2012 at 10:47 pm

      • My XIRR implementation is very “crude” – as PowerPivot does not have recursion, I simple calculate XIRR value multiple times and this way I can find value that matches required condition (value closest to 0). But as Chris said – I hit “nested IFs” issue and I am hoping that this issue will be resolved in next SQL Server 2012 CU. Currently my XIRR implementation works for basic calculations, but I cannot add more code to check conditions when XIRR should come back with special values, as extra IFs substantially increase memory consumption.

        Vidas Matelis

        July 31, 2012 at 1:54 am

  10. Very impressed with Excel 2013 MS has been busy!
    Can Excel Power View be deployed to SharePoint?

    Note; Unfortunately, Power View doesn’t display my Hierarchies from a BISM tabular model as it does PowerPivot. So cannot drill up and down one of the best features – hope its added\fixed.
    Mapping does work with BISM tabular – nice.

    Rick Schulte

    July 23, 2012 at 1:21 am

  11. [...] deguster sans modérationLa preview d’Excel 2013Microsoft BI Team blogDan English BlogChris Webb Blog Share this:TwitterFacebookJ'aime ceci:J'aimeSoyez le premier à aimer [...]

  12. [...]  does seem to indicate Microsoft positioning themselves more securely within the BI marketplace. Chris Webb, a independent consultant specializing in Analysis Services, MDX, PowerPivot and DAX, cites on his [...]


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

%d bloggers like this: