Microsoft SQL Server

1
???????????

• Microsoft SQL Server
• ????

2
??

• ??? SQL Server
• SQL Server 2000 ???BI???
• OLAP?????
• ? Office ???????
• T3 ??TB??????

3
???SQL Server

• ?????,??????????,?????
• ????????,??????15???
• ????????
• ?????????
• ????????
• ?????

4
?SQL Server???, SQL Server??????????!
5
??????????

• ?????
• ???????????
• ??40? ?????????????????

100
40
20
80
60
6
Microsoft SQL Server 2000
??????????? ??????????????????,?????????????

• ??Web??
• ??XML??
• ??Web???,?????
• ?????????
• ???????
• ??????? SMP ??
• ??????
• ??,??????
• ???????

7
??

• ??? SQL Server
• SQL Server 2000 ???BI???
• OLAP?????
• ? Office ???????
• T3 ??TB??????

8
???????
Source Gartner Research
9
???????
http//www.olapreport.com
10
Microsoft????????
??????????????,Microsoft OLAP???2001?????? OLAP
Report?OLAP Survey????Nigel Pendse?,??OLAP????(??
?????????)??,Microsoft???2001?????,?????Microsoft?
???????????????????????????
Source OLAP Report
11
????? ????
CRM and eCRM
Call Center

• ?????? (ERP)
• ?????? (CRM)
• ????? (SCM)
• Web ??????
• ????????

Marketing Campaign Mgmt
Internet
Financial/ Accounting
Inventory
Procurement
HR
Intelligence is data with relevance and context
12
????????
??? RDBMS, ETL, OLAP, Data Mining, Meta Data, Mgt
tools
Data Warehousing Alliance and Framework, Open
Data Access APIs, Data Source Connectivity
Easy to Build and Manage, Lowest TCO
?????????
13
SQL Server 2000 Analysis ServicesMicrosofts
????????
14
??? BI ???

• SQL Server 2000
• ?????RDBMS??????,??
• Data Transformation Services
• Analysis Services (OLAP and Data Mining)
• Metadata Services
• English Query
• Office XP
• Excel ??? PivotTable Service
• ??????Web??
• SharePoint
• Microsoft Data Analyzer

15
???BI???
16
???BI???
EXTRACTION TRANSFORM LOAD MANAGE
SOURCE SYSTEMS (LEGACY)
DATA WAREHOUSE AND / OR DATA MARTS
END USER ACCESS AND ANALYSIS
PRESENTATION / OLAP
OLE DB
OLAP including HOLAP, ROLAP, MOLAP query
services may store atomic data may be
frequently refreshed
XML/A
Data Warehouse or Data Mart (dimensional,
subject oriented) and Operational Data Store
OLE DB
Data Mining Models forecasting clustering
scoring
XML/A
17
Analysis Services ????
Analysis Services
Application
Analysis Server
TCP/IP
HTTP
OLAP Engine
Data Mining Engine
18
Data Transformation Services

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

19
OLAP ???

• SQL Server??????
• ????,?????????
• ?????? OLE DB???
• ???????????????
• ????? MOLAP, HOLAP, ROLAP

20
??

• ??? SQL Server
• SQL Server 2000 ???BI???
• OLAP?????
• ? Office ???????
• T3 ??TB??????

21
SQL Server 2000 Analysis Services OLAP?????

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

22
SQL Server 2000 Analysis Services?????????-- OLAP

• OLAP Online Analytical Processing
• OLAP ??????????
• OLAP ??????? cubes
• ?????????????????
• ????(dimension)???(measures)???
• ???????????????
• ????????????????????
• ???????????

23
SQL Server 2000 Analysis ServicesCube ??
Schema and Tables

• Schema
• ????????????(???)
• ?cube????
• ???
• ??schema??????
• ????????? (facts)
• ?????????
• ???
• ???????????

24
SQL Server 2000 Analysis ServicesCube ?? ?????

• ?? (Measures)
• ???????
• Values that are analyzed and aggregated
• ??(Dimensions)
• ???????
• ????????????
• ??(Levels)
• ?????????
• ????
• Time levels might be Year, Quarter, Month

25
SQL Server 2000 Analysis Services????????

• Formerly OLAP Services (SQL Server 7)
• Middle tier for OLAP and Data Mining
• OLAP ????
• ?????,???????

• ???????
• ??? cube?????
• ????????
• Find products by brand and customer

26
OLAP An Example
???????????????24?????????
Coca-Cola
Food
03
10
17
24
24
Week Ending
27
SQL Server 2000 Analysis Services???? ???????

• ??????? ????????
• ?????????
• ????
• ?????
• ?? ?????!
• ???????????
• ???????
• ?????????
• ????
• ?????????
• ??????????
• Decision tree from SQL Analysis Services

28
SQL Server 2000 Analysis ServicesData Mining
????????

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

29
SQL Server 2000 Analysis ServicesDecision Trees
First Classification
30
SQL Server 2000 Analysis ServicesDecision Trees
?????
31
Data Mining Services

• ???????????? - ???
• ??????
• ??????

Income
Good (2)
Poor (3)
High
Low
Good (2)
Good (0)
Poor (1)
Poor (2)
Married
Not married
Good (2)
Good (0)
Poor (0)
Poor (1)
32
Data Mining Services

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

33
SQL Server 2000 Analysis Services??

• ?????????
• ????????????
• Queries lead to more queries
• ?????????
• Answers questions ahead of time
• Improves query performance
• Set when cube is saved, and is reconfigurable
• Trade-off Performance against space
• More aggregations take up more space
• Analysis services provides estimates

34
Analysis Services ???? ???? ???????

• ????
• OLAP???????
• 100??
• ????????
• ?????????
• ????

35
OLAP -- Scalability

• ???,??????
• ?????????
• ?????/????????????
• Virtual cubes (similar to views)
• Partitioned cubes, ?????????

36
??

• ??? SQL Server
• SQL Server 2000 ???BI???
• OLAP?????
• ? Office ???????
• T3 ??TB??????

37
Analyzing Data with Data Analyzer??????????
Tool OfferingsCURRENT
Tool OfferingsTODAY
????
????
??????? - ????? - ????? - ?????
MS partner offerings--ProClarity--Cognos--Busin
ess Objects --and more
MS partner offerings--ProClarity--Cognos--Busin
ess Objects--and more
???
Excel 2002 Data Analyzer --data
visualization--simple analysis--publish to
Excel, PowerPoint, and web
???????--?????? --?????/?? --????
Excel 2002 --PivotTables--PivotCharts
?????
Integrated analysisand publishingexperience
Sharing .xls filesSimple save to web
??????
???????
38
Microsoft Office XP

• OLE DB for OLAP in Excel PivotTables
• Office Web Components for browser-based analysis
• Access 2000 compatibility with SQL Server

39
BI Integration with OfficeExcel BI Integration

• ???????????????
• Excel ???
• ??????????
• ??
• OLAP PivotTables
• Data Connection Wizard and Office Data Connection
  Files
• XML support
• Excel PivotTable

40
BI Integration with OfficeOffice Web Components

• Office Web Components deliver
• Spreadsheets
• Charts
• PivotTables/PivotCharts
• ?????????? web ??!
• ???

41
Digital DashboardAn aggregated personalized view
of a Digital Nervous System
42
Microsoft Data Analyzer

• ???????????
• ??????????????,?????
• ??????????
• ?? SQL Server ?????

43
Analysis ServicesHTTP Connectivity Architecture

• ConnectionString Provider MSOLAP
• Data Source http//www.cubeserver.com

msolap.asp
44
??

• ??? SQL Server
• SQL Server 2000 ???BI???
• OLAP?????
• ? Office ???????
• T3 ??TB??????

45
T3 Project Goals

• ??????,??,??????OLAP?????
• ???????,??????? ???????cube?!
• ????,??,???,?? Analysis Services, ES7000 server
  ,EMC???????
• ProClarity??????????
• ?????1.2TB????????MOLAP??????

Unisys
46
T3 Cube

• ??????,?? 7.7 billion ?, ??
• 716,000 ??
• 3 ??
• 71 ??
• ????, ???????
• ????31?measures
• ??????140???
• 1.2 TB ????????? ????
• ???????

47
?????

• ?????

48
??

• ??
• 7.7 billion ?, 50 ??
• 153 million ?/??
• 42,000?/?
• 60-70 CPU ???
• ??
• ??????1.2 ? (???)
• ??????0.08 ? (warm??)
• ????CPU??

49
(No Transcript)
50
T3 ??

• ??Cube???
• 50??18?? ?????cube
• ???(8?)?????4?????
• 50 ????
• 1.2 ??????? (cold cache)
• 0.08???????(warm cache)
• T3 ??????????????TB?MOLAP???
• The OLAP Report

51
(No Transcript)