Implement Business Intelligence Solutions Using Excel Services

Course Duration – 3 days

PDF Download:

Overview
This is a 3-day course designed to provide students with the knowledge and skills to use Excel Services to create business intelligence (BI) applications. In this course, we cover the Excel Services components of SharePoint Server 2007. The course is designed for those who intend to design, implement, and manage an Excel Services deployment, and also build BI solutions using SharePoint Server 2007. We use the Enterprise license of the product since the BI features are available only with that license. The course’s primary focus is on the Excel Services components of the BI features in the SharePoint Server 2007 Enterprise version. Much of the information covered will assist in your deployment of Excel Services with SharePoint Server 2007.

Students learn through instructor presentations and hands-on labs.

Since the focus of this course is on Excel Services functionality, this course does not cover what is new in Excel 2007, nor does it broadly cover Microsoft Office SharePoint Server 2007.

VPrerequisites
Students taking this course should have the prerequisite knowledge or experience listed below.

• Basic knowledge of Office SharePoint Server 2007 Enterprise
• Some knowledge of administering Office SharePoint Server 2007
• Understanding of BI concepts such as Analysis Services, Key Performance Indicators (KPIs), and data analysis
• Implemented Microsoft Office Excel 2007 in your environment
• Working knowledge of Microsoft Office Excel 2007

Audience
This course is intended for:

• Current SharePoint Server 2007 administrators 
• Administrators who are experienced or have some knowledge of SharePoint Server 2007 
• Data analysts or business intelligence consultants 
• Business users 
• Experienced Excel 2007 users 
• Business managers and financial analysts who are interested in functionality of KPIs in reports and BI Dashboards 

Module 1: Excel Services Overview
This module covers several points including:

• Problems addressed by Excel Services 
• Explanation of what Excel Services is 
• How the main Excel Service components work 
• Different types of workbooks 
• Which Excel features are supported by Excel Services

Module 2: Configure Excel Services
This module walks you through the steps of setting up Excel Services on your server. These settings provide for granular control over the behavior of Excel Services.
Some of the settings you will learn are:

• Creating a Trusted File Location 
• Creating a Trusted Data Connection Library 
• Excel Services Setting Properties

Module 3: Security and Performance
This module reviews security and performance considerations for a Microsoft Office SharePoint Server 2007 deployment that includes Excel Services.
You will learn:

• Excel Services security overview 
• lan authentication models 
• Security threats 
• Performance optimization 
• Ways to scale up Excel Services 
• Load balancing 
• Resource requirements

Module 4: Capacity and Deployment Planning
This module discusses supported topologies and capacity planning. We consider topologies such as single-server, multiple servers, and extranet. In addition, we have an opportunity to discuss capacity planning for the Web front-end tier and application-server tier.

Module 5: Working with Excel Services
This module describes the steps needed to publish a workbook to the server(s) and then view it in the browser.
You will also learn:

• Ways to use Excel Services to control the workbook distribution 
• Functionality available when interacting with the workbook in the Excel Web Access browser view

Module 6: Analysis Services
In this module you learn how to use SQL Server Analysis Services (SSAS) 2005 as a source for data to render as Key Performance Indicators (KPIs). SSAS provides a premium data source for BI scenarios for both Excel client and Excel Services.
You will learn:

• The basics of SSAS 2005 
• How to leverage the use of SSAS 2005 with Excel Services 
• How to bring data together in dashboards

Module 7: Configure Report Center
In this module you learn the required components to install to integrate Reporting Services with SharePoint Server 2007.
You will learn:

• How Report Centre is another BI feature available in SharePoint Server 2007 
• How to configure Report Server SharePoint Integration

Module 8: Reporting Services
In this module you learn that the Report Center site contains a document library named Reports Library. We discuss how the Reports Library is tuned for storing and displaying BI reports such as Excel workbooks and reports built for SQL Reporting Services.
You will learn:

• How to create reports 
• How to access reports 
• How to manage reports 
• How to create dashboards with KPIs and filters

Module 9: Extend Excel Services
This module introduces extending Excel Services by using Excel Web Services and user-defined functions (UDFs). UDFs offer a great advantage by extending Excel’s capabilities, which include custom library functions and the ability to connect to indigenous data sources. Excel Web Services provides an even greater advantage because it allows a number of options for reusing business models contained in Excel spreadsheets.

Module 10: Business Intelligence Quality Assurance
In this module we discuss the importance of Quality Assurance (QA) and the role it plays within BI solutions.
You will learn:

• QA goals 
• Benefits and strategies to ensure a successful BI solution 
• Illustrate BI concepts and goals when delivering BI solutions with Excel Services