SSIS et ACE 12.0 : Unspecified error

Si comme moi vous avez un package SSIS permettant de charger un fichier Excel via le provider Microsoft Access Database Engine 12.0 et que ce dernier est exécuté par un job via un compte proxy, vous serez certainement confronté à cette erreur :

SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred.
Error code: 0x80004005 Description: "Unspecified error".

Comme son nom l’indique, l’erreur est non spécifiée, prévoyez donc  de longues heures de recherches hasardeuses….

Lire la suite

Import de fichiers Excel avec SSIS

Ce type de source est loin d’être mon préféré et cela en raison du typage implicite des données effectué par le provider :

  • par défaut les colonnes texte sont typées en nvarchar(255) ou mémo (pour les chaines de plus de 255 caractères) et les numériques en réel. Pour convertir vos données dans le format désiré, vous n’avez plus qu’à utiliser un Data Conversion, je vous déconseille vivement de la faire via l’éditeur avancée mais bon (certains sont plus joueur que d’autres…)
  • de plus, quel que soit le provider (JET4.0 ou ACE12.0), le typage des données est effectué à l’aide d’un échantillonnage, par défaut sur les 8 premières lignes. Ce paramètre peut être modifié dans la base de registre

pour le Jet4.0 : HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Excel

pour le ACE 12.0 : HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\12.0\Access Connectivity Engine\Engines\Excel

  • si vous avez une colonne contenant des valeurs textuelles et numériques il vous faudra alors aussi gérer les types mixtes. Pour cela, vous n’avez qu’à modifier la chaîne de connexion en ajoutant aux propriétés étendues la valeur IMEX=1, exemple :

pour le Jet4.0 : Provider=Microsoft.Jet.OLEDB.4.0; Data Source=C:\MyExcel.xls;Extended Properties= »Excel 8.0;HDR=Yes;IMEX=1« ;

pour le ACE 12.0 : Provider=Microsoft.ACE.OLEDB.12.0; Data Source=C:\MyExcel.xlsx;Extended Properties= »Excel 12.0 xml;HDR=Yes;IMEX=1« 

Sachez toutefois que :

  • même si la valeur du TypeGuessRows est à 0, seules les 16 384 premières lignes sont scannées. Dommage si vous avez des chaines  longues de plus de 255 caractères ou bien des valeurs de types mixtes sur les lignes suivantes (cela m’est encore arrivé il y a peu), vous n’avez plus qu’à pleurer…
  • si le fichier Excel est en cours édition, il est verrouillé ce qui fera planté votre package SSIS
  • que le connecteur 64 bits pour Excel fonctionne, il suffit d’installer le ACE.OLEDEB.12.0 version 64 bits sur le serveur SSIS. Notez toutefois qu’il n’est pas possible d’installer les 2 versions du provider (32 et 64 bits) sur la même machine (mais qui de nos jours continue à installer Office sur les serveurs MS BI ?)

Mis à part ces considérations, les fichiers Excel peuvent s’avérer utiles du fait qu’un même classeur Excel puisse contenir plusieurs onglets (très pratique pour des tables de paramétrage « end-user »), et qu’un même onglet puisse contenir plusieurs tables. Rentrons dans le vif du sujet et voyons comment intégrer différentes tables contenues dans un seul et même onglet.

Lire la suite

Benchmark SSIS de chargement de dimension en SCD de type 1

Pour faire suite à l’article traitant de l’optimisation des chargements de dimensions en SCD de type 1 et au commentaire de David sur l’utilisation du MERGE T-SQL, je me suis dit qu’un benchmark permettant de comparer les différentes techniques serait intéressant (un petit complément à un certain article de Matt Mason).

Pour être impartial, il faut bien sûr que mon jeu de test soit en adéquation avec les spécifications de ma machine, de façon à ne pas avantager telle ou telle technique. J’ai donc créé une dimension Customer avec 18 colonnes contenant 10 000 000 lignes représentant une taille de  3 GB. Ma table contient une clé métier et la gestion du changement est de type 1 sur l’ensemble des colonnes.

Lire la suite

Optimiser le chargement des dimensions en SCD de type 1

Sur les différents projets MS BI sur lesquels il m’a été donné de travailler, j’ai souvent rencontré le même type d’implémentation pour alimenter une dimension en SCD de type 1.

Etant donnée la persistance des données dans les dimensions celle-ci sont chargées en grande partie en UPSERT en respectant le modèle suivant :

  1. Récupération des données sources (à partir d’une table de staging ou d’un ODS)
  2. Transformations des données (conversion, colonnes dérivées, dé doublonnage, etc…)
  3. Recherche d’existence des enregistrements sources dans la dimension à partir de(s) la(les) clés métiers (business keys)
  4. Insertion des enregistrements sans correspondance
  5. Mise à jour des enregistrements existants

Je rappelle au passage qu’il est reconnu de ne PAS utiliser le composant SCD (Slowly Changing Dimension) et de préférer le LOOKUP, ce qui nous donne le package suivant :

SCD1 - CommonImplementation

Ce type d’implémentation est correct, mais il est bien loin d’être optimisé. Cela n’a pas trop d’impact sur des dimensions de quelques centaines de lignes, mais plus la volumétrie augmente plus les temps de traitements explosent (la courbe étant plutôt exponentielle que logarithmique).

Certains diront que les traitements d’intégration étant exécutés la nuit, cela n’a pas beaucoup d’importance. Cette remarque est sans doute pertinente dans un contexte Franco-Français, avec une seule et unique solution sur votre serveur BI. Mais cela devient beaucoup plus critique lorsque votre serveur héberge plusieurs solutions BI et que vous devez travailler dans un contexte international. Par ailleurs, il n’est pas rare de devoir exécuter partiellement ou complètement des traitements en cours de journée.

Je préfère donc définir un Template qui sera utilisé sur toutes les dimensions un peu comme un Design-Pattern. J’en profite pour glisser une petite digression : dans toute implémentation de package SSIS les deux maîtres mots à respecter sont « performance » et « maintenance ». Voyons comment optimiser notre package de départ. 
Lire la suite

SSIS Partition Switching Template gérant la compression et les ColumnStore Indexes

Pour faire suite au post précédent sur l’administration des partitions sous SQL Server (Gestion dynamique des partitions SQL Server), voyons désormais comment créer un package SSIS Template permettant d’implémenter le partition switching en gérant la compression et les ColumnStore Indexes.

Lire la suite

Manager vos Foreign Keys en BI

Bon suite aux posts de Florian http://fleid.net/2011/12/13/pour-ou-contre-les-clefs-etrangeres-dans-le-datawarehouse et de moi-même http://wp.me/p2yhHc-6T sur le « contre » ou le « pour » des Foreign Keys (FKs) dans un DWH, je vous propose un Pattern pour ceux :

  • qui voudraient rester libre (voir anarchiste) et se passer de FKs lors de la phase de l’alimentation (pour faire plein de Truncate et s’affranchir des contraintes, sans jeux de mots)
  • mais qui, avec leur âme de papa, pourraient vouloir les réactiver afin d’exposer aux utilisateurs, les métadonnées « complètes » du DWH (BI Self-Service oblige) et de sécuriser les données qui ont été chargées (les équipes BI ne sont pas les seules à intervenir sur un DWH)

Merci à Florian pour cette analogie anarchiste/papa, j’adore.

Le principe est simple :

  1. designer l’entrepôt avec les FKs
  2. stocker les définitions des FKs et les supprimer les FKs avant chaque alimentation
  3. alimenter l’entrepôt
  4. recréer les FKs

Et pour cela, nous n’avons besoin que d’une table et de deux packages SSIS.

Lire la suite

Gestion dynamique des partitions SQL Server

Si vous travaillez sur un entrepôt à haute volumétrie, vous serez certainement confronté à des problèmes de performances pour alimenter et requêter les tables les plus volumineuses. Pour résoudre ces problèmes vous devrez avoir recours au partitionnement (fonctionnalité disponible depuis SQL Server 2005, je le précise car il y a quelques irréductibles qui utilise « encore » du 2000).

La mise en œuvre du partitionnement n’est pas la plus complexe qui soit ; seulement le faire une fois c’est bien, mais réinventer la roue sur chaque projet ça use. Voyons comment nous pouvons industrialiser pour tous nos projets BI la création et la gestion des partitions sous SQL Server. Lire la suite