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

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

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

Gestion dynamique des partitions OLAP avec SSIS

Sur des projets de haute volumétrie, le partitionnement des groupes de mesures est indispensable aussi bien :

  • pour optimiser les temps de traitement d’un cube
  • que pour minimiser les temps de requêtes

C’est une bonne pratique reconnue mais pas toujours bien mise en oeuvre.

Tout d’abord, utilisons les seuils indiqués par les différents « white papers ». Si tout le monde les lisait, nos projets ne s’en porteraient que beaucoup mieux. Encore faut-il aimer son job, s’y intéresser et investir un peu de son temps personnel (bon là je dérive …)

La bonne pratique veut que tout groupe de mesures de plus de 20 millions de lignes ou de plus de 250 Mo doit être partitionné. A cela s’ajoute aussi les considérations hardware, nombre de coeurs et de disques, de façon à s’assurer que les opérations de lecture/écriture (I/O) puissent être parallélisées. Bien sur, il ne s’agit que de valeurs indicatives mais cela vous aidera à déterminer s’il faut partitionner ou bien revoir la stratégie de partitionnement déjà en place.

Lire la suite

Chargement efficient de fichiers avec SSIS

En règle générale, lorsqu’il faut charger plusieurs fichiers ayant le même format, tout développeur SSIS utilise une simple boucle en récupérant les fichiers un à un pour les charger séquentiellement en base.  Dans la majorité des cas, tout cela fonctionne très bien tant que vous pouvez vider le répertoire dans lequel ils sont stockés.

Lire la suite