Download - SharePoint Saturday Los Angeles 2011 SharePoint 2010 as The Business Intelligence Platform
Exposing Business Intelligence with SharePoint 2010
Ivan SandersSharePoint MVP Developer, EvangelistDimension [email protected]
Agenda
Configuration of SharePoint 2010Business Intelligence (BI) PrimerBI Tools in SharePoint 2010Excel ServicesVisio ServicesPerformancePoint ServicesReporting ServicesPutting it all TogetherQA
COMMON SHAREPOINT ISSUES
Codeplex
SharePoint 2010 AutoSPInstallerSearch.StandardMasterPageAdapter.wspSharePointManagerSharePointSolutionInstaller
Preparing the SharePoint 2010 Environment
demo
BUSINESS INTELLIGENCE (BI) PRIMER
Business Intelligence
What is Business Intelligence?AnalyticsOLAPData MiningBenchmarkingReporting
Information decision makers need in order to make effective business critical decisions.
BI TOOLS IN SHAREPOINT 2010
Microsoft SharePoint 2010 The business collaboration platform for the enterprise and the web
All other things being equal, more usage, as measured by number of page views & searches reflects more satisfied users.
EXCEL SERVICES
What is Excel Services?
Real-time, interactive, end-user BI reporting toolDisplays all or targeted elements of Excel workbooks through the browserExtends Excel capabilities such as external data connectivity and Excel based modeling to the browser
Application Development platform for building data-centric applications
Automate workbooks via Web Services APIProvide highly interactive application via JSOMEasily distribute Excel content via REST
http://server/_vti_bin/ExcelRest.aspx/Shared%20Documents/File.xlsx/Model/Ranges(‘Sheet!A1’)
Handler – the ASPX that handles all Excel Services REST requests
Name of the file as you would access it in SharePoint
Path inside the workbook.
Resource Name Allows access to…
/Model The workbook (the binary or the available feeds)
/Model/Ranges List of named ranges in a workbook (some of them)
/Model/Ranges(‘RangeName’) Specific range.
/Model/Charts List of charts
/Model/Charts(‘ChartName’) Specific Chart
/Model/Tables List of tables (Lists)
/Model/Tables(‘TableName’) Specific table
/Model/PivotTables List of PivotTables
/Model/PivotTables(‘PivotName’)
Specific PivotTable
REST Syntax
Excel Services
Demo
VISIO SERVICES
Visio Services Key FeaturesShare Diagrams in SharePoint• Visualize your business intelligence with Visio diagrams• Diagrams can be viewed without having Visio client
installed• Full Fidelity rendering with Silverlight, or PNG
Connect Diagrams to Data• Use data graphics to build front ends for your real-time
business intelligence solutions• Diagrams can be connected to various Data sources
Build Dashboards• Use Web Part Connections to integrate with other
SharePoint web parts• Use the Mashup API to fully customize your dashboards and
manipulate data
Visio Services features
RenderingBrowser-agnosticFull Fidelity rendering using Down-level experience in PNG
Visual Refresh of Data DiagramsRefresh data connected diagrams to:
Supports Kerberos, SSO & Unattended Auth.Automatic refresh
OLEDB ODBCSP
ListsSQL Excel
Visio Services, At a Glance“Data-Driven Diagrams in SharePoint”
Data Source
1 hour later… Top Features:Diagrams all live in
SharePoint Diagrams viewable in the
browserDiagrams created once
onlyDiagrams containing data
graphics are refreshableDiagrams give data
context and improve insights into the state of a system
Visio Services
demo
PERFORMANCEPOINT SERVICES
PerformancePoint Services
Context-driven dashboards across systems
Provides transparency and accountabilityInteractive access using browserCan be created/updated by power-users/professionals
Designing PPS Applications
Dashboard designer used to create content
ClickOnce application launched from browserManages content directly in SharePoint
Lists for content, data sources, dashboards
PPS Content in the Browser
Business users view content in the browser
Stored as Web Part pagesWeb Part connections used to relate content
Ex. Item choice in a scorecard updates reports
Visual Data Exploration
Decomposition trees allow data explorationNew in SharePoint 2010Available by right clicking data in browser
Analyze -> Decomposition Tree
Allows filtering\expanding across dimensionsAllows faster and more relevant visualization of data
PerformancePoint
demo
SQL Server 2008 R2Reporting Services & PowerPivot
Reporting servicesDoes not ship with SharePoint 2010
Reporting Services Web Parts can be installed as add-on to SharePoint
Used to create professional reports for BI apps
Includes reports builder for modifying reports
PowerPivotUsed either for client or server side excel filesMakes it possible to work with an extremely large scale of data from multiple, heterogeneous data sources at one time
Reporting Services
demo
What are Dashboards?
Visual displays of information needed to achieve one or more objectives*Single-Screen display of informationShould answer fundamental questionsAlert the user to issues or problemsOperational, Performance, Personal
What are Scorecards?
Elements of a dashboardAlign strategies and organizational goalsMeasure and manage Key Performance Indicators (KPI)Modeled after the business, not the data
BI Center Site Template
Evolution of the Reporting Center template
Predefined lists for PPS, Excel ServicesStarting point for BI
BI functionalities can be also used in other sites as well
BI Indexing Connector
Add-in to FAST Search Server for SharePointQuery and Crawl XLSX/XSLM and RDL reports (2005 and higher)Improve relevance and search across all documentsDedicated Reports tab
SharePoint 2010 BI Web Parts
Simple web parts that provide visualization
Quick summary of dataCan access multiple data sources
Other Web PartsLists and External Lists Excel Services
Fixed ValuesLists and External ListsExcel ServicesSQL Analysis Services
Putting it All Togther
demo
Summary
CodeplexBusiness Intelligence (BI) PrimerBI Tools in SharePoint 2010Excel ServicesVisio ServicesPerformancePoint ServicesReporting ServicesPutting it all Together with Dashboards
Don’t ForgetSharePint!!
Join us at 6:30 pm at The Daily Pint for a free drink, kindly
provided by Rackspace! A great opportunity to network with other
SharePoint Professionals!
Please complete your Speaker Evaluation and turn it in before you leave. Drop off at the Community table or at the Cafeteria
before closing.
Hyatt San Diego Manchester – SAN DIEGO, CADay 2 - May 3rd Creating Business Intelligence Solutions using SharePoint 2010Day 2 - May 3rd Overview of Enterprise Content Management in SharePoint 2010Day 2 - May 3rd Migrating SharePoint 2007 Solutions to SharePoint 2010 Grand Hyatt San Francisco – SAN FRANCISCO, CADay 2 - May 10th Creating Business Intelligence Solutions using SharePoint 2010Day 2 - May 10th Migrating SharePoint 2007 Solutions to SharePoint 2010 Starting next month I will be on SharePoint ShopTalk every other Thursday
Don’t forget to register for the SharePoint Conference 2011 in Anaheim 10/03 – 10/-06 http://www.mssharepointconference.com/Pages/default.aspx
Ivan SandersSharePoint MVP Developer, EvangelistDimension [email protected]
Who can you trust??The blogs I trust through all of the noise.
Maurice Prather http://www.bluedoglimited.com/default.aspx Andrew Connell http://www.andrewconnell.com/blog Spence Harbar http://www.harbar.net Jim DuncanHeather Solomon http://www.heathersolomon.com/blog Todd Klindt http://www.toddklindt.com/default.aspxTodd Baginskihttp://www.toddbaginski.com/blog Todd Bleeker http://bit.ly/edlSm5 Jan Tielens http://weblogs.asp.net/jan Patrick Tisseghem http://www.u2u.info/Blogs/Patrick/default.aspx Wictor Wilen http://www.wictorwilen.se Ted Patisson http://blog.tedpattison.net/default.aspx Lars Fastrup http://www.fastrup.net Carsten Keutmann http://keutmann.blogspot.comKeith Richie http://blog.krichie.comBill Baer http://blogs.technet.com/b/wbaer
Thanks to Our Sponsors
Performance
Addendum
TOP PERFORMANCE KILLERS
Search Search uses SQL in a very I/O intensive fashion. It is sensitive to I/O latencies on the TempDB and the Query and Crawl file groups. One of the more difficult and time consuming jobs for a Search Administrator is to schedule the Crawls so they are not over lapping while keeping Search results fresh
Indexing/Crawling Crawling and indexing a large volume of information, documents, and Web pages requires a large amount of computer processing. The crawl process also consumes network and other resources. The SharePoint environment must be configured properly and monitored, to ensure that the crawling and indexing process does not adversely affect the service available to users. For example, content is usually crawled and indexed during off-peak hours when servers are underused in order to maintain peak-hour services for users. Applications that may be crawling content in your production environment
Coveo Full and Incremental crawls to enable search Newsgator to Update all of the colleague information and RSS feeds DocAve for Reporting on and Performing SharePoint Management tasks WSS Search indexes the Help information provided with SharePointSharePoint Profile Import syncs people profileOffice Search Full and Incremental updates Coveo would replace
Top Performance Killers
Top Performance KillersProfile Import
Profile imports are used with NGES to sync your AD user details to provide access to your feed subscriptions and with SharePoint to sync your AD user details with your SharePoint User Profile
Large List Operations Having large lists by itself is not necessarily a performance issue. When SharePoint Server renders the many items in those lists, that can cause spikes in render times and database blocking. One way to mitigate large lists is to use subfolders and create a hierarchical structure where each folder or subfolder has no more than 3,000 items. Identify large lists and work with the owners of the sites and lists to archive items or pursue other mitigation strategies
Heavy User Operation List Import/Write Another scenario of users having power they don’t realize. Importing large lists using excel or synchronizing an access db. In SQL there’s little difference between these types of user operations.
Backup (SQL & Tape) Serious CPU and write disk I/O performance hit. SQL Litespeed or SQL 2008 backup with compression all help to lessen the performance hit.
SHAREPOINT PERFORMANCE
Database PerformanceDatabase Volumes
Separate database volumes into unique LUN’s consisting of unique physical disk spindles.Prioritize data among faster disks with ranking:
SQL TempDB data filesDatabase transaction log filesSearch databaseContent databases
In a heavily read-oriented portal site, prioritize data over logs.Separate out Search database transaction log from content database transaction logs.
Database PerformanceSQL TempDB Data Files
Recommended practice is that the number of data files allocated for TempDB should be equal to number of core CPU’s in SQL Server.TempDB data file sizes should be consistent across all data files.TempDB data files should be spread across unique LUN’s and separated from Content DB, Search DB, etc…TempDB Log file separated to unique LUN.Optimal TempDB data file sizes can be calculated using the following formula: [MAX DB SIZE (KB)] X [.25] / [# CORES] = DATA FILE SIZE (KB)Calculation result (starting size) should be roughly equal to 25% of the largest content or search DB.Use RAID 10; separate LUN from other database objects (content, search, etc…).“Autogrow” feature set to a fixed amount; if auto grow occurs, permanently increase TempDB size.
Content Databases100 content databases per Web application100GB per content database
CAUTION: Major DB locking issues reported in collaborative DM scenarios above 100GBNeed to ensure that you understand the issues based on number of users, usage profiles, etc…Service Level Agreement (SLA) requirements for backup and restore will also have an impact on this decision.KnowledgeLake Lab testing demonstrated SharePoint performance was NOT impacted by utilizing larger DB sizes; tests included content DB sizes that were 100GB, 150GB, 200GB, 250GB, 300GB and 350GB.
Database Performance
Content Databases - ContinuedPre-construct and pre-sizeScript generation of empty database objects“Autogrow” feature onUse RAID 5 or RAID 10 logical units
RAID 10 is the best choice when cost is not a concern. RAID 5 will be sufficient and will save on costs, since content databases tend to be more read intensive than write intensive.
Multi-core computer running SQL ServerPrimary file group could consist of a data file for each CPU core present in SQL Server. Move each data file to separate logical units consisting of unique physical disk spindles.
Database Performance
Search DatabasePre-construct and pre-sizeScript generation of empty database objects“Autogrow” feature onUse RAID 10 logical units
Should be a requirement for large-scale systemsSearch database is extremely read/write intensive
Multi-core computer running SQL ServerPrimary file group could consist of a data file for each CPU core present in SQL Server. Move each data file to separate logical units consisting of unique physical disk spindles.
Database Performance
Search DatabaseSearch database is VERY read/write intensive!Do not place any other database data files on any logical unit where search database files reside.If possible, try to ensure that the RAID 10 logical units for the search database data files do not share their physical spindles with other databases.Place the search database log files on an independent logical unit.
Database Performance
Database MaintenancePhysical Volume File Fragmentation:
Defragment your physical volumes on a regular schedule for increased performance!LUN’s need to be 20-50% larger than the data stored on them allow for effective defragmentation of the data files.
Performance Monitor Counters to watch:Average Disk Queue Length
Single Digit values are optimal.Occasional double-digit values aren’t a large concern.Sustained triple-digit values require attention.
Database Performance
Page PerformanceMinimize HTTP Requests
80% of the end-user response time is spent on the front-end. Most of this time is tied up in downloading all the components in the page: images, stylesheets, scripts, Flash, etc. Reducing the number of components in turn reduces the number of HTTP requests required to render the page. This is the key to faster pages.
For static components: implement "Never expire" policy by setting far future Expires header
Avoid RedirectsRedirects are accomplished using the 301 and 302 status codes. Here’s an example of the HTTP headers in a 301 response:
Optimize ImagesAfter a designer is done with creating the images for your web page, there are still some things you can try before you uploading the images to your web server
Avoid Empty Image srcImage with empty string src attribute occurs more than one will expect.
What's on the Flash drive
Extras
Extras
SharePoint 2007SharePointSolutionInstallerWSPBuilderKerberosBuddySharePoint SushiSharePointManager2007 / 2010SPViewPermissionSetting.wsp2007 Office SDK1.5SQL Scripts
SharePoint 2010AutoSPInstaller2010 DocumentationBusinessIntelligenceLabs2010 Tips and tricksSQL Scriptsnpp.5.8.6Search.StandardMasterPageAdapter.wsp