MASAI Consulting
Microsoft SQL Server
SQL Server 2008 R2: Business Intelligence for Excel and SharePoint
Information Platform
Microsoft SQL Server 2008 R2 is an Information Platform. This platform addresses three primary groups, or sets of needs:
- Empower IT, including DBAs, to manage services easier
- Provide tools to support the development of data rich apps
- Support business users who focus on business intelligence using familiar toolsets
Microsoft has identified three investment pillars R2 focused on as shown below:
SQL Server 2008 R2 Investment Pillars
- Trusted, Scalable Platform: SQL Server 2008 R2 has been built to be very scalable and secure. It now offers Master Data Services — a new service offering/technology enabler that provides organizations with the ability to ensure data consistency across heterogeneous systems. Organizations can determine what data they would want centrally managed, and allows applications to subscribe to that data. It includes versioning, business rules, hierarchy management, an audit trail and reporting. StreamInsight is high scale event processing. It provides near real-time streaming of events and enables developers to write applications using LINQ in .NET that use the information. One example could be for hospital patient monitoring.
- IT & Developer Efficiency: For DBAs there is multi-server management from a single window, similar to what you can do in System Center, but now within a familiar environment. One cool capability offered a bird's eye view across all instances and components, something many DBAs will appreciate. There's also the ability to manage capacity by setting policies — affecting one or more instances and easier deployment using single unit packaging. For developers, SQL Azure is built on SQL Server 2008 and has all the same capabilities with the exception of BI and Reporting. And there is now direct connectivity to an Azure database because it is simply considered another data source.
- Managed Self-Service Business Intelligence: Empowering business users to get to analyse information using tools they are already very familiar with Excel and SharePoint.
Managed Self-Service BI
Among other things, SQL Server 2008 R2 is a BI platform. Designed to support the needs of the BI analyst, or power user, it offers connections to some tools you are already using everyday.
PowerPivot for Excel
An add-in for Excel, PowerPivot is built on the SQL Server Analysis Service Engine Vertipaq. Vertipaq enables faster processing for PowerPivot datasets (Excel can support up to 1 million rows of data) in both a SharePoint farm and for offline access. Excel worksheets, using PivotTables, PivotCharts, filters and Slicers are used to represent your PowerPivot data.
PowerPivot for Excel
Additional new features include:
Relationship Definition Tool:
- Relate columns in different data sources, offering the ability to create multi-dimensional data source.
- Data Feeds: Data feeds come from URL addressable data sources and can be one-time feeds or on-going.
- Table Wizard: Pull in data from other sources either by cutting and pasting, using external data sources or predefined connection objects.
Reporting Services 3.0 for Excel
Report Builder 2.0 offered you the Office 2010 look and feel, but 3.0 offers you more: Grab and Go reporting. To start there is a report part Gallery where you can access report parts that have already been built allowing you to create mashups of data for new reports.
Report Builder 3.0
You can also use a PowerPivot Excel Workbook as the data source for a new report — it must be published in the PowerPivot Gallery Library. Another nice feature is the ability to take existing reports created in other versions of SQL Server and move them into Report Builder 3.0. You can break these reports into report parts and save them in the Gallery.
PowerPivot for SharePoint
If SharePoint is a primary collaboration tool for your organisation then you will like how it can support BI activities. A new PowerPivot Gallery provides an interface that allows you to view an image of a workbook and see what's in it without having to open the workbook itself.
You can also schedule regular automatic data refreshes for workbooks so you know the data is always up to date. There's also a status and history view to see when the data has been updated.
PowerPivot for SharePoint
And for those who don't want to deal with a separate authentication method, PowerPivot workbooks uses the same authentication and authorization methods as SharePoint.
Reporting Services 3.0 for SharePoint
A Report Viewer Web Part let's you easily view and move through reports directly within SharePoint. You can even use a SharePoint list as a data source for a report. Also new is the ability to view report server items from more than one SharePoint zone (internet, extranet, intranet, default or custom).
It's important to note here that connection to SharePoint enables people who don't have or need access to Reports Services or PowerPivot directly to view reports and workbooks.
SharePoint also has an Operations Dashboard that let's Administrators see what workbooks and reports are used the most. If needed they can then take under their wing support for those more widely used workbooks and reports ensuring they are secure and available to those who do have access.
Empowering the Business User
We've really only scratched the surface of the new BI capabilities with SQL Server 2008 R2. BI activities are becoming a primary activities of power users and business analyst and they need access to tools that make their job easier and faster to do. You can learn more about how SQL Server 2008 R2 meets those needs and more by checking out their digital tour website.
Socialize with us