April 28, 2015 // By Steve Hughes
On July 16, 2012, Microsoft made the Office 2013 Preview publicly available. As I worked through the install and saw the hype, I was really curious about some of the enhancements made to the business intelligence (BI) capabilities in Excel. Keep in mind that Microsoft has continually added BI components to the Excel product. Agree or disagree, this means that they have been positioning Excel as an “already installed” BI client for everyone.
With the previous release of Excel 2010 and PowerPivot, we were given an extremely powerful data mash-up tool that could be used by anyone and that introduced the xVelocity in-memory analytics engine (Vertipaq at that time). This was in addition to Excel’s capability to load data, create charts, use pivot tables and even perform data mining with the data mining add-in. When SQL Server 2012 was released, the capability of PowerPivot increased even more. Now PowerPivot had increased its calculation capabilities substantially and could be migrated to the new Tabular Model database in SQL Server 2012 Analysis Services.
The Big Additions to Excel 2013
As we waited to hear or see anything about the Office 2013 (a.k.a. Office 15) release, one had to wonder what would change. Now we know. The biggest, most significant BI change (in my opinion) is the inclusion of Power View in the product! Yes, you heard correctly, no longer do you need SharePoint to take advantage of Microsoft’s Power View reporting capabilities; you can now create the reports in Excel. As noted in the image below, you can take advantage of the entire set of capabilities previously only available in SharePoint:
Also in this version, much of the core PowerPivot functionality, including the xVelocity in-memory analytics engine, has been built into the Data Model in Excel. This brings the capability to import millions of rows into a table and create relationships between the tables, including disparate data sources. When you add a connection, you can now choose to import multiple tables. If the relationships are discoverable such as in a relational database, the relationships will be built into the model:
Furthermore, this data will be stored as part of the spreadsheet, making it very portable. This is possible because xVelocity compresses the data to a manageable size and keeps the sheet very responsive.
If this is not enough for you, the PowerPivot add-in is still available and gives power users who require more advanced modeling techniques the full capabilities of the current PowerPivot release, including KPI creation, user-defined hierarchies, and perspectives.
Other New Features that May Interest BI Users & Developers
Not only did they include Power View in this release, but they added some other features that I found interesting.
- Recommendations: Excel will now recommend PivotTables and Charts based on the data content you have in the sheet.
- Insert a Timeline: Excel can now insert a timeline that is based on dates in the data you have.
For some more information, check out the information on Excel 2013 BI capabilities on the Microsoft site.
A Pleasant Install Experience... No Really
I also want to commend Microsoft on how simple the install was. I currently have the Office 2013 Preview and Office 2010 installed side-by-side and the install was painless. I used the download software option that was available on the Admin page of my Office 365 Preview, and I was able to start working with the Preview immediately. Besides being painless, the installer also added my PowerPivot and TFS add-ins into Excel with no additional effort from me. Kudos to the Microsoft team for making this easy.