By Rick Kight and Charles Wieland, Quilogy, Inc.
The Microsoft® SQL Server™ 2000 Report Pack for Microsoft Office SharePoint® Portal Server 2003 is a set of predefined report definition files that work with a sample database of information extracted from a SharePoint Portal Server environment. You can download the Report Pack from the Microsoft Download Center.
You can populate this database with data from your own SharePoint Portal Server environment by using the Data Extraction Program (DEP) that is included in the report pack. The DEP can read the SharePoint Portals Server data by using the object model and can also parse the Microsoft Windows® SharePoint Services logs. You can install Log Parser to parse the Microsoft Internet Information Services (IIS) logs and add that data to the database.
You can also use the sample reports as templates for designing new reports. The "Report pack components" section later in this topic describes the sample reports that are provided in this report pack.
Overview
This guide provides a high-level overview of the Report Pack for SharePoint Portal Server including installation and description of the components contained in the Report Pack. It is meant to provide you with sufficient guidelines to install and configure the Report Pack.
This document assumes that you have some familiarity with the following topics:
- Microsoft SQL Server 2000
- Microsoft Internet Information Server (IIS)
- Microsoft Windows SharePoint Services
- Microsoft SharePoint Portal Server
- Microsoft SQL Server 2000 Reporting Services
- Deployment scenarios for Microsoft SharePoint Portal Server including medium and large farms
Report pack components
To understand this guide, you should familiarize yourself with the main components of the Report Pack.
The three main components of the Report Pack — the reports, the databases, and the data extraction program — are shown in following tables.
Table 1: Reports
| Area |
Report name |
Description |
| Storage |
Storage Report |
Shows a listing of the virtual servers and the number of collections, sites, areas, lists, files and size. There is also a size distribution and storage usage chart, and a top 20 sites based on size. You can click through the virtual server to go to the Storage Trend Report. |
| Storage Trend Report |
Shows four charts illustrating the virtual server storage trend, site collection growth trend, area growth trend and list growth trend. |
| Hit Trends |
Site Trend Report |
Shows hit counts for virtual servers, collections, areas and lists. Also shown are the top 20 sites based on hits. You can click through the virtual server to go to the Comprehensive Site Collections Report. |
| Comprehensive Site Collections Report |
Shows the list of site collections, who owns the collection, configurable characteristics about the owner and the date the collection was last accessed. You can click on a site collection to go to the Detailed Site Collection Report. |
| Detailed Site Collection Report |
Shows top 20 pages accessed (based on hit count) for this site collection. You can click on a page to go to the Detailed Page Report. |
| Detailed Page Report |
Shows users who have access this page, when they last accessed it, any referrer URL and number of hits. Also shown are two charts illustrating user distribution and referrer distribution. |
| Search |
Best Bet Keyword |
Shows top 20, top 10, bottom 10 or bottom 20 keywords used for searching. It also shows which keywords have Best Bets. |
| Search Terms |
Shows top 20, top 10, bottom 10 or bottom 20 search terms used for searching. It also shows which search terms match a defined keyword. |
Table 2: Databases
| Type |
Database name |
Description |
| Staging |
dbSPSReportingStaging |
This database is a relational database. Its purpose is to hold the latest data temporarily until it can be transformed into the Reporting database. Typically the Data Extraction Program (DEP) is run daily and the data is placed in the Staging database. Once the data is loaded the transformation process occurs moving the data into the Reporting database. |
| Reporting |
dbSPSReporting |
This database is structured using a star schema design. It contains dimension and fact tables in order to hold data about the SharePoint environment. Over time this design will help in minimizing the storage needs. This database also contains the stored procedures that transform the data in the Staging database to Reporting. |
The third component is the Data Extraction Program (DEP). The DEP is the primary workhorse for the Report Pack and is responsible for loading the data needed for reporting. Data used for reporting comes from three data sources: SharePoint Portal Server databases, Windows SharePoint Services logs, and IIS logs. The DEP first loads the data into the Staging database in the order of IIS logs, Windows SharePoint Point Services logs, and SharePoint Portal Server data. As logs are processed the DEP deletes them. This is why it is recommended to copy the logs from the source server to a directory on the server that the DEP is running. This allows the DEP to restart if needed and resume with the next log file, plus this reduces the risk of the original log file being corrupted. To execute more efficiently the DEP also uses the LogParser program to load the data. Table 3 describes the data sources the DEP pulls from.
Table 3: DEP data sources
| Data source |
Loaded by |
Description |
| SharePoint Portal Server data |
DEP |
Ideally, the DEP will be run daily. This will allow the Reporting database to hold a daily snapshot of the SharePoint Portal Server data. In order to get the SharePoint Portal Server data the DEP leverages the SharePoint Portal Server Object Model and Web Services. Over 90 percent of the data is attained via the Object Model. The data retrieved includes properties about:
- Servers
- Virtual directories
- SharePoint Portal Server databases
- Site collections
- Sites
- Webs
- Document libraries
- Lists
- Files
The Web Services are used to pull information that cannot be attained via the Object Model. This would include properties from SharePoint Portal Server user profiles |
| Windows SharePoint Services logs |
DEP |
The Windows SharePoint Services logs contain information about what users have viewed within the SharePoint Portal Server environment. The primary use of this data is to determine “hits” within the SharePoint Portal Server environment. The data pulled from the Windows SharePoint Services logs includes:
- Date and time of entry
- User account
- Site collection GUID
- Site/area name
- List name
- Document name (file)
- Referrer URL
- Relative URL
|
| IIS logs |
Log Parser (via DEP) |
The IIS Logs contain information on what kind of searches users are performing within SharePoint Portal Server. To more efficiently read and load the IIS Logs the DEP leverages the Log Parser tool. The data pulled from the IIS logs includes:
- Search terms
- Date and time of search
The DEP executes the LogParser and instructs it what file to load, what data to pull and what database to write it to. It applies a filter to the LogParser to exclude a "robot" searches which are searches performed by SharePoint Portal Server services.
|
Figure 1: Report Pack architecture
United Kingdom
Printer-Friendly Version
