Bonjour,

Je suis assez nouveau dans la pratique de la méthodologie BI pour la modélisation dimensionnelle d'une BDD relationnelle et ce dans une optique d'analyse Dashboarding (PowerBI, Qlik).
J'ai un cas pratique à réaliser où il est demandé de transformer le modèle relationnelle en modèle dimensionnel (tables dimensions et table de faits dans une structure en étoile). Je dispose d'un set de fichiers CSV provenant d'une BDD réelle.
Le set est disponible et peut être pré-visualisé ici : https://www.kaggle.com/olistbr/brazi...ms_dataset.csv

Il m'est demandé de pouvoir répondre aux questions suivantes :

Volume/Quantité vendus par produit
Répartition des ventes par type de produits
Identification des meilleurs clients
Identification des fournisseurs les plus importants
Identification des produits les plus vendus
Analyse des ventes selon le temps (Jour, Mois, Année, Year Over Year, Saison, etc,..)
Localisation géographique des clients et revendeurs
Produits les mieux évalués
Revendeurs les mieux côtés

La granularité est à définir selon moi au niveau de la ligne de commande. En effet une commande peut avoir plusieurs produits différent. Le fichier csv Orders_items reprend outre l'attribut order_id également le order_items qui est un nombre séquentiel pour identifier chaque produit dans une même commande.
J'ai identifié plusieurs dimensions tel que Customers, Seller, Product et Date. Chaque dimension aura une clé primaire auto-incrémentée qui viendra référencer la Table de Faits qui elle ne reprendra selon moi que les attributs de mesures suivant : price, freight_value, mesures des poids et des dimensions des produits, note attribué au vendeur et dates estimés et d'envois.

Les questions que je me pose sont les suivantes :

- Comment modéliser la géolocalisation par coordonnées? Le code postal semble représenter la clé de liaison entre les donnés géographiques, les Seller et les Customers. Dois-je créer une dimension géographie directement liée à la table de fait ou une dimension liée aux dimensions Customers et Sellers via le code postal? (modèle en flocons de neige).

- chaque Dimension sera référencée dans la table de Fait par la FK de la clé primaire. Lors de la création des tables du modèle dimensionnel dans SQL et de l'ETL faut t'il concatener les FK en une clé unique pour la table de Faits? Une Surrogate key auto-incrémentée sera également crée pour chaque ligne de la table de faits.

- je suppose qu'une Dimension Orders_details est nécessaire également?

Voici une image du modèle en étoile que j'ai établi. Vous semble t-il cohérent (les mesures de poids et volume pour les produits sont à passer en table de faits)?



Merci d'avance pour tout conseil reçu pour la mise sur pied de ce modèle dimensionnel.

Je vous liste la nomenclature complète de tous les attributs disponibles dans chacun des fichiers csv de dataset :

● Customer_dataset

customer_id
key to the orders dataset. Each order has a unique customer_id.

customer_unique_id
unique identifier of a customer.

customer_zip_code_prefix
first five digits of customer zip code

customer_city
customer city name

customer_state
customer state

● Geolocation_dataset

geolocation_zip_code_prefix
first 5 digits of zip code

geolocation_lat
latitude

geolocation_lng
longitude

geolocation_city
city name

geolocation_state
state

● Order_items_dataset

order_id
order unique identifier

order_item_id
sequential number identifying number of items included in the same order.

product_id
product unique identifier

seller_id
seller unique identifier

shipping_limit_date
Shows the seller shipping limit date for handling the order over to the logistic partner.

price
item price

freight_value
item freight value item (if an order has more than one item the freight value is splitted between items)

● Order_payments_dataset

payment_sequential
a customer may pay an order with more than one payment method. If he does so, a sequence will be created to accommodate all payments.

payment_type
method of payment chosen by the customer.

payment_installments
number of installments chosen by the customer.

payment_value
transaction value.

● Order_reviews_dataset

review_id
unique review identifier

order_id
unique order identifier

review_score
Note ranging from 1 to 5 given by the customer on a satisfaction survey.

review_comment_title
Comment title from the review left by the customer, in Portuguese.

review_comment_message
Comment message from the review left by the customer, in Portuguese.

review_creation_date
Shows the date in which the satisfaction survey was sent to the customer.

review_answer_timestamp
Shows satisfaction survey answer timestamp.

● Orders_dataset

order_id
unique identifier of the order.

customer_id
key to the customer dataset. Each order has a unique customer_id.

order_status
Reference to the order status (delivered, shipped, etc).

order_purchase_timestamp
Shows the purchase timestamp.

order_approved_at
Shows the payment approval timestamp.

order_delivered_carrier_date
Shows the order posting timestamp. When it was handled to the logistic partner.

order_delivered_customer_date
Shows the actual order delivery date to the customer.

order_estimated_delivery_date
Shows the estimated delivery date that was informed to customer at the purchase moment.

● Products_dataset

product_id
unique product identifier

product_category_name
root category of product, in Portuguese.

product_name_lenght
number of characters extracted from the product name.

product_description_lenght
number of characters extracted from the product description.

product_photos_qty
number of product published photos

product_weight_g
product weight measured in grams.

product_length_cm
product length measured in centimeters.

product_height_cm
product height measured in centimeters.

product_width_cm
product width measured in centimeters.

● Sellers_dataset

seller_id
seller unique identifier

seller_zip_code_prefix
first 5 digits of seller zip code

seller_city
seller city name

seller_state
seller state

● Product_category_name_translation

product_category_name
category name in Portuguese

product_category_name_english
category name in English