1 / 52

SQL Server 2012 for Business Intelligence

SQL Server 2012 for Business Intelligence. UTS Short Course. Mehmet Ozdemir – SA @ SSW. w: blog.ozdemir.id.au | e: mehmet @ssw.com.au | t: @ mozdemir_au. SQL Server, BI, Infrastructure Specializes in Application architecture and design SQL Performance Tuning and Optimization

rachel
Download Presentation

SQL Server 2012 for Business Intelligence

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. SQL Server 2012 for Business Intelligence UTS Short Course

  2. Mehmet Ozdemir – SA @ SSW w: blog.ozdemir.id.au | e: mehmet@ssw.com.au | t: @mozdemir_au • SQL Server, BI, Infrastructure • Specializes in • Application architecture and design • SQL Performance Tuning and Optimization • HyperV, SCVMM • Technology aficionado • Virtualization • Reporting/BI • Cubes

  3. Admin Stuff • Attendance • You initial sheet • Hands On Lab • You get me to initial sheet • Homework • Certificate • At end of 5 sessions • If I say if you have completed successfully 

  4. Course Website • http://sharepoint.ssw.com.au/Training/UTSSQL/Pages/default.aspx • Course Timetable • Course Materials

  5. Course Overview

  6. Last week(s) • Cubes • What is a cube? • Measures/Facts? • Dimensions? • Hierarchies? • Time Dimensions? • Cube Browser? • Why?

  7. Homework • Where does the cube live? • Why do we need to provide "Impersonation Information" in our Data Source? • What is a dimension hierarchy?

  8. Session 3: Tonight’s Agenda • What is SQL Reporting Services? • Steps in Creating a Report • Demo: Creating a Report • Hands on Lab

  9. Why are we doing this?

  10. Business Intelligence Defined Business intelligence (BI) is a broad category of applications and technologies for gathering, storing, analyzing, and providing access to data to help enterprise users make better business decisions.

  11. The plan

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

  13. SSRS

  14. What is SQL Server Reporting? • Reporting platform • Traditional • Interactive reports • Scalable and manageable server infrastructure • Integrated with • SharePoint • Office applications • Browser and other familiar tools • Single platform and tools for all types of structured data • Relational • Hierarchical • Multidimensional

  15. SQL Server BI Platform Reporting Services Development Tools Management Tools Analysis Services OLAP & Data Mining Integration Services ETL SQL Server Relational Engine

  16. Authoring Management Delivery Reporting Lifecycle • Reporting Services is an open and extensible platform supporting the authoring, management and delivery of rich, interactive reports to the entire enterprise.

  17. Report Authoring

  18. Report Authoring • Reports are defined in Report Definition Language (RDL), a documented XML schema • Use Microsoft or 3rd party tools that support RDL • Create single reports from multiple data sources(SQL, OLE DB, ODBC, Oracle, and .NET data providers) More info on the RDL spec: http://bit.ly/11lcd3n

  19. Report Authoring - Controls • Data regions • Tablix • Table • List (like Access) • Matrix • Chart • Gauge • Databar • Subreports • Images • Custom Controls • 3rd Party Controls

  20. Tablix • Generalized layout report item • Grouped and Detail data

  21. Tablix - Table • Use a table to display detail data • Organize the data in row groups, or both. • The Table template contains three columns with a table header row and a details row for data.

  22. Tablix - Matrix • Use a matrix to display aggregated data summaries • Grouped in rows and columns, similar to a PivotTable or crosstab. • The number of rows and columns for groups is determined by the number of unique values for each row and column groups

  23. Tablix - List • Use a list to create a free-form layout. You are not limited to a grid layout, but can place fields freely inside the list. • You can use a list to design a form for displaying many dataset fields or as a container to display multiple data regions side by side for grouped data.

  24. Charts

  25. Charts

  26. Charts

  27. Gauge

  28. Interactive reports

  29. Drill-through Reports • Start in Report Builder • Get the data you want • In report properties choose Allow users to drill to this report option • Optionally customize in Report Designer

  30. Interactivity • Document maps • Collapse / Expand • Actions

  31. Report Management

  32. Report Management • Report definitions, folders, and resources are published and managed in a reporting web service • Managed reports can be executed either on-demand or via schedule and can be cached for consistency and performance • Scalable & Extensible server architecture

  33. SQL Server Reporting Architecture

  34. Configuration Manager

  35. Report Manager

  36. Report Delivery

  37. Report Delivery • Traditional (paper) and interactive (web) reports • On-demand (“pull”) or event-based (“push”) delivery • Many formats (HTML, Excel, PDF, XML, Word, Zip File*) • Deliver reports to many devices (e-mail, file share, etc.) • Ad-hoc Reporting

  38. SSRS and SSAS – Working Together Standard reports Ad hoc reports Detail reports SSAS SSRS • Intuitive reporting • Interactive analysis • High performance • Powerful calculations • Detail reporting • Standard reporting • Ad hoc reporting • Flexible delivery

  39. SSAS and Reporting Services Data Layer SSAS Presentation Layer UnifiedDimensionalModel ETL DB1 Interactive reporting UDM DB2 DW Standard reporting DB3 multiple data sources supported Ad hoc reporting • Dimensional schema = intuitive reporting • SSAS for better performance and business metrics • UDM serves many reporting needs

  40. Report Builder

  41. Report Builder v3

  42. Report generation

  43. Server side

  44. Server side

  45. Client side • Reportviewer Control in "local mode" • ASP.NET • Windows Forms • Silverlight • WPF • What about MVC? • http://stackoverflow.com/questions/15208437/how-can-i-use-a-reportviewer-control-with-razor

  46. Client-side

  47. New for SSRS 2012

  48. Resources • http://www.ssw.com.au/ssw/standards/Rules/RulesToBetterSQLReportingServices.aspx All about the ReportViewer control • http://www.gotreportviewer.com/

  49. Usergroups SQL Server UG • http://www.ssw.com.au/ssw/netug/ • http://www.sqlserver.org.au/

  50. Summary • What is Reporting Services? • Steps in Creating a Report • Demo: Creating a Report • Hands on Lab

More Related