I’m pretty skeptical of BI marketing pitches, (working as I do as a consultant in the trenches somewhere between business leaders, end users, and IT developers). But after hands-on experience with Microsoft’s latest BI tools – Power Pivot and Power View for SharePoint—I feel persuaded that “self-service BI” are not just buzz words. PowerPivot is an awesome tool, and the SharePoint integration ensures that not only can your business intelligence be centrally located in one place, but also that your IT teams can centrally manage security and access. These are user-empowering and cost-saving benefits.
So, what is Power Pivot? If I use Power Pivot do I need SQL Analysis Services cubes?
I used to describe Power Pivot as an Excel Pivot that lets users join multiple data sources from within an organization without the need to engage an IT developer. But I recently realized that some of my clients haven’t even worked with traditional Excel Pivot tables, (not to mention wrapping their heads around the notion of a “cube”). The key capability common to these pivots is the nearly “one click” automated aggregation of data, so that you can slice and dice any which way your data model allows.
And speaking of data models – you no longer need to be schooled in the Kimball data warehouse design and complex ETL tools like SQL Server Integration Services (SSIS) to get data from multiple sources in a way that supports complex business reporting scenarios. Nor do you have to spend thousands of dollars or wait six months for IT to get the data the way you want it. PowerPivot lets you build tables and calculated columns yourself, using methods similar to those we already know and love in Excel. The kicker is that you can now build relationships across different data sets in a single data model, and pivot just as easily.
How does Power Pivot work with Excel?
Power Pivot works with an Add-In to Excel 2010 but is expected to be native in Excel 2013. If you can build a pivot table in Excel, you can build a Power Pivot. The difference is that instead of using a single Excel tab for your data, you can join multiple tabs of data from different sources in a single pivot.
As your business needs change, you can change the Power Pivot model as easily as you change a formula or add a column in Excel today. Power Pivot uses a new language for building formulas, called “DAX,” but the learning curve is pretty short if you already know traditional Excel formulas.
How does Power Pivot work with SQL Analysis Services? How is Power View different from Power Pivot?
You can see where Microsoft is headed with Power Pivot – they’ve already updated SQL Analysis Services to be able to handle the new “columnar” data model, in addition to the multidimensional models in SQL that SSAS traditionally leverages. Once business users create a Power Pivot model, then SSAS can leverage it, as can any of the other BI tools in the MS stack, including SQL Server Reporting Services (SSRS), SQL Report Builder, and other Excel workbooks.
There may be times when a traditional data warehouse and SSAS multidimensional model may be needed. But those times may be less frequent, given the new Vertipaq in-memory storage engine in Excel, and the new Analysis Services Tabular model, which handles huge data sets with excellent performance and connects easily to other reporting tools.
Power Pivot delivers user friendly ways to manage business intelligence more effectively than previous generations of ETL & reporting tools, especially when loaded to a SharePoint PowerPivot gallery. Once loaded to the gallery, the Power Pivot model becomes available as a data source to Microsoft tools for creating charts and dashboards, including SSRS and their new tool, Power View. Power View provides a browser-based tool for easily and quickly visualizing data stored in Power Pivot.
What I love about Power View is how it pushes the edge even further into domains that previously only top-end developers dabbled in. Silverlight tables and charts in the hands of business users are, well, pretty darn cool.
In my next blog, I’ll share my thoughts on how Power View further realizes the “self-service” promise of the Microsoft BI stack.