As a follow up to my last post on Business Intelligence, which you can find at the below link, I want to dive deeper into the PowerPivot solution from Microsoft. Let’s explore how we can leverage the PowerPivot solution and improve our BI capabilities.
As I discussed in my last post, many organizations are currently struggling to meet the BI needs of their end users either due to the sheer volume of requests or failure to provide the insights the users are looking for.
So how can we scale Business Intelligence to the masses and make sure we are addressing the insights our end users are interested in?
We can do this by empowering our end users to develop their own BI solutions, which meet their specific needs, and address the questions they want to ask about the business through easy to use tools. Microsoft’s PowerPivot solution does just that while allowing IT to still manage the solution.
There are two pieces to the PowerPivot solution which include PowerPivot for Excel and PowerPivot for SharePoint. PowerPivot for Excel is the end user tool which allows our users to pull vast amounts of data from a variety of sources into their Excel client and develop a Tabular data model to relate, format, and aggregate the data. PowerPivot for SharePoint is the other piece to the puzzle providing easy collaboration among end users in a central location while allowing IT to have oversight and management of the solution. Microsoft’s goal was to provide a scalable BI solution that delivers faster ROI and empowers our end users.
Here at CDW my role as a Solution Architect is to provide expertise and evangelize Microsoft solutions but I’m also an end user that needs to consume data/information. I’ve been using PowerPivot since it was first released with SQL Server 2008 R2 and as an end user I’ve been able to utilize PowerPivot in my daily work to better understand and visualize sales data that impacts my role. PowerPivot is surprisingly easy for many end users to pick up quickly.
Let’s explore the PowerPivot solution in the below video demonstration:
How can we scale?
A huge benefit of the PowerPivot solution is that it allows us to start small with our BI solution and grow as the solution gains popularity and use in the organization. PowerPivot is a great option for personal and team BI solutions where the scale is limited as there is a 2GB size limit for a PowerPivot workbook. To be clear though, since PowerPivot greatly compresses data when it’s imported we can typically bring greater than 2GB of data into PowerPivot due to the compression.
So what do we do when we grow beyond that size limit? Microsoft has recently introduced the Analysis Services Tabular Model with the release of SQL Server 2012 which mimics the data modeling experience we have in PowerPivot but will allow us to scale beyond the 2GB limit and partition the data model for better management and processing. Microsoft has made it easy to port a PowerPivot data model into an Analysis Services Tabular Model providing us greater scale for enterprise size solutions without the need to rebuild or make any changes to our solution. The below image illustrates how we can move from a personal BI solution, to a team solution, and ultimately to an enterprise wide solution with the same tools and capabilities throughout each phase. This enables an organization to start small to realize faster ROI while still having the ability to scale to the enterprise as needed.
Up Next: Power View
In a future post we’ll take the next step and discuss the Power View component of the Microsoft BI solution which will provide our end users a simple to use tool for building visually rich, interactive reports to explore data and gain business insights. Power View will get your organization’s end users excited about being able to build their own reports rather than dreading the task.