Blog Homsys

Plusieurs techniques existent permettant de détecter et traiter des lignes en doublon avec Microsoft SQL Server Integration Services (SSIS).

La plupart des ressources sur le web proposent de gérer ce point uniquement avec du SQL dans la source de données, certaines proposent même de passer par des tables temporaires.

Je vous propose ici une solution rapide à mettre en œuvre tout en bénéficiant des avantages de SSIS en termes de maintenance, de performance et de lisibilité en phase de développement.

Cette solution répond au besoin suivant :

• Ma table cible doit être alimentée avec des lignes uniques par rapport à une clé,
• Dans le cas où des doublons sont rencontrés, l’une des lignes doit être conservée,
• Les doublons peuvent être identifiés et éventuellement stockés dans une table de rejet.

Ce cas est notamment rencontré lorsque l’on charge un référentiel client. Souvent, la base client doit être la plus complète possible mais dans le cas où la donnée serait dupliquée, on ne souhaite ni dupliquer le client ni perdre la trace de son existence. On appliquera alors une règle fonctionnelle permettant de déterminer la version du client à conserver en cas de doublon (le numéro de création le plus élevé ou la date de mise à jour la plus récente par exemple).

1 : Contrôle de doublon avec conservation de l’une des lignes du doublon

1) DOUBLON : Source contenant potentiellement des doublons

2) Contrôle doublon : Recherche effectuée avec les paramétrages spécifiques suivants :

a. Onglet Général : « Rediriger les lignes en erreur vers la sortie sans correspondance »

b. Onglet Connexion : requête sur la même table que la source DOUBLON permettant de renvoyer l’ensemble des lignes qui ne sont pas en doublon et, pour les lignes en doublon, la ligne qui devra être conservée. La requête est construite comme suit :

select CLE, max(COND) COND from doublon group by CLE

Où CLE correspond à la ou les colonnes constituant la clé sur laquelle le test d’unicité est effectué, COND correspond au critère permettant de choisir la ligne à conserver en cas de doublon (numéro de création, date de mise à jour…). Selon les cas, le Max peut être remplacé par un Min.

c. Onglet Colonne : Faire la jointure sur la ou les colonnes CLE et COND

3) Le flux de sortie « Sortie de recherche avec correspondance » fournit les lignes non doublonnées ainsi que la ligne devant être conservée en cas de doublon.

4) Le flux de sortie « Sortie de recherche sans correspondance » fournit les lignes qui sont doublonnées, qui pourront ensuite être insérées dans une table de rejet.

Remarque : Dans le cas où la présence de doublons doit interrompre le chargement, on pourra implémenter un compteur de nombre de lignes sur la branche « sortie sans correspondance » qui, s’il est différent de 0 bloquera la suite de l’exécution.

Autres solutions permettant de traiter des doublons :

• Utilisation du composant MSDN : http://msdn.microsoft.com/fr-fr/library/ms160916%28SQL.90%29.aspx (je ne l’ai personnellement pas testé).
• Utilisation de la tâche de Tri et de l’option « Supprimer les lignes avec valeurs de tri en double » : L’avantage de cette solution est sa simplicité de mise en œuvre, son inconvénient est qu’elle ne permet pas de récupérer les lignes en doublon.
• Utilisation des composants multidiffusion, agrégation, jointure pour reproduire un fonctionnement identique à la requête SQL présente dans le lookup de la solution présentée ci-dessus. L’avantage de cette solution est qu’elle est uniquement basée sur l’utilisation de composants SSIS, son inconvénient est qu’elle est un peu plus longue à développer et ses performances sont un peu moins bonnes.

Afin de profiter pleinement de l’optimisation des temps de réponses que procurent les indexes, il ne faut pas oublier de prévoir une tâche régulière d’ »entretien ». Elle consiste à effectuer des REBUILD ou des REORGANIZE en fonction de l’état de fragmentation de l’indexe. Cette tâche pourra ensuite être intégrée à un job de chargement quotidien par exemple.

Voici un récapitulatif des « best practises » en la matière.

Astuce

Utiliser la fonction ‘sys.dm_db_index_physical_stats‘ et son champ ‘avg_fragmentation_in_percent‘

Syntaxe

sys.dm_db_index_physical_stats (
{ database_id | NULL }
, { object_id | NULL }
, { index_id | NULL | 0 }
, { partition_number | NULL }
, { mode | NULL | DEFAULT }
)

Exemple

SELECT b.name, a.* FROM
sys.dm_db_index_physical_stats (
DB_ID(N’Ma_base’)
, OBJECT_ID(N’dbo.Ma_table’)
, NULL
, NULL
, DEFAULT
) AS a
JOIN sys.indexes AS b ON a.object_id = b.object_id AND a.index_id = b.index_id

Recommendations Microsoft

Valeur de avg_fragmentation_in_percent	Correction à apporter
> 5% et < = 30%	ALTER INDEX REORGANIZE
> 30%	ALTER INDEX REBUILD WITH (ONLINE = ON)

Exemples

ALTER INDEX [Mon_indexe] ON dbo.Ma_table REBUILD
ALTER INDEX [Mon_indexe] ON dbo.Ma_table REORGANIZE

Lors d’une récente mission chez un client nous avons été confronté au besoin d’agréger des mesures de cube sous forme de médiane.

Intérêt d’agréger une mesure en médiane

Concrètement, ce besoin revient à retourner la valeur médiane d’une mesure pour les dimensions en cours, plutôt que classiquement la somme ou la moyenne.  Sur un exemple basique, pour une mesure Chiffre d’Affaire, autour d’un axe d’analyse Région, le besoin client est de retourner la valeur médiane du CA de chacune des régions :

EX :    [Bretagne=10M€,   Sud Ouest=17M€, PACA=23M€,   Rhone Alpes=31M€,   IDF=51M€ ]

Le CA au niveau national sera la valeur médiane de la liste des valeurs régions, soit 23M€.

Fonctionnellement prendre la valeur médiane (plutôt que la valeur moyenne) a pour effet de minimiser l’impact des valeurs extrêmes, et donc de minimiser le « bruit » que pourrait provoquer des erreurs de saisie ou de chargement.

EX : Si l’on rajoute [Alsace=300M€] à la liste précédente (ce qui pourrait correspondre à une erreur de saisie – avec un zéro de trop)

La valeur médiane au niveau national est 27M€

=> L’impact de l’erreur de saisie de l’opérateur Alsacien sur le CA national est minimisé. Une agrégation en moyenne aurait « tiré vers le haut » le CA national (moyenne = 72M€)

Sur l’exemple donné – qui est volontairement simpliste – le bénéfice de l’agrégation en médiane est plutôt léger, mais sur une liste de valeurs plus importante, il y a de nombreux cas fonctionnels où la médiane d’une liste de valeurs est bien plus représentative (et intéressante pour l’utilisateur) que la moyenne .

Problématique

Le problème vient du fait que les agrégation de type médiane n’est pas supporté nativement par Analysis Services.

Solution

La solution que nous avons proposée est d’utiliser la fonction MDX  MEDIAN(). C’est une fonction mathématique de base qui retourne la valeur médiane d’une mesure pour un « set » donné.

Elle doit être appelée avec 2 paramètres :

• L’ensemble de valeurs sur lequel « ventiler » la valeur médiane, le « set »
  
• et la mesure à agréger

Dans le cas du CA par région cela donnerait une mesure calculée du style  :

CA_National =     MEDIAN( [Geography].[Region].Members, (= liste de valeurs)
[Measures].[CA]) (= mesure à agréger)

=> Cette solution fonctionne et retourne effectivement la valeur médiane du CA de chaque région

En allant plus loin

On remarquera qu’en réalité la solution proposée n’agrège pas – à proprement parler – en médiane : elle calcule la valeur médiane d’une mesure existante. Et ce n’est pas tout à fait la même chose ! Car cette mesure existante a son propre type d’agrégation…

C’est à dire, qu’elle calcule la valeur médiane, d’une liste de valeurs qui restent à leur type d’agrégation par défaut. Dans l’exemple, la formule d’agrégation ne fonctionne qu’au niveau national : la valeur retournée est la médiane des valeurs CA au niveau région, valeurs qui sont elle même agrégées… dans leur type d’agrégation par défaut définit dans SSAS (c’est à dire Somme, Moyenne etc…)

Le besoin de notre client était plus complexe que cela : il souhaitait une valeur médiane d’une mesure quelque soit l’axe d’analyse et quelque soit le niveau de navigation dans la dimension.

Par exemple sur une mesure DélaisRésolutionIncident, le souhait est de voir retournée la valeur médiane (de toute les valeurs disponibles) quelque soit l’axe d’analyse : que ce soit au niveau d’un mois, d’une année, d’un service, d’une région, d’une sous région etc.

Nous avons proposé une solution calée sur la précédente : utiliser la fonction MEDIAN en passant cette fois comme « set »  l’ensemble des lignes  de faits disponibles. Cela donne quelque chose comme :

MEDIAN_DelaisResIncident =     MEDIAN( [DimFait].[DimFait hierarchy].[DimFait fact line].Members, (= ensemble des lignes de fait)
[Measures].[ValDelaisResolutionIncident]) (= mesure à agréger)

Pour cette formule nous avons du créer dans le cube – via le DSV – une dimension de fait dont le nombre de lignes au niveau le plus fin est exactement le même que celui de la table de fait. Cette dimension « virtuelle » ne sert qu’à définir le « set » de valeurs.

=> Cette solution fonctionne et permet une réelle agrégation de mesure sous forme de médiane.

PS : Ce second exemple illustre bien l’intérêt de la médiane évoqué en introduction : si par exemple un incident est resté non clôturé plusieurs mois, alors que généralement ils le sont en quelques minutes, cette anomalie « pourrira » l’indicateur de moyenne. L’indicateur de médiane en revanche sera bien plus pertinent pour l’utilisateur.

Temps de réponse

Si la solution proposée fonctionne sur le papier et avec nos jeux de tests, elle s’est révélée un peu décevante dans la pratique. Avec l’augmentation du nombre de lignes de faits, le temps de réponse du cube explose lorsque l’on fait appel à la mesure utilisant la médiane.

En effet, avec un nombre de ligne de l’ordre de plusieurs dizaines de milliers d’enregistrements, le temps de réponse dépasse les 2 minutes (le « time out » de notre outil de restitution). A titre de comparaison, l’agrégation classique en moyenne, sur les mêmes données met moins d’une seconde…

=> Cette solution est donc à utiliser avec précaution.

Analyse

Mais pourquoi cela prend-t-il autant de temps ?

Cette fonction MDX  MEDIAN est à l’origine une fonction mathématique, pas une fonction d’agrégation. Les valeurs ne sont donc pas pré-calculées avec cette médiane comme elles le seraient avec les agrégations en Moyenne ou en Somme. La solution proposée est en réalité une utilisation détournée de la fonction MEDIAN

Dans tous les exemples d’utilisation que l’on trouve de cette fonction,  le premier paramètre (le « set ») est de cardinalité très faible. Alors que dans le cas exposé ci-dessus l’ensemble de « ventilation » est l’ensemble des lignes de faits, donc un ensemble à cardinalité plutôt élevée.

La fonction Median ne permet pas de pré-aggrégation dans la mesure ou son algorithme de résolution nécessite 2 « passages » sur la liste de valeur (là où la Moyenne n’en nécessite qu’un) : un passage pour trier toutes les valeurs, un autre pour prendre la moyenne des deux valeurs du milieu de la liste triée.

Dans le premier exemple avec le CA par région, en réalité le cube se base sur les pré-agrégations sous forme de Somme du CA stockées pour chaque région, et calcule ensuite la médiane de ces pré-agrégations. L’algorithme des 2 « passages » n’est en fait déroulé que sur une liste de 22 valeurs. Tandis que dans notre cas il est déroulé sur le nombre de lignes de fait.

=> La fonction MDX  MEDIAN n’est donc à utiliser pour agréger une mesure que sur des tables de faits de faible population.

Vous avez peut-être déjà rencontré un problème d’extrème lenteur à l’ouverture de BI Developement Studio (BIDS), lors de développement de flux SSIS.

Symptômes :

Le problème se manifeste par un « blocage technique » de Visual Studio (avec perte de la main) d’une durée de 8 à 15 minutes lorsque :

• l’on ouvre un fichier de flux SSIS (*.dtsx)
• l’on lance une exécution de flux SSIS (*.dtsx) en mode debug, (l’attente déclenchant généralement un timeout sur le « fork » du processus de debug de Visual Studio cf. ci-après)

Ce blocage est indépendant du processus (normal) de vérification des lots du package, qui intervient une fois les 8/15 minutes de blocage technique passées.

Ce problème persiste :

• au changement de user
• au changement de machines
• à la bascule des fichiers et/ou bases de données en local

Le retard à l’exécution est parfois tellement important que le « fork » du thread de debug par rapport au thread de Visual Studio, passe en « time out », provoquant le message d’erreur suivant :

Une fois que le « fork » a échoué une fois, toute les autres tentatives échoueront venant s’ « empiler » par process ID :

Pour qu’un nouveau test d’exécution de flux en mode debug soit de nouveau effectif, il faut réinitialiser les paramètres utilisateur de Visual Studio :

• Menu Démarrer -> Exécuter -> ‘devenv /resetsettings‘

Analyse :

Une analyse poussée du problème a permis de se rendre compte :

• lorsque le problème se manifeste, une connexion HTTP est effectuée vers un serveur distant de Microsoft (crl.microsoft.com) visible avec la commande MS-DOS NETSTAT
• cette connexion est bloquée en attente de réponse (statut SYN_SENT) (voir la copie d’écran ci-jointe)
• au bout des 10 à 15 minutes d’attente cette connexion se ferme et le fichier s’ouvre/s’exécute -> cette connexion en attente est à l’origine du problème
• CRL veut dire « Certificate Revocation List« , et cette connexion automatique est une vérification de licence/certificat
• une ouverture/exécution de flux à partir d’une connexion avec un compte local (hors domaine) sur les machines concernées par le problème, ne pose pas de souci -> avec un compte local la vérification de licence n’est pas effectuée

Il semble donc qu’il s’agisse soit d’un problème de domaine utilisateur, soit de proxy/firewall qui refuse la connexion HTTP vers le site de vérification de licence.
Le compte local doit être considéré comme « suffisamment sûr » par Microsoft pour ne pas effectuer de vérification de certificat.

En attendant, l’installation du SP2 de SQL-Server 2005 sur le poste ne change rien au problème ci-dessus.

Solution/Palliatif :

Au final on peut désactiver cette vérification de certificat en effectuant les opérations suivantes :

1. Dans les options avancées d’Internet Explorer décocher les cases « Vérification de révocations des certificats »
2. Changer la valeur de la base de registre [HKEY_USERS\S-1-5-20\Software\Microsoft\Windows\CurrentVersion\WinTrust\Trust Providers\Software Publishing]< de ‘23c00‘ à ‘23e00‘

(source : http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=125768 )

Ces manipulations, semblent corriger définitivement le problème (par contre la première est à effectuer pour chaque utilisateur utilisant le poste). En attente d’une plus ample utilisation pour confirmation.
Les utilisateurs appartenant à des domaines sont probablement configurés pour faire ces vérifications de certificats par défaut, tandis que les utilisateurs locaux ne les font pas, ce qui peut expliquer les différences que nous constations.

Sources :

• http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=125768
• http://www.bizbert.com/bizbert/default,date,2007-04-01.aspx

La tâche SSIS (Microsoft SQL Server Integration Services) de chargement en bloc est utile pour charger un fichier plat dans une table d’une base de données dans les 2 cas suivants:

• Le chargement d’un fichier volumineux
• Le chargement d’un fichier à champs fixes contenant un nombre de colonnes important (ce qui permet un gain de temps non négligeable en phase de développement)

Tâche d’insertion en bloc

Son utilisation n’étant pas toujours triviale, voici une description des différentes étapes à suivre :
• 1) Création d’une connexion vers le fichier texte à charger
• 2) Utilisation de la tâche d’insertion en bloc (présente dans le flux de contrôle)
• 3) Paramétrage de l’onglet Connexion de la tâche d’insertion en bloc
• 4) Paramétrage de l’onglet Options de la tâche d’insertion en bloc

Etape 3) Paramétrage de l’onglet Connexion de la tâche d’insertion en bloc:

Tâche d’insertion en bloc – Onglet Connexion

• Connexion : Choisir la connexion vers la base de données dans laquelle les données seront chargées.
• DestinationTable : Choisir le nom de la table dans laquelle les données seront insérées. La table devra contenir l’ensemble des colonnes présentes dans le fichier.
• File : Connexion vers le fichier plat source.
• Format : Spécifier la valeur Utiliser un fichier pour charger un fichier dont le format est décrit dans un fichier de format .fmt. Pour les fichiers au format délimité sélectionner le format Spécifier.

Options pour les fichiers au format champs fixes :

• FormatFile : Définir le chemin d’accès vers le fichier .fmt décrivant le format du fichier.

Options pour les fichiers au format délimité :

• RowDelimiter : Définir les caractères séparateurs de lignes (en général {CR}{LF} en environnement windows).
• ColumnDelimiter  : Définir les caractères séparateurs de colonnes

Le chemin utilisé vers le fichier de format dans la propriété FormatFile doit être au format UNC (Universal Naming Convention): \\NomServeur\Partage.

Etape 4) Paramétrage de l’onglet Option de la tâche d’insertion en bloc

Tâche d’insertion en bloc – Onglet Options

• Options : Décocher les différents contrôles.
• SortedDate : Laisser la valeur non renseignée par défaut.
• MaxErrors : Laisser la valeur 0
• CadePage : Spécifier la valeur RAW
• DataFileType : Spécifier la valeur char
• BatchSize : Laisser la valeur 0
• LastRow : Laisser la valeur 0
• FirstRow : Laisser la valeur 1 (sauf si le fichier contient un en-tête qui ne doit pas être chargé)

Exemple de fichier .fmt:

Le fichier .fmt permet de décrire le format du fichier à charger. Il liste les colonnes contenues dans le fichier ainsi que leurs caractéristiques. Sa construction est en général rapide à partir d’un document excel décrivant les colonnes du fichier (pour ceux qui le souhaitent, j’ai un exemple de fichier permettant la génération automatique de fichier fmt à partir de formules excel).

Voici un exemple de fichier .fmt  simple :

10.0

2

1 SQLCHAR 0 3 «  »       1 CH1 French_CI_AS

2 SQLCHAR 0 5 « \r\n » 2 CH2 French_CI_AS

• La première ligne correspond à la version SQL Server utilisée (10.0 pour la version 2008, 9.0 pour la version 2005, 8.0 pour la version 2000 et 7.0 pour la version SQL 7…)
• La deuxième ligne précise le nombre de colonnes présentes dans le fichier
• Les lignes suivantes décrivent chaque colonne du fichier en précisant : la position de la colonne, SQLCHAR, 0, le nombre de caractères de la colonne, le numéro de la colonne, le nom de la colonne, la collation (FRENCH_CI_CI_AS).

La dernière ligne du fichier doit être un retour à la ligne.

La génération du fichier fmt peut être également effectuée automatiquement à partir de la table cible en utilisant la commande bcp.

Générer un fichier .fmt à partir de la commande bcp:

Ouvrir une fenêtre d’Invite de commande et exécuter la commande suivante :

bcp NomBaseDeDonnées.Schéma.NomTable format nul -c -f

CHGT_BLOC.fmt -T -S NomServeur\NomInstance

Exemple d’utilisation de la commande BCP

Conclusion:

Ce composant a l’avantage d’être performant. Il a par contre l’inconvénient d’être difficile à auditer en cas d’erreur. En effet, si le fichier source ne respecte pas strictement le format décrit, le fichier sera rejeté en bloc sans possibilité d’identifier la ligne ou la colonne ayant généré une erreur. Il n’est pas possible également de placer une visionneuse sur les données pour suivre le chargement.

Ce composant n’est donc à utiliser que sur des fichiers dont la structure est connue et bien maîtrisée. Pour des fichiers non volumineux et contenant un nombre de colonnes raisonnables, on préférera utiliser le chargement classique via des sources OLE DB.