1 / 37

An i ntroduction to the Microsoft BI Stack

An i ntroduction to the Microsoft BI Stack. Dr. John Tunnicliffe Business Intelligence Consultant john@bovi.co.uk www.bovi.co.uk. Why are you here? I am here because. I am a geek too I live next door & just popped in My wife wanted me out of the house today

nile
Download Presentation

An i ntroduction to the Microsoft BI Stack

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. An introduction to the Microsoft BI Stack Dr. John Tunnicliffe Business Intelligence Consultant john@bovi.co.ukwww.bovi.co.uk

  2. Why are you here?I am here because... • I am a geek too • I live next door & just popped in • My wife wanted me out of the house today • I heard the sheep in Wales were good looking • I am looking for a new job • I want to get paid more • I want to add value to my organisation • I want to deliver the right information at the right time to the right people so they can act

  3. Agenda • Introduction • Analysis Services and Excel 2007 • Demo – building and browsing a cube • SSRS & Report Builder 2.0 • Demo – building a report with RB2 • SharePoint 2007 BI Features • PerformancePoint Services • Essential BI Books

  4. What is BI? “An interactive process for exploring and analysing structured, domain-specific information ... to discern business trends or patterns, thereby deriving insight and drawing conclusions.” Gartner

  5. SQL Server BI Components

  6. SharePoint BI Components

  7. Analysis Services OLAPHow it work? • Cube has to be ‘processed’ before any data appears • Processing reads data from relational data source • Takes a copy of the data and stored in proprietary format • Creates aggregations • Cube only gets new data when re-processed • Processing can be done at several levels / ways • Full OLAP database • Individual Dimension or Partition

  8. Typical Analysis Services ImplementationsData Warehouse Architecture

  9. Typical Analysis Services ImplementationsOperational System Architecture • Cube built on operational system • e.g. Finance / HR / CRM • Two processing models • Passive • Proactive caching • Hints and tips • Build a data-mart • Don’t build a historical cube • Heavy load?

  10. Analysis Services OLAPKey Concepts • Dimensions • Criteria by which user’s wish to sliced and dice the data • Measures / Facts • Numerical data • User-hierarchies • Provide drill-down paths • Member properties • Provide metadata about data • Drill-through • See underlying data Let’s just see it in action...

  11. Analysis ServicesHints and Tips • Get your facts right! • Don’t simply make your OLTP data into a fact • Focus facts on specific business problems • Get your data clean! • Start simple and build from there • Build cube on views • Create one schema per cube • Follow best practice hints • VS2008 shows hints while building cube • For SQL 2005 use Microsoft Best Practice Analyser 2.0

  12. Creating a Headcount FactSource Table

  13. Creating a Headcount FactModel 1

  14. Creating a Headcount FactModel 2 Let’s BUILD it...

  15. Reporting Services • Supports T-SQL & MDX data sources • Reports developed with Visual Studio (BIDS) • VS2005 for SQL Server 2005 • VS2008 for SQL Server 2008 • Ensure you have the latest service pack ! • Visual Studio & SQL Server (even on client) • Develop MDX in SSMS first!

  16. Report Builder 2.0 • Plug-in for SSRS 2008 • Did not ship with RTM • Requires separate download & installation on server • Change URL in Report Manager Site Settings • For user • ClickOnce deployment from Report Manager • Office 2007 Interface • Provides Ad-Hoc Reporting • Easy to use, especially with cubes Let’s SEE it...

  17. What is a dashboard? “Visual display of the most important information needed to achieve one or more objectives which fits entirely within a single computer screen so it can be monitored at a glance” Stephen Few, Information Dashboard Design

  18. Dashboard DesignExample dashboard

  19. Dashboard DesignExample dashboard

  20. SharePoint BI Components

  21. SharePoint BI ComponentsSSRS in Integrated Mode • Reports published to document library • User clicks ‘document’ and it runs report • Developer publishes to document library • Provides version control !! • Integrated security • Report Manager becomes redundant • “My Reports” feature not available • Report Viewer web part allows reports to be placed on any web page • Manage subscriptions • Subscriptions can be published to a SharePoint document library

  22. SharePoint BI ComponentsExcel Services • Excel 2007 only • Office Open XML (OOXML) file format • Publish menu option • Excel Calculation Servers (ECS) • Loads workbooks • Refreshes external data • Recalculates with ‘full fidelity’ • Excel Web Access (EWA) • DHTML web part for viewing Excel spreadsheets • Display named areas / objects • Excel Web Services (EWS) • Develop custom applications based on Excel workbooks

  23. SharePoint BI ComponentsData Connections Library • Publish ‘trusted’ connections • Central store for database connections • OLAP or relational connections • Fundamental to Excel Services • Must utilise ‘trusted’ connections in order for the ECS to refresh data

  24. SharePoint BI ComponentsKPI Web Part • Data sources • Manually entered data • SharePoint lists • Excel workbooks • KPIs published in OLAP Cube

  25. SharePoint BI ComponentsReport Center

  26. SharePoint BI ComponentsUseful non-BI features • Business Data Catalog (BDC) • Publish relational data • Document Library • Version control • Web Page web part • Links web part • Wiki

  27. PerformancePoint Services

  28. PerformancePoint Server 2007A short history • PerformancePoint Server 2007 • PPS Monitoring = Business Scorecard Manager 2005 • PPS Analytics = ProClarity • PPS Planning = new product • Product withdrawal announced Jan 2009 • Support for PPS Planning withdrawn April 2009 • Functionality of PPS Monitoring and Analytics to be rolled into SharePoint 2010 PerformancePoint Planning Nov 2007 – April 2009

  29. PerformancePoint Services • PerformancePoint Services = PPS Monitoring • Rebranded after product withdrawal in Jan 2009 • Get it now... • Free with SharePoint Enterprise Edition • Install PerformancePoint Monitoring • SP2 supports SQL Server 2008 / WS2008 • However, tricky installation • Get it in 2010... • Monitoring & Analytics functionality rolled into SharePoint 2010

  30. PerformancePoint ServicesDashboard Designer • Data Sources • Indicators • KPIs • Reports • Scorecards • Kaplan et.al. • Dashboards

  31. PerformancePoint ServicesExamples

  32. PerformancePoint ServicesExamples

  33. PerformancePoint ServicesExamples

  34. SharePoint BI Components

  35. Essential BI Books • General BI • Information Dashboard Design, Stephen Few • Building a Data Warehouse, Vincent Rainardi • PerformancePoint • PerformancePoint Monitoring, Nick Barclay • Analysis Services • Applied Microsoft Analysis Servers 2005 , Teo Lachev • SQL Server 2008 MDX Step by Step, Smith/Clay • Expert Cube Development, Chris Webbet. al. • Reporting Services • Applied Microsoft Reporting Services 2008, Teo Lachev • Integration Services • Microsoft Integration Services, Ken Hasleden

  36. Thank you SQLBits

More Related