Transcript SharePoint MN Users Group - Minnesota SharePoint User Group

Welcome to the
Minnesota SharePoint
User Group
Agenda
• Quick Intro
• Announcements and News
• Business Intelligence in SharePoint 2007
• Excel Services
• Dashboards
• Key Performance Indicators (KPI’s)
• Reports
• Filters
• Other Microsoft BI Solutions
• Q&A
User Group Goal / Objectives
Develop and support a local community focused on
Microsoft SharePoint Technologies
• Educate user group members about SharePoint Technologies
• Transfer knowledge within the community
• Communicate best practices
• Introduce new products / solutions
Introductions – MNSPUG Sponsors
• Inetium (www.inetium.com)
• Twin Cites based Microsoft consulting company
• Microsoft Gold Certified Partner
• Practice Area focused on Information Worker Technologies
• New Horizons – Minnesota (www.newhorizonsmn.com)
• Microsoft Gold Certified Partner
• Training on many technologies
• Microsoft (www.microsoft.com)
• I’m guessing you’ve heard of them
www.sharepointmn.com
• Website for user group
• SharePoint resource documents
• SharePoint resource websites links
• RSS Feeds
• Meeting Schedule
• Past User Group Presentations
• Watch for changes soon! V3…?
www.sharepointmn.com
News and Announcements
• Public preview release of 2007 Microsoft Office System
• Server and client applications available for download (requires registration)
• More info at: http://www.microsoft.com/office/preview/default.mspx
• Beta Downloads  $1.50
+
=
Upcoming Schedule
• Next Meeting
• September 13th 9:00 AM to 11:30 AM
• Microsoft’s Bloomington Office
• Topics:
Web Content Management in SharePoint 2007
• Ongoing Schedule
• 2nd Wednesday of every month
• 9:00 to 11:30 am
• Microsoft’s Bloomington Office
SharePoint 2007 – Key Microsoft Investment Areas
• Business Intelligence
• Collaboration
• Search
• Business Forms / Processes
Business
Intelligence
• Portals
• Content Management
Business
Forms /
Processes
Collaboration
Platform
Services
Workspaces, Mgmt,
Security, Storage,
Topology, Site Model
Content
Management
Search
Portal
SQL Server 2005
The Foundation
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
Analyze
Report
Drill down, exception reports, root-cause
Detailed operational data Sharing
standardized reports
analysis, multidimensional analysis
Monitor
Dashboards, scorecards and alerts
Plan
Create plans, models and scenarios, which are then fed back into the monitoring layer and encoded
as targets and thresholds
?
Microsoft’s Business Intelligence Strategy
Improving organizations by
providing business insights to all
employees leading to better,
faster, more relevant decisions
•
•
•
Complete and integrated BI offering
Widespread delivery of intelligence
through Microsoft Office
Enterprise grade and affordable
SQL Server 2005 Analysis Services (SSAS)
•
•
•
•
•
OLAP Engine
Advanced business intelligence
• KPI/Perspectives
• Measures
• Dimensions
Web services
Data mining in the platform
Integrated developer tools
SQL Server 2005 Reporting Services (SSRS)
Introduced with
SQL Server 2000
Open, extensible enterprise reporting
solution
Report authoring,
management, delivery
Office System integration
Visual Studio .NET development
environment
SQL Server 2005 enhancements
Integration with SSAS, SSIS,
and management tools
Rich end-user reporting
SQL Server 2005 Integration Services (SSIS)
Extract, Transform and Load (ETL)
High performance
High scale
Rich development environment
Source control
Visual debugging of control
flow and data
Extensible
Custom tasks
Custom enumerations
Custom transformations
Custom data sources
SQL Server 2005 – 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
Business Intelligence in
SharePoint Server 2007
Microsoft SharePoint 2007 BI Server Investments
• Excel Services
• Share spreadsheets via zero-footprint browser experience
• Live, interactive Excel 2007 spreadsheets in SharePoint
• Only publish what you want, control versions
• Use Excel models in custom applications via programmatic access
• Dashboards & Web components
• Common filtering (“slicing”) across web parts
• KPIs, Excel workbooks, and SQL Reporting Services reports
• Easy to create and personalize dashboards
Microsoft SharePoint 2007 BI Server Investments
• Report Center Websites
• Out of the box website optimized for report access and management
• Consistent management of reports, spreadsheets, and data connection
• Supports integration with SQL Server 2005 Reporting Services
• Data Connection Libraries
• Centralize storage of important data connections
• Update many spreadsheets, solutions with 1 central update
• Maintain 1 version of the truth
SharePoint 2007 Excel Services – What is it?
• New server technology in
MOSS 2007
• 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
unnecessary proliferation of
Excel files
Excel Services – How does it work?
•
•
•
Excel 2007
•
•
•
Design and author
BI Abilities
Visualization
Publish
Spreadsheets
View and
Interact
Web rendering
Zero-footprint
Interactive
Browser
Export/Snapshot into Excel
Programmatic Access
Excel 2007
MOSS 2007
Protect, Share & Reuse Spreadsheets
•
•
•
Custom
applications
Spreadsheets stored in document libraries
Server calculation and rendering
External data retrieval and caching
•
•
•
•
•
Set & get spreadsheet values
Perform calculations
Retrieve full workbook file
Open snapshots
Open full spreadsheet
Excel Services Architecture
• Web front-end
• SharePoint UI
• HTML rendering + web services
• Application server
• Loads spreadsheets, refreshes data,
calculates
• Maintains state for interactivity
• File + query caches for performance
• Single box or multi-tier
• Independent scale-out
Excel Services
Business
Intelligence
Zero-footprint, interactive, browser access to spreadsheets
Table Formatting
Pivot Tables
KPIs from SQL
Graphs & Charts
Microsoft Confidential –
Presentation under NDA
Basic Example – Publishing a Spreadsheet
• End user creates spreadsheet
• Wants to share it with others online
• Publishes it to SharePoint
• Configures the view of the spreadsheet
Basic Example – Publishing a Spreadsheet – Step 1
• Create Spreadsheet in Excel 2007
Basic Example – Publishing a Spreadsheet – Step 2
• Step 2 – Publish to Excel Services in MOSS 2007
Basic Example – Publishing a Spreadsheet – Step 3
•Step 3 – Add Excel Web Access web part to display published workbook
Demo
Publishing an Excel 2007 file using
Excel Services
Using Parameters in Excel Services
• Allows publication of parameters in spreadsheet
• Publisher defines what to expose to end users for data
• Displays parameter pane
• All other cells are read only
Demo
Setting up Parameters
using Excel Services
Excel Services
for the Administrator
Excel Services Architecture
• Web front-end
• SharePoint UI
• HTML rendering + web services
• Application server
• Loads spreadsheets, refreshes data,
calculates
• Maintains state for interactivity
• File + query caches for performance
• Single box or multi-tier
• Independent scale-out
Configuring Excel Services
• Performed by the server administrator
• Activate the Excel Services Feature
• Edit Excel Services settings
• Configure Trusted File locations
Activating the Excel Services Feature
• Turn on or off in the Operations Page in the Global Configuration section
Starting the Excel Services Service
• Start the Excel Calculation Services Service
Excel Services Configuration
• Shared Service > Excel Services Settings
• Edit Excel Services Settings
• Trusted File Locations
• Trusted Data Connection Libraries
• Trusted Data Providers
• User Defined Function Assemblies
Excel Services Settings
• Configurable by administrator
• Security
• Load Balancing
• Session Management
• Memory Utilization
• Workbook Cache
• External Data
Adding a Trusted File Location
• Configurable by administrator
• Location
• Session Management
• Workbook Properties
• Calculation Behavior
• External Data
• User Defined Functions
Using More
Excel Services Features
Example – Spreadsheet w/ External Data
• End user creates spreadsheet
• Connects to external data (i.e. database, cube etc.)
• Only wants to show a piece of the file
Spreadsheet w/ External Data – Step 1
• Create Office Data Connection (ODC)
• Upload to SharePoint Data Connection Library
Spreadsheet w/ External Data – Step 2
• Connect Excel file to ODC created in step 1
• Build spreadsheet as desired (i.e. insert charts, pivot tables etc.)
Spreadsheet w/ External Data – Step 3
• Name chart
• Publish to Excel Services  Select only the chart
Spreadsheet w/ External Data – Step 4
• Add and configure the Excel Web Access (EWA) web part
Demo
Connecting to External data
with Excel Services
The Report Center
The Report Center – What is it?
• 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
Demo
Creating a Report Center Site
Key Performance Indicator (KPI) List
• List template
• Graphically displays KPI’s
• Use to track metrics
• Data can come from multiple
sources
• SharePoint Lists
• Excel Services
• SQL Analysis Services 2005
• Manually entered
• Only show problems filter
KPI Web Parts
• Key Performance Indicators
• Displays KPI’s from a KPI List on the site
• Can change icon
• KPI Details
• Displays a single KPI
Demo
Creating a New KPI
Dashboard Pages in the Report Center
• SharePoint page
• Create individual dashboards
• Contains a number of different parts
• Content Editor
• Contact Details
• Filter Actions
• Excel Web Access
• KPI
• Summary Link
Filters and Filter Actions Web Part
• Used to filter other parts on page
• Multiple filter types
• Authored List
• Business Data Catalog
• Current User
• Date
• Query String (URL)
• SharePoint List
• SSAS
• Text
Authored List Filter Part
• Allows user to enter a list of values
• Manually entered into configuration
• Good for hard coded situations
Business Data Catalog Filter
• Leverages the Business Data Catalog (BDC)
• Allows user to search by entity in BDC
• Good for filters requiring relational data
• Another example of putting business data to use
Current User Filter
• Leverages current logged in user
• Can utilize a user profile value for the current user
• Hidden on screen
• Good for web parts that need a context of ‘My’
• Example ‘My Orders’ would filter all orders in
another web part
Date Filter
• Allows user to specify a date
• Different types of defaults
• Specific date
• Offsets from today
Query String Filter
• Sends value of query string parameter to web part
• User can define the parameter to pull from
• Hidden except when in edit mode
• Good for passing context
SharePoint List Filter
• Pulls value from SharePoint list
• User can define the value field
• Good for using other lists in the site to drive filters
SSAS Filter
• Leverages SQL Server 2005 Analysis Services
• Can use shared data connections
• User selects dimension
• Great for reusing existing dimensions defined in
SSAS
Resources
•Microsoft Windows SharePoint Services Developer Center
http://msdn.microsoft.com/sharepoint/
•Developer Introduction to Workflows for Windows SharePoint Server V3 and
SharePoint Server 2007
http://msdn2.microsoft.com/en-us/library/ms406057.aspx
•Windows SharePoint Services v3: Workflow Developer Starter Kit for Windows
SharePoint Services v3
http://www.microsoft.com/downloads/details.aspx?FamilyID=5DDF902D-95B14640-B9E4-45440DC388D9&displaylang=en
•MSDN Webcast coming up:
July 18th: Developing SharePoint Workflows using VS 2005
http://msevents.microsoft.com/cui/webcasteventdetails.aspx?eventid=1032301595
&eventcategory=4&culture=en-us&countrycode=us
More Resources
• Windows
Workflow :
http://wf.netfx3.com/
• Community Created Activities:
http://wf.netfx3.com/files/14/default.aspx
Thanks for coming!