Transcript Who is Synergy? - SynergyOnline.com

SharePoint 2007 Business Intelligence
SharePoint Business Intelligence
Randy Williams, MVP MOSS
Synergy Corporate Technologies
[email protected]
SharePoint 2007 Business Intelligence
Who is Synergy?
• Global company with offices in USA, UK, Australia and
Singapore
• Premier SharePoint consulting
– Architecture, Implementation, Development, Design
• SharePoint Training
–
–
–
–
Custom-crafted curriculum
Three, one-week technical (Master) courses
Three, one-day end-user courses
Offered through COMAT in Singapore
• http://www.synergyonline.com/sg
SharePoint 2007 Business Intelligence
Agenda
• What is Business Intelligence?
• Microsoft’s Business Intelligence Offering
– SQL Server 2005/2008
• SQL Server Reporting Services (SSRS)
• SQL Server Analysis Services (SSAS)
– SharePoint 2007
•
•
•
•
•
Excel Services
KPIs
Business Data Catalog (BDC)
Report Center
Dashboards
– PerformancePoint Server 2007
– Q&A
SharePoint 2007 Business Intelligence
What is Business Intelligence?
“A broad category of applications
and technologies for gathering,
storing, analyzing, sharing and
providing access to data to help
enterprise users make better
business decisions.”
– Gartner
SharePoint 2007 Business Intelligence
What is Business Intelligence?
SharePoint 2007 Business Intelligence
Why is Business Intelligence Growing in Importance?
• The amount of corporate data is doubling every 2-3 years
• Barriers of entry (costs/technology) are being removed
• Continued pressure on businesses to find efficiencies and
new market opportunities, client expectations
• More disparate data sources than ever before
CRM
Intelligence is data with
relevance and context
Call
Center
Marketing
Campaign
Mgmt
Internet
Financial/
Accounting
Inventory
Procurement
HR
SharePoint 2007 Business Intelligence
Key Business Drivers Behind BI Growth
• Allow business users the ability to query and write reports
• To simplify reporting across multiple transaction systems
• To store historical data longer than you can/would in
transaction system
SharePoint 2007 Business Intelligence
Business Intelligence Statistics
• Are you currently deploying BI in your organization?
Source: WiseAnalytics, 2008 survey
SharePoint 2007 Business Intelligence
Microsoft’s BI Strategy
Improving organizations by
providing business insights to all
employees leading to better,
faster, more relevant decisions
• Major investment area
• Leverage familiar tools such as the Office
System and SharePoint
• Built on top of SQL Server 2005 & 2008
• Performance Management is a key growth area
SharePoint 2007 Business Intelligence
Types of Business Intelligence
Reporting and
Analysis
• Analyze information with familiar tools
• Enabling end-users with ad hoc reporting capabilities
• Share reports and analysis over the web
Performance
Management
• Improve corporate performance
• Accelerate management decision making
• Enable collaborative analysis
Data
Warehousing
• Integrate information from disparate sources
• Store and manage vast amounts of detail data
• Analyze information with an integrated view of your
business
SharePoint 2007 Business Intelligence
Microsoft Tools for Business Intelligence
Reporting and
Analysis
• Reporting Services (SSRS)
• SharePoint Business Data Catalog (BDC)
• Excel 2007 / Excel Services
Performance
Management
• Excel 2007 / Excel Services
• Performance Point Server 2007
• SharePoint KPI’s
Data
Warehousing
• SQL Server 2005 / 2008
• Analysis Services (SSAS)
• Integration Services (SSIS)
SharePoint 2007 Business Intelligence
End User
Applications
Business
ProClarity Analytics
Scorecard PerformancePoint Server
2007
6
Manager 2005
Office SharePoint Server
2007
Office Excel
2007
SQL Server
SQL Server
Analysis
Integration Services
SQL Services
Server
BI
Platform
SQL Server
Reporting
2005Services
SQL Server 2008
SQL Server
SharePoint 2007 Business Intelligence
Data Warehousing Basics
SharePoint 2007 Business Intelligence
What is a Data Warehouse?
• A Data Warehouse is the main repository of an
organization's historical data, its corporate memory. It
contains the raw material of a company's business
intelligence system.
• It’s designed to store historical data
• It brings information together from multiple source
systems into a single data store.
• The data is stored using special techniques that are
optimized for analysis and reporting.
SharePoint 2007 Business Intelligence
Extraction, Transformation & Loading (ETL)
• The process of gathering data from the production
systems, cleansing it, validating it and moving it into the
Data Warehouse. This process can be considered part of
the Data Warehouse Infrastructure.
SharePoint 2007 Business Intelligence
Key Terms
• Cubes: An enhanced storage mechanism that allows an
end user to look at data from multiple perspectives. Think
about pivot tables.
• Facts: Numeric data we are evaluating (cost, count, etc.)
• Dimensions: Attributes like Geography, Marital Status,
Date/Time, etc. that will be used to summarize data.
SharePoint 2007 Business Intelligence
Data Warehouse
Corporate
Accounting
System
CRM/
Contacts
System
Human
Resource
/Payroll
System
Work Order
System
Forecasting/
Planning System
Excel, PDF,
DOC
ETL Process: Extract, Transform, & Load
Property
Management
System
Reporting
Platform
Reports
Cubes
Analysis
Platform
Data Elements
Ad hoc Queries
Business
Intelligence
Portal
End Users
Computing
Device
Facts/Dimension
Metadata
Performance
Management
Platform
Analytics/
Scorecards
SharePoint 2007 Business Intelligence
Microsoft SQL Server
SharePoint 2007 Business Intelligence
SQL Server 2005/08 – Business Intelligence
Integrate
• Data acquisition
from source systems
and integration
• Data transformation
and synthesis
Analyze
• Data enrichment,
with business logic,
hierarchical views
• Data discovery
via data mining
Report
• Data presentation
and distribution
• Data access
for the masses
SharePoint 2007 Business Intelligence
SQL Server Integration Services (SSIS)
• Extract Transform and Load (ETL)
–
–
–
–
Updated DTS environment
More trustworthy and reliable
Development environment
Many out-of-the-box transforms
• Extensible
–
–
–
–
Custom tasks
Custom enumerations
Custom transformations
Custom data sources
SharePoint 2007 Business Intelligence
SQL Server Integration Services (SSIS)
• Business Perspective
– Data in different systems needs
to be connected and related
– Reporting often requires looking
beyond more than one system
• IT Perspective
– Provides the glue to connect
back ends
SharePoint 2007 Business Intelligence
SQL Server Analysis Services (SSAS)
• OLAP engine / Cubes
• Advanced business intelligence
– KPI/Perspectives
– Custom/limited aggregations
and semi-additive measures
• Web services
• Data mining in the platform
• Integrated developer tools
SharePoint 2007 Business Intelligence
SQL Server Analysis Services (SSAS)
• Business Perspective
–
–
–
Multi-dimensional views of
information
Improved speed
Slicing and dicing
• IT Perspective
–
–
Stores predetermined aggregations
Reduces reporting load on
transactional systems
SharePoint 2007 Business Intelligence
Reporting Services
• Reporting solution
– Report authoring,
management, delivery
– Visual Studio .NET development
environment
– Report Builder
– Integrates with SharePoint
SharePoint 2007 Business Intelligence
Methods of Report Design
• Visual Studio .NET
– For developers
– Leverages .NET
• Report Builder
– End user reporting
– Ad-hoc
• PerformancePoint
– Best for OLAP
SharePoint 2007 Business Intelligence
Microsoft Office SharePoint Server
SharePoint 2007 Business Intelligence
Microsoft Office SharePoint Server 2007 (MOSS)
• Major component of
Office System 2007
• Web platform
• 6 major components
– Business Intelligence
– Business Process
– Content Management
– Search
– Portal
– Collaboration
SharePoint 2007 Business Intelligence
SharePoint as a Platform
• Single infrastructure
• Multiple scenarios
SharePoint 2007 Business Intelligence
Collaboration
Discussions
Calendars
E-Mail
Presence
Project Mgt
Offline
Content Mgt
Authoring
Approval
Web Publishing
Policy & Auditing
Rights Mgt
Retention
Multi-Lingual
Staging
Portal
MySites
Targeting
People Finding
Social Networking
Privacy
Profiles
Site Directory
Search
BPM
BI
Indexing
Relevance
Metadata
Alerts
Customizable UX
Rich\Web Forms
Biz Data Catalog
Data in Lists
LOB Actions
Single Sign-On
BizTalk Integ.
Excel Services
Report Center
KPIs
Dashboards
SQL RS\AS Integ.
Data Con. Library
Core Services
Management
Security
Storage
Topology
Site Model
APIs
Delegation
Provisioning
Monitoring
Staging
Rights\Roles
Pluggable Auth.
Per Item
Rights Trimming
Repository
Metadata
Versioning
Backup
Config. Mgmt.
Farm Services
Feature Policy
Extranet
Rendering
Templates
Navigation
Visual Blueprint
Fields\Forms
OM and SOAP
Events
Deployment
Web Parts | Personalization | Master Pages | Provider Framework (Navigation, Security…)
Database services
Search services
Operating System Services
Workflow services
SharePoint 2007 Business Intelligence
SharePoint Demo
SharePoint 2007 Business Intelligence
SharePoint 2007 Business Intelligence Features
•
•
•
•
•
•
Excel Services
Dashboards
Key Performance Indicators (KPI’s)
Filter Web Parts
Report Center/Report Library
Business Data Catalog (BDC)
SharePoint 2007 Business Intelligence
Excel 2007
• Richer formatting
• Improved pivot
• Data connectivity
SharePoint 2007 Business Intelligence
Excel Services – What is it?
•
•
•
•
•
New server technology in MOSS 2007 Enterprise
Load, calculate and display Excel Workbooks in MOSS 2007
Thin client view in browser
Published workbooks can connect to external data
Designed to cut down on proliferation of Excel files
SharePoint 2007 Business Intelligence
Excel Services – How does it work?
•
•
•
Excel 2007
•
•
•
Publish
Spreadsheets
View and
Interact
Design and author
BI Abilities
Visualization
Web rendering
Zero-footprint
Interactive
Browser
Export/Snapshot into Excel
Programmatic Access
Excel 2007
MOSS 2007
Protect, Share & Reuse Spreadsheets
•
•
•
Spreadsheets stored in document libraries
Server calculation and rendering
External data retrieval and caching
Custom
applications
•
•
•
•
•
Set & get spreadsheet values
Perform calculations
Retrieve full workbook file
Open snapshots
Open full spreadsheet
SharePoint 2007 Business Intelligence
Example – Publishing from Excel to Excel Services
• End user creates spreadsheet in Excel 2007
• Publishes it to SharePoint Excel Services
• Configures the view of the spreadsheet in SharePoint
SharePoint 2007 Business Intelligence
Example – Create spreadsheet in Excel 2007
SharePoint 2007 Business Intelligence
Example – Publish to Excel Services
SharePoint 2007 Business Intelligence
Example – Connect Excel Web Access Web Part
SharePoint 2007 Business Intelligence
Excel Services Demo
SharePoint 2007 Business Intelligence
The Report Center
•
SharePoint 2007 Site Template
•
Repository for reports
•
Includes many features
•
Sample KPI’s
•
Dashboard Pages
•
Excel Web Access
•
Report Library
•
Report Calendar
•
Filter web parts
SharePoint 2007 Business Intelligence
SSRS Reports in SharePoint
• Report Library
• SQL 2005 Service Pack 2, or
SQL Server 2008
• Report viewer web part
• Publish Reports to SharePoint
• Leverage features of SharePoint
– Versioning
– Workflow
– Etc.
SharePoint 2007 Business Intelligence
Reporting Services Demo
SharePoint 2007 Business Intelligence
Key Performance Indicator (KPI) List
•
•
•
•
List template
Graphically displays KPI’s
Use to track metrics
Data from multiple sources
–
–
–
–
SharePoint Lists
Excel Services
SQL Analysis Services 2005
Manually entered
SharePoint 2007 Business Intelligence
KPI Web Part
• Displays KPI’s in SharePoint
List 
Detail 
SharePoint 2007 Business Intelligence
KPI Demo
SharePoint 2007 Business Intelligence
Dashboard Pages in the Report Center
• SharePoint page
• Create individual dashboards
• Contains a number of different
web parts
• Content Editor
• Filter Actions
• Excel Web Access
• KPI
• Reporting Services
• Others
SharePoint 2007 Business Intelligence
Filtering Data in SharePoint
• Used to filter data
–
–
–
–
–
–
–
–
–
Multiple filter types
Authored List
Business Data Catalog
Current User
Date
Query String (URL)
SharePoint List
SSAS
Text
SharePoint 2007 Business Intelligence
Business Data Catalog
•
•
•
•
•
Expose Data to SharePoint
Custom Line of Business Data
Database or Web Services
Can be used as metadata
Automatically provides lookup functions
SharePoint 2007 Business Intelligence
SharePoint Business Data Catalog (BDC)
• Expose Data to
SharePoint
• Custom Line of
Business Data
• Database or Web
Services
• Can be used as
metadata
• Search Data
SharePoint 2007 Business Intelligence
Business Data Catalog Filter Part
• Leverages the Business Data Catalog (BDC)
• Allows user to search by entity in BDC
• Good for filters requiring relational data
SharePoint 2007 Business Intelligence
Business Data Catalog Demo
SharePoint 2007 Business Intelligence
Bringing it all together in SharePoint
Filters
Excel Services
KPI’s
SharePoint 2007 Business Intelligence
What is PerformancePoint Server 2007?
• Business analytics
• Financial reporting
• Consolidation
• Planning
• Budgeting
• Forecasting
• Scorecard management
SharePoint 2007 Business Intelligence
How is PPS Structured?
• Planning Business Modeler
• Excel Add-in
• Dashboard Designer
SharePoint 2007 Business Intelligence
Latest Update on PPS
• Merging with MOSS Enterprise
• Customers with MOSS Enterprise license can now
get PPS for free
• Stand-alone product no longer available for purchase
SharePoint 2007 Business Intelligence
Performance Point Demo
SharePoint 2007 Business Intelligence
Q&A
SharePoint 2007 Business Intelligence
Resources
•
•
•
•
•
•
•
Microsoft Business Intelligence Resources: http://www.microsoft.com/bi/
Microsoft SQL Server Data Mining Add-Ins for Office 2007:
http://tinyurl.com/qldrdw
PerformancePoint Server 2007: http://tinyurl.com/y7nbjn
Everything You Need to Know About Business Data Catalog (SharePoint
Magazine articles): http://sharepointmagazine.net/author/randywilliams
SQL Server 2008 Business Intelligence: http://tinyurl.com/pamdvv
Microsoft SQL Server 2008 Reporting Services Add-in for Microsoft
SharePoint Technologies: http://tinyurl.com/p3l4hu
Microsoft SQL Server Reporting Services (SSRS) - Installation/Configuration
Guide for SharePoint Integration Mode (file download):
http://tinyurl.com/cxxkdn
SharePoint 2007 Business Intelligence
Thank You