implémenter une Dimension de Type 2 qui change lentement dans SQL Server Integration Services-Partie 1

Problème

Nous aimerions garder l’histoire dans notre entrepôt de données pour plusieurs dimensions.Nous utilisons SQL Server Integration Services (SSIS) pour implémenter L’ETL (ExtractTransform and Load). Nous avons essayé l’assistant de Dimension à changement lent intégré, mais les performances semblent médiocres. Comment Pouvons-nous implémenter la fonctionnalité souhaitée avec des composants SSIS réguliers?

Solution

Introduction aux Dimensions qui changent lentement

Une dimension qui change lentement (SCD) garde une trace de l’histoire de ses membres individuels., Il existe plusieurs méthodes proposées par Ralph Kimball dans son livre The DatawarehouseToolkit:

• Type 1 – écraser les champs lorsque la valeur change. Pas d’histoire iskept.
• Type 2 – Créer une nouvelle ligne avec les nouvelles valeurs pour les champs. Extracolumns indiquent quand dans le temps une ligne était valide.
• Type 3 – Garder l’ancienne valeur d’une colonne dans une colonne séparée.
• Il existe plus de types de SCD, mais ils sont la plupart du temps une combinaison hybride dela ci-dessus.

dans cette astuce, nous allons nous concentrer sur la situation de type 2. Illustronsavec un exemple., Nous avons une table simple stockant les données client.

Le SK_Customer colonne est une colonne avec un identityproperty qui va générer une nouvelle valeur pour chaque ligne. Nous aimerions keephistory pour L’attribut Location. Lorsque L’emplacement change D’Anvers à Bruxelles, nous ne mettons pas à jour la ligne, mais nous insérons un nouvel enregistrement:

En utilisant les champs ValidFrom et ValidTo, nous indiquons quand un enregistrement était valide dans le temps. Une nouvelle clé de substitution est générée, maisla clé métier – CustomerName – reste la même., Lorsqu’une table de faits est chargée, une recherche sera effectuée sur la table client. En fonction de l’horodatage de l’enregistrement des faits, l’une des deux lignes sera renvoyée. Par exemple:

Tous les faits sont pour le même client. Lorsque vous demandez le montant total des ventes pour CustomerA, le résultat est 31. Le total des ventes par site est de 12,75 pour Anvers et de 18,25 pour Bruxelles, même si les données concernent le même client. En utilisant SCDType 2, nous pouvons analyser nos données avec des attributs historiques.,

méthodes D’implémentation

Il existe plusieurs méthodes pour charger une Dimension de type 2 ina Data warehouse qui change lentement. Vous pouvez opter pour une approche T-SQL pure, soit avec des instructions multipleT-SQL, soit en utilisant L’instruction MERGE. Ce dernier est expliqué dans leen utilisant L’instruction SQL Server MERGE pour traiter le type 2 en changeant lentement les Dimensions.

avec SSIS, vous pouvez utiliser l’assistant de Dimension à changement lent intégré, qui peut gérer plusieurs scénarios., Cet Assistant est décrit dans la section tipsLoading Historical Data into a SQL Server Data Warehouse andHandle Slowly Changing Dimensions in SQL Server Integration Services. L’inconvénient de cet Assistant est la performance: il utilise la commande OLE DB pour everyupdate, ce qui peut entraîner de mauvaises performances pour des ensembles de données plus volumineux. Si vous apportez des modifications au flux de données pour résoudre ces problèmes, vous ne pouvez pas exécuter à nouveau l’assistant car vous perdrez toutes les modifications.

la dernière option – en plus d’utiliser les composants 3rdparty – consiste à construire vous-même la logique SCD de Type 2 dans le flux de données,que nous décrirons dans la section suivante.,

implémentation dans SSIS

la solution proposée dans cette astuce fonctionne pour n’importe quelle version de SSIS. Nous prendrons l’exemple de la dimension client, mais un champ supplémentaire a été ajouté:l’attribut email. Nous ne conservons pas l’historique des adresses e-mail, donc anynew value écrasera toutes les autres valeurs.

Tout d’abord, nous lisons les données d’une source, probablement un environnement intermédiaire. Lors de l’utilisation d’une source relationnelle, vous pouvez utiliser le composant source OLE DB avec une requête SQL pour lire les données. Sélectionnez uniquement les colonnes dont vous avez réellement besoin pour votre dimension. Le champ validfrom est également calculé ici., Puisque vous chargez généralement les données du jour précédent, ValidFrom est défini sur la date d’hier. Si vous n’avez pas de source relationnelle, vous pouvez ajouter cette colonne à l’aide d’une Columntransformation dérivée.

Dans la prochaine étape, nous faisons une recherche sur la dimension.Ici, nous allons vérifier si les lignes entrantes sont une insertion ou une mise à jour.Si aucune correspondance n’est trouvée, la ligne est un nouveau membre de la dimension et elle doit être insérée.Si une correspondance est trouvée, le membre de dimension existe déjà et nous devrons vérifier les modifications de type SCD 2., Sauf si vous avez une très grande dimension, Vous pouvez utiliser le cache complet:

configurez la transformation de recherche pour envoyer des lignes non correspondantes à la sortie no match. Dans SSIS 2005, cette option n’existe pas encore, vous pouvez donc utiliser la sortie d’erreur ou définir la transformation pour ignorer les échecs et séparer les insertions et les mises à jour en utilisant une division conditionnelle.

dans le volet connexion, la requête SQL suivante récupère la surrogatekey, la clé métier (CustomerName) et les colonnes SCD Type 2., Pour chaque membre, seule la ligne la plus récente est récupérée, en filtrant sur le champ ValidTo.

Le champ Emplacement est renommé pour Location_OLD pour plus de clarté. Dans le volet colonnes, faites correspondre la clé métier et sélectionnez Toutes les autres colonnes.

Maintenant, nous pouvons ajouter une Destination OLE DB sur la toile. Cette destination écrira toutes les nouvelles lignes dans la dimension. Connectez la sortie de recherche sans correspondance de la transformation de recherche à la destination.,

Sur le volet correspondance, carte les colonnes du flux de données avec les colonnes de thedimension table.

la colonne SK_Customer n’est pas mappée, car c’est une colonne D’IDENTITEET ses valeurs sont générées par le moteur de base de données. La colonne ValidTo est également laissée vide. Les nouvelles lignes n’ont aucune valeur pour cette colonne.

dans la partie 2 de cette astuce,nous allons continuer notre configuration du flux de données, où nous allons vérifier si une ligne est une mise à jour de type 2 ou non.,

prochaines étapes

• Si vous voulez en savoir plus sur la mise en œuvre de dimensions à changement lent inssis, vous pouvez consulter les conseils suivants:
  • utilisation de L’instruction SQL Server MERGE pour traiter les Dimensions à changement lent de type 2
  • chargement des Données historiques dans un entrepôt de données SQL Server
  • Gérer les Dimensions à changement lent dans SQL Server Integration Services
• vous trouverez d’autres conseils de développement SSIS dans cet aperçu.,

Dernière mise à Jour: 2018-08-09

a Propos de l’auteur

Koen Verbeeck est un BI professionnel, spécialisé dans la pile Microsoft BI avec un amour particulier pour les SSIS.
Afficher tous mes conseils