datamarts orientés analyse de panier · pour savoir quel produit est vendu avec quel produit, on...

Datamarts orientés analyse de panier

Stéphane Crozat

Data warehousehttp://dwh.crzt.fr

1 septembre 2016

Table des matières

Objectifs 3

Introduction 4

I - Projet Fantastic : Rappel 5

II - Analyse de panier 6

1. Définition de l'analyse de panier ............................................................................................. 6

2. Analyse de structure de panier ................................................................................................ 6

3. Analyse de ventes conjointes .................................................................................................... 8

III - Exercice : Data mart pour l'analyse de ticket de caisse 10

Solutions des exercices 11

Bibliographie 14

3Stéphane Crozat

Savoir implémenter un datamart pour l'analyse de tickets de caisse

Objectifs

4Stéphane Crozat

-- Volume de cours : 1h

Volume d'exercice : 3h

Introduction

Projet Fantastic : Rappel

5

http://dwh.crzt.fr/mod

http://dwh.crzt.fr/etl

Projet Fantastique : Problème posé (cf. p.)

Projet Fantastic : Données disponibles (cf. p.)

Projet Fantastic : Rappel I

Conseil : Pré-requis

Rappel : Problème

Rappel : Données sources

http://dwh.crzt.fr/modhttp://dwh.crzt.fr/etl

Analyse de panier

6

1. Définition de l'analyse de panier

L'analyse de panier ( ) consiste à étudier des ventes en fonction de la structure market basket analysisdu panier de l'acheteur.

Un cas typique est l'étude des produits qui sont vendus ensemble.

Synonyme : Analyse de ticket de caisse.

L'utilisation du data warehouse en l'état est souvent mal adapté à ces analyse, il faut donc construire des data marts spécifiquement orientés vers ces questions.

2. Analyse de structure de panier

Soit le DW :

Vue intégrée

Soit la question suivante :

Quelle est l'influence de l'organisation des magasins (rayonnage et rayon best-seller) sur la « structure des tickets de caisse, en fonction des dates ? Par exemple les clients achètent-ils plusieurs livres du même auteur, du même éditeur, du même genre ? »

1 IndiceProduit, IndiceAuteur, IndiceEditeur, IndiceGenre2 / magasin (rayonnage, bestseller)3 / date

Analyse de panierII

Définition

Méthode

Exemple

IndiceProduit, IndiceAuteur, IndiceEditeur, IndiceGenre / magasin (rayonnage, bestseller) / date

Analyse de structure de panier

7

-

--

L'indice est un indicateur à calculer rendant compte du nombre de livres achetés ensemble du même auteur, éditeur ou genre :

indiceProduit=0.5 signifie que 50% des livres achetés au sein du ticket sont diférents.indiceAuteur=0.5 signifie que 50% des livres différents achetés au sein du ticket sont du même auteur....

Cette requête interroge la structure du ticket de caisse et non le volume des ventes, elle sera traitée par un data mart particulier. En effet elle serait trop complexe à résoudre avec le data warehouse commun, elle nécessite une pré-agrégation.

Modèle dimensionnel du data mart

Description de la table des faits du data mart

Les faits ne sont pas additifs et seront analysés en moyenne.

Date NumTicket RefProduit ...

1 1 1

1 1 1

1 1 2

1 2 1

1 3 5

1 3 10

... ... ...

Méthode

Exemple

Attention

Méthode : Pré-agrégation des faits

Analyse de ventes conjointes

8

Table de faits du data warehouse (une ligne par article vendu)

Date NumTicket ... NbArticles IndiceProduit IndiceAuteur ...

1 1 3 0.67 0.5

1 2 1 1 1

1 3 2 1 1

... ...

Table de faits pré-agrégée du data mart (une ligne par ticket)

On extrait les informations supplémentaires (indices) de la dimension produit, qui disparaît.

3. Analyse de ventes conjointes

Pour savoir quel produit est vendu avec quel produit, on construit une nouvelle table des faits avec un fait pour chaque couple de produits existants (ou le sous ensemble des produits effectivement vendus ensemble) .(Kimball, Ross, 2008, p.62) **

Exemple de table de faits pour l'analyse de panier (Kimball, Ross, 2008, p.63)

Attention

Méthode

Exemple

Analyse de ventes conjointes

9

-

-

Il y a potentiellement (N x N-1) combinaisons de produits, ce qui peut conduire à un très grand nombre de faits.

En général l'analyse est effectuée :

sur une sélection préalable d'un sous-ensemble des produits : les plus vendus, des combinaisons que l'on sait vouloir étudier, des sous-catégories...via une approche progressive par produit : on analyse les produits vendus avec X, puis Y en fonction des résultats...

Attention

Exercice : Data mart pour l'analyse de ticket de caisse

10

[2h]

Afin d'analyser la structure des tickets de caisse, l'on va créer un data mart dédié.

Question 1

Question 2

Question 3

Exercice : Data mart pour l'analyse de ticket de caisse

III

Réaliser un data mart pour l'analyse de structure de tickets de caisse. On se contentera d'une analyse en quantité (sans intégrer le chiffre d'affaire donc).

[ ]solution n°1 *[ ] p.11

Exploiter le data mart pour analyser globalement les comportements d'achats multiples en fonction des rayonnages, des dates...


-

-

Enrichissez le data mart avec les indicateurs adéquats pour analyser si le rayonnage influe sur la structure du ticket de caisse.

Indice :

Typiquement on va chercher à étudier si :

les rayonnages de type A ou E induisent des tickets de caisse avec plusieurs livres du même auteur ou du même éditeur ;les rayons BS induisent des tickets avec plusieurs livres de type BS


Solutions des exercices

11Stéphane Crozat

---

Exercice p. 10> Solution n°2


1 CREATE TABLE f_dm1_ventes (2 num char(10),3 mag char(4),4 dat date,5 qte number(2)6 );

1 ALTER TABLE f_dm1_ventes2 ADD CONSTRAINT f_dm1_ventes_fkdat3 FOREIGN KEY (dat) REFERENCES f_dw_date(dat);4 ALTER TABLE f_dm1_ventes5 ADD CONSTRAINT f_dm1_ventes_fkmag6 FOREIGN KEY (mag) REFERENCES f_dw_mag(mag);

1 -- TODO constraints desactivation2 INSERT INTO f_dm1_ventes (num, mag, dat, qte)3 SELECT num, mag, dat, count(*) as q4 FROM f_dw_ventes5 GROUP BY num, mag, dat, num;6 -- TODO constraints reactivation

1 SELECT m.ray, avg(qte)2 FROM f_dm1_ventes v JOIN f_dw_mag m3 ON v.mag=m.mag4 GROUP BY m.ray;

Y : 1.69A : 1.86E : 1.69

1 SELECT m.bs, avg(qte)2 FROM f_dm1_ventes v JOIN f_dw_mag m3 ON v.mag=m.mag4 GROUP BY m.bs;


Exemple

Exemple

CREATE TABLE f_dm1_ventes (num char(10),mag char(4),dat date,qte number(2));

ALTER TABLE f_dm1_ventesADD CONSTRAINT f_dm1_ventes_fkdatFOREIGN KEY (dat) REFERENCES f_dw_date(dat);ALTER TABLE f_dm1_ventesADD CONSTRAINT f_dm1_ventes_fkmagFOREIGN KEY (mag) REFERENCES f_dw_mag(mag);

-- TODO constraints desactivationINSERT INTO f_dm1_ventes (num, mag, dat, qte)SELECT num, mag, dat, count(*) as qFROM f_dw_ventesGROUP BY num, mag, dat, num;-- TODO constraints reactivation

SELECT m.ray, avg(qte)FROM f_dm1_ventes v JOIN f_dw_mag mON v.mag=m.magGROUP BY m.ray;

SELECT m.bs, avg(qte)FROM f_dm1_ventes v JOIN f_dw_mag mON v.mag=m.magGROUP BY m.bs;


12Stéphane Crozat

-

-

-


-- 1 : 1.83

0 : 1.38

1 ALTER TABLE f_dm1_ventes2 ADD ind_aut1 NUMBER(2);3 ALTER TABLE f_dm1_ventes4 ADD ind_aut2 NUMBER(1);

1 DELETE FROM f_dm1_ventes;

1 INSERT INTO f_dm1_ventes (num, mag, dat, qte, ind_aut1, ind_aut2)2 SELECT r1.num, r1.mag, r1.dat, r1.q, round(max(r2.a),2), case when max(r2.a)>1 then 1 else 0 end3 FROM4 (SELECT num, mag, dat, count(*) as q5 FROM f_dw_ventes6 GROUP BY num, mag, dat, num7 ) r1,8 (SELECT num, p.auteur, count(*) as a9 FROM f_dw_ventes v JOIN f_dw_produit p

10 ON v.pro=p.isbn11 GROUP BY num, p.auteur12 ) r213 WHERE r1.num = r2.num14 GROUP BY r1.num, r1.mag, r1.dat, r1.q;

On obtient ici :

un indice entier qui contient le nombre maximum de livres du même auteur dans ind_aut1un ticket (il vaut 1 si tous les auteurs sont différents ou si le ticket ne contient qu'un seul produit) ;un indice booléen qui vaut 1 s'il y a au moins deux livre d'un même auteur sur le ind_aut2ticket, et 0 sinon.

1 SELECT m.ray, avg(v.ind_aut1)2 FROM f_dm1_ventes v JOIN f_dw_mag m3 ON v.mag=m.mag4 GROUP BY m.ray

Y : 1.07

A : 1.21

E : 1.05

On observe que les magasins de type A favorisent la vente de plusieurs livres de même auteur.

1 SELECT m.ray, round(avg(v.ind_aut2),2)2 FROM f_dm1_ventes v JOIN f_dw_mag m3 ON v.mag=m.mag4 WHERE v.qte>15 GROUP BY m.ray

Y : 0.16

Exemple

ALTER TABLE f_dm1_ventesADD ind_aut1 NUMBER(2);ALTER TABLE f_dm1_ventesADD ind_aut2 NUMBER(1);

DELETE FROM f_dm1_ventes;

INSERT INTO f_dm1_ventes (num, mag, dat, qte, ind_aut1, ind_aut2)SELECT r1.num, r1.mag, r1.dat, r1.q, round(max(r2.a),2), case when max(r2.a)>1 then 1 else 0 endFROM(SELECT num, mag, dat, count(*) as qFROM f_dw_ventesGROUP BY num, mag, dat, num) r1,(SELECT num, p.auteur, count(*) as aFROM f_dw_ventes v JOIN f_dw_produit pON v.pro=p.isbnGROUP BY num, p.auteur) r2WHERE r1.num = r2.numGROUP BY r1.num, r1.mag, r1.dat, r1.q;

SELECT m.ray, avg(v.ind_aut1)FROM f_dm1_ventes v JOIN f_dw_mag mON v.mag=m.magGROUP BY m.ray

SELECT m.ray, round(avg(v.ind_aut2),2)FROM f_dm1_ventes v JOIN f_dw_mag mON v.mag=m.magWHERE v.qte>1GROUP BY m.ray


13Stéphane Crozat

-- A : 0.37

E : 0.14

Ici on a considéré uniquement les tickets pour lesquels il y a au moins deux livres de vendus (qte > ).1

On observe que, pour les magasins de type A, pour 37% des tickets comprenant au moins deux livres, il y a au moins deux livres du même auteur (contre 15% environ pour les autres magasins) ; on en conclut que les magasins de type A permettent de vendre deux fois plus souvent des livres du même auteur que les autres.

Bibliographie

14Stéphane Crozat

Kimball R., Ross M. (2008, 2002). The Data Warehouse Toolkit: The Complete Guide to . Wiley Publishing, second edition.Dimensional Modeling

Bibliographie

ObjectifsIntroductionProjet Fantastic : RappelAnalyse de panierDéfinition de l'analyse de panierAnalyse de structure de panierAnalyse de ventes conjointes

Exercice : Data mart pour l'analyse de ticket de caisseSolutions des exercicesBibliographie

datamarts orientés analyse de panier · pour savoir quel produit est vendu avec quel produit, on...

Documents