SQL Server 2016 : L’analyse en temps réel sur des données opérationnelles

Petit rappel :

Les systèmes d’analyse et de reporting traditionnels étaient basés sur une architecture qui jusqu’ici a fait et continue de faire ses preuves.

Ci-dessous un schéma récapitulatif d’une telle architecture:

ArchiBI3

Nous avons en entrée une ou plusieurs sources de données (Applications transactionnelles, Bases de données, CRM, différents formats de fichiers txt, csv, XML etc…

  • La partie ETL pour les extractions, transformations et chargements de données
  • La modélisation du Dwh
  • Design des cubes
  • Partie front End: Reporting, Dashboards, KPI

Avantages :

  • Classique bien maitrisé
  • Faible surcharge des serveurs opérationnels : Faible impact(induit une faible surcharge des traitements sur les systems opérationnels)
  • Elimine les risques de dégradation des performances des systemes opérationnels liées aux requêtes rappatriant de grosses quantités de données (io) ou très consommatrices de ressources (CPU).

Inconvénients :

  • Complexité
  • Nécessité ETL: Souvent compliqué à metre en oeuvre (Méthodes de chargement, annule et Remplace, Différentiel, Incrémental, etc…)
  • Données en décalage : Analyse en retard de phase : Semaine, Jour, heure en function de la frèquence de chargement et de raffraichissement des données
  • Coût : Nécessite de plus de serveurs, plus de licences (Serveurs BDD, Serveurs ETL, Serveurs OLAP)

Aujourd’hui les business ont de plus en plus besoin d’effectuer leurs analyses sur les données les plus récentes possibles (voir données réelles Ex. Détection des fraudes).

Solution : Columnstore index updatable dans SQL Server 2016

Avec SQL Server 2016 on peut effectuer les analyses/Reporting directement sur les bases opérationnelles sans pour autant affecter négativement le workload sur le systéme opérationnel moyennant la mise en place de columnstore index:

La figure ci-dessous présente l’architecture préconisée pour une telle solution

ArchBI4

Mise en place de la solution:

  • Identifier toutes les tables et les colonnes nécessaires pour effectuer les analyses
  • Créer les columnstore index sur chacune de ces tables.
  • Brancher la partie Analyse (cube SSAS ou autre) pour qu’elle se source directement sur le système opérationnel.

Avantages

  • Point besoin de modifier l’application source.
  • Pas besoin d’ETL
  • Faible coùt de maintenance
  • faible impact sue la charge du systéme opérationnel : le query optimizer de SQL choisira d’utiliser le columnstore index pour toutes les requêtes d’analyse (les performances du columnstore index sont de loin supéreures aux index classiques du fait du mode de stockage des données et du niveau de  compression de celles-ci.

Inconvenient:

Le principal inconvenient est que cette solution ne peut s’appliquer que pour les solutions BI comportant une seule source, pour les autres il faudra se baser sur l’architecture traditionnelle décrite plus haut.

Il est possible de réduire d’avantage l’impact de la charge induite par l’analyse sur le système opérationnel en se basant sur plusieurs techniques à savoir:

  • Filtrer le columnstore index pour qu’il ne prenne en compte que certaines données les plus stables c-a-d moins aptes à évoluer (Ex: Factures djà réglées, commandes déjà expédiées, etc..)
  • Déporter l’analyse sur le server Secondary lorsqu’on se trouve dans un environnement de haute disponibilité en AlwaysOn

 

Dans un prochain article on mettra en oeuvre la solution technique présentée ci-dessus