SQL Server chez les clients – La mise en cache proactive

Les plateformes de Business Intelligence permettent aux décideurs d’analyser leur activité afin de prendre des décisions. Il est important que les indicateurs essentiels soient rapidement accessibles et puissent se baser sur des données d’activité les plus récentes.

Microsoft SQL Server Analysis Services est une solution de gestion de base de données analytique (OLAP : OnLine Analytical Processing) permettant de stocker et de restituer des données analytiques suivant différents domaines : comptable, stock, achat, etc.

Cet article illustre comment, au travers de SQL Server Analysis Services et de son mécanisme de Proactive Caching, nous pouvons améliorer l’accessibilité et la mise à jour des données d’analyses en réduisant leur temps de mise à disposition.

Problématique

• SSAS offre différents modes de stockage : quel mode pour quel besoin ?
• Comment obtenir des données le plus à jour possible et rapidement accessible ?

Bénéfices

• Une solution temps réelle nativement prise en charge par le produit.
• Une analyse et une prise de décision basée sur les données les plus récente.

Présentation des différents modes de stockage

SSAS utilise plusieurs modes de stockage des données

• MOLAP (Multidimensional OLAP)  : le modèle, les données ainsi que les agrégations sont stockés sur le serveur SSAS

  • Avantage : les données sont rapidement accessibles

  • Inconvénients :

    • Les mises à jour ne sont pas automatiques lorsque les données sources évoluent

    • Plus il y a de données sources, plus la mise à jours est longue

• ROLAP (Relational OLAP)  : le modèle est stocké sur le serveur SSAS tandis que les données et les agrégations se situent dans une base de données relationnelle

  • Avantage : les données disponibles sont toujours les plus récentes

  • Inconvénients :

    • L’accès aux données est plus long

    • Plus il y a de données sources, plus l’accès sera long

• HOLAP (Hybrid OLAP)  : cette solution est un compromis entre MOLAP et ROLAP. Le modèle et les agrégations sont stockés sur le server SSAS tandis que les données se trouvent dans une base de données relationnelle

Remarque : si les données sources évoluent, les agrégations seront fausses tant que la base de données SSAS ne sera pas de nouveau mise à jour

Qu'est-ce que la mise en cache proactive ?

La mise en cache proactive ou  "Proactive Caching" est un mécanisme de SQL Server Analysis Services permettant de détecter les modifications des données sources puis de les intégrer automatiquement lorsque nous utilisons le mode de stockage MOLAP.

L’intégration peut être :

• complète : les données sont intégralement mises à jour

• incrémentale : les mises à jour sont réalisées uniquement pour les données modifiées

L’intérêt de ce mécanisme est de bénéficier de l’accès rapide aux données du mode de stockage MOLAP tout en réduisant le délai de mise à jour

Remarque : en fonction de paramétrage réalisé, le Proactive Caching peut entrainer une consommation de ressources CPU supplémentaire, engendré par le suivi de l’évolution des données sources. Réaliser un test de charge sur le serveur est un excellent moyen pour analyser le comportement de la plateforme et affiner son paramétrage.

Principe de fonctionnement

1. A intervalle régulier, SQL Server Analysis Services vérifie l’état des données sources. S’il n’y a aucune modification, il ne fait rien.

2. S’il y a eu des modifications, les données sont récupérées, traitées dans un cache temporaire. Pendant le traitement, le serveur peut soit laisser accès à l’ancien cache, soit activer le mode ROLAP.

3. Lorsque le nouveau cache est prêt, il devient accessible aux utilisateurs

Remarque  : les paramètres « SilenceInterval » et « SilenceOverrideInterval » permettent de définir à quelle fréquence la création du nouveau cache doit être exécutée. Ceci évite qu’un flux de modification constant des données sources provoque une création continue du nouveau cache dégradant ainsi l’expérience utilisateur.

Temps de latence

Lorsqu’une modification des données source est détectée, la création d’un nouveau cache est déclenchée par SSAS.

Pendant la création du nouveau cache, Analysis Services peut adopter 2 comportements :

• Transition Molap-Molap : pendant la création du nouveau cache, l’ancien cache reste accessible. Les données à jour, ne seront accessibles qu’après la création du nouveau cache.

Remarque  : le paramètre « Latency » permet de définir le temps durant la création du nouveau cache pendant lequel l’ancien cache est valable. Si le temps de création dépasse cette valeur, l’ancien cache est supprimé et les données ne seront plus accessibles.

• Transition Molap-Rolap-Molap : pendant la création du nouveau cache, le mode de stockage est paramétré en Rolap. Les données les plus à jours sont accessibles mais les performances risquent d’être dégradées.

Remarque  : si une dimension est dans ce mode de transition, tous les objets dépendant de cette dernière verront leur mode stockage paramétré en Rolap durant la création du nouveau cache.

Comment implémenter la mise en cache proactive ?

Le Proactive Caching s’implémente au niveau de chaque dimension et de chaque partition des cubes dans les propriétés. Il faut l’activer dans le choix du mode de stockage.

• L’onglet « General > Cache Setting » : définis la fréquence de recréation du cache

  • Silence interval : définis le temps durant lequel SSAS attend avant de créer le nouveau cache lorsqu’il détecte un changement dans les données sources (la valeur -1 équivalent à « infini », signifie qu’il va ignorer toutes les notifications)

  • Silence override interval : définis le temps au bout duquel SSAS va recréer le cache, même si un flux continu de modifications associé à une valeur de « Silence interval » trop élevé l’empêche. (la valeur -1 équivalent à « infini », pas de recréation)

  • Latency : temps au bout duquel l’ancien cache sera supprimé si l’objet est en transition Molap-Molap.

  • Rebuild interval : temps au bout duquel le cache est recréé

• L’onglet « General > Options » : définis le mode de transition lors de la création d’un nouveau cache. Par défaut, le mode transition est Molap-Molap

  • Bring online immediately : active la transition Molap-Rolap-Molap

  • Enable ROLAP aggregations : déclenche la création de vues indexées dans SQL Server

  • Apply settings to dimensions : lors du paramétrage du Proactive Caching sur un cube, cette option permet de propager le même paramétrage sur toutes les dimensions associées.

• L’onglet « Notification » permet de définir comment SSAS va détecter les modifications dans les données sources

  • SQL Server : permet de spécifier une table de suivi SQL Server permettant d’identifier les modifications

  • Client initiated : l’application cliente doit envoyer une commande du type « NotifyTableChange » afin d’avertir SSAS qu’une modification a eu lieu

  • Scheduled polling : une requête est exécutée sur la base de données source afin de détecter les modifications des données sources

    • • Polling interval : temps au bout duquel la requête de vérification est exécutée

      • Enable incremental updates : permet d’activer les colonnes « Processing Query » et « Table »

      • Polling Query : requête permettant de détecter les modifications des données sources (souvent basé sur un champ de type « Date de modification » présent dans la table source)

      • Processing Query : requête qui sera exécutée par SSAS afin de récupérer les données modifiées sur un champ de type « Date de modification » présent dans la table source)

      • Pooling Query : requête permettant de détecter les modifications des données sources (souvent basé sur un champ de type « Date de modification » présent dans la table source)

      • Table : nom de la table source

Lorsque que toutes les dimensions et les partitions de cubes ont été paramétrées, il faut exécuter un traitement total des données afin d’activer le processus de Proactive Caching.

Scénarios

Le Proactive Caching s’implémente au niveau de chaque dimension et de chaque partition des cubes dans les propriétés. Il faut l’activer dans le choix du mode de stockage.

Scénario sans Proactive Caching :

Scénario avec Proactive Caching : 

Analysis Services en mode tabulaire

Le mécanisme de Proactive Caching s’applique uniquement à la version multidimensionnelle de SSAS. La version tabulaire permet également de faire du quasi « temps réel » en implémentant le mode DirectQuery.

Si vous souhaiter en savoir plus, vous pouvez consulter la page TechNet - DirectQuery Mode (SSAS Tabular)

Aller plus loin...

Le mécanisme de Proactive Caching s’applique uniquement à la version multidimensionnelle de SSAS. La version tabulaire permet également de faire du quasi « temps réel » en implémentant le mode DirectQuery.

Il est également possible d’associer les fonctionnalités de BI temps réel d’Analysis Services avec la solution de traitement complexe des évènements « StreamInsight », présentée par mon collègue Frédéric Gisbert dans son article - La BI temps réel avec Microsoft StreamInsight

Si vous souhaitez en savoir plus sur l’implémentation du mode Rolap, ses avantages et ses inconvénients, le white paper - Analysis Services ROLAP for SQL Server Data Warehouses – devrait vous intéresser.

L’expertise Microsoft Consulting Services au service de ses clients

MCS propose une offre de service packagée pour la mise en œuvre et la réalisation de tests de charge sur des plateformes et solutions SQL Server & MSBI.

Cette méthodologie a été éprouvée à travers de nombreuses réalisations sur des applications critiques chez des clients grands comptes dont voici quelques exemples :

- Grande banque française d'investissement

- Leader mondial de l'industrie cosmétique

- Acteur majeur du transport d'énergie

Cette méthodologie peut aussi bien être mise en œuvre dans le cadre d’un projet géré par Microsoft Services, que dans le cadre d’une assistance technique sur certains aspects du projet (architecture, validation…).

Les tests de charge peuvent être réalisés suivant les cas :

- Directement sur l’infrastructure cible ou équivalente si celle-ci est disponible

- Au sein du Microsoft Technology Center (MTC) situé sur le campus d’Issy les Moulineaux, qui mettra à disposition les ressources techniques et logistiques nécessaires

Pour plus d’informations sur les offres packagées Microsoft Consulting Services, rendez-vous sur https://www.microsoft.com/france/services

Plus d’informations sur les blogs « SQL Server chez les clients ».

Frédéric Bonneau, Consultant Data Insights, Microsoft Consulting Services Avec 6 ans d’expérience sur les solutions décisionnelles Microsoft, j’interviens chez mes clients pour les accompagner dans la conception, la gestion, l’audit et le développement de leurs projets décisionnels.