SQL Server 2008 for Business Intelligence

1. SQL Server 2008 for Business Intelligence UTS Short Course

2. Recap • Reporting services • Building reports • Export to which formats? • Adding interactivity with ? • Building ad hoc reports • Tools to create and modify reports? • Processing • Local vs server side? • Report Administration • Creating scheduled reports • Performance - Caching • Security

3. SSRS - More on reports

4. The plan

5. Step by step to BI • Create Data Warehouse • Copy data to data warehouse • Create OLAP Cubes • Create Reports • Browse the cube • Do some Data Mining • Discovering a Relationship that was not obvious • Predict future events (e.g. targeting and forecasting)

6. Alternative cube browsers

7. Session 4: Cube Browsers • Overview • Microsoft Data Analyzer • Proclarity • Excel 2003/2007/2010 • Excel services • Thinslicer • Performance Point • Power Pivot

8. Microsoft Data Analyzer Data analysis software for Microsoft Office XP Stand alone app Part of Office XP suite Not updated beyond version 3.5

9. Microsoft Data Analyzer

10. ProClarity (now assimilated by MS)

11. ProClarity – Selection of Data & Charts • Drillable Charts e.g. Bar, Line, Pie, Area • Simple selection of data via Hierarchies • Auto-Highlighting for Readability

12. ProClarity - Decomposition • Analyse via the decomposition Tree

13. ProClarity – Performance Map • Use for showing relative performance & change between Dimensions

14. ProClarity – Perspective View • Allow for comparison of 2 measures against selected hierarchies • Good for detecting values outside the norm • Statistical Sliders

15. ProClarity – Other Features • Views • Briefing Books to share your analysis

16. Excel 2007 - Pivot Table • Connecting to an OLAP cube • General navigation of member information and cube data • Drilling down to details of reporting summaries • Venturing beyond the " X-Y " dimensions, and making the PivotTable Report truly multidimensional • Select formatting options for our Excel 2007 PivotTable Reports.

17. Pivot Sample

18. Excel 2007 – Pivot Table - Fields and Drop Zones

19. Excel Services • Publishes Excel pivot tables onto SharePoint • http://richardlees.com.au/sites/Demonstrations/Pages/NZCensus_Excel.aspx

21. ThinSlicer • http://richardlees.com.au/sites/Demonstrations/Pages/TS_Foodmart.aspx • Bought by Angry Koala in 2008 – www.angrykoala.com.au • Adhoc Cube Browser on the Web

22. ThinSlicer

23. Microsoft Performance Point • http://richardlees.com.au/sites/Demonstrations/Shared%20Documents/Windows%20Performance%20Monitor/Perfmon.aspx

27. Introducing the Dashboard Designer • ClickOnce application • Makes it easy to: • Connect to datasources and define time intelligence • Import KPIs • Create KPIs • Create Reports (Chart and Grid) • Create Scorecards • Create Dashboard

28. Connecting to our cube • Add a Data Connection

34. Add an Analysis Chart • I want to learn about my customers • I know most of our customers are from the U.S. but what other countries make up our customer base?

39. Which country should we target to grow our customer base?

42. Q:\ Which country should we target to grow our customer base? • A:\ Australia – we saw an awesome increase in customers in 2003 but that dropped sharply in 2004

43. What’s the health of our business? • Create a Scorecard

49. I think we’re doing OK... Not the best presentation of KPIs there, so let’s create our own...

50. Add a KPI