presentation

Microsoft TechTalk
OLAP Programmierung
Gerhard Hieber
Partner Group
Microsoft GmbH
Agenda




Einführung in OLAP
Clientprogrammierung mit ADO/MD
MDX - Multidimensionale Erweiterung
Programmierung mit DSO
Data Warehousing Framework
Building
Managing
Data Warehouse Design
(Logical/physical schema/Data flow)
Operational
Data
(OLE-DB)
DB Schema
Data
Transformations
(DTS)
Transformation
Using
Data Mart Design
(Schema)
Data Marts
(SQL Server &
OLAP Server)
Scheduling
End-User Tools
3rd Party
(Excel, Access,
English Query)
OLAP
Microsoft Repository
Data Warehouse Management
(Console, Scheduling, Events,Topology)
Data Flow
Meta-Data Flow
Beste OLAP Definition





Fast
Analysis
Shared
Multidimensional
Information
Source: The OLAP Report (http://www.olapreport.com)
OLAP Konzepte





Dimensionen
Hierarchien
Maße/Meßwerte
Aggregationen
Cubes
Mehrdimensionale Daten
“Hey . . . I sold $100M worth of goods”
Produkt
USA
Asien
Europa
Juice
Cola
Milk
Cream
Toothpaste
Soap
1 2 3 4 5 6 7
Dimension: Produkt, Region, Zeit
Hierarchisches Summieren
Produkt
Industrie
Region
Land
Zeit
Jahr
Kategorie
Region
Quartal
Produkt
Stadt
Monat
Monat
Büro
Tag
Woche
Was ist Analysis Services?

Microsoft SQL Server Analysis Services
•
•
•
OLAP server
OLAP Manager MMC Snap-In
PivotTable Service auf dem Client
•
•
•
Stellt OLE-DB for OLAP bereit
Object Modell ist ADO MD
Wird mit Excel 2000 geliefert; unterstützt Offline-Nutzung
Architektur
OLAP Manager
Processing
Querying
Applikation
OLAP
Store
OLAP
Server
ADO MD
Other
OLE DB
Providers
OLEDB for OLAP
DSO
PivotTable Service
SQL Server
Data
Warehouse
Flexible Architektur



Diskussion zwischen MOLAP und ROLAP
Anbietern über Kundenbedürfnisse
Analysis Services bietet beste Unterstützung und
nahtlose Integration von MOLAP, ROLAP, und
HOLAP
Benutzer und Applikation sieht nur Cubes
OLAP Problem
Anzahl Aggregationen
Datenexplosion
70000
65536
60000
50000
40000
30000
20000
16384
10000
0
16
2
3
4
4096
1024
256
81
5
6
Anzahl Dimensionen
(4 Level in jeder Dimension)
7
8
Produkt
Familie
Produkt
Quartal
Monat
Quartal
Produktfamilie
Höchste Aggregation
Produkt
Monat
Aggregationsassistent
findet die optimalen
Aggregationen
Fakten Tabelle
Datenexplosion

Aggregation Wizard findet die “80-20”Regel
•
•
Kleinste Anzahl aller Pre-Aggregations, die größte
Performanceverbesserung versprechen
Prüft Level Anzahl jeder Dimension und Parent-Child
Verhältnis jedes Levels
Zusammenarbeit Client/Server Cache
Query 1) Zeig mir Jan99,
Feb99, und März99 Sales
Query 2) Zeig mir
Q199 Sales
Client
Client kann
1) Jan99, Feb99, and Mar99 Sales
kalkulieren !
2) Q199 Sales
3) Q198 Sales
Query 3) Zeig mir
Q199 & Q198 Sales
Nur Q198
vom Server
benötigt !
Server
1) Jan99, Feb99, and Mar99 Sales
3) Q198 Sales
PivotTable Service
ISV Apps
Excel 2000 ADO/MD
Cube
files
Lokaler
Store
OLE DB for OLAP
PivotTable Service
•APIs
•Caching
•Desktop Engine
ROLAP
cubes
OLE DB
OLAP Services
SQL Server
Microsoft Universal Data Access
Business Application
Active Data Objects (with ADO/MD)
OLE DB
OLE DB
for OLAP
OLE DB
OLE DB
ADs
RDBMS
MD
STORE
Mail
Server
Active
Directory
OLE DB for OLAP

OLEDB f. OLAP

ADO MD

MDx

COM Interface (OLE DB)
•
Teil von OLE DB 2.x
Clientseitige Datenquery
Erlaubt
•
•
•
Schema Information
Query Definition
Holt Zellen des ErgebnisDatasets
Nur VC++
Industrie-Unterstützung



OLE DB for OLAP
•
•
von ca. 40 Herstellern unterstützt
ACG, Arbor, Brio, Business Objects,
Cognos, Comshare, Hummingbird,
Knosys, MIS AG, Portola, Seagate,
TopTier und weitere haben Versionen
MS Excel und Access Gruppe !!
OLAP Services benutzt OLE DB for
OLAP als Standard-Schnittstelle
Office 2000 und OLAP


Excel 2000 (XL2K)
•
•
•
Pivot-Table Service auf Client
Native OLEDB for OLAP Support
Local CUBE Storage und Caching
Access 2000 (A2K)
•
•
•
Pivot-Table Service auf Client
Server Page Generation (.ASP)
Jet oder die neue MSDE
Office Web Components


Komponenten auf Basic der Office Analyse und
Reporting Funktionen
ActiveX Komponenten, die interaktives Reporting
und Analyse bereitstellen
• PivotTable Component
• Charting Component
Office Web
• Spreadsheet Component
Components
IIS
• DataSource Component
Internet
Explorer
ASP
Office Web
Component
HTTP
Component Services
lokal
Office services
auf dem Server
ADO MD

OLEDB f. OLAP

High Level Objekt Modell
•
Exposes OLEDB f. OLAP
ADO MD
•
•
•
MDx
•


Basiert auf ADO
Browsen von CubeDefs
Ausführen der Query
Fetchen der Zellen vom
resultierenden Cellset
Erzeugen neuer Formeln
unterstützt VB, J++, VC++
Einfache Anwendung
ADO und ADOMD Objekte
Data
Access
Meta Data
CellSet
Catalog
Connection
Axis
CubeDef
Cell
Cube
Cellset Beispiel
X-axis has 2 dims SalesRep & Geography
2 Slices - Measures
& Products
SELECT List has
2 <axis specs>
Sales Of Computers
Nelson
White
USA
Japan
USA_North
USA_
Seattle Boston South
1991,
Qtr1
USA
Japan
USA_North
USA_
Seattle Boston South
Jan
00
10
20
30
40
50
60
70
Feb
01
11
21
31
41
51
61
71
Mar
02
12
22
32
42
52
62
72
1991, Qtr2
03
13
23
33
43
53
63
73
1991, Qtr 3
04
14
24
34
44
54
64
74
Oct
05
15
25
35
45
55
65
75
Nov
06
16
26
36
46
56
66
76
Dec
07
17
27
37
47
57
67
77
1991,
Qtr4
Y-axis has 1 dimension - Time
ADO MD Objektmodell
CubeDefs
Dimensions
Hierarchies
Connection
Levels
Cellset
Members
Axes
Positions
Members
Cells
Connecting ...

Ähnlich dem ADO Recordset
Dim ActiveCon As New ADODB.Connection
Dim cs As New ADOMD.Cellset
ActiveCon.Open "PROVIDER=MSOLAP;” _
& “Data source=MyServer;” _
& “INITIAL CATALOG=Foodmart”
cs.Open Mdx_Statement, ActiveCon
Browsen des Cellset

CellSet vergleichbar mit
Array …
for i = 0 to _
cs.Axes(0).Positions.Count - 1
For j = 0 To _
cs.Axes(1).Positions.Count -1
MsgBox cs.Item(i, j).Value
next j
next i
MDx - Multidimensional Expressions
OLEDB f. OLAP
ADO MD
MDx

Syntax zur Definition
eines Datasets
SELECT <axis-spec>,<axis-spec>,..
FROM <cube-spec>
WHERE <slicer-spec>
Erste MDX Query !!!
MDX

select [Gender].children on rows,
{[Time].[1997], [Time].[1997].[Q1],
[Time].[1997].[Q2].children,
[Time].[1997].[Q3]:[Q4] } on columns
from Sales
OLAP ist Multidimensional

“Verkaufte Produkte pro Jahr in den USA”
Produkt
USA
SLICE
Asia
Europe
Juice
Cola
Milk
Cream
Toothpaste
Soap
1 2 3 4 5 6 7
Monat
Demo
Pivot Report
Dimension
Level
Row
Produkt
Column Zeit
Name
Monat
Slice
Slice
USA
Sales
Region
Measures
SELECT {
([Time].[1997]),
([Time].[1998].[Q1]:[Q3]), [Time].[1998].[Q4].Children}
ON COLUMNS,
{([Product].Members)}
ON ROWS
FROM Office2000
WHERE ([Measures].[Sales Store] , [Region].[USA])
Cellset Beispiel aus der MDX Sicht
X-axis: Tuples made of 2 members 1 from Salesrep, 1 from Geography
Slicer: 1 tuple, made of 2
members - 1 from Measures,
1 from Products
Sales Of Computers
Nelson, Nelson, Nelson, Nelson, White, White, White, White,
Seattle Boston USA_ Japan Seattle Boston USA_ Japan
South
South
1991Jan
00
10
20
30
40
50
60
70
Each axis is a set 1991Feb
of tuples
01
11
21
31
41
51
61
71
1991Mar
02
12
22
32
42
52
62
72
1991Q2
03
13
23
33
43
53
63
73
1991Q3
04
14
24
34
44
54
64
74
1991Oct
05
15
25
35
45
55
65
75
1991Nov
06
16
26
36
46
56
66
76
1991Dec
07
17
27
37
47
57
67
77
Data cells occur at the
intersection of tuples
Y-axis: Tuple is just a single
member from Time dimension
Set Expressions

Top und Bottom Anweisungen:
•
TOPCOUNT(<set>,<index>,<num-exp>)
liefert die top <index> Member
basierend auf <num-exp>
•
•

TOPPERCENT, TOPSUM
BOTTOM ebenso
Filtering eines Sets:
•
FILTER(<set>, <search condition>)
entfernt Member aus <set>, die nicht der
<search condition> entsprechen
Example Set Expressions

Produkte geordnet nach Sales,
hierarchisch, aufsteigend
ORDER(Products.MEMBERS, Sales)

Produkte geordnet nach Sales, nicht
hierarchisch, aufsteigend
ORDER(Products.MEMBERS, Sales, BASC)

Produkte deren Sales 1996 > 1997 Sales
war
FILTER(Products.MEMBER,
(Sales,[1996]) > (Sales,[1997]))
Children
Time.[97].FirstChild
All
97
Jan
98
Q1
Q2
Q3
Q4
Q1
Feb
Mar
Oct
Nov
Dec
Q2
Q3
Q4
Children
Time.[97].Children
All
97
Jan
98
Q1
Q2
Q3
Q4
Q1
Feb
Mar
Oct
Nov
Dec
Q2
Q3
Q4
Descendants
Descendants(Time.[97], Quarter) All
97
Jan
98
Q1
Q2
Q3
Q4
Q1
Feb
Mar
Oct
Nov
Dec
Q2
Q3
Q4
Descendants
Descendants(Time.[97], Month)
All
97
Jan
98
Q1
Q2
Q3
Q4
Q1
Feb
Mar
Oct
Nov
Dec
Q2
Q3
Q4
“It’s just a question of time ….”
Wie ist der Umsatz der gegenwärtigen im
Vergleich zur vorhergehenden Periode ?
Umsatzvergleich der gegenwärtigen Periode
mit der gleichen Periode des Vorjahres ?
Voraussetzung ist die
richtige Zeit ....
Wie ist der Umsatz der gegenwärtigen im
Vergleich zur vorhergehenden Periode ?
1Year
1997
Quarter
Month
Q1
Jan
Feb
Mar
Q2
April
May
Jun
Q3
Jul
Aug
Sep
Q4
Oct
Nov
Dec
Sales
790
120
30
40
50
200
65
45
90
185
55
60
70
285
80
100
105
Result
10
10
80
15
-20
45
-15
-45
5
10
100
10
20
5
Wie ist der Umsatz der gegenwärtigen im
Vergleich zur vorhergehenden Periode ?
Year
Quarter
Q2
Month
April
May
Jun
Sales
200
65
45
90
Result
80
15
-20
45
Time.CurrentMember, Measures.CurrentMember
Time.CurrentMember,Measures.Sales
Time.CurrentMember.PrevMember, Measures.Sales
Umsatzvergleich der gegenwärtigen Periode mit
der gleichen Periode des Vorjahres ?
Year
1997
Quarter
Q1
Q4
1998
Q1
Q4
Month
Total 97
Total Q1
Jan
Feb
Mar
Sales
Sales
Total Q4
Oct
Nov
Dec
Total 98
Total Q1
Jan
Feb
Mar
285
80
100
105
170
50
55
65
50
20
Total Q4
Oct
Nov
Dec
275
90
100
85
-10
10
Result
120
30
40
50
Umsatzvergleich der gegenwärtigen Periode mit
der gleichen Periode des Vorjahres ?
Year
1997
Quarter
Q1
Q4
1998
Q1
Q4
Month
Total 97
Total Q1
Jan
Feb
Mar
Sales
Sales
Total Q4
Oct
Nov
Dec
Total 98
Total Q1
Jan
Feb
Mar
285
80
100
105
Total Q4
Oct
Nov
Dec
275
90
100
85
Result
120
30
40
50
170
50
55
65
20
(Measures.Sales,Time.CurrentMember)(Measures.Sales,ParallelPeriod(Year,1,Time.CurrentMember)
Warum MDX, statt SQL?


In OLAP ist Ergebnis ein Cube
•
In OLAP sind Aggregationen immer
vorhanden.
•
•

Abb. Cubes auf Tables ist unnatürlich
SQL Tables speichern atomare Daten
Aggregationen sind Teil der Query
(GROUP BY)
Hierarchien üblich bei OLAP Dimensionen
OLAP Services Architektur
OLAP Manager
Add-Ins
DSO
Custom Apps
Rep.
OLAP Service
Rep.
MOLAP
Store
Client Side
ROLAP Store
(SQLServer &
Any OLE DB)
DSO Basisstruktur
MDStore:
• Database
• Cube/Virtual Cube
• Partition
• Aggregation
MDStore
Dimensions
Levels
Measures
MDStores
And the 5th Element...
So sieht es dann aus ...
Server
MDStores
Dimensions
Levels
Databases...
Measures
MDStores
Dimensions
Levels
enthält Cubes ...
Measures
MDStores
Dimensions
Levels
enthält Partitionen ...
Measures
MDStores
Dimensions
Measures
Levels
enthält Aggregationen ...
Fazit



Integration Office2000 - SQL 2000/OLAP
Eigenentwicklung mit ADO und DSO
Notwendige Kenntnisse
•
•
•
•
ADO 2.x Objektmodell
Cellset und Catalog Objektstruktur
Zugriffssprache MDX sehr wichtig
evtl. Methoden/Eigenschaften der Office
2000 Web Components
Wo gibt’s weitere Info’s?


msdn online
•
•
msdn
•
•
•

http://www.microsoft.com/sql/
http://www.microsoft.com/germany/backoffice/sql/
MSDN Online SQL Server Developer Center
Developing Effective Decision Support Objects
(DSO)
Data Transformation Services (DTS) in SQL2000
msnews.microsoft.com
• microsoft.public.sqlserver.olap
• microsoft.public.sqlserver.datawarehouse
Fragen!?
Uff...
Glossar









Datawarehousing: Verdichtete, bereinigte und konsolidierte
Datenbasis als Voraussetzung für OLAP Analyse.
OLAP: Online Analytical Processing. Schnelle fachliche Analyse von
mehrdimensional aufbereiteten Daten.
DTS: Data Transformation Services, ETL Komponente von SQL Server.
ETL: Extraction, Transformation und Loading Tool.
MDAC: Microsoft Data Access Components.
OLEDB for OLAP: Spezifikation für den einheitlichen Zugriff auf und
die Darstellung von mehrdimensionalen Daten basierend auf dem
OLEDB API.
DSO: Decision Support Objects. Objektinterface für den Zugriff auf und
die Definition von mehrdimensionalen Datenbanken (Cubes).
Cube: Mehrdimensionaler Würfel (Datenbank)
Office Web Component: Active X Komponenten für den Zugriff auf
und die Darstellung von mehrdimensionalen Cubes. Bestandteil von
Office 2000.
Empower people
through great software
any time, any place,
and on any device