Principe de modélisation et performance

Version imprimable

01/10/2010, 14h03
Jinroh77

Principe de modélisation et performance

Bonjour à tous,
Je suis en train de tester une petite modélisation simple pour tester les différence entre une modélisation OLAP et une purement à plat.

Contexte :
Une table de fait Opération avec (5id (FK) et 3 mesures) et autour 5 table de dimension (avec 1 id (PK) et 1 code).
Mes tables de dim sont indexées sur le code.
Ma table de fait a 1 index sur chaque ID.

De l'autre côté, j'ai 1 seule table avec les codes de mes ex-tables de dim directement dans la table et 1 index sur chacun de ces code.

J'insère 2000 dans chacune de mes tables de dim et 2 000 000 de lignes de faits.
Sur l'autre j'ai 2 000 000 de lignes de faits.

Bilan OLAP/A plat:
La volumétrie est à peu près identique : 725/818

Les plans d'exécution ne sont pas beaux (en %)
Select sans filtre rapporter chacun des codes : 65/35
Group by 1 champ having count(*) : 83/17
Group by chacun des codes : 46/54
Group by + filtre sur 2 codes : 85/15
Update 1 champ avec filtre sur 1 autre : 83/17

Bref... autant dire que pour le moment, le bilan pour une modélisation OLAP est désastreux...
Moi qui pensait que cela était autre que simplement faciliter le chargement d'un cube....
01/10/2010, 14h09
Ptit_Dje

Hello,

Peux tu poster le script utilise pour effectuer ton test ?

Merci :)

Effectivement, j'ai oublié le principal...

Les tables
A plat

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
if exists (select 1
            from  sysobjects
           where  id = object_id('FTOPERATION')
            and   type = 'U')
   drop table FTOPERATION
go
 
create table FTOPERATION (
   NPV                  decimal(20,10)       null,
   DELTA                INT                  null,
   VALUE                decimal(20,10)       null,
   CODEISSUER           varchar(100)         null,
   CODEPRODUCT          varchar(100)         null,
   CODEBOOK             varchar(10)          null,
   CODETRADE            varchar(20)          null,
   TYPETRADE            varchar(10)          null,
   DATE                 date                 null
)
go
 
--index
CREATE NONCLUSTERED INDEX [Ftoperation_issuer] ON [dbo].[FTOPERATION] (	[CODEISSUER] ASC) 
CREATE NONCLUSTERED INDEX [Ftoperation_product] ON [dbo].[FTOPERATION] (	Codeproduct ASC) 
CREATE NONCLUSTERED INDEX [Ftoperation_book] ON [dbo].[FTOPERATION] (	codebook ASC) 
CREATE NONCLUSTERED INDEX [Ftoperation_trade] ON [dbo].[FTOPERATION] (	codetrade ASC) 
CREATE NONCLUSTERED INDEX [Ftoperation_typetrade] ON [dbo].[FTOPERATION] (	typetrade ASC) 
CREATE NONCLUSTERED INDEX [Ftoperation_date] ON [dbo].[FTOPERATION] (	[date] ASC) 
GO

Mode Olap

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
/*==============================================================*/
/* Nom de la base :  HORUSREPORTOLAP                            */
/* Nom de SGBD :  Microsoft SQL Server 2008                     */
/* Date de création :  01/10/2010 14:25:06                      */
/*==============================================================*/
 
 
drop database HORUSREPORTOLAP
go
 
/*==============================================================*/
/* Base de donnée : HORUSREPORTOLAP                             */
/*==============================================================*/
create database HORUSREPORTOLAP
go
 
use HORUSREPORTOLAP
go
 
/*==============================================================*/
/* Table : DIMBOOK                                              */
/*==============================================================*/
create table DIMBOOK (
   IDBOOK               int                  identity,
   CODE                 varchar(10)          not null,
   constraint PK_DIMBOOK primary key nonclustered (IDBOOK)
)
go
 
/*==============================================================*/
/* Index : BOOK_CODE                                            */
/*==============================================================*/
create index BOOK_CODE on DIMBOOK (
CODE ASC
)
go
 
/*==============================================================*/
/* Table : DIMDATE                                              */
/*==============================================================*/
create table DIMDATE (
   IDDATE               int                  not null,
   JOUR                 datetime             not null,
   constraint PK_DIMDATE primary key nonclustered (IDDATE)
)
go
 
/*==============================================================*/
/* Index : DATE_JOUR                                            */
/*==============================================================*/
create index DATE_JOUR on DIMDATE (
JOUR ASC
)
go
 
/*==============================================================*/
/* Table : DIMISSUER                                            */
/*==============================================================*/
create table DIMISSUER (
   IDISSUER             int                  identity,
   CODEISSUER           varchar(100)         not null,
   constraint PK_DIMISSUER primary key nonclustered (IDISSUER)
)
go
 
/*==============================================================*/
/* Index : ISSUER_CODE                                          */
/*==============================================================*/
create index ISSUER_CODE on DIMISSUER (
CODEISSUER ASC
)
go
 
/*==============================================================*/
/* Table : DIMPRODUCT                                           */
/*==============================================================*/
create table DIMPRODUCT (
   IDPRODUCT            int                  identity,
   CODE                 varchar(50)          not null,
   constraint PK_DIMPRODUCT primary key nonclustered (IDPRODUCT)
)
go
 
/*==============================================================*/
/* Index : PRODUCT_CODE                                         */
/*==============================================================*/
create index PRODUCT_CODE on DIMPRODUCT (
CODE ASC
)
go
 
/*==============================================================*/
/* Table : DIMTRADE                                             */
/*==============================================================*/
create table DIMTRADE (
   IDTRADE              int                  not null,
   CODE                 varchar(20)          not null,
   TYPE                 varchar(10)          not null,
   constraint PK_DIMTRADE primary key nonclustered (IDTRADE)
)
go
 
/*==============================================================*/
/* Index : TRADE_CODE                                           */
/*==============================================================*/
create index TRADE_CODE on DIMTRADE (
CODE ASC
)
go
 
/*==============================================================*/
/* Table : FTOPERATION                                          */
/*==============================================================*/
create table FTOPERATION (
   IDBOOK               int                  not null,
   IDPRODUCT            int                  not null,
   IDISSUER             int                  not null,
   IDDATE               int                  not null,
   IDTRADE              int                  null,
   NPV                  decimal(20,10)       null,
   DELTA                INT                  null,
   VALUE                decimal(20,10)       null
)
go
 
/*==============================================================*/
/* Index : FT_TRADE                                             */
/*==============================================================*/
create index FT_TRADE on FTOPERATION (
IDTRADE ASC
)
go
 
/*==============================================================*/
/* Index : FT_BOOK                                              */
/*==============================================================*/
create index FT_BOOK on FTOPERATION (
IDBOOK ASC
)
go
 
/*==============================================================*/
/* Index : FT_DATE                                              */
/*==============================================================*/
create index FT_DATE on FTOPERATION (
IDDATE ASC
)
go
 
/*==============================================================*/
/* Index : FT_ISSUER                                            */
/*==============================================================*/
create index FT_ISSUER on FTOPERATION (
IDISSUER ASC
)
go
 
/*==============================================================*/
/* Index : FT_PRODUCT                                           */
/*==============================================================*/
create index FT_PRODUCT on FTOPERATION (
IDPRODUCT ASC
)
go
 
alter table FTOPERATION
   add constraint FK_FTOPERAT_REFERENCE_DIMBOOK foreign key (IDBOOK)
      references DIMBOOK (IDBOOK)
go
 
alter table FTOPERATION
   add constraint FK_FTOPERAT_REFERENCE_DIMPRODU foreign key (IDPRODUCT)
      references DIMPRODUCT (IDPRODUCT)
go
 
alter table FTOPERATION
   add constraint FK_FTOPERAT_REFERENCE_DIMISSUE foreign key (IDISSUER)
      references DIMISSUER (IDISSUER)
go
 
alter table FTOPERATION
   add constraint FK_FTOPERAT_REFERENCE_DIMDATE foreign key (IDDATE)
      references DIMDATE (IDDATE)
go
 
alter table FTOPERATION
   add constraint FK_FTOPERAT_REFERENCE_DIMTRADE foreign key (IDTRADE)
      references DIMTRADE (IDTRADE)
go

Chacun des modèles est sur une base différente : HORUSREPORTOLAP et HorusReportOlapRelationnel

Les scripts de test :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
------------------
-- Espace Disque
------------------
use HORUSREPORTOLAP
go
SELECT
    a.FILEID,
    CONVERT(decimal(12,2),ROUND(a.size/128.000,2)) as [FILESIZEINMB] ,
    CONVERT(decimal(12,2),ROUND(fileproperty(a.name,'SpaceUsed')/128.000,2)) as [SPACEUSEDINMB],
    CONVERT(decimal(12,2),ROUND((a.size-fileproperty(a.name,'SpaceUsed'))/128.000,2)) as [FREESPACEINMB],
    a.name as [DATABASENAME],
    a.FILENAME as [FILENAME]
FROM
    dbo.sysfiles a
 
use HorusReportOlapRelationnel
go
 
SELECT
    a.FILEID,
    CONVERT(decimal(12,2),ROUND(a.size/128.000,2)) as [FILESIZEINMB] ,
    CONVERT(decimal(12,2),ROUND(fileproperty(a.name,'SpaceUsed')/128.000,2)) as [SPACEUSEDINMB],
    CONVERT(decimal(12,2),ROUND((a.size-fileproperty(a.name,'SpaceUsed'))/128.000,2)) as [FREESPACEINMB],
    a.name as [DATABASENAME],
    a.FILENAME as [FILENAME]
FROM
    dbo.sysfiles a
 
 
 
 
------------------
-- Select *
------------------
select FTOPERATION.* from HORUSREPORTOLAPRELATIONNEL.dbo.FTOPERATION
 
select 
--ft.*, 
db.CODE, dt.JOUR, ds.CODEISSUER, dp.CODE, dtr.CODE, dtr.[TYPE],
ft.NPV, ft.VALUE, ft.DELTA 
from HORUSREPORTOLAP.dbo.FTOPERATION ft
JOIN HORUSREPORTOLAP.dbo.DIMBOOK db    ON db.IDBOOK = ft.IDBOOK
JOIN HORUSREPORTOLAP.dbo.DIMDATE dt    ON dt.IDDATE = ft.IDDATE
JOIN HORUSREPORTOLAP.dbo.DIMISSUER ds    ON ds.IDISSUER = ft.IDISSUER
JOIN HORUSREPORTOLAP.dbo.DIMPRODUCT dp    ON dp.IDPRODUCT = ft.IDPRODUCT
JOIN HORUSREPORTOLAP.dbo.DIMTRADE dtr    ON dtr.IDTRADE = ft.IDTRADE
 
 
 
------------------
-- Group by Simple
------------------
select FTOPERATION.CODETRADE from HORUSREPORTOLAPRELATIONNEL.dbo.FTOPERATION
group by FTOPERATION.CODETRADE
having COUNT(*) > 100
 
 
select 
--ft.*, 
--db.CODE, dt.JOUR, ds.CODEISSUER, dp.CODE, dtr.CODE, dtr.[TYPE],
--ft.NPV, ft.VALUE, ft.DELTA 
dtr.CODE
from HORUSREPORTOLAP.dbo.FTOPERATION ft
JOIN HORUSREPORTOLAP.dbo.DIMBOOK db    ON db.IDBOOK = ft.IDBOOK
JOIN HORUSREPORTOLAP.dbo.DIMDATE dt    ON dt.IDDATE = ft.IDDATE
JOIN HORUSREPORTOLAP.dbo.DIMISSUER ds    ON ds.IDISSUER = ft.IDISSUER
JOIN HORUSREPORTOLAP.dbo.DIMPRODUCT dp    ON dp.IDPRODUCT = ft.IDPRODUCT
JOIN HORUSREPORTOLAP.dbo.DIMTRADE dtr    ON dtr.IDTRADE = ft.IDTRADE
group by dtr.CODE
having COUNT(*) > 100
 
 
 
------------------
-- Group by 1
------------------
select 
ft.TYPETRADE, ft.CODETRADE, ft.CODEISSUER, ft.CODEPRODUCT
, ft.[DATE], ft.CODEBOOK 
from HORUSREPORTOLAPRELATIONNEL.dbo.FTOPERATION ft
--where ft.CODETRADE = 'UL'
group by ft.TYPETRADE, ft.CODETRADE, ft.CODEISSUER, ft.CODEPRODUCT
    , ft.[DATE], ft.CODEBOOK
--having COUNT(*) > 100
 
 
select 
dtr.[TYPE], dtr.CODE, ds.CODEISSUER, dp.CODE, dt.IDDATE, dp.CODE
from HORUSREPORTOLAP.dbo.FTOPERATION ft
JOIN HORUSREPORTOLAP.dbo.DIMBOOK db    ON db.IDBOOK = ft.IDBOOK
JOIN HORUSREPORTOLAP.dbo.DIMDATE dt    ON dt.IDDATE = ft.IDDATE
JOIN HORUSREPORTOLAP.dbo.DIMISSUER ds    ON ds.IDISSUER = ft.IDISSUER
JOIN HORUSREPORTOLAP.dbo.DIMPRODUCT dp    ON dp.IDPRODUCT = ft.IDPRODUCT
JOIN HORUSREPORTOLAP.dbo.DIMTRADE dtr    ON dtr.IDTRADE = ft.IDTRADE
--where dtr.CODE = 'UL'
group by dtr.[TYPE], dtr.CODE, ds.CODEISSUER, dp.CODE, dt.IDDATE, dp.CODE
--having COUNT(*) > 100
 
 
------------------
-- Group by 2
------------------
select ft.TYPETRADE, ft.CODETRADE
, SUM(VALUE) as value, SUM(npv) as npv
from HORUSREPORTOLAPRELATIONNEL.dbo.FTOPERATION ft
where 
    ft.CODEPRODUCT = 'FG'
    AND ft.CODEISSUER = 'UGYHHGFHJDF'
group by ft.TYPETRADE, ft.CODETRADE, ft.CODEISSUER, ft.CODEPRODUCT
    , ft.[DATE], ft.CODEBOOK
 
 
select 
--ft.*
--db.CODE, dt.JOUR, ds.CODEISSUER, dp.CODE, dtr.CODE, dtr.[TYPE],
--ft.NPV, ft.VALUE, ft.DELTA 
dtr.[TYPE], dtr.CODE
, SUM(VALUE) as value, SUM(npv) as npv
from HORUSREPORTOLAP.dbo.FTOPERATION ft
JOIN HORUSREPORTOLAP.dbo.DIMTRADE dtr    ON dtr.IDTRADE = ft.IDTRADE
JOIN HORUSREPORTOLAP.dbo.DIMBOOK db    ON db.IDBOOK = ft.IDBOOK
JOIN HORUSREPORTOLAP.dbo.DIMDATE dt    ON dt.IDDATE = ft.IDDATE
JOIN HORUSREPORTOLAP.dbo.DIMISSUER ds    ON ds.IDISSUER = ft.IDISSUER
JOIN HORUSREPORTOLAP.dbo.DIMPRODUCT dp    ON dp.IDPRODUCT = ft.IDPRODUCT
where 
    dp.CODE = 'FG'
    AND ds.CODEISSUER = 'UGYHHGFHJDF'
group by dtr.[TYPE], dtr.CODE, ds.CODEISSUER, dp.CODE, dt.IDDATE, dp.CODE
 
 
 
 
------------------
-- UPDATE
------------------
UPDATE HORUSREPORTOLAPRELATIONNEL.dbo.FTOPERATION
SET 
    CODEISSUER = 'UGYHHGFHJDF'
WHERE 
    CODETRADE = 'UL'
 
 
UPDATE HORUSREPORTOLAP.dbo.FTOPERATION
SET HORUSREPORTOLAP.dbo.FTOPERATION.IDISSUER = ds.IDISSUER
FROM 
    HORUSREPORTOLAP.dbo.DIMTRADE dt
    , HORUSREPORTOLAP.dbo.DIMISSUER ds
WHERE
    dt.CODE = 'UL'
    and ds.CODEISSUER = 'UGYHHGFHJDF'
    AND dt.IDTRADE = HORUSREPORTOLAP.dbo.FTOPERATION.IDTRADE

Je précise également que les données sont générées via l'outil de RedGate SQL Data generator.

01/10/2010, 15h42
SQLpro

Attention, pour une base OLAP vous devez utiliser le serveur OLAP et non le serveur relationnel. Ce n'est pas le même stockage !

A +
01/10/2010, 16h02
Jinroh77

Le principe est que cette modélisation sert à une application de reporting.
Purement BDD pour le moment, mais potentiellement avec des cubes OLAP dessus ensuite.

Je pensais qu'une modélisation "au format" olap serait plus appropriée dès le début. (imaginant que nous n'utilisons pas de cubes par la suite).

Visiblement un stockage purement à plat est + performant, pas forcément plus volumineux etc.
J'en pensais le contraire et trouver quelqu'un me le confirmant.

Citation:

Envoyé par Jinroh77

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
------------------
-- Espace Disque
------------------
USE HORUSREPORTOLAP
go
SELECT
    a.FILEID,
    CONVERT(decimal(12,2),ROUND(a.size/128.000,2)) AS [FILESIZEINMB] ,
    CONVERT(decimal(12,2),ROUND(fileproperty(a.name,'SpaceUsed')/128.000,2)) AS [SPACEUSEDINMB],
    CONVERT(decimal(12,2),ROUND((a.size-fileproperty(a.name,'SpaceUsed'))/128.000,2)) AS [FREESPACEINMB],
    a.name AS [DATABASENAME],
    a.FILENAME AS [FILENAME]
FROM dbo.sysfiles a

Juste une petite remarque [Ce n'est pas l'objectif de ton post ;) ]
Puisque tu travailles sous SQL Server 2008, utilise sys.database_files
à la place de sysfiles

Code:

1
2
3
4
5
6
7
8
SELECT
    a.file_id,
    CONVERT(decimal(12,2),ROUND(a.size/128.000,2)) AS [FILESIZEINMB] ,
    CONVERT(decimal(12,2),ROUND(fileproperty(a.name,'SpaceUsed')/128.000,2)) AS [SPACEUSEDINMB],
    CONVERT(decimal(12,2),ROUND((a.size-fileproperty(a.name,'SpaceUsed'))/128.000,2)) AS [FREESPACEINMB],
    a.name AS [DATABASENAME],
    a.physical_name AS [FILENAME]
FROM sys.database_files a

A+

01/10/2010, 16h40
Ptit_Dje

Ce qui est interessant avec une modelisation en etoile s'est de pouvoir re-utiliser les differentes dimensions dans differents datamarts afin de pouvoir faire des analyses transersalles a plusieurs domaines metiers d'une entreprise.
En effectuant une matrice Metier/Dimension (Bus matrix) on peut facilement voir les dimensions communes a plusieurs metiers d'une entreprise.
C'est l'interet des shared dimensions.

De plus, dans ce cas-ci, tu effectues ton test en te basant sur des dimensions possedant uniquement un code. Hors une dimension est une table qui s'etire plutot horizontalement que verticalement (fact).
De ce fait si tu entres differentes descriptions dans tes dimensions (imaginons une traduction de tous les champs en differentes langues et autres...), avec une dimension d'une 100aine de colonnes, la ligne definie une seule fois et referencee via une clef dans une fact prendra BEACOUP moins de place que de dupliquer cette information des millions de fois (le nombre de fois des lignes de ta facts).

Imagine 5 dimensions de 10000 lignes de 5ko, et une table de fact de 10 000 000 de lignes.
Je te laisse calculer le poids de ces 6 tables si tu mets tout a plat par rapport a ce que tu as si chaque ligne est referencee par un integer de 4bytes.

Je pense que ca repond en partie a ta question.

Un chouette bouquin etant : " The Data Warehouse Toolkit, 2nd Edition" des editions Kimball.
01/10/2010, 16h54
Jinroh77

Merci pour cette analyse.
Cela bénéficie sur l'argument de la taille de la base qui sera alors moins important en terme de données pour une modélisation OLAP.
Mais si l'on considère que l'on pose un index sur les même attributs des tables de dimensions que ces mêmes attributs présent directement dans la table de faits de la modélisation à plat, la taille des index est alors sensiblement identique.
On économise en espace sur les données mais on est identique sur la taille des index.

Entre temps, j'ai augmenter le nombre de ligne dans les tables de faits, je suis passé à 4 000 000 de lignes et là, la tendance s'inverse...

Bilan OLAP/A plat:
La volumétrie est à peu près identique : 1360/1598

Les plans d'exécution sont maintenant meilleurs (en %)
Select sans filtre rapporter chacun des codes : 26/74
Group by 1 champ having count(*) : 56/44
Group by chacun des codes : 7/93
Group by + filtre sur 2 codes : 65/35
Update 1 champ avec filtre sur 1 autre : 82/18

Je précise qu'après l'ajout des 2 000 000 de lignes j'ai fait tourner un plan de maintenance pour tout recalculer, mettre à jour etc.

on voit donc bien que cette modélisation commence à devenir un peu plus intéressante lorsque l'on augmente le nombre de ligne de faits sans augmenter la partie dimensions. Ce qui est finalement logique et bien dans le sens de cette utilisation de la modélisation OLAP.

Cette application de reporting, intégrera au final 1 000 000 de lignes par jour dont elle doit conserver 6 semaines glissantes de données + le dernier du mois sur 5 ans.
Même si les dimensions vont augmenter au cours du temps, elle resteront beaucoup plus statiques que les faits qui vont exploser.

Je retrouve enfin les bienfaits de cette modélisation...

Les tests continuent et si vous avez des idées, arguments, je suis tout preneur.
03/10/2010, 13h15
SQLpro

Encore une fois, si vous stockiez cela dans une base réellement OLAP en utilisant le moteur SSAS, les performances seraient toutes autres :
1) le stockage est organisé pour que les NULL n'y figurent pas (aucun volume, alors que dans une base OLTP cela occupe toujours de la place.
2) au fur et à mesure de vos requêtes des agrégats sont pré calculés et stockés dans la base
3) le gestion du cache n'est pas la même : en OLTP on ne stocke pas de résultats, mais en OLAP oui, (cache warm up).

A +
04/10/2010, 09h06
Jinroh77

Merci pour ces recommandations sur le stockage OLAP.
Je suis moi-même consultant BI mais là je travaille une autre équipe qui cherche à stocker ces données.

Le problème est un peu plus large car cette première opération est une "simple" table intermédiaire qui permet ensuite de calculer des agrégats, toujours les mêmes, que nous stockerons dans une seconde table. et c'est uniquement cette seconde qui est ensuite interrogé.
De plus, cette autre table sera interrogé systématiquement au niveau le plus fin, sans agrégats, mais avec des filtres particuliers.
De plus, ils restituent ensuite ces données sur une grille .NET qui va interroger cette table avec l'entity framework, en T-SQL.

La modélisation OLAP sert donc dans un premier temps d'optimiseur de stockage (volume, performance) et dans un second temps (moyen terme) pour créer ensuite un véritable cube libre d'accès aux utilisateurs.