SQL Server 2000, Analysis Services et DTS

Description

SQL Server 2000, Analysis Services et DTS Cyril Gruau ∗ 4 mars 2004 Resume Ce support de cours regroupe quelques notions concernant la l'implementation et le developpement de bases de donnees avec le langage SQL, une presentation de la phase ETL, la construction et la consultation de cubes OLAP en langage MDX, ainsi qu'une introduction au data mining. Les logiciels retenus pour cette etude sont SQL Server 2000 (Microsoft), accompagne des Analysis Services et des DTS.

• sql-dmo
• consultation de cubes olap en langage mdx
• multidimensional olap
• alimentation de l'entrepot
• syntaxe du langage sql
• sql
• cube
• cubes
• sql server
• donnees
• bases de donnees
• base de donnees

Sujets

• Transaction
• Oracle
• Microsoft
• Donnée
• Langage
• Entrepôt
• Server

Informations

• Publié par: iwlo0
• Nombre de lectures: 153
• Langue: Français

Extrait

SQL Server 2000, Analysis Services et
DTS
∗Cyril Gruau
4 mars 2004
R´esum´e
Ce supportdecoursregroupequelques notionsconcernantlal’impl´ementationetled´eveloppement
de bases de donn´ees avec le langage SQL, une pr´esentation de la phase ETL, la construction et la
consultation de cubes OLAP en langage MDX, ainsi qu’une introduction au data mining. Les logiciels
retenus pour cette ´etude sont SQL Server 2000 (Microsoft), accompagn´e des Analysis Services et des
DTS. Les notions abord´ees sont bien ´evidemment utilisables avec d’autres syst`emes.
Mots-clef : base de donn´ees, requˆete, SQL, transaction, OLTP, ETL, DTS
entrepˆot de donn´ees, data warehouse, cube, MDX, OLAP, data mining
Compl´ements apport´es `a l’´edition de f´evrier 2003 :
– les illustrations ..............................................................................106
– un nouveau paragraphe sur les requˆetes multibases ........................................... 25
– un nouveau chapitre sur les formulaires .......................................................51
– un nouveau chapitre sur les ´etats .............................................................62
– une r´e-organisation et une r´e-´ecriture du chapitre sur le stockage des cubes ................... 68
– une r´e-´ecriture compl`ete du chapitre sur la phase ETL ........................................75
– et quelques compl´ements MDX ...............................................................91
∗[e-mail]
1`TABLE DES MATIERES 2
Table des mati`eres
Introduction 6
I Le syst`eme transactionnel 10
1 Syntaxe du langage SQL 10
1.1 Commentaires . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 10
1.2 Noms . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 11
1.3 Op´erateurs . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 11
1.4 Variables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 11
1.5 Structures . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 12
1.5.1 Blocs . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 12
1.5.2 Branchements conditionnels . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 12
1.5.3 Boucles conditionnelles. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 13
2 Modes d’ex´ecution du code SQL 14
2.1 Ex´ecution imm´ediate . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 14
2.2 Utilisation de script . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 14
2.3 Ex´ecution par lots . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 14
2.4 Transactions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 14
2.5 D´ebogage . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 15
3 Mise en place d’une base 15
3.1 Une base et son journal . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 15
3.2 Une table . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 16
3.3 Num´erotation automatique . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 17
3.4 D´efinir les relations . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 17
4 S´electionner les donn´ees 18
4.1 S´election simple . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 18
4.2 Jointures internes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 20
4.3 Jointures externes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 20
4.4 Union des s´elections . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 21
4.5 Sous-requˆetes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 22
4.5.1 Sous-requˆete renvoyant une valeur . . . . . . . . . . . . . . . . . . . . . . . . . . . 22
4.5.2 Sous-requˆete renvoyant une liste de valeurs . . . . . . . . . . . . . . . . . . . . . . 22
4.5.3 Requˆetes correl´ees . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 24
4.5.4 Requˆetes imbriqu´ees vs. jointures . . . . . . . . . . . . . . . . . . . . . . . . . . . . 24
4.5.5 Sous-requˆete renvoyant plusieurs colonnes . . . . . . . . . . . . . . . . . . . . . . . 24
4.6 Requˆetes multibases . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 25
4.7 Quelques fonctions SQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 25
4.7.1 Fonctions d’agr´egat . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 25
4.7.2 Op´erateurs . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 26
4.7.3 Fonctions sur les dates . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 26
4.7.4 F sur les chaˆınes de caract`eres . . . . . . . . . . . . . . . . . . . . . . . . . 27
4.7.5 Principales fonctions math´ematiques . . . . . . . . . . . . . . . . . . . . . . . . . . 27
4.7.6 Fonctions utilisateur . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 27
4.8 Conclusion . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 28`TABLE DES MATIERES 3
5 Modifier les donn´ees 28
5.1 Insertion . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 28
5.2 Suppression . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 29
5.3 Mise-`a-jour . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 30
6 Contraintes 31
6.1 Syntaxe . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 31
6.2 CHECK . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 32
6.2.1 Syntaxe . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 32
6.2.2 R`egle . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 33
6.3 Valeur par d´efaut . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 34
6.4 Cl´e primaire . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 35
6.5 UNIQUE . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 35
6.6 Cl´e ´etrang`ere . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 35
6.7 Conclusion . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 36
7 Programmation ´ev´enementielle 37
7.1 Mise-`a-jour et suppression en cascade . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 37
7.2 D´eclencheurs AFTER . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 37
7.3 D´eclencheurs INSTEAD OF . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 39
7.4 Compl´ements . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 39
7.5 Conclusion . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 40
8 Vues 41
8.1 Syntaxe . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 41
8.2 Int´erˆets . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 41
8.3 Modification de donn´ees . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 42
9 Proc´edures stock´ees 44
9.1 Syntaxe . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 44
9.2 Utilisation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 45
9.3 Cryptage . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 46
10 Verrous 46
10.1 Isolation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 46
10.2 Verrouillage de niveau table . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 47
10.3 Conclusion . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 47
11 Connexions 48
11.1 Cr´eation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 48
11.2 Rˆole . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 49
11.2.1 Sur le serveur . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 49
11.2.2 Dans une base de donn´ees . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 49
11.3 Droits . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 50
11.3.1 Sur les instructions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 50
11.3.2 Sur les objets . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 50
11.3.3 Chaˆıne d’autorisation . . . . . . . . . . . . . . . . . . . . . . . . . . .