Is Excel the Next Killer Business Intelligence Application?

Love it or hate it, export to Excel is still the most specified requirement in contemporary analytic tool selections, despite all the advances in business intelligence (BI) technologies. Excel is comfortable, flexible and with the new Microsoft Office 365 Excel Power BI add-ins (Power Query, Power Pivot, and Power Map), it's growing to become exponentially more powerful—pun intended. With the latest Microsoft strategy shift of embedding self-service BI applications right within Excel.

Excel in the BI World

Business users may love Excel, but in the professional BI world, it carries emotional baggage. Countless data warehouses and reporting applications have been built with the intent of removing Excel risks and dreaded "spreadmarts." Excel has been blamed for high profile analytic disasters such as JPMorgan's $6 billion trading loss. Keep in mind, people make mistakes using many tools—not just Excel—yet Excel remains an extremely popular attack target.

Quite a few data discovery and BI vendors advertise anti-Excel messaging in one way, shape, or form. Since Excel is where most analytic tasks are performed today, if Microsoft makes the right investment decisions and quickly executes, popular data discovery applications are most at-risk and they know it. Reviewing an admittedly subjective list of top requested data discovery tool capabilities, Excel Power BI already arguably meets or exceeds more than half of them and rapid releases new features monthly.

Most Common Data Discovery Tool Requirements*

Data discovery requirements typically are business user driven requirements (a.k.a. the masses). If Excel can meet most data discovery requirements, why would you buy another stand-alone tool?    

Microsoft Business Intelligence in Excel 2013, SharePoint 2013 and SQL Server 2012 SP1

Excel enables business users to do self-service Business Intelligence directly in the client, which now becomes a complete and powerful self-service BI tool - users have all they need in one familiar application in order to do data modeling, exploration and visualization of the data.

Also, SharePoint 2013 supports corporate deployments and significantly improves governance and compliance over the self-service BI assets. SQL Server 2012 SP1 is required for server side compatibility.

With these significant investments in Excel and SharePoint 2013 self-service BI becomes a natural part of user’s day-to-day activities. See below for more details!

New capabilities delivered for end users in Excel 2013:

• Ability to analyze data ranging from a few rows to hundreds of millions of rows with extreme analytical performance on your desktop using Excel data models powered by xVelocity in-memory analytics engine
• Opportunity to speed up analysis in Excel by easily cleaning up and shaping your data with Flash Fill; by previewing, applying conditional formatting, creating tables, charts and PivotTables using Quick Analysis; by easily navigating multidimensional and tabular data models and creating trend charts to analyze information over time using Quick Explore.
  Quick Explore enables a user to look at the product categories summarized by Sales Quantity with 1 click:
  
  Quick Analysis suggests a Pivot Table type with a preview of actual data:
  
• Empower users of all levels to access, mash-up and analyze data from virtually any source and rapidly create compelling analytical applications with PowerPivot. Click here to learn more about new features in PowerPivot in Excel 2013
  
  PowerPivot Diagram View shows the data relationship:

• Provide stunning data visualization to discover new insights at the speed of thought with a highly interactive and familiar data exploration, visualization, and presentation experience for users of all levels
  with Power View. Click hereto learn more about new features in Power View in Excel 2013
  
  Interactive visualization with Power View in Excel 2013:
  
  

New capabilities delivered for IT:

At the same time, we are strengthening our IT management and oversight capability by

• Enabling discovery and assessment of user-created spreadsheets with SharePoint 2013
• Enabling comparing spreadsheets, tracking lineage, conducting interactive diagnostics and creating spreadsheet analysis reports with Inquire in Excel

SharePoint 2013 discovery and assessment criteria setup:

Inquire in Excel showing spreadsheet lineage and workbook analysis:

Explore these new features yourself:

We are excited to deliver these new capabilities for our customers. But don’t just take our word for it – download Excel, SharePoint 2013 Preview and SQL Server 2012 SP1 CTP3 and try these features for yourself!

Download Office Professional Plus 2013 Preview here: http://go.microsoft.com/fwlink/p/?LinkId=256624

Download SharePoint 2013 Preview here: http://go.microsoft.com/fwlink/p/?LinkId=256619

Download SQL Server 2012 SP1 CTP3 here: http://go.microsoft.com/fwlink/?LinkID=251592&clcid=0x409

Learn more about Microsoft Business Intelligence here: http://microsoft.com/bi

New Features in SQL Server 2014

Here are 10 new features in SQL Server 2014.

1. In-Memory OLTP Engine

SQL Server 2014 enables memory optimization of selected tables and stored procedures. The In-Memory OLTP engine is designed for high concurrency and uses a new optimistic concurrency control  mechanism to eliminate locking delays. Microsoft states that customers can expect performance to be up to 20 times better than with SQL Server 2012 when using this new feature. For more information, check out “Rev Up Application Performance with the In-Memory OLTP Engine.”

2. AlwaysOn Enhancements

Microsoft has enhanced AlwaysOn integration by expanding the maximum number of secondary replicas from four to eight. Readable secondary replicas are now also available for read workloads, even when the primary replica is unavailable. In addition, SQL Server 2014 provides the new Add Azure Replica Wizard, which helps you create asynchronous secondary replicas in Windows Azure.

3. Buffer Pool Extension

SQL Server 2014 provides a new solid state disk (SSD) integration capability that lets you use SSDs to expand the SQL Server 2014 Buffer Pool as nonvolatile RAM (NvRAM). With the new Buffer Pool Extensions feature, you can use SSD drives to expand the buffer pool in systems that have maxed out their memory. Buffer Pool Extensions can provide performance gains for read-heavy OLTP workloads.

4. Updateable Columnstore Indexes

When Microsoft introduced the columnstore index in SQL Server 2012, it provided improved performance for data warehousing queries. For some queries, the columnstore indexes provided a tenfold performance improvement. However, to utilize the columnstore index, the underlying table had to be read-only. SQL Server 2014 eliminates this restriction with the new updateable Columnstore Index. The SQL Server 2014 Columnstore Index must use all the columns in the table and can’t be combined with other indexes.

5. Storage I/O control

The Resource Governor lets you limit the amount of CPU and memory that a given workload can consume. SQL Server 2014 extends the reach of the Resource Governor to manage storage I/O usage as well. The SQL Server 2014 Resource Governor can limit the physical I/Os issued for user threads in a given resource pool.

6. Power View for Multidimensional Models

Power View used to be limited to tabular data. However, with SQL Server 2014, Power View can now be used with multidimensional models (OLAP cubes) and can create a variety of data visualizations including tables, matrices, bubble charts, and geographical maps. Power View multidimensional models also support queries using Data Analysis Expressions (DAX).

7. Power BI for Office 365 Integration

Power BI for Office 365 is a cloud-based business intelligence (BI) solution that provides data navigation and visualization capabilities. Power BI for Office 365 includes Power Query (formerly code-named Data Explorer), Power Map (formerly code-named GeoFlow), Power Pivot, and Power View. 

8. SQL Server Data Tools for Business Intelligence

The new SQL Server Data Tools for BI (SSDT-BI) is used to create SQL Server Analysis Services (SSAS) models, SSRS reports, and SSIS packages. The new SSDT-BI supports SSAS and SSRS for SQL Server 2014 and earlier, but SSIS projects are limited to SQL Server 2014. In the pre-release version of SQL Server 2014, SQL Server Setup doesn’t install SSDT-BI.

9. Backup Encryption

One welcome addition to SQL Server 2014 is the ability to encrypt database backups for at-rest data protection. SQL Server 2014 supports several encryption algorithms, including Advanced Encryption Standard (AES) 128, AES 192, AES 256, and Triple DES. You must use a certificate or an asymmetric key to perform encryption for SQL Server 2014 backups.

10. SQL Server Managed Backup to Windows Azure

SQL Server 2014’s native backup supports Windows Azure integration. Although I’m not entirely convinced that I would want to depend on an Internet connection to restore my backups, on-premises SQL Server 2014 and Windows Azure virtual machine (VM) instances support backing up to Windows Azure storage. The Windows Azure backup integration is also fully built into SQL Server Management Studio (SSMS).