Chapter 5. Using Microsoft's Self-Service Business Intelligence Tools

The focus of Business Intelligence is to empower business users with more accurate, reliable, and better information than they could obtain from operational systems. Traditional BI solutions have always relied on a centralized, cleansed, and transformed datastore that users can access through standardized reports and occasionally, an ad hoc query tool. Reports are developed by BI experts and the minimum requirement was to hire a set of professionals to run and maintain the infrastructure and the solutions.

After the deployment of such a system, requests for a catered data source for a specific need or a new data source could take days to be served as it has to go through the defined change management requests. Since only a few resources are trained well enough to handle these requests, the changes may even take weeks to implement.

Many executives have realized that the current BI approach is unable to keep pace with the...

PowerPivot enhancements

SQL Server 2012 brings in enhanced data analysis capabilities for both PowerPivot client in the form of the Excel add-in as well as the PowerPivot server-side component in SharePoint. This enables Microsoft Office users to have self-service business intelligence capabilities with merely a few clicks, something which was never there before. Users are now able to integrate data from heterogeneous sources more easily and provide powerful visualization on the data using familiar tools such as Excel and SharePoint, thus enabling the businesses to take correct decisions.

PowerPivot is offered as an add-in to Excel 2010 that allows data integration from any source and syndication, including Open Data Protocol (ODATA) feeds, RDBMS sources, Hadoop, and so on. Using PowerPivot, you could create your own data models from any of the diverse data sources previously mentioned and import large amounts of data into Excel directly. If there is a need to have these data models as a...

Power View for Excel

Microsoft Excel 2013 introduces a brand new self-service BI tool called Power View. This is also a part of Microsoft SharePoint 2013 included with SQL Server 2012 Reporting Services Service Pack 1 Add-in for Microsoft SharePoint Server 2013 Enterprise Edition. Both of the client side (Excel) and server side (SharePoint) implementations of Power View offer an interactive way to explore and visualize your data as well as to generate interactive reports on top of the underlying data.

The rest of this chapter shows a sample Power View report based on the facebookinsights table's data to give you a quick idea about the powerful reporting features from the surface level. The details on 'How to design a Power View report' as well as Power View integration with SharePoint is outside the scope of this book and are not discussed in depth.

Note

Power View is only supported in Excel 2013. You need to install the Power View add-in for Excel.

To create a Power View report based on the...

Summary

In this chapter, we learned how to integrate Microsoft self-service BI tools with Hadoop and Hive to consume data and generate powerful visualizations on the data. With the paradigm shifts in technology, the industry is trending towards an era where Information Technology will be a consumer product. An individual should be able to visualize the insights he needs to an extent from a client side add-in like Power View. These self-service BI tools provide the capability of connecting and talking to a wide variety of data sources seamlessly and create in-memory data models combining the data from these diverse sources for powerful reporting.