Flexibles Reporting mit Analysis Services und Reporting Services 2008

1. Flexibles Reporting mit Analysis Services und Reporting Services 2008 Meinrad Weiss Principal ConsultantTrivadis AG

2. Business Intelligence Managed Services Business Communication Application Development Training Integration – Application Performance Mngt. – Security Integration – Application Performance Management – Security Unternehmensdaten Bestehende IT Kunden Infrastruktur Technologiekompetenz: Microsoft, Oracle, IBM, Open Source

3. Trivadis Facts & Figures • Currently 13 locations with over 500 employees • Baden, Basel, Bern, Brugg, Lausanne, Zurich • Düsseldorf, Frankfurt, Freiburg, Hamburg, Munich, Stuttgart • Vienna • SinceJanuary 2008, the Trivadis Group has a qualifiedmajority of shares in MIK AG, based in Brugg • Financially independent and sustainably profitable • Key figures in 2007 • Consolidated incomeCHF 98 million / EUR 60 million • Services for more than 600 clients in over 1‘500 projects • Over 125 Service Level Agreements • More than 5'000 training participants per year • Research budget: CHF 6.0 million / EUR 3.6 million

4. Agenda Layouting &Controls Web Data& Logic Rendering& Delivery SharePoint CustomApplication Report Server& Processing

5. Report Server & Processing • No IIS dependency • IIS is replace by native SQL Server components • Better scalability • Higher throughput • More/betteroutput formatsupport • Word • CSV improvements Report Builder 1.0 Report Builder 2.0 Management Studio 3rd Party Applications Report Designer SharePoint v3 Configuration Tool Windows Service SOAP Report Manager URL Access Report Models Scheduling & Delivery Report Processing Database Maintenance Extensibility Platform ASP.NET App Domain Management Authentication WMI Memory Management Service Network Interface Service Plaform WMI Provider ReportingServices Databases SharePoint v3 Databases Configuration Files

6. Agenda Layouting &Controls Web Data& Logic Rendering& Delivery SharePoint CustomApplication Report Server& Processing

7. Two Report Designers Report Builder 2.0 BIDS Report Designer RDL*

8. Report Builder 2.0

9. BI Development Studio Globals Parameters Data Source & Dataset Group Task Pane

10. Improved Textbox • Textbox supports • Mixed formatting • Multiple bands of data • Embedded fields • Editing is similar to Word • Paragraphs • Textruns • Replaces complex string expressions =“Report rendered at " & Format(Globals!ExecutionTime, "dd.MM.yyyy") & “ by “

11. Introducing Tablix Tabl e+ Matr ix

12. Chart Examples Frame Effects Scale Breaks Drawing Styles

13. Chart Examples Multiple Legends Calculated Series Strip lines Multiple Chart Areas

14. Introducing Gauge • Gauge is a new data region in SQL Server Reporting Services 2008 • 2 Main Gauge Types • Radial and Linear • Scenarios • Typically bound to a single or a few data points (value, target, goal, etc.) • Perfect for Analysis Services KPIs • Stand-alone or embedded in a tablix (bullet graph)

15. BI DevStudio Feature Comparison VS Integration Office 12 Look and Feel Full RDL Support Shared Layout Surface Shared Dialogs Report Builder 2.0 Report Models Integrated Query and Layout Full Report Model Support Clickthrough Reports Limited RDL Support Report Builder 1.0

16. Agenda Layouting &Controls Web Data& Logic Rendering& Delivery SharePoint CustomApplication Report Server& Processing

17. Analysis Services Reporting Services AnalysisServices Relational DB Data, Aggregates & Logic Data • Simple Navigation • Speed • Central place for calculations MDX SQL

18. Simple Navigation/Selection Relational DB AnalysisServices

19. „Perf-Problem“ of relationaleData Warehouse Database • SELECT Sum(«Measure»)FROM «Source»WHERE «Slicers»GROUP BY «Dicers»

20. Central placeforcalculations Data, Aggregates & Logic • Calculations canbe consumedby many clients • RS, Excel, … • MDX is a very powerfullanguage • Fewer records must be transferred to the client • Calculations must be defined in each report • Client side programming language is good for simple calculations • All raw data must be transferred to the client

21. TopNversusTopCount SELECT {[Measures].[Internet Order Quantity]} ON COLUMNS ,NON EMPTY {[Customer].[Customer].[Customer].ALLMEMBERS} ON ROWS FROM [Adventure Works] 18‘485 rows =5

22. TopNversusTopCount SELECT {[Measures].[Internet Order Quantity]} ON COLUMNS ,TopCount([Customer].[Customer].[Customer].MEMBERS ,5 ,[Measures].[Internet Order Quantity]) ON ROWSFROM [Adventure Works] 5 rows Why notSQL ?

23. TopCount (2) • MDX has access to the full cube space • Filtering of members is independant of displaying them SELECT NON EMPTY {[Date].[Calendar Year].[Calendar Year].MEMBERS} ON COLUMNS ,NON EMPTY TopCount([Product].[Color].[Color].MEMBERS ,5 ,([Measures].[Order Quantity] ,[Date].[Calendar Year].&[2002]) ) ON ROWS FROM [Adventure Works] WHERE ([Measures].[Order Quantity])

24. The whole yard

25. Median One type of average, found by arranging the values in orderand then selecting the one in the middle. If the total number of values in the sample is even, then the median is the mean of the two middle numbers. The median is a useful number in cases where the distribution has very large extreme values which would otherwise skew the data. http://www.investorwords.com/3030/median.html Avg()

26. Median Dimvalues As System.Collections.ArrayList FunctionAddValue(newValue As Decimal) If (valuesisNothing) values = newSystem.Collections.ArrayList() End If values.Add(newValue) End Function FunctionGetMedian() As Decimal Dimcount As Integer = values.Count If (count > 0) values.Sort() GetMedian = values(count/2) End If End Function Hiddengroup =Code.AddValue(Fields!Order_Quantity.Value) Table in Table ="Median: " & Code.GetMedian() Code is too simple!

27. Median (MDX) WithMember [Measures].[MedianOrder_Quantity] as Median([Product].[Color].[Color].MEMBERS ,[Measures].[Order Quantity]) SELECT {[Measures].[Order Quantity] ,[Measures].[MedianOrder_Quantity]} ON COLUMNS , {[Product].[Color].[Color].MEMBERS} ON ROWS FROM [Adventure Works] Null isignored RS: Simple Table ="Median: " & First(Fields!MedianOrder_Quantity.Value ,"Categories")

28. Agenda Layouting &Controls Web Data& Logic Rendering& Delivery SharePoint CustomApplication Report Server& Processing

29. SSRS in SharePoint Integrated Mode • Since SQL Server 2005 Reporting Services SP2 can be integrated with Windows SharePoint Services to enable publishing, viewing, and management of Reporting Services reports • Single point of management (e.g. permissions) • Integrated User Experience for Reporting Services users

30. Report Viewer SharePoint Web Part • SharePoint Integration: • Report viewed directly from document libraries • Reports are secured using SharePoint security model • Can Create BI dashboards • Report Viewer web part integrates with other web parts • MOSS provides a filter web part • Programmatic interfaces • Allow filtering report parameters • Allow launching reports • Allow direct URL links to reports

31. Report Viewer (Server Mode) Report Server Your Application Report Viewer (Server Mode) Session RDL Report Processing Retrieve Data Rendering Publish RDL Design Report Data Source

32. Report Viewer (Local Mode) Your Application Report Viewer (Local Mode) Report Processing Rendering Embed Report Retrieve Data RDL Design Report Data Source

33. Mixed Mode Sample Server Mode Client Mode

34. Report Viewer • Integrates with Visual Studio • Supports Visual Studio data tools including data sources (XSD) and TableAdapter • Full Interactivity • Expand/Collapse, Drill through, Interactive sorting • Integrates with applications by providing event handlers • Local Mode supports Object Data Sources • Any Class with Public properties is a data source • I.e. No interface to implement or inherit from • Supply a collection (Array, List, IEnumerable, etc.) of instances of your class at runtime • Does not Support RDL 2008! • Wait for VS 2010

35. Conclusion • Reporting Services is a flexible, scalable reporting platform • Web and/or client based • Power user and professional development tools • Rich data visualization • Analysis services offers speed and calculations flexibility • Dimensions • Aggregates • MDX

36. Your Benefits today 30% 30% 3days • on SQL Server Trainings • on Application Builder (TAB) • TAB Pilot Project for free Limited Offer Only

37. Your MSDN resourcescheck out these websites, blogs & more! PresentationsTechDays: www.techdays.chMSDN Events: http://www.microsoft.com/switzerland/msdn/de/presentationfinder.mspxMSDN Webcasts: http://www.microsoft.com/switzerland/msdn/de/finder/default.mspx MSDN EventsMSDN Events: http://www.microsoft.com/switzerland/msdn/de/events/default.mspxSave the date: Tech•Ed 2009 Europe, 9-13 November 2009, Berlin MSDN Flash (our by weekly newsletter)Subscribe: http://www.microsoft.com/switzerland/msdn/de/flash.mspx MSDN Team BlogRSS: http://blogs.msdn.com/swiss_dpe_team/Default.aspx Developer User Groups & CommunitiesMobile Devices: http://www.pocketpc.ch/Microsoft Solutions User Group Switzerland: www.msugs.ch.NET Managed User Group of Switzerland: www.dotmugs.chFoxPro User Group Switzerland: www.fugs.ch

38. Your TechNet resourcescheck out these websites, blogs & more! PresentationsTechDays: www.techdays.ch TechNet EventsTechNet Events: http://technet.microsoft.com/de-ch/bb291010.aspx Save the date: Tech•Ed 2009 Europe, 9-13 November 2009, Berlin TechNet Flash (our by weekly newsletter)Subscribe: http://technet.microsoft.com/de-ch/bb898852.aspx Schweizer IT Professional und TechNet BlogRSS: http://blogs.technet.com/chitpro-de/ IT Professional User Groups & CommunitiesSwissITPro User Group: www.swissitpro.chNT Anwendergruppe Schweiz: www.nt-ag.chPASS (Professional Association for SQL Server): www.sqlpass.ch

39. Save the date for tech·days nextyear! 7. – 8. April 2010Congress Center Basel

40. Premium Sponsoring Partners Classic Sponsoring Partners Media Partner