Création du schéma ROLAP en flocon de neige

Étape 1 : Comprendre les besoins analytiques

Avant de concevoir le schéma, il est essentiel de bien comprendre les questions analytiques posées par le département BI. Voici les principales questions auxquelles le schéma devra répondre :

  • Quels sont les artistes les plus écoutés par genre et par pays ?
  • Quelles sont les périodes de l’année où les utilisateurs premium écoutent le plus de musique ?
  • Quels albums ou morceaux sont les plus populaires selon les régions géographiques ?
  • Quels dispositifs (mobile, ordinateur, etc.) sont les plus utilisés pour écouter de la musique, par tranche d’âge ?

Chaque question révèle des axes d’analyse qui seront intégrés sous forme de dimensions dans notre modèle ROLAP.

Étape 2 : Identifier les mesures et la table de faits

La table de faits centralise les mesures principales de l’analyse, c’est-à-dire les données quantitatives que l’on souhaite analyser.

Table de faits : listening_facts

  • Cette table stocke les écoutes de musique, liées aux morceaux, artistes, et utilisateurs, ainsi que des informations sur la date et le lieu de l’écoute.

Mesures principales :

  • streams_count : Nombre d’écoutes d’un morceau.
  • listening_time (optionnel) : Temps passé à écouter un morceau.

Clés étrangères dans la table de faits :

  • track_id : Clé étrangère vers la dimension Morceau.
  • user_id : Clé étrangère vers la dimension Utilisateur.
  • date_id : Clé étrangère vers la dimension Temps.
  • device_id : Clé étrangère vers la dimension Device.
  • location_id : Clé étrangère vers la dimension Lieu.

Étape 3 : Définir les dimensions principales

Les dimensions permettent d’organiser les axes d’analyse (qui, quand, où, comment). Voici les dimensions que nous avons définies pour répondre aux questions analytiques :

a) Dimension Temps : time_dim

Elle permet de structurer les données temporelles pour analyser les écoutes par période.

  • date_id (clé primaire)
  • year
  • quarter
  • month
  • day

Hiérarchie : Année → Trimestre → Mois → Jour

b) Dimension Artiste : artist_dim

Cette dimension permet d’analyser les écoutes par artiste, genre musical et label.

  • artist_id (clé primaire)
  • artist_name
  • genre_id (clé étrangère vers la dimension Genre)
  • label_id (clé étrangère vers la dimension Label)

Hiérarchie : Label → Artiste → Genre

Sous-dimension Genre : genre_dim
  • genre_id (clé primaire)
  • genre_name
Sous-dimension Label : label_dim
  • label_id (clé primaire)
  • label_name

Cette hiérarchie permet d’analyser les artistes en fonction de leur genre musical et du label auquel ils sont affiliés.

c) Dimension Utilisateur : user_dim

Cette dimension permet de capturer des informations sur les utilisateurs qui écoutent de la musique.

  • user_id (clé primaire)
  • user_name
  • age
  • gender
  • subscription_type (ex : gratuit, premium)
  • location_id (clé étrangère vers la dimension Lieu)

Cela permet de segmenter les analyses par type d’utilisateur (âge, genre, abonnement, etc.).

d) Dimension Lieu (hiérarchique) : location_dim

Cette dimension permet d’analyser les écoutes selon la localisation géographique.

Dimension Lieu : location_dim
  • location_id (clé primaire)
  • city_id (clé étrangère vers la dimension Ville)
Sous-dimension Ville : city_dim
  • city_id (clé primaire)
  • city_name
  • country_id (clé étrangère vers la dimension Pays)
Sous-dimension Pays : country_dim
  • country_id (clé primaire)
  • country_name
  • continent_id (clé étrangère vers la dimension Continent)
Sous-dimension Continent : continent_dim
  • continent_id (clé primaire)
  • continent_name

Hiérarchie : Continent → Pays → Ville → Lieu

Cette hiérarchie permet d’analyser les écoutes à plusieurs niveaux géographiques : continent, pays, ville.

e) Dimension Morceau : track_dim

Cette dimension décrit les morceaux écoutés et est liée aux albums.

  • track_id (clé primaire)
  • track_name
  • album_id (clé étrangère vers la dimension Album)
  • duration
  • popularity_score
Sous-dimension Album : album_dim
  • album_id (clé primaire)
  • album_name
  • release_date
  • label_id (clé étrangère vers la dimension Label)

Hiérarchie : Album → Morceau → Label

Cela permet d’analyser les morceaux selon leur popularité et leur appartenance à des albums et labels.

f) Dimension Écoute (Device) : device_dim

Cette dimension décrit le dispositif utilisé par les utilisateurs pour écouter la musique.

  • device_id (clé primaire)
  • device_type (ex : mobile, tablette, ordinateur)

Cela permet d’analyser les écoutes selon le type de dispositif.

Étape 4 : Répondre aux questions analytiques

Ce schéma en flocon de neige permet de répondre efficacement aux questions posées par le département BI.

  1. Quels sont les artistes les plus écoutés par genre dans chaque pays ?

    • Cette question utilise les dimensions artist_dim, genre_dim, location_dim (pays), et les faits d’écoute (streams_count).

  2. Quelles sont les périodes de l’année où les utilisateurs premium écoutent le plus de musique ?

    • Cette question nécessite l’utilisation de time_dim et user_dim (avec subscription_type).

  3. Quels albums ou morceaux sont les plus populaires par région géographique ?

    • Pour répondre à cette question, on utilise track_dim, album_dim, et location_dim (pays, continent).

  4. Quels dispositifs sont les plus utilisés pour écouter de la musique, par tranche d’âge ?

    • Cela fait appel aux dimensions device_dim et user_dim (avec age).