Statistiques, partitioning, vues matérialisées, query rewrite, dimensions, indices bitmap et bitmap join, parallelisme, Olap ou Exadata Storage Server, les fonctionnalités Oracle Database 11g Release 2 liées au décisionnel sont nombreuses et pas toujours simples à tester. Bien sur, il y a le schéma SH sur lequel vous pouvez vous appuyer pour une bonne partie de vos tests. Toutefois, SH implémente déjà toute ces fonctionnalités et je vous propose plutôt la démarche inverse, à savoir de partir de rien et de construire, étape par étape, votre système décisionnel.
Dans ce premier article, intitulé « Dimensions, Partitions et Données », vous trouverez la première étape de la construction d’un système décisionnel qui s’appuie sur Oracle 11g, un partitionnement composite par interval et hash et génère des données de manière désordonnées. Contrairement à SH, les noms des tables ne sont pas toujours très opportuns, mais l’important n’est-il pas d’avoir un exemple pour effectuer ses tests?
Table et Partitionnement Composite par Interval/Hash
Dans un premier temps, nous allons créer une table de faits qui contient les informations de détail que l’on veut exploiter. La table pointera au niveau de détail sur 4 dimensions dont la première est une dimension de type date et la 3ème a une clé utilisant 2 colonnes. Une fois la table créée, nous pourrons générer un jeu de données et prendre les statistiques associées :
drop table fact purge;
create table fact(time_id date,
dim2_id number,
dim3_id1 number,
dim3_id2 number,
dim4_id varchar2(15),
attr1 varchar2(2),
attr2 varchar2(5),
attr3 number,
attr4 date,
metric1 number,
metric2 number)
partition by range(time_id) interval(NUMTOYMINTERVAL(1,'MONTH'))
subpartition by hash(dim2_id) subpartitions 2
(partition P0 values less than (to_date('01/01/2009','DD/MM/YYYY')))
nologging;
insert /*+append */ into fact
select t.time_id,
d2.dim2_id,
d3.dim3_id1,
d3.dim3_id2,
d4.dim4_id,
to_char(mod(t_val,9)),
'ZZ'||to_char(mod(t_val+d3_val,127)),
mod(t_val+d3_val+d2_val,1034),
to_date('01/01/2009','DD/MM/YYYY')
+ mod(t_val+d3_val+d2_val-d4_val,365),
mod(t_val*d3_val*d2_val+d4_val,2009),
mod(t_val*d3_val*d2_val*d4_val,40003)
from (select rownum t_val,
to_date('01/01/2009','DD/MM/YYYY')
+ floor(rownum/27) time_id
from dual
connect by level <= 10000) t,
(select rownum d2_val,
rownum dim2_id
from dual
connect by level <= 10) d2,
(select mod(rownum,7) d3_val,
mod(rownum,3) dim3_id1,
floor(rownum/3) dim3_id2
from dual
connect by level <= 30) d3,
(select rownum d4_val,
'AXIS '||to_char(rownum) dim4_id
from dual
connect by level <= 4) d4;
commit;
exec dbms_stats.gather_table_stats(user, -
'FACT', cascade=>true, -
method_opt=>'for all columns size 254');
Dimension temporelle
La première dimension est une date. Nous allons créer et alimenter une table qui contient l’ensemble des jours stockés dans la table de faits ainsi que les différents niveaux que nous pourrons utiliser pour nos aggrégations (mois, trimestres, années) :
drop table time purge;
create table time (time_id date,
date_lib varchar2(25),
month_id date,
month_lib varchar2(25),
quarter_id date,
quarter_lib varchar2(25),
year_id date,
year_lib varchar2(25))
partition by range(time_id) interval(NUMTOYMINTERVAL(1,'MONTH'))
(partition P0 values less than (to_date('01/01/2009','DD/MM/YYYY')))
nologging;
insert /*+ append */ into time
select time_id,
to_char(time_id,'YYYYMMDD'),
trunc(time_id, 'MM'),
to_char(trunc(time_id, 'MM'),'YYYYMMDD'),
trunc(time_id, 'Q'),
to_char(trunc(time_id, 'Q'),'YYYYMM'),
trunc(time_id, 'YYYY'),
to_char(trunc(time_id, 'YYYY'),'YYYY')
from (select to_date('01/01/2009','DD/MM/YYYY')+rownum-1 time_id
from dual
connect by level <= 371) t;
commit;
exec dbms_stats.gather_table_stats(user, -
'TIME', cascade=>true, -
method_opt=>'for all columns size 254');
create unique index time_pk
on time (time_id) local;
alter table time
add constraint time_pk
primary key (time_id)
using index time_pk;
Seconde dimension
La seconde dimension est utilisée comme clé de hash du sous partitionnement de la table de fait. Nous allons créer et remplir la dimension associée :
drop table dim2 purge;
create table dim2 (dim2_id number,
dim2_lib varchar2(25),
dim2_l1 number,
l1_lib varchar2(25))
partition by hash(dim2_id) partitions 2
nologging;
insert /*+ append */ into dim2
(select rownum,
'Value '||to_char(rownum),
mod(rownum ,3),
'Aggregate '||to_char(mod(rownum ,3))
from dual
connect by level <= 10);
commit;
exec dbms_stats.gather_table_stats(user, -
'DIM2', cascade=>true, -
method_opt=>'for all columns size 254');
create unique index dim2_pk
on dim2 (dim2_id) local;
alter table dim2
add constraint dim2_pk
primary key (dim2_id)
using index dim2_pk;
3ème dimension multi-colonnes
La clé primaire de la troisième dimension est constituée de 2 colonnes. Nous allons créer et remplir la dimension associée :
drop table dim3 purge;
create table dim3 (dim3_id1 number,
dim3_id2 number,
dim3_lib varchar2(25),
dim3_l1 number,
l1_lib varchar2(25))
nologging;
insert /*+ append */ into dim3
(select mod(rownum,7) d3_val,
mod(rownum,3) dim3_id1,
'Value ('||to_char(mod(rownum,7))||','||to_char(mod(rownum,3))||')',
floor(rownum/3) dim3_l1,
'Aggregate '||to_char(floor(rownum/3))
from dual
connect by level <= 21);
commit;
exec dbms_stats.gather_table_stats(user, -
'DIM3', cascade=>true, -
method_opt=>'for all columns size 254');
create unique index dim3_pk
on dim3 (dim3_id1,dim3_id2);
alter table dim3
add constraint dim3_pk
primary key (dim3_id1,dim3_id2)
using index dim3_pk;
4ème dimension
La clé primaire de la quatrième dimension est constituée d’une colonne de type varchar2. Cette dimension, contient 2 niveaux en plus du niveau de détail et du total :
drop table dim4 purge;
create table dim4 (dim4_id varchar2(15) ,
dim4_lib varchar2(25),
dim4_l1 varchar2(15),
l1_lib varchar2(25),
dim4_l2 varchar2(15),
l2_lib varchar2(25))
nologging;
insert /*+ append */ into dim4
(select 'AXIS ' ||to_char(rownum) dim4_id,
'Label ' ||to_char(rownum) dim4_id,
'L1.' ||to_char(case when rownum<3 then 1
when rownum=3 then 2
else 3 end),
'Level1 '||to_char(case when rownum<3 then 1
when rownum=3 then 2
else 3 end),
'L2.' ||to_char(case when rownum<=3 then 1
else 3 end),
'Level2 '||to_char(case when rownum<=3 then 1
else 3 end)
from dual
connect by level <= 4);
commit;
exec dbms_stats.gather_table_stats(user, -
'DIM4', cascade=>true, -
method_opt=>'for all columns size 254');
create unique index dim4_pk
on dim4 (dim4_id);
alter table dim4
add constraint dim4_pk
primary key (dim4_id)
using index dim4_pk;
Et maintenant ?
Nous voilà, en 5 minutes, avec un schéma pour servir de base à nos futurs articles. Nous allons commencer dès le prochain article par la création d’une vue matérialisée et son utilisation avec les fonctions de query rewrite…
