sql server bi: adding reporting and analysis to smart client applications jamie maclennan and brian...

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

Jamie MacLennan and Brian Jamie MacLennan and Brian WelckerWelckerDAT313DAT313SQL Server Business IntelligenceSQL Server Business IntelligenceMicrosoft CorporationMicrosoft Corporation

2

AgendaAgenda

Why integrate BI into your smart Why integrate BI into your smart client application?client application?

Integrating Data Mining into your Integrating Data Mining into your applicationapplication

Integrating Reporting into your Integrating Reporting into your applicationapplication

3

What Is Business What Is Business Intelligence?Intelligence?

““Business intelligence (BI) is a broad Business intelligence (BI) is a broad category of business processes, category of business processes, application software and other application software and other technologies for gathering, storing, technologies for gathering, storing, analyzing, and providing access to analyzing, and providing access to data to help users make better data to help users make better business decisions”business decisions”

Why integrate BI into your app?Why integrate BI into your app?BI makes applications smarter!BI makes applications smarter!

Your customers get more value from Your customers get more value from their datatheir data

Big payoff with small amounts of codingBig payoff with small amounts of coding

4

Intelligent CallIntelligent CallCenter ApplicationCenter Application

Jamie MacLennanJamie MacLennanDevelopment ManagerDevelopment ManagerBrian WelckerBrian WelckerGroup Program ManagerGroup Program ManagerSQL Server Business IntelligenceSQL Server Business Intelligence

5

What Is SQL Server Data What Is SQL Server Data Mining?Mining?

Data Mining platform for analysis and Data Mining platform for analysis and deployment of data mining solutionsdeployment of data mining solutions

Nine algorithms developed in Nine algorithms developed in conjunction with Microsoft Researchconjunction with Microsoft Research

Tools for the creation and Tools for the creation and comprehension of modelscomprehension of models

Familiar API’s and query language for Familiar API’s and query language for embedding data mining into embedding data mining into intelligent applicationsintelligent applications

6

Mining ModelMining Model

What Does Data Mining What Does Data Mining Do?Do?IllustratedIllustrated

DMDMEngiEnginene

Data Data To PredictTo Predict

DMDMEngiEnginene

Predicted DataPredicted Data

Training DataTraining Data



DB dataDB dataClient dataClient dataApplication dataApplication data

DB dataDB dataClient dataClient dataApplication dataApplication data““Just one row”Just one row”

7

Intelligent ApplicationsIntelligent Applications

Make decisions without codingMake decisions without codingData Mining algorithms learn business rules Data Mining algorithms learn business rules directly from the data, freeing you from trying directly from the data, freeing you from trying discover and code them yourselvesdiscover and code them yourselves

Customized for each clientCustomized for each clientData Mining learns the rules from the client’s Data Mining learns the rules from the client’s data – resulting in logic that is automatically data – resulting in logic that is automatically specialized for each individual clientspecialized for each individual client

Automatically update themselvesAutomatically update themselvesAs your client’s business changes, so do the As your client’s business changes, so do the factors that impact their business. Data Mining factors that impact their business. Data Mining allows your application logic to be automatically allows your application logic to be automatically updated through a simple processing step. updated through a simple processing step. Applications do not need to be rewritten, Applications do not need to be rewritten, recompiled or redeployed, and are always online recompiled or redeployed, and are always online – even during processing– even during processing

8

Application ExamplesApplication Examples

Clalit HealthcareClalit HealthcareBuilds models predicting health Builds models predicting health degradation in senior citizensdegradation in senior citizens

Sends reports to physicians of top Sends reports to physicians of top patients likely to get sickpatients likely to get sick

ABS-CBN InteractiveABS-CBN InteractiveSends ring tone recommendations to Sends ring tone recommendations to customers cell phones based on past customers cell phones based on past historyhistory

Inrix, IncInrix, IncReal-time predictive traffic info delivered Real-time predictive traffic info delivered to web and mobile devicesto web and mobile devices

9

Analysis ServicesAnalysis ServicesServerServer


Data Mining AlgorithmData Mining Algorithm DataDataSourceSource

Server Mining ArchitectureServer Mining Architecture

Your ApplicationYour Application

OLE DB/ ADOMD/ XMLAOLE DB/ ADOMD/ XMLA

DeployDeploy

BI Dev BI Dev StudioStudio (Visual (Visual Studio)Studio)

AppAppDataData

10

Server Mining ModelsServer Mining Models

Jamie MacLennanJamie MacLennanDevelopment ManagerDevelopment ManagerSQL Server Analysis ServicesSQL Server Analysis Services

11

Programmatically Creating Programmatically Creating ModelsModels

Analysis Management ObjectsAnalysis Management ObjectsObject model for administrative tasksObject model for administrative tasks

Data Mining Extensions (DMX)Data Mining Extensions (DMX)

CREATE MINING MODELCREATE MINING MODEL TargetMailDTTargetMailDT(CustID(CustID LONG KEYLONG KEY,,Gender Gender TEXT DISCRETETEXT DISCRETE,,CommuteDist CommuteDist TEXT DISCRETETEXT DISCRETE,,Education Education LONGLONG CONTINUOUSCONTINUOUS,,……BikeBuyerBikeBuyer LONG DISCRETE PREDICTLONG DISCRETE PREDICT))USINGUSING Microsoft_Decision_Trees Microsoft_Decision_Trees

INSERT INTOINSERT INTO TargetMailDTTargetMailDT(CustID, Gender, CommuteDist,(CustID, Gender, CommuteDist, Education, …, BikeBuyer) Education, …, BikeBuyer)OPENQUERYOPENQUERY ([My Data Source],([My Data Source], ‘SELECT CustID, Gender, ‘SELECT CustID, Gender, ComDist, Education, … ComDist, Education, … BikeBuyer’ BikeBuyer’))

12

Programmatically Creating Programmatically Creating ModelsModels

Analysis Management ObjectsAnalysis Management ObjectsObject model for administrative tasksObject model for administrative tasks

Data Mining Extensions (DMX)Data Mining Extensions (DMX)

Dynamic mining using client dataDynamic mining using client data

CREATE SESSION MINING MODELCREATE SESSION MINING MODEL TargetMailDTTargetMailDT(CustID(CustID LONG KEYLONG KEY,,Gender Gender TEXT DISCRETETEXT DISCRETE,,CommuteDist CommuteDist TEXT DISCRETETEXT DISCRETE,,Education Education LONGLONG CONTINUOUSCONTINUOUS,,……BikeBuyerBikeBuyer LONG DISCRETE PREDICTLONG DISCRETE PREDICT))USINGUSING Microsoft_Decision_Trees Microsoft_Decision_Trees

INSERT INTOINSERT INTO TargetMailDTTargetMailDT(CustID, Gender, CommuteDist,(CustID, Gender, CommuteDist, Education, …, BikeBuyer) Education, …, BikeBuyer)@InputRowset@InputRowset

13


Local Mining ArchitectureLocal Mining Architecture

ADOMD.Net/OLE DBADOMD.Net/OLE DB

Local Analysis Services Local Analysis Services (msmdlocal)(msmdlocal)

Model FileModel FileMining ModelMining Model

Decision Tree/Clustering algorithmsDecision Tree/Clustering algorithms

RetrievRetrieve Datae Data

DataDataSourceSource

AppAppDataData

14

Local Mining ModelsLocal Mining Models

Jamie MacLennanJamie MacLennanDevelopment ManagerDevelopment ManagerSQL Server Analysis ServicesSQL Server Analysis Services

15

Why Integrate Reporting Why Integrate Reporting Services Into Your App?Services Into Your App?

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

16

Reporting Services In Reporting Services In Visual Studio 2005Visual Studio 2005

Microsoft Visual Studio 2005 includesMicrosoft Visual Studio 2005 includesReporting Services functionality in Reporting Services functionality in standard editions and abovestandard editions and aboveWindow Forms and ASP.NET report viewer Window Forms and ASP.NET report viewer controls make it easy to embed Reporting controls make it easy to embed Reporting Services functionality in applicationsServices functionality in applicationsIntegrated report designer for designing Integrated report designer for designing and embedding reports in language and embedding reports in language projectsprojectsFreely redistributable with no run-time Freely redistributable with no run-time restrictionsrestrictions

Report Server mode requires a SQL Server Report Server mode requires a SQL Server 2005 license2005 license

17

Report ServerReport Server

SQL Server Catalog (Metadata)SQL Server Catalog (Metadata)

Web Services APIWeb Services API

Report Processing and RenderingReport Processing and Rendering DataDataSourceSource

Report Server ModeReport Server Mode


Report ViewerReport Viewer(Remote Mode)(Remote Mode)

PublishPublishReportReport

ReportReportDesignerDesigner(Visual (Visual Studio)Studio)

18

Report Viewer Control In Report Viewer Control In Server ModeServer Mode

Brian WelckerBrian WelckerGroup Program ManagerGroup Program ManagerSQL Server Reporting ServicesSQL Server Reporting Services

19


Report ViewerReport Viewer(Local Mode)(Local Mode)

Report ProcessingReport Processing

Local ModeLocal Mode

Report Report DefinitionDefinition

ReportReportDesigneDesigne

rr(Visual (Visual Studio)Studio)

EmbedEmbedReportReport

Retrieve Retrieve DataData

Report Report DataData

DataDataSourceSource

20

Designing In Local ModeDesigning In Local Mode

Report creation is integrated with Report creation is integrated with Windows or Web language project Windows or Web language project (Add New Item…)(Add New Item…)Integrates with Visual Studio data Integrates with Visual Studio data toolstools

Build report from project data sources Build report from project data sources (XSD)(XSD)Supports Data Sources windowSupports Data Sources windowSupports Data Source Configuration Supports Data Source Configuration WizardWizardSupports TableAdapter Configuration Supports TableAdapter Configuration WizardWizard

Full InteractivityFull InteractivityExpand/Collapse sectionsExpand/Collapse sectionsDrillthrough reports, including Drillthrough reports, including drillthrough from chartsdrillthrough from chartsInteractive sortingInteractive sorting

21

Report Viewer Control In Report Viewer Control In Local ModeLocal Mode


22

Object Data SourcesObject Data Sources

Build reports on top of middle-tier objectsBuild reports on top of middle-tier objects

Objects need not implement any particular Objects need not implement any particular interface or inherit from any particular interface or inherit from any particular classclass

Public properties of your class are the Public properties of your class are the “fields”“fields”for the report for the report

Properties of class appear in the Data Sources Properties of class appear in the Data Sources window where they can be dragged and window where they can be dragged and droppeddropped

Use Data Source Configuration Wizard to Use Data Source Configuration Wizard to select a class in your application to use as select a class in your application to use as the data source, or point to a class in an the data source, or point to a class in an external assemblyexternal assembly

Supply a collection (Array, List, Supply a collection (Array, List, IEnumerable, etc.) of instances of your IEnumerable, etc.) of instances of your class at runtimeclass at runtime

23

Using Object Data SourcesUsing Object Data Sources


24

Call To ActionCall To Action

Integrate Business Intelligence into Integrate Business Intelligence into your smart client applicationsyour smart client applications

Leverage SQL Server 2005 BI for Leverage SQL Server 2005 BI for enterprise capabilitiesenterprise capabilities

Integration ServicesIntegration Services

Analysis ServicesAnalysis Services

Reporting ServicesReporting Services

Notification ServicesNotification Services

25

Community ResourcesCommunity Resources

At the PDCAt the PDCDAT021 – Building Portals for BI and ReportingDAT021 – Building Portals for BI and Reporting(Thursday at 10:00 AM)(Thursday at 10:00 AM)

DATL02 – BI “Power Hour” (Thursday at 1:00 PM)DATL02 – BI “Power Hour” (Thursday at 1:00 PM)

OFF323 – Excel “12” and SQL Server 2005 Analysis OFF323 – Excel “12” and SQL Server 2005 Analysis ServicesServices(Friday at 10:30 AM)(Friday at 10:30 AM)

SQL Cabana and Ask the ExpertsSQL Cabana and Ask the Experts

After the PDCAfter the PDCSQL Server Developer CenterSQL Server Developer Center

msdn.microsoft.com/sqlmsdn.microsoft.com/sql

SQL Server Data MiningSQL Server Data Miningwww.SQLServerDataMining.comwww.SQLServerDataMining.com

SQL Server ForumsSQL Server Forumshttp://forums.microsoft.com/msdnhttp://forums.microsoft.com/msdn

sql server bi: adding reporting and analysis to smart client applications jamie maclennan and brian...

Documents