Business Intelligence Use Case Scenarios
by Bill English, Ben Curry
Microsoft® Office SharePoint® Server 2007 Best Practices
- Microsoft® Office SharePoint® Server 2007 Best Practices
- SPECIAL OFFER: Upgrade this ebook with O’Reilly
- A Note Regarding Supplemental Files
- Acknowledgments
- Introduction
- I. Planning and Designing
- 1. Introducing Microsoft Office SharePoint Server 2007
- 2. Change, Power, and Conflict
- 3. SharePoint Server 2007 Design Life Cycle
- 4. Defining Business Requirements
- Requirements
- Bridging the Gap Between Business Need and Technology Solution
- Characteristics of Good Requirements
- Implementing Requirements Traceability and a Requirements Matrix
- How Many Requirements per Project?
- Using Requirements to Solve Problems
- Negotiating Service Level Agreements
- Summary
- Additional Resources
- 5. SharePoint Server 2007 and Governance
- Governance Best Practices
- Fit the Organization’s Existing Workflow and Culture
- Keep Technology Aligned with Business Objectives
- Define and Manage the Organization’s High-Level Information Taxonomy
- Simple Is Beautiful in the World of Taxonomies
- Keep the Organization Aware of the Financial and Performance Impacts of Its Technology Decisions
- Balance Long-Term and Short-Term Views When Making Technology Decisions
- Encourage Excellence and Innovation
- Guide Through Merit and Service
- Handle Questions and Issues Quickly, Concisely, and Effectively
- Maintain a Technology-Agnostic Viewpoint
- Start Small and Grow Over Time, Intentionally
- Standardize Enterprise-Wide Information with Minimal Intrusion
- Getting a Technology Governance Team Started
- Create an Effective Governance Team Site
- Summary
- Additional Resources
- Governance Best Practices
- 6. Project Plans for a SharePoint Server 2007 Deployment
- Understanding Microsoft’s SharePoint Server 2007 Deployment Plan
- The Envisioning Stage
- The Planning Stage
- Assemble Project Teams and Define Roles
- Review Technical Requirements
- Review Preliminary End-User and Business Requirements
- Determine Preliminary Design Objectives
- Identify Coexistence Strategies
- Establish Test Lab Environment
- Perform Risk Analysis
- Define Communication Strategy
- Define Education Strategy
- Review Client Hardware and Software
- Create Governance Plan with Mission, Vision, and Strategy
- Plan Server Configuration
- Plan Security
- Plan for Performance
- Plan Failover and Disaster Recovery
- Plan for Localization
- Plan Integration
- Plan Maintenance
- Plan Content and Navigation Structure
- Deployment, Implementation, and Configuration Management
- Post-Implementation Operations, Optimization, and Business Review
- Summary
- Additional Resources
- 7. Developing an Information Architecture
- II. Building
- 8. Document Management
- What Is a Document?
- What Is Document Management?
- The Document Life Cycle
- Should SharePoint Replace File Servers?
- Other Best Practices Concerning Documents and Document Libraries
- Working with the SharePoint Server 2007 DoD 5015.2 Add-On Pack
- Summary
- Additional Resources
- 9. Enterprise Content Management
- 10. Business Processes and Workflows
- Identifying Workflow Candidates
- Overview of Out-of-the-box Workflows
- Publishing Workflows
- Workflow Deployment Considerations
- The Other Side of the Coin: Code-free Custom Workflows
- Custom Workflow Considerations
- Workflow Deployment Options
- Workflows and Client Applications
- Security Considerations
- Summary
- Additional Resources
- 11. Branding and Customization
- 12. Web Parts, Features, and Solutions Management
- Content and Infrastructure
- Developer’s Role in SharePoint
- Environments
- Web Parts
- Web Part Infrastructure
- Web Part Manager
- Web Part Zone
- Editor Zone and Tool Parts
- Web Parts
- ASP.NET and Windows SharePoint Services 3.0 Web Parts
- Legacy and ASP.NET Web Parts
- Web Part Connections
- Web Parts with User Controls
- Web Part Verbs
- Customization and Personalization with Web Parts
- Web Part Execution Environments
- Resource Locations
- Features
- Solutions
- Sample Web Part (Available Online)
- Summary
- Additional Resources
- 13. Creating and Managing Publishing Sites
- 14. Understanding and Implementing Microsoft Search Server 2008
- Search Server 2008 Features and Benefits
- Understanding OpenSearch Standards
- OpenSearch Description Documents
- OpenSearchDescription Element
- ShortName Element
- LongName Element
- Description Element
- URL Element
- Contact Element
- Tags Element
- Image Element
- Query Element
- Developer Element
- Attribution Element
- SyndicationRight Element
- AdultContent Element
- Language Element
- InputEncoding Element
- OutputEncoding Element
- AutoDiscovery of RSS/Atom
- OpenSearch Response Elements
- totalResults Element
- startIndex Element
- itemsPerPage Element
- OpenSearch Description Documents
- Installing Search Server 2008
- Building Federated Location Definition Files and Integrating Search Server 2008 with Live Search
- Best Practices for Implementing Search Server 2008
- Summary
- Additional Resources
- 8. Document Management
- III. Deploying
- 15. Implementing an Optimal Search and Findability Topology
- Findability: What Is It and Why Is It Important to You?
- Business Requirements and Search
- Designing Crawl and Query Topologies
- Scaling Out Your Index and Query Servers
- When to Use the Federated Query Features
- Scenario: You need to conserve bandwidth
- Scenario: You need to display the content in the results set, but the remote index’s robots.txt file blocks SharePoint’s crawler
- Scenario: You need to see results from a content source only when certain keywords and/or keyword patterns are entered by the user
- Scenario: Content at the content source changes very often, and those changes must immediately be reflected in the result set. You can crawl and index the content source
- Scenario: Content at the content source changes frequently, but those changes need not be reflected quickly in the result set. You can crawl and index the content source
- Scenario: You need to tightly define which URLs from the remote index will appear in the result set
- Scenario: You need to have queries executed under different security contexts to different remote indexes to receive security-trimmed results within the FLD’s result Web part
- Scenario: You need to have content in the result set that is not indexed locally at the remote site or server
- Scenario: The remote index is intermittently available when users execute queries. You are able to crawl and index the content source’s content
- Scenario: The remote index does not return results in RSS or Atom or the presentation of the results is not usable within the FLD Web part
- Scenario: The content needs to be available in the result set, but the query frequency of the content is not sufficient to justify the resources needed to crawl and index that content
- Scenario: You have more than 250,000 start addresses and/or 500 content sources that you need to include in the result set, and you do not want to or cannot build a second Shared Services Provider in
- Scenario: You have too many FLDs in your result set, and the usability of the result set interface has diminished significantly
- Scenario: Best Bet–type results have several content items and are easily displayed within either the Best Bet Web part or the FLD Web part
- Scenario: Scoping effects are needed on a remote content source that you are not crawling and indexing
- Scenario: You have created FLD files to federate queries in your SharePoint Server 2007 implementation, but the results are returned too slowly from the remote index and users are complaining
- Findability Tools in SharePoint Server 2007
- Summary
- Additional Resources
- 16. Leveraging Shared Services Providers
- 17. Optimizing Information Security
- 18. Business Intelligence and Reporting
- 19. Extending Business Intelligence
- Reporting Services
- Integrating Reporting Services with SharePoint
- Other Reporting Services Features Within SharePoint
- Security Considerations
- Creating, Publishing, and Deploying Reports to SharePoint Sites
- Distributed Server Environment Consideration
- PerformancePoint Server 2007
- Working with Dashboard Designer
- Security and Dashboard Designer
- Deploying Dashboards to SharePoint Sites
- Interacting with Dashboards Within SharePoint Sites
- Saving a Dashboard Designer Workspace
- Business Intelligence Use Case Scenarios
- Summary
- Additional Resources
- 20. Intranet, Extranet, and Internet Scenarios
- 15. Implementing an Optimal Search and Findability Topology
- IV. Operating
- 21. Data Protection, Recovery, and Availability
- 22. Upgrading from SharePoint Portal Server 2003 to SharePoint Server 2007
- Overview of the Four Migration Methods
- Pre-upgrade Tasks
- Upgrading SQL and Office Platforms
- SharePoint Tasks
- Deprecated Features
- Perform a Full Exam of Your SharePoint Portal Server 2003 Environment
- Decide Which Hardware You Will Use for Your SharePoint Server 2007 Implementation
- Upgrading from 32-Bit SharePoint Portal Server 2003 to 64-Bit SharePoint Server 2007
- Do You Need to Redo Your URL Topology in SharePoint Server 2007?
- Develop a Communication Plan to Inform Users and Management About Migration Activities
- Understand When and How to Use Prescan.exe
- Ensure Your SharePoint Portal Server 2003 and SQL Backups Are Working
- If You Choose to Perform a Gradual Upgrade, Ensure You Have Enough SQL Disk Space
- Be Sure that You Have Removed All Orphaned Objects from the SQL Database
- Increase the Web Site and ASP.NET Timeout Settings
- Plan for Broken Links
- Increase the SQL Transaction Log File Size
- Upgrading Customizations
- Post-upgrade Tasks
- Upgrading Shared Services
- Combining Migration Methods
- Upgrading Between Active Directory Forests
- When to Use the Different Upgrade Methods
- Summary
- Additional Resources
- 23. Capacity Planning and Performance Monitoring
- Glossary
- About the Authors
- Additional Resources for IT Professionals from Microsoft Press
- More Great Resources for IT Professionals from Microsoft Press
- Index
- SPECIAL OFFER: Upgrade this ebook with O’Reilly
Now that you’ve had a chance to understand the vast array of BI features—both those built into SharePoint Server 2007 and those additional BI products that can be integrated into SharePoint Server 2007 for enhanced data analysis and reporting—let’s consider which features you should use in specific scenarios and how to use them properly.
Note
The following recommendations do not include programmable solutions, such as using the SharePoint Server 2007 SDK or other XML/Web service–based solutions.
Scenario I. I have existing Reporting Services reports I want to have exposed in SharePoint Server 2007.
How you do this depends on your existing SQL Server version. If you are running SQL Server 2005 Service Pack 2, then one option is to switch Reporting Services to SharePoint Integrated mode and install the Reporting Services add-in on each SharePoint Web front-end server. If you are running SQL Server 2000, then consider installing the Web parts included within the RSWebParts.cab.
Scenario II. I have an existing Access 2007 database I want to send to my SharePoint site.
One option would be to create a new SharePoint list from an existing Access 2007 database, using the External Data > Export to SharePoint List Wizard, but this creates complications when you have multiple tables and queries. A more viable option would be to upgrade the Access 2007 database to SQL Server 2005 and then use Reporting Services in either SharePoint Integrated or native mode to create and deploy reports to SharePoint.
Scenario III. I have a multiple worksheet Excel Workbook(s) and I want to have end-users update only certain sections of the workbook. I also want the data to be exposed in my SharePoint sites real-time, including charts.
This is an ideal-use case for PerformancePoint Server 2007.
Scenario IV. I want to do some additional reporting against my SharePoint lists.
It depends what you’re trying to report against—actuals, forecast? You could potentially use the built-in KPIs. You could export lists (or import lists) to Access 2007 and use Access as the front-end reporting tool. If you’re currently running SQL Server 2005 Reporting Services, then you could consider using a third-party tool such as Enesys.
Scenario V. I want to do some in-depth analysis based on my SQL Server 2005 Analysis Services and have the ability to expose that analysis within my SharePoint site.
You could use Excel 2007 to create an Office Data Connection (ODC) to Analysis Services and then create PivotTables and PivotCharts directly using Excel. If you’re running Excel Services, you could publish the workbook while maintaining an active link to Analysis Services, providing the ODC is added to the Trusted Data Connections library within Report Center.
Scenario VI. I want to create views of my database within my SharePoint site and also have the ability to search the imported data.
In this scenario, the Business Data Catalog (BDC) would be an ideal solution. Data imported via the BDC can be integrated into SharePoint search, and document libraries using BDC data columns.
A few years ago, if you had asked developers at Microsoft how to build a scorecard, they may have shrugged. Then Microsoft released the Business Scorecard Accelerator, a free technology mainly designed to showcase SQL Server Analysis Services.
The Business Scorecard Accelerator was so popular that Microsoft made a product out of it—Business Scorecard Manager (BSM) 2005. While BSM could pull data from ODBC data sources, its best buddy was still Analysis Services. With the right cube, you could throw together a scorecard in under a day. Of course, BSM still had its quirks.
After Microsoft bought ProClarity in 2006, the BI vision coalesced. BSM v2, ProClarity, and Biz# (a multidimensional planning application) were unified into PerformancePoint. At the same time, SharePoint Server 2007 has "KPI lists"—a special type of document list that can display collections of KPIs. This can be distracting as a type of "scorecard." SharePoint also adds Excel Services, which can allow users to build scorecards in Excel and display them inside SharePoint.
Inside Track: Scorecard Technologies Overview
Here is a brief overview of scorecard technologies and the pros and cons of using each technology.
SharePoint Server 2007 KPI Lists
Pros
Easy to use
Integrated with SharePoint Server 2007 (Web parts and lists)
Can show multiple data sources
Can show KPIs from SQL Server Analysis Services
Cons
Does not scale across the organization
Limited functionality
Basically, SharePoint Server 2007 KPI lists are a good way to pull KPI functionality on to a SharePoint page—surface some business intelligence on a portal page or shared site. They make a good companion to a BI effort, but this is not the way to start a BI or scorecard initiative. You will quickly be frustrated by some of the limited functionality (formatting, lack of drill down, and so on).
Business Scorecard Manager (BSM) 2005
Pros
Strategic
Scalable
Linked to analytic charts
Annotations
Alerts
Cons
Requires SQL Server 2000 Notification Services for alerts
Builder is quirky
Working with ODBC data sources is labor intensive
The best thing about BSM is how easy it is to set up a manual scorecard and publish it in SharePoint. Once you learn your way around the Builder, you can create a manual scorecard in a few hours. This is powerful because, in a scorecard initiative, putting an ad hoc scorecard on the Web can wake up stakeholders and get their attention while you start wiring it to back-end sources.
Having said that, wiring BSM to those back-end sources can be labor intensive. Each current value, target, and trend has to be wired as an independent query. Again, using SQL Server Analysis Services can make this far easier because dimensions automatically display across the scorecard.
Excel Services Scorecard
Pros
Easy to use
Ad hoc
Cons
No drill down
May not scale
No linked charts
Creating a scorecard in Excel and publishing it to Excel Services is a nice way to publish an ad hoc scorecard. However, it lacks the linked ad hoc charting and drill-down capabilities. It also may be difficult to maintain due to its nature as an Excel spreadsheet. (This is not meant as a limitation of Excel, but rather how Excel .xls files often invite sloppy processes.)
ProClarity Dashboard Server
ProClarity had a Dashboard Server Product, which is being discontinued.
PerformancePoint Monitoring
Pros
Designed for the enterprise
Annotations
Linked analytic reports
Drill down
Dashboard builder
Extensible
Cons
A more expensive option
No alerts
Honestly, PerformancePoint Dashboard and Scorecard Builder is awesome. The designer is drag-and-drop, and it is intuitive. It still works best with multidimensional data, but it can also show data from other data sources, such as SharePoint lists, Excel spreadsheets, ODBC data sources, and so on. In addition, PerformancePoint includes powerful analytics from ProClarity and the new planning engine for what-if and forecast modeling.
In conclusion, SharePoint KPI lists and Excel Services are good introductions to scorecards, but for real scorecard/dashboard/BI initiatives, PerformancePoint is the way to go.
Philo Janus, Senior Technology Specialist, Microsoft
-
No Comment