With new PowerPivot for Excel, Microsoft is democratizing business intelligence (BI) in the same way that PowerPoint transformed business presentations. A free, downloadable add-in for Excel 2010, PowerPivot taps into the rich analytic capabilities of Microsoft SQL Server 2008 R2 Analysis Services, offering centrally managed, self-service, ad hoc BI within the Excel interface we’re all familiar with. To keep BI manageable and allow robust collaboration, analytical results can be shared using Microsoft SharePoint 2010.
PowerPivot’s end-to-end capabilities — enhanced by its optimization on the new multicore client and server platforms — provide compelling reasons to accelerate your technology refresh sooner than later.
Triple Play: Excel + SQL Server + SharePoint
PowerPivot meets a long-awaited business need. Organizations are awash in data, and the ability to turn that data into actionable information can deliver a competitive advantage by improving product development, customer service, and operational efficiency. Despite the benefits, a recent Gartner survey found that only 28 percent of potential BI users actually employ BI.
PowerPivot aims to make it easier for businesses to achieve those advantages, while allowing IT to increase its return on previous investments in databases and data warehouses. Three of Microsoft’s core products — SQL Server, Office and SharePoint — all released new versions at the same time, and the reason is PowerPivot.
High-speed Analysis at Your Fingertips
Excel has long been used for lightweight analysis, but users have been hampered by performance barriers as well as limitations on the amount and types of data and the analytic functions available. PowerPivot — Microsoft SQL Server PowerPivot for Microsoft Excel 2010 — removes those limitations. It provides a powerful tool set to help users gain deeper insight into business activities and compress their decision cycles.
Among the most important enhancements:
- Virtually unlimited support for data sources provides a foundation for users to create mashups of source data from any location — relational databases, multidimensional sources, cloud services, data feeds, Excel files, text files, data from the Web, etc.
- In-memory analysis using the VertiPaq engine enables high-speed desktop analysis of hundreds of millions of rows of data. VertiPaq uses columnar storage and efficient compression algorithms to load and process even the largest data sets into memory.
- Data Analysis Expressions (DAX) bring powerful relational capabilities into the hands of power users who want to create advanced analytics applications.
- SharePoint integration enables users to share data models and analysis to publish their PowerPivot workbooks through the familiar portal.
PowerPivot won’t replace enterprise-scale BI applications, such as SQL Server Analysis Services, which will continue to play an important role in company-wide decision-making. Because it plays to the area of analysis that people do for their own needs — or the needs of their team or department — an end-to-end solution would be overkill.
Hardware and Software Make Self-service Manageable
For IT leaders already plagued by proliferating spreadmarts, self-service BI might sound like a potential nightmare. To address those concerns, Microsoft emphasizes that managed self-service BI and a variety of capabilities — both in the software and in processor technologies — allow greater IT control while making PowerPivot easy to deploy, monitor and manage.
Because analytic workbooks and reports are stored in SharePoint, IT can create an environment for users to access and share them. A management dashboard enables IT administrators to monitor and manage reports, maintain control over how the data is used and configure refresh cycles to ensure data remains current automatically.
PowerPivot features remote repair and securing of PCs, and works with Microsoft System Center Configuration Manager (SCCM) 2007 SP2 and Intel vPro technology to provide a secure, encrypted power-up capability for pushing PowerPivot for Excel 2010 and security software updates down the wire. [Disclosure: Intel is the sponsor of Intelligence in Software.] On those vPro processor-based PCs, you can also embed credentials in the hardware and use SCCM SP2 to authenticate network traffic.
Adding to the Load on Clients and Servers
As an end-to-end software solution, PowerPivot raises the demands on all layers of the IT infrastructure. PC users will need multicore computational power and security for rich analysis on the desktop or laptop. In the data center, the need for performance, memory bandwidth and energy efficiency rises at each tier to support higher numbers.
Platforms based on the latest Intel technologies deliver unique value for PowerPivot deployments. Excel and SQL Server take optimal advantage of multiple threads and cores. And at the operating system level, Windows optimizes performance and energy efficiency. If the workload is light, Windows sources Intel power management technologies to allow server cores to run idle and lower energy consumption. If you’re doing a lot of year-end or quarter-end analysis and really stressing your systems, the Intel Turbo Boost Technology kicks them into higher gear. Running databases on the Intel Xeon Processor 7500 series for higher scalability delivers benefits from features, like Machine Check Architecture and error recovery capabilities, which Windows Server 2008 R2 utilizes to improve reliability.
PowerPivot raises the requirements at each tier, with SharePoint Server 2010 for reporting at the front end, SharePoint Server 2010 for PowerPivot in the application tier, and SQL Server 2008 R2 for data and workbook storage at the back end.
Business analysis staff requires far more performance and reliability than aging PCs can provide. At the client level, the second-generation Intel Core vPro processor family provides right-sized performance and cost savings. Efficient maintenance features help users speed time-to-data while increasing security and manageability capabilities.
In the data center, processors require massive memory bandwidth (with up to 50 percent more cores and cache) in order to increase performance for self-service BI analytics and to provide greater throughput and responsiveness for multithreaded SQL Server 2008 R2-based applications accessed using PowerPivot. Windows Server 2008 R2 Hyper-V offers greater virtualization security and lowers data center costs for PowerPivot infrastructure in a virtualized environment.
Get Ready for a Tsunami
Although end-to-end enterprise BI solutions will continue to be the bedrock for company-wide decision-making, all indications are that PowerPivot will soon begin to transform ad hoc decision-making by business units, financial teams and individual planners. We’re going to see a BI tsunami coming very soon. This will permanently change access and utilization of BI for the masses and will transform the fundamental way people use data to accelerate their businesses.