Microsoft SQL Server 2005 Business Intelligence

1
Microsoft SQL Server 2005 Business Intelligence

• ?????? ??????
• Microsoft Corporation

2
???????? SQL Server
1-? ?????????
2? ?????????
3? ?????????
SQL Server 7.0
SQL Server 2005
SQL Server 2000
SQL Server 6.0/6.5

• ??????? ???????????
• ??????? ????????????????
• ????????????
• ?????????????? ?????????????
• ???????? ????????? XML
• ?????? ??? ??????? Enterprise ETL ???????? Data
  Mining

• ?????? ?? ?????????????-????? ? ???????????????
• ????????? XML
• ?????? ??? ??????? ??????? ???????????
• ?????? ??? ??????? Data Mining Reporting

• ????????? ??????????? ???????????? ???????
• ?????? ??? ?????? ?????????????? ?????????? ?
  ??????????? ????????????? ????????
• ?????? ??? ??????? OLAP ETL

• ???????? ?????????? ? Windows

3
Microsoft IT ?????? ? ???????? ?????????

• ?????? Microsoft
• ??? ???? ?????? ??? ??????????? SQL Server
• Microsoft ?????? ?? ???? ????????? ???? ????????
  ????? ?? ????????
• ????? 15 ?????????? ???????? ?? SQL Server 2005
  ??? ? ??????? 2004?.
• ????? 100 ?????????? ? RTM
• ???????? ??? ??????? ?????????? ???????? ??? ??
  Beta 2
• ????????? SAP R/3, 1.8 TB
• Feedstore ????????? ???? ?????? Microsoft
• 2 TB ??????, ????? 1800 ??????, 500 ???????????
  ??????
• ??????? Microsoft Sales Revenue Reporting ? BI
• 9,000 ????????????? ?? ????? ????
• 25,000 ??????? ? ??????
• ?????? ???? ????? ?????? ? SQL Server 2005
• Sales ? SAP ? Feedstore ? Microsoft Sales

4
???????? Microsoft ???????????? SQL Server
5
SQL Server 2005

• .NET Framework
• Common Language Runtime Integration
• User-defined Aggregates
• User-defined Data Types
• User-defined Functions
• SQL Server In-Proc Data Provider
• Extended Triggers
• Data Types
• File Stream Storage Attribute
• Managed SQL Types
• New XML Datatype
• SQL Server Engine
• New Message Service Broker
• HTTP Support (Native HTTP)
• Database Tuning Advisor
• Enhanced Read ahead scan
• Extended Indexes
• Multiple Active Result Sets
• Persisted Computed Columns

• Database Maintenance
• Backup and Restore Enhancements
• Checksum Integrity Checks
• Dedicated Administrator Connection
• Dynamic AWE
• Fast Recovery
• Highly-available Upgrade
• Online Index Operations
• Online Restore
• Parallel DBCC
• Parallel Index Operations
• Management Tools
• MDX Query Editor
• MDX Intellisense
• T-SQL Intellisense
• Version Control Support
• XML/A
• SQLCMD Command Line Tool
• Performance Tuning

• Replication
• Auto-tuning Replication Agents
• Oracle Publication
• Improved Blob Change Tracking
• OLAP and Data Mining
• Analysis Management Objects
• Windows Integrated Backup and Restore
• Web Services/XML for Analysis
• DTS and DM Integration
• Eight new DM algorithms
• Auto Packaging and Deployment
• Data Transformation Services
• New Architecture (DTR DTP)
• Complex Control Flows
• Control Flow Debugging
• For Each Enumerations
• Property Mappings
• Full Data Flow Designer
• Full DTS Control Flow Designer

6
????? ????? Business Intelligence?
??????????? ?????? ? ???????????
??????????? ??????? ????????? ? ?????????? ?????
???????e ????????? ???? ????????????
???????????? ????????? ????????????
?? ???? 80 ??????? IT ???? ?? ???????????
????????????
?????? ? ???????? ?????????? ????????? ? ??????
??????????
7
Microsoft Business Intelligence Vision
????????? ?????? ??????????? ????? ???????????
??????? ???? ?????????? ? ??????????, ???????????
??? ???????? ????????????? ? ?????????? ???????

• ??????????? BI ?????????
• ????????? ?????? ??????? ? ?????? ????? Microsoft
  Office
• ??????? ??????? ? ????????? ????

8
????????? ??????? ??????
??????? ???????????
????????? ?????????? ??????
?????????? ??????
??????????? ??????
???????????? ??????? ? ??????
?????? ???????? ?????????
??? ?? ..

• ????????? ?????????????? ??? ??????? ?????????
  ??????
• ???????????? ??????? ?????? ? ???????????
• ?????????? ??????? ????????? ?? ?????? ? ??
  ???????
• ???????????? ?????? ?????????? ? ?????? ??????

9
(No Transcript)
10
Integration Services?????? ????????? ETL?

• ???????????? ?????? ?????? ???????? ?????? ???
  ???????????? ??????
• ????????? ?????????? ?????? ?? ????????????
  ??????????
• ????????? ?????
• ??????????
• ??????????? ???????
• ??????????? ??
• ??????? ? ????????????? ?????? ??? ????????
• ???????????????? ???????? ?????? ? online
  transaction processing (OLTP) ? online analytical
  processing (OLAP).

11
Data Integration Architecture With Integration
Services
??????????? ?????????? ?????? ? ???????
Integration Services

• ?????? ??????????? ?? ?????????, ????????????????
  ? ??????????? ?????? ?????????.
• ???????? ??????? ? ????????? ???????? ?????
  ??????????? ??????????? ? ????????? ??????.
• ?????????? ? ????????????? Integration and
  warehousing ??????? ? ??????????? ????????.
• ?????????????? ??? ????????? ??????? ??????? ?
  ??????? ??????????.

12
Integration Services????????? ? ETL ?????????

• ???????????? ETL ?????????
• ??????? ??????????????????
• ??????? ????????????????
• ??????? ??????????
• ??????? ????????? ????????????
• ??????? ???????? ??????????
• ???????? ???? (Source control)
• ?????????? ???????? ??????? ?????????? ? ???????
  ??????
• ??????? ?????? ????????????? out-of-the-box
• ????? ???????????
• ?????????????? ??????
• ?????????????? ????????????
• ?????????????? ??????????????
• ?????????????? ????????? ??????

13
?????
??????
14
Analysis ServicesOLAP ? Data Mining

• ????????????? ??????-??????????
• ???????????????? ??????????
• ?????????? ???????????? ? ??????????????
  ?????????????
• ??????? ??????? ?? MOLAP ? ROLAP
• ?????? ????????????? ???????????
• ???????? ?????????? ?????????????????? ?
  ???????????
• ????? ????????? ???????, ???????
  ??????????????????
• ?????? ????????? ??????? ? ????????????? ?????
• ??????? ? ?????? ?????????
• XML ????????? ??? ??????? ? ?????? ? ???????????
  ? web-????????
• ??????????? ?????? ?????????? ? ??????? ?????????

15
Analysis Services??????????? ??????????? OLAP ?
Data Mining

• ?????? ?????? ????????? (UDM)
• ??????????? ???????????
• Web-??????
• ?????????? Data Mining ??????????????? ?
  ?????????
• ??????????????? ??????????? ??????????
• ????????? ???????????????? ?????????

• ????
• Logistic Regression
• Linear Regression
• Text Mining

Decision Trees
Clustering
Naïve Bayes
Introduced in SQL Server 2000
16
UDM ?????? ????????????? ?????????

• MDX ???? ??????????? ??????????
• MDX Scripts ???????? ?????????? ? ?????????
  ??????
• ????????? ????????
• ?????????? ? ??????? ??????? ????????????????
  ????????
• Time Intelligence
• Account intelligence
• Financial Aggregations
• Currency Conversion
• Semi-additive measures
• ???????? ?????????

17
UDM??????????? ????????????

• ??????????? ????? ???????????? ??? ??????????
  ??????????? ??????
• ??????????? ????? ????????????
• ???????????? Visual Studio.NET
• ??????????? ???????? ?????????? ? ???????? ????
  ??????? ?????????????
• ????????? ??????? ????? ?????? ? ??????? ??
  ?????????? ?? ?????????????

18
????????? ????????????????? ???????????

• ??? ????????????? ?????? ?? ?????????? ??????,
  ??????????????
• ??????? ?????? ?? ????? ?????
• ?????? ????????? ????? ????????? ???????? ??
  ????? ?????
• ?????? ??? ????? ??????????????? ??? ????? ??????

19
Value of Data Mining
SQL Server 2005

• 9 ?????????
• ??????????? ???????? ???????? ? ?????????????

• SQL Server 2005 ?????? ??????? ? ?????????
• ??????????????? ????? BI

20
?????????
Introduced in SQL Server 2000
21
?????? ??? Data Mining
?????? ?????????
???????????? ???????? ?????????? ??????????. ?????? ??????????? ?????????? ????????? ???????? ??? ??????????? ??????????. Decision Trees Naive Bayes Clustering Neural Network Logistic Regression Linear Regression
???????????? ???????? ???????????? ?????????. ?????? ??????????? ?????? ?????? ? ????????? ???? Decision Trees Time Series
???????????? ?????????????????? ???????. ???????? ???????????????? ?????????????????? ????????? ????? ????????. Sequence Clustering
????? ????? ? ?????? ??????????? ? ??????????. ???????? ????????????? ?????????????? ?????? ??? ??????????. Association Rules Decision Trees
????? ????? ?? ???????? ??????????. ?????? ??????? ????????????? ?? ?????? ?? ??????????????? ??????. Clustering Sequence Clustering
22
?????
??????
23
Reporting Services???????????

• ?????? ? ?????? ???? ?????? ???????????
• ?????????? ??????? ?? ?????????? ??????????
  ??????
• ????????????? ?????? ? ??????????
• ???????????? ????? ??????? ???? ???????????
  ??????
• ?????????????? ?????? ? ??????? ??????? ???
  ????????????
• ????? ?????????? ??????? ? ??????????

24
Reporting Services??????????? ??????? ??????????

• ?????????????? ??????
• ??????? ????????? ??? ??????????? ? ?????????????
  ??????????
• ????????? ???????? ?????????? ?????????? ??????
• ????????? ????????? ??????? ????????
• ??????????? ??????????? ?? web-???????
  ??????????????, ??????????? ? ????????????
• ??????????, ??????, ???????????,
• ??????????? ??? ???????? ???????
• Visual Studio IDE
• ???????????? XML (RDL)
• ??????????? ?????????? ??????-??????????
  ??????????-??????????????
• ???????? ??????? ????????? ??????????????
• ?????????? ??????????? ??????????
• SOAP Web Service APIs
• ?????? Report Manager
• ??????????? ?????? ????????????
• ?????????? ? AS, IS, ????????????? ??????????

25
Reporting Services

• ?????? ?????? ???? ??? SQL Server 2000
• ???????? ? ??????????? ??????? ??? ?????? ?
  ????????
• ???????? ???????, ?????????? ? ????????
• ?????????? ? Office System
• ?????????? ? VS.NET
• ????????? ? SQL Server 2005
• ?????????? ? AS, SSIS, ?????????? ??????????
• ??? ?????? ???????????????
• ??? ?????? ???????????? ??? ?????????????

26
??????? ?????????????????? ?????????????

• ?????? ???????? ???????????
• ?????????? ?????? (???????, ???????, ?????)
• ?????????? ?????????????, ?????????? ?
  ????????? ? ?????????????? ????????????
• ???????????? ??????
• ????????? ??????? ???????? ? ??????????
• ?????????? ??????, ???????????? ???????????
• B2B ??????
• ????????? ??????? ??? ????????? ????? extranets,
  Internet
• ?????????? ???????? ??????, ?????? ??????? ????

27
????????? ???? ???????
SQL Server Reporting Services ???????????? ??????
????????? ???? ???????, ??????? ????????,
?????????? ? ????????.
28
??????????? ????????????? ??

• ?????????? ? ??????
• ???????? ????????? ?????? (??????????, ???????) ?
  ????????? ??????
• ????????
• ???????? ??????? ????????????? ??????
• ?????????????? ???????
• ?????????, ?????????? ???????????

29
??????????? ??????? (Report Designer)

• ???????????? ? SQL Server BI Development Studio
• ????? ??????? ???????????????? ??? ????????
  ???????
• ???????????? ??????????????? ???????? ???????
• ???????????? ?????????? ? ???? ?????? Report
  Server
• ????????? Report Definition Language (RDL)

30
???????? ???????

• ?????? XML Report Definition Language
• ?????? ????????? / ????????? / ???????????
• ????????? (????????????, ?????????????)
• ?????????? / ?????????? / ??????????? / ????????
• ??????????????? (Drill-down, Drill-through)
• ??????????????????? ???? VB.NET Expression
  Language
• ??????????? ??????????? ?????????? ????????
• SQL Server / Analysis Services
• OLE DB / ODBC / Oracle
• ??????????? ??????? Report Designer
• ???????????? ? SQL Server BI Development Studio
• ??????????? ???????? ??? SQL, Analysis Services
• ??????????? ????????? ???????????? ???????????
  ?????????? ?? ??????? ????????

31
?????????? ????????
??????, ????? ? ??????? ??????????? ? ???????????
? ??????? ??????????? Web-?????? Reporting Web
Service. ??????????? ?????? ????? ???? ????????
???? ?? ?????????? ???? ?? ??????????. ???
????????? ?????????????????? ?????? ??????????.
????????
??????????
????????
Online ??????
??????????? ??????
??????????? ?????
?????? ????????
32
?????????? ????????

• Web Service APIs
• Web ?????????
• ??????? ?????????? ????????
• ?????????? ?????? (????????, ????????,
  ??????????, ????????? ???????????)
• ??????????? ???????????? ? SQL Server Agent
• ????????? ??????? (? ?????? ????????? ???????,
  ???????????? ?????? ??? ?? ??????????)
• ??????? ?????????? ????????
• ??????????? ???????????? ?????????? ? ????????
  ?? ?????

33
Report Manager
34
???????? ???????
Reporting Services ???????????? ???????? ??
?????????? (Pull) ? ???????? ?????????? ??
???????? (Push). ???????????? ?????
????????????? ?????? ?? web-?????????? ???
??????????? ?? ???????? ?? ??????????? ?????.
????????
??????????
????????
Online ??????
??????????? ??????
??????????? ?????
?????? ????????
35
???????? ???????

• ????????? ????????? ????????
• Web-??????? - HTML 4, HTML 3.2, HTML w/OWC
• ??????? ??? ?????? - TIFF, PDF
• ??????? ?????? - Excel, XML, CSV
• ????????????? ?????????? ???????? ? ???????? ?
  ???????????? ? ?????????????? ????????????
• ????? ??????? ?????
• ??????????? ?????????? ?????? ??? ???? ??????
• ?????????? ??????? ???????? ??? ?????? ????????
• ?????? ??????????? ?????????? ????????
• E-mail, File System, SharePoint

36
??????????? ??????????

• ?????????? ????????????
• URL ?????????
• Web-???????
• ?????????? ??????????
• Web-???????
• WMI ?????????
• ????????????
• ????????????? XML-??????????? ??????
• ?????????? ???????????
• ????????? ??????, ????????, ??????????????,
  ????????????

37
??????????? Reporting Services
Microsoft Office
?????????? ?????????????
???????
Web UI
??????????? ??????????
Report Server
????????? ??????
????????? ??????
????????? ??????
????????????
?????? ????????????
??????????????
????????
?????? ????????
??????? ??????
Report Server Database
38
Reporting Services ?????? ???????
Report Server

• NT Service
• (?????????? / ????????)
• ??????????????? ??????? (???????? ??????)
• ???????? (????? / ????? ??????????? ???????)

• ASP.NET Web Service
• (????????? ??????)
• ?????? ???????? ??????
• ??????????, ?????????, ?????????????? ?????? ?
  ???????? ??????
• ?????????? ????????

??????? Report Server
ReportServerTempDB
ReportServer

• ??????????? ???????
• ????????? ??????
• ????????????, ????????, ????
• ?????? ??????? ??????? ???????

• ?????? ??????
• ???????????? ??????

39
???? ?????????? ??????

• ?????????? ??? ??????? ??????????????
  ??????????? ?????? ? ??????? ?????

• ??????????? ?????????? ??? ???????? ?????? ?
  ????????????? ???????
• ??? ???????
• ? ????? ??? ????????? ?????????????
• ???
• Snapshot (???????????? ??????) / ???????

40
?????? ?????? ?? ??????????

• ?????? ?????? ?????????? ????????? ???????
• ????? ?????? ? ?????????? ??????
• ???????? ?????? ? ????????????? ??????? ?
  ????????? ??? ?????????? ? ???? ?????? ? ??
  ReportServerTempDB
• ?????????????? ? ????????? ????????????? ??????

Report Server
41
??????????? ??????

• ?????????? ???????
• ?????????? ?????????? ?????? ???? ?????? HTTP
• ??????????? HTTP ???????? ?? ????? ? ??? ??
  ??????
• ?????? ? ???? ??????
• ?????????? ?????????? ? ?????? ?????????
• ??????????? ? ReportServerTempDB
• ????????? ?????????? ??? ????????? ??????
• Managing Report Sessions on a URL

42
?????????? ????????????? ??????

• ????????? ?????? ?????????? ??????? ??????????
• ????? ?????? ? ?????? ????????? ??????
• ???????? ?????? ? ????????????? ??????? ?
  ????????? ??? ?????????? ? ???? ?????? ?
  ReportServerTempDB
• ??????? ?????? ? ????????????? ??????? ???
  ???????????? ?????????
• ?????????????? ?????? ?? ????????????? ??????????
  ? ????????? ????????????? ??????

Report Server
43
?????????? ????????????? ??????

• ??????????? ??????? ?????? ??????????
  ???????????? ?????????
• ????????? ??? ????? ??? ????????? ? ????
• ?????????? ?????????? ?????? ?? ????
• ?????????????? ????? ?????? ? ???????? ??????

Report Server
44
???????????? ??????????

• ???????????? ?????????? ???????? ? ???? ?? ?????
  ?????? ????????
• ? ???????????? ? ???????????????? ??????????
• ? ???????????? ?? ?????????? ?????? ???
  ?????????????
• ?????????????? ????????? ????? ?????
• ??? ??????????? ?? ?????????? ???????????
• ???? ????????? ????? ???? ??????? ?? SQL Server
  Agent
• ???????????? ?????????? ?????? ????????????
  ??????????? ?????/??????
• ????? ???????????? Data-Driven Subscription ???
  ??????????????? ???????? ? ???

45
????????? ??????? ???

• ????????? ??????? ??????????? ??? ????????
  ????????????? ??????????
• ?????? ????????? ? ????????? ?????????? ???????
  ????????? ???????????? ?????????
• ??? ?????? ? ???? ?? ?????? ????? ????????????
  ????????? ???????????
• ??? ???????? ? ?? ReportServerTempDB

46
??????? ???

• ??????? ??????????? ??? ?????? ?????????????
  ?????????? ? ?????? ????????? ??????
• ??? ????????? ???????? ?????????? ??????
• ?? ????????? ????????? ????? ?????????? ?????????
• ?????? ????????????? ?????? ?? ????????????
• ????????? ??????? ??? ?????? ?????????
• ? ??????????? ?? ???????? ??????

47
?????? ? ??????? ?? ??????

• ??????? ???????????
• ???????? ?????????
• ????????? ???????? ?? ????? ???? ???????? ?
  ?????? ??????????
• ????? ??????????? ? ??????????? ?? ?????????
• ??????? ?????? ????????? ???????????? ????? ???
  ???? ????? ?????????? ?? ?????????
• ?????? ?????? ? ???? ?? ?????? ????? ???????
• ????? ? ?????????? ? ???? ????????
• URL ????????? BOL link
• ???????? ? ?? ReportServer (??? ???????? ? ??
  ReportServerTempDB)

48
?????????? ? Microsoft Office System

• ?????? ??????? ?? Microsoft Access
• ????????? ???????? ?????????? Office
• HTML ????????? ?????????? ???????????? Office Web
  Components
• ????????????? Charts, PivotTables, Spreadsheets
• ????????????? ? Windows SharePoint Services ?
  SharePoint Portal Server

49
File System Snapshot (FS)

• ?? ??????? ??????????? ???????
• ???? ????????? ? ?????? ??????????? ???????
  ???????? ??????
• ????????? ????? ? ????????????? ??????? ? ?????
  ??????????? ? ???????? ???????
• ????? ?????????? ??? ???????????? ?????????
• ?????????????? ????????????? ?????????? ?
  ???????? ??????

Report Server
50
File System Snapshot (FS)

• ??????????? ??????? ??? ?????????? ????????????
  ?????????
• ????????? ??? ????? ??? ???????????
• ?????????? ?????????? ?????? ?? ???? ????????
  ???????
• ?????????????? ? ???????? ?????? ?? ?????????????
  ?????????? ??? ????????? ? ????????

Report Server
51
?????? ?? ???????

• ?????? ??? ???????? ? ???????????? ??????
• ????????? ActiveX control ??????????? ?????
  ????????????
• ????? ????????? ???????, ??????????, ????????
• ??????????????? ????????

52
SharePoint Web Parts

• Report Explorer ???????????? ????????? ?? ???????
  ???????
• Report Viewer ???????????? ??? ????????? ???????
• ???????? ? SPS ? WSS

53
Reporting Services???????????? ?????????????

• ?????????? ?????????????? ?????????????
• ????????????? ????? ??????????
• ??????????? ???????? BI Development Studio
• ????? ?????????? ? ????????? ??????????
• ?????? ???????? ??? ???????? ?????????????
• ???????????? ????? ????????????? ?? ??????
• ????? ???????? ?????? ?????? ??????????????? ??
  ????? ??????? ??????????
• ?????????, ??????? ? ???????????? ?????,
  ???????????? Web ??????? ? ?????????? MS Office

54
Report Builder Client
55
Report Builder View
56
Model Designer
57
Report Builder vs Report Designer
Report Builder Report Designer
??? ??????-????????????? ??? IT-?????????????? ? ?????????????
??????????? ?????? ???????????? ??????
??????? ????????? ????????????? ? ??????? ?????????????? ???? ??? ??????????? ?????? ??????? ??????? (SQL, OLE DB, XML/A, ODBC, Oracle)
?????? ???????? ?? ???????? ?????? ? ???????????? ?????
?????????? ? ??????????? Click-once ???????????? ? Visual Studio
?? ????? ????????????? ?????? ????????? ? Report Designer ????? ???????? ? ????????, ???????????? ? Report Builder
58
?????? ??????? ??????? ??? RS
59
SQL Server 2005?????? ??????????????? ?????????
??? ?????? ? ???????
60
???????? ???????

• ??????????? ????????
• http//www.microsoft.com/sql
• SQL Server Reporting Services ?? MSDN
• http//msdn.microsoft.com/library/en-us/RSPORTAL/H
  TM/rs_gts_portal_3vqd.asp
• ?????????? ?????????? ?? SQL Server
• http//www.sql.ru/

61
(No Transcript)