It’s very common these days to find organizations that have spent a lot of time and effort gathering data about their business, because data is valuable, but have yet to ask themselves, “How do we effectively use this data?”
Some of the challenges we face when trying to leverage our data effectively include:
- Having data located in multiple different sources with no single way to get to this data and get a holistic picture of how this data relates
- Trying to report directly against operational data without providing any intelligence or context to the data
- Providing rigid standard reports that don’t answer the questions our end users are asking without any option for drilldown or data exploration
Often times we find a few stressed out employees with limited tools that are pulling their hair out trying to figure out the best way to make sense of the organization’s data and present that data to the end users. This typically results in a struggle to keep up with the demand of the organization’s reporting needs and failure to provide the insights the end users are looking for.
So the real question may be, “How do we empower our end users with easy to use, interactive tools to perform their own data analysis and reporting while also allowing IT to provide oversight and management?”
A Brief History
Microsoft first brought reporting capabilities to SQL Server (Microsoft’s Relational Database Management System) in the 2000 version release. The first iteration of Reporting Services was a solid component of SQL Server that provided standard reporting capabilities against a variety of data sources. It has been refined throughout each SQL Server release and received some terrific upgrades in the 2008 and 2008 R2 releases providing much improved visualizations that brought it to the level of many 3rd party reporting products. Traditional Reporting Services functionality still provides a great solution for standard reporting and report distribution in a variety of formats but the user interface for building reports, Report Builder, isn’t easy enough for any old end user to pick up and start using right away to build their own reports. This results in that small number of users in an organization still bearing the responsibility to build everyone’s reports. Traditional Reporting Services functionality also doesn’t provide any robust drilldown and/or click-through capabilities to allow end users to develop business insights through data exploration.
Microsoft also introduced the Analysis Services Multidimensional Model in the 2000 version release of SQL Server which has been used as the analytic layer for the majority of Microsoft BI solutions for the past 10+ years. The Analysis Services Multidimensional Model is an incredibly powerful solution for OLAP (Online Analytic Processing) providing multidimensional model design, build, and management to aggregate data, add intelligence/context to our data, and optimize for reporting performance. It has been refined and improved throughout each release of SQL Server just like Reporting Services but has never been close to being anything that an end user or even power-user would be able to work with due to the complexities involved with multidimensional modeling design and management. Multidimensional models also rely on the MDX expression language which is daunting even for experienced SQL Server professionals to use.
As Microsoft looked to address the emerging option of self-service Business Intelligence they introduced the PowerPivot solution with the release of SQL Server 2008 R2. There are two components to the PowerPivot solution which include PowerPivot for Excel and PowerPivot for SharePoint. PowerPivot for Excel is built into the familiar Excel application that our users have been exposed to for many years. The PowerPivot add-in allows an end user 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. This is putting data analytics into the hands of our end users for the first time with simpler data modeling design and management which most power users can pick up quickly. The Tabular Model also utilizes the DAX expression language, which is very similar to Excel formulas, making the user experience even more familiar and easier for users to get started with quickly without a huge learning curve.
PowerPivot for SharePoint is the other piece to the puzzle providing management and easy collaboration. It allows us to host these tabular data models we built in Excel on a SharePoint site for easy sharing of the data model presentation of the data in the model through familiar PivotTables and PivotCharts. PowerPivot for SharePoint also provides proper management of user access, data refresh of the model, and performance monitoring. This allows the IT department to provide oversight and management of our self-service BI solution.
To round out the self-service BI stack, Microsoft added the Power View solution with the release of SQL Server 2012. Power View is a simple to use ad-hoc reporting and data exploration tool which truly brings self-service report design and creation to the masses. Power View provides drag-and-drop report creation with powerful visualizations and dynamic data exploration to empower our end users to represent and explore the data however they prefer and allow for business insights to be discovered.
In my next few posts we’ll dive deeper into the PowerPivot and Power View solutions to better understand how these tools can be leveraged through the evolution of a BI solution from individual BI, to team BI, and ultimately scale to Enterprise wide BI. I’ll be providing video demos of each so you can actually see them in action. Stay tuned…