SQL Server BI: Adding Reporting And Analysis To Smart Client Applications

1. SQL Server BI: Adding Reporting And Analysis To Smart Client Applications Jamie MacLennan and Brian Welcker DAT313 SQL Server Business Intelligence Microsoft Corporation

2. Agenda • Why integrate BI into your smart client application? • Integrating Data Mining into your application • Integrating Reporting into your application

3. What Is Business Intelligence? • “Business intelligence (BI) is a broad category of business processes, application software and other technologies for gathering, storing, analyzing, and providing access to data to help users make better business decisions” • Why integrate BI into your app? • BI makes applications smarter! • Your customers get more value from their data • Big payoff with small amounts of coding

4. Intelligent CallCenter Application Jamie MacLennan Development Manager Brian Welcker Group Program Manager SQL Server Business Intelligence

5. What Is SQL Server Data Mining? • Data Mining platform for analysis and deployment of data mining solutions • Nine algorithms developed in conjunction with Microsoft Research • Tools for the creation and comprehension of models • Familiar API’s and query language for embedding data mining into intelligent applications

6. Data To Predict Training Data Mining Model Mining Model Mining Model What Does Data Mining Do?Illustrated DB dataClient dataApplication data DB data Client data Application data “Just one row” DMEngine DM Engine Predicted Data

7. Intelligent Applications • Make decisions without coding • Data Mining algorithms learn business rules directly from the data, freeing you from trying discover and code them yourselves • Customized for each client • Data Mining learns the rules from the client’s data – resulting in logic that is automatically specialized for each individual client • Automatically update themselves • As your client’s business changes, so do the factors that impact their business. Data Mining allows your application logic to be automatically updated through a simple processing step. Applications do not need to be rewritten, recompiled or redeployed, and are always online – even during processing

8. Application Examples • Clalit Healthcare • Builds models predicting health degradation in senior citizens • Sends reports to physicians of top patients likely to get sick • ABS-CBN Interactive • Sends ring tone recommendations to customers cell phones based on past history • Inrix, Inc • Real-time predictive traffic info delivered to web and mobile devices

9. BI Dev Studio (Visual Studio) Your Application OLE DB/ ADOMD/ XMLA App Data Deploy Analysis Services Server Mining Model Data Source Data Mining Algorithm Server Mining Architecture

10. Server Mining Models Jamie MacLennan Development Manager SQL Server Analysis Services

11. Programmatically Creating Models • Analysis Management Objects • Object model for administrative tasks • Data Mining Extensions (DMX) CREATE MINING MODELTargetMailDT(CustID LONG KEY,Gender TEXT DISCRETE,CommuteDist TEXT DISCRETE,Education LONGCONTINUOUS,…BikeBuyer LONG DISCRETE PREDICT)USING Microsoft_Decision_Trees INSERT INTOTargetMailDT(CustID, Gender, CommuteDist, Education, …, BikeBuyer)OPENQUERY([My Data Source], ‘SELECT CustID, Gender, ComDist, Education, … BikeBuyer’)

12. Programmatically Creating Models • Analysis Management Objects • Object model for administrative tasks • Data Mining Extensions (DMX) • Dynamic mining using client data CREATE SESSION MINING MODELTargetMailDT(CustID LONG KEY,Gender TEXT DISCRETE,CommuteDist TEXT DISCRETE,Education LONGCONTINUOUS,…BikeBuyer LONG DISCRETE PREDICT)USING Microsoft_Decision_Trees INSERT INTOTargetMailDT(CustID, Gender, CommuteDist, Education, …, BikeBuyer)@InputRowset

13. Your Application App Data Data Source Retrieve Data Local Mining Architecture ADOMD.Net/OLE DB Local Analysis Services (msmdlocal) Mining Model Model File Decision Tree/Clustering algorithms

14. Local Mining Models Jamie MacLennan Development Manager SQL Server Analysis Services

15. Why Integrate Reporting Services Into Your App? • Process data efficiently: filtering, sorting, grouping, aggregations, etc. • Present data in a variety of ways: lists, tables,charts, matrices • Add visual appeal to data: fonts, colors, border styles, background images, etc. • Enable interactive reports: collapsible sections, document map, bookmarks, interactive sorting, etc. • Support printing and print preview • Export to multiple formats (Microsoft Office Excel, PDF, XML) • Integrate with managed report server environment: security, caching, scheduling, delivery, etc. Bottom Line: More functionality, less code

16. Reporting Services In Visual Studio 2005 • Microsoft Visual Studio 2005 includesReporting Services functionality in standard editions and above • Window Forms and ASP.NET report viewer controls make it easy to embed Reporting Services functionality in applications • Integrated report designer for designing and embedding reports in language projects • Freely redistributable with no run-time restrictions • Report Server mode requires a SQL Server 2005 license

17. ReportDesigner (Visual Studio) Report Viewer(Remote Mode) Publish Report Report Server Web Services API DataSource Report Processing and Rendering SQL Server Catalog (Metadata) Report Server Mode Your Application

18. Report Viewer Control In Server Mode Brian Welcker Group Program Manager SQL Server Reporting Services

19. Your Application Report Viewer(Local Mode) Report Processing ReportDesigner (Visual Studio) DataSource Report Definition Report Data Embed Report Retrieve Data Local Mode

20. Designing In Local Mode • Report creation is integrated with Windows or Web language project (Add New Item…) • Integrates with Visual Studio data tools • Build report from project data sources (XSD) • Supports Data Sources window • Supports Data Source Configuration Wizard • Supports TableAdapter Configuration Wizard • Full Interactivity • Expand/Collapse sections • Drillthrough reports, including drillthrough from charts • Interactive sorting

21. Report Viewer Control In Local Mode Brian Welcker Group Program Manager SQL Server Reporting Services

22. Object Data Sources • Build reports on top of middle-tier objects • Objects need not implement any particular interface or inherit from any particular class • Public properties of your class are the “fields”for the report • Properties of class appear in the Data Sources window where they can be dragged and dropped • Use Data Source Configuration Wizard to select a class in your application to use as the data source, or point to a class in an external assembly • Supply a collection (Array, List, IEnumerable, etc.) of instances of your class at runtime

23. Using Object Data Sources Brian Welcker Group Program Manager SQL Server Reporting Services

24. Call To Action • Integrate Business Intelligence into your smart client applications • Leverage SQL Server 2005 BI for enterprise capabilities • Integration Services • Analysis Services • Reporting Services • Notification Services

25. Community Resources • At the PDC • DAT021 – Building Portals for BI and Reporting(Thursday at 10:00 AM) • DATL02 – BI “Power Hour” (Thursday at 1:00 PM) • OFF323 – Excel “12” and SQL Server 2005 Analysis Services(Friday at 10:30 AM) • SQL Cabana and Ask the Experts • After the PDC • SQL Server Developer Center • msdn.microsoft.com/sql • SQL Server Data Mining • www.SQLServerDataMining.com • SQL Server Forums • http://forums.microsoft.com/msdn

26. © 2005 Microsoft Corporation. All rights reserved. This presentation is for informational purposes only. Microsoft makes no warranties, express or implied, in this summary.