[SQL] Construire un DWH Agile avec SQL Server 2012

Pré conférence J2 : journée entière

Sujet : Construire un DWH Agile avec SQL Server 2012 Speaker : Davide Mauri (SolidQ)

Construire un DWH Agile avec SQL Server 2012

Pré conférence J2: journée entière

Speaker : Davide Mauri SoliQ

Lexique :

DWH : Data Warehouse

DMT : Datamart

OLTP : On Line Transactionnal Processing

OLAP : On Line Analytical Processing

Intérêt d’un DWH

  • Comparaison entre une base OLTP et un DWH
  • OLTP : pour écrire, normalisé.., bases éparpillées  dans tte la société

Accès aux données en direct :

Avantages : temps réel, pas de copie, stockage réduit (3NF)…

Inconvénients : Change rapidement, performance, requêtes lentes (jointures), schéma complexe (normalisé, beaucoup de tables)…

  • DWH :

1 seul point d’entrée (consolidé)

Données ont été nettoyées, vérifiées, enrichies et certifiées

Données historisées

Cœur de la solution BI

1 seul version de données : véracité de l’information

Modèle de données « User Friendly » conçu pour faire une analyse plus naturellement

  • Schéma du DWH

Montre le schéma de sources (Bases, Fichiers, Cloud) qui convergent vers un entrepôt de données unique.

L’approche Agile

  • Les pièges lors de l’expression du besoin:
  • L’utilisateur nous demande la solution qu’il veut, pas le problème qu’il a
  • Le besoin change pendant le développement
  • La réponse à ce qu’il veut analyser est « Tout »
  • L’utilisateur sais mieux parler de ce qu’il fait et comprend
  • Les piège lors du cycle de développement traditionnel (méthode en V)
  • Effet tunnel
  • Une nouvelle approche est nécessaire (l’agilité)
  • l’objectif :
    • livrer rapidement et avoir le feedback client
    • Livrer fréquemment en mettant des priorités et des cycles courts

http://agilemanifesto.org/principles.html

  • Dans un cycle de développement agile on a :
    • Equipe multidisciplinaire
    • Beaucoup d’itérations
    • Livraison rapide
    • Couts maitrisés
    • Meilleur contrôle

Et

  • Le périmètre global est connu, pas le détail
  • Tout peut changer et changera
  • Seulement la donnée certifié doit rester stable
  • Le prototypage au début du projet est obligatoire :
    • Il faut prendre un petit périmètre de données
    • Il permet de bien pour comprendre données
    • De mieux estimer ses efforts et le coût du projet
    • D’étudier la faisabilité du projet
    • Il faut laisser l’utilisateur tester le prototype pour pouvoir redéfinir le besoin à ce stade du projet
  • Avant de livrer quoi que ce soit il faut obligatoirement le tester

Modélisation d’un DWH

  • Comparaison DMT/DWH

Pas de définition standard mais en général :

  • DMT : données départementales de l’entreprise
  • DWH : données de toute l’entreprise
  • Compare la vision Kimball/Immon (schéma)
  • Kimball : le DWH est issu des DMT
  • Immon : les DMT sont issu du DWH
  • Solutions qui s’appuient sur DWH :
  • Multi Dim DB (SSAS, Cognos)
  • In Memory (PowerPivot, Clickview)
  • Column store system (SQL 2012 Vertipak)
  • Le modèle dimensionnel
  • Entités : Faits et Dimensions

Utilisé autant dans un modèle relationnel (SQL Server) que multidimensionnel (SSAS)

  • Un fait est quelque chose qui s’est passé :
    • Un produit a été vendu, Un contrat a été signé, Un paiement a été fait
    • Contient des données mesurable appelées mesures
    • Sont stockées dans des tables de faits
  • Les mesures sont:
    • Généralement additive : peuvent être additionné quel que soit l’axe (ex montant)
    • Ou additive : ne peuvent pas être additionné sur tous les axes (ex stock)
  • Les dimensions définissent comment les faits peuvent être analysé (produits, clients..)
    • Possèdent des attributs (nom client, couleur produits…)
    • Sont stockées dans des tables de dimension et les valeurs s’appellent des membres
  • Schéma en étoile

Relation table de fait et tables de dimensions (dé normalisé, habituellement plus de jointure à faire entre les tables, la table de fait réuni les données normalement sur plusieurs table dans un modèle normalisé 3NF)

  • Schéma en flocon

Plus normalisé (sur les tables de dimensions) que schéma en étoile

  • Granularité
    • Niveau de détail
    • Doit être déterminer avant autant pour les tables de faits que de dimensions
    • Différence de granularité sur les faits = différentes table de faits
  • Slowly Changing Dimension (SCD) :
    • Design pattern défini par Kimball pour gérer l’ « Update » de données
    • SCD Type 1 : On remplace la nouvelle valeur par l’ancienne, bien pour corriger des données erronée et on ne garde pas d’historique.
    • SCD Type 2 : On conserve tout l’historique des modification de données
    • SCD Type 3 : On préserve que la dernière modification
  • Type de tables de faits
    • Transactionnelle (habituelle)
    • Snapshot (on extrait les données du moment sans empiler les mouvements,  bien pour stock ou inventaire)
    • Temporelle Snapshot (on utilise un intervalle de temps pour stocker plusieurs ligne si la valeur des mesures est la même) :

voir http://www.cs.arizona.edu/people/rts/tdbbook.pdf

  • Bridge (ou Factless) utilisé dans une many to many
  • Best practices
    • Pas plus de 20 dimensions
    • Couper les dimensions en 2 si SCD 1 et SCD 2 par exemple
    • Penser à la sécurité dès le début de la conception

Concevoir la solution

  • Objectif :
  • Travail d’équipe, doit être fait en harmonie avec des règles communes pour atteindre l’objectif plus facilement.
  • Qualité du travail et discipline demandé aux intervenants
  • Facilité de monter en charge
  • Habilité à remplacer facilement une personne
  • Règle à mettre en place :
  • Convention de nommage
  • Objets/Attributs obligatoire
  • Solution aux problèmes communs
  • Dépendance entre objets
  • Méthodologie : Best practice , guidelines

Construire le DWH

  • Les différente couches :
  • Producteur : contient toutes les sources de données
  • Coordinateur : Tous les objets qui traitent les sources dans le DWH
  • Consommateur : Où le DWH est consommé
  • Les différentes bases :
  • Helper : contient vues et proc stock de la source pour maquer complexité et exposer les données utiles
  • Staging : pré base de travail, contient données volatiles, table de rejet/erreurs
  • Configuration : ex table de lookup de données connu, tables de transcodage….
  • DWH : cible
  • Les conventions de nommages
  • Bases
    • Projectname_STG
    • Projectname_DWH
    • Projectname_CFG
    • Projectname_HLP
  • Schémas (découpage logique d’objetx (tables, vues..))

DWH

  • dwh (tables de fait et dim)
  • olap (vues utilisées depuis SSAS)
  • reporting (vues utilisées dans SSRS)

STG

  • staging (copie des sources OLTP)
  • etl (vue, prock stock point d’entrée de SSIS + extraction des fichiers source (xls, txt..))
  • tmp (table temporaire provenant de SSIS)
  • err (erreur et rejet provenant de SSIS)

CFG

  • config

HLP

  • bi (généralement des vues)

OLTP

  • bi (généralement des vues)

Alimenter le DWH

  • ETL
  • Représente près de 80 % du projet global
  • SSIS est le moteur ETL utilisé coté MS (tout est chargé en mémoire)
  • Ne substitue pas le TSQL plus utilisé pour des manipulations simple de données, que SSIS plus pour des opérations multi-stage complexes.
  • Le développement dans SSIS est plus maintenable que celui avec du TSQL.
  • Type de process
  • Business Process : Traitement ETL utilisé spécifiquement pour le besoin de la solution métier
  • Technical Process : Traitement ETL utilisé quel que soit le data-warehouse cible et qui peut être automatisé
  • En agilité utilisez ces 2 processus séparément
  • Cycle de chargement
  • Dim tables
  • Fact tables
  • Factless tables
  • Convention de nommage des packages
  • load_ (chargement cible)
  • master_ (package parent)
  • prepare_ (avant le chargement cible, exemple chargement table temporaire)
  • setup_ (amorce, opération d’initialisation)
  • Utilisation d’un numéro de séquence pour grouper des package indépendants

010s peuvent être exécutés indépendamment

  • prepare_010_orders
  • prepare_010_customers

020s peuvent être exécutés indépendamment

mais attente que 010s s’exécutent

  • prepare_020_invoices
  • prepare_020_product
  • Best practices
  • Utiliser vues en source plutôt que requêtes tables avec option RECOMPILE
  • 2 projets différents : load staging, load dwh
  • Eviter OLEDB Command (donc SCD)
  • Essayer de faire le max de transformation dans SSIS et pas dans SSAS et SSRS
  • Une seule action par package (plusieurs petit package est préférable à un gros)

Test unitaire de données

BI.Quality sur Codeplex

Sébastien

Laisser un commentaire

Entrez vos coordonnées ci-dessous ou cliquez sur une icône pour vous connecter:

Logo WordPress.com

Vous commentez à l'aide de votre compte WordPress.com. Déconnexion / Changer )

Image Twitter

Vous commentez à l'aide de votre compte Twitter. Déconnexion / Changer )

Photo Facebook

Vous commentez à l'aide de votre compte Facebook. Déconnexion / Changer )

Photo Google+

Vous commentez à l'aide de votre compte Google+. Déconnexion / Changer )

Connexion à %s