Implémentation d’une DateTool avec PowerPivot

L’objectif de cet article est de montrer comment implémenter une DateTool en Tabular.
Sur les cubes multidimensionnels, ce n’est pas bien compliquer, on arrive « facilement » à nos fins à l’aide des scopes assignments. Mais étant donné que cette notion n’existe pas en DAX, voyons comment mettre en place ce type de solution.

Lire la suite

PowerPivot SamePeriodLastYear on non contiguous date selections

Avec PowerPivot V2, vous avez un bon nombre de fonctions de manipulations de dates dont la très célèbre SamePeriodLastYear. Cette dernière permet de « retourner une table qui contient une colonne de dates décalées d’une année en arrière par rapport aux dates de la colonne dates spécifiée, dans le contexte actuel » (cf. TechNet)

Mais ce qui n’est pas indiqué, c’est qu’elle n’est utilisable que sur une plage de dates contiguës. Ce qui la rend inutile pour des analyses temporelles à périmètre constant; provoquant même un joli message d’erreur lors de la mise en place de filtres  sur des plages de dates non contiguës :

Calculation Error SAMEPERIODLASTYEAR only works with contiguous date selections

Voyons comment contourner ce problème épineux 😉 Lire la suite

PowerPivot : calculate Iso Last Year Value

Comme vous avez déjà pu le constater (ou pas encore), en DAX :

  • les fonctions de manipulation de dates ne propose pas grand-chose concernant les dates ISO
  • il n’est pas possible de manipuler vos hiérarchies calendaires tout comme on le fait en MDX ( lag, lead, parent, cousin, etc.)

Alors comment faire pour créer des mesures permettant d’avoir la valeur de l’année précédente en se basant sur le calendrier Iso ? C’était si simple en multidimensionnel …

Lire la suite

PowerPivot Excel2013 – Table temps universelle pour vos POCs

Le triptyque Excel 2013, PowerPivot et Power View forme une excellente plateforme nous permettant de (dé)montrer à nos clients :

  • les biens fondés de BI Self-Service
  • la puissance et la performance du moteur xVelocity (ou VertiPaq)
  • la simplicité d’utilisation de ces outils, puisque désormais tout est intégré dans Excel

Bon je ne vais pas faire un n-ième post sur Excel 2013 et les fonctionnalités BI, pour ceux qui désireraient plus d’infos sur le sujet, je ne saurai que vous renvoyer aux excellents articles de :

pour ne citer qu’eux, désolé pour les autres…

Bon vanter les mérites d’une table temps universelle, je ne citerai que les points suivants :

  • un POC s’effectue dans un court délai => tout gain de temps est donc bon à prendre
  • la demande de POCs PowerPivot est sans cesse grandisssante => rien ne sert de réinventer la roue
  • vous serez ammener à faire vos démos dans des environnements dépourvus de SQL Server => pour l’universalité de notre solution autant partir sur les fonctionnalités offertes par Excel et PowerPivot plutôt que de s’appuyer sur un SGBDR.

Passons désormais en mode tutoriel pour la réalisation de notre dimension Temps.

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

Récupérer les informations de partitionnement d’une table

Si avez besoin de récupérer de façon exhaustive les metadada de partitionnement d’une table, à savoir :

  • la fonction de partitionnement
  • le schéma de partitionnement
  • le mode de gestion des bornes (LEFT / RIGHT)
  • la liste des partitions
  • le mode de compression utilisé sur chacune des partitions
  • le filegroup de destination de chaque partition
  • les paramètres de la fonction de partionnement
  • le mapping paramètres/colonnes
  • les valeurs des bornes de partitionnement par paramètre
  • les bornes min et max relatives à chaque (partition, paramètre)

Lire la suite

Scripter la (re-)création des indexes

Ayant besoin, pour un article qui devrait être publié incessamment sous peu, d’un script permettant de récupérer la définition des indexes d’une table, je me suis mis à faire quelques recherches sur le Net. Malheureusement au bout de 10 mn de recherche infructueuse (je n’ai trouvé aucun script complet), je me suis donc décidé à le faire moi-même.

Le but est de générer dynamiquement le code sql de (re-)création d’indexes d’une table. Je me restreins uniquement aux indexes que j’utilise à 99,99% en BI à savoir : les indexes clustered, nonclustered et nonclustered columnstore (je laisse donc de côté les fulltext, xml et spatial 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

5 bonnes raisons d’utiliser les Foreign Keys dans un DWH

Il s’agit là d’un sujet à controverse. Bon nombre de BI Pros estiment qu’il ne faut pas déclarer les Foreign Keys sur un entrepôt car les contraintes d’intégrités sont « sensées » être gérées dans vos lots SSIS et qu’elles ralentissent les performances lors de l’alimentation des tables de faits.

Et là je dis NON !  Quelles sont pour moi les 5 raisons principales pour lesquelles il faut déclarer des Foreign Keys :

 #5 – Les Foreign Key ne dégradent pas de façon significatives les performances d’intégration. Si tel est le cas, c’est peut-être parce que votre intégration elle-même n’est pas bonne (chargement de plusieurs millions de lignes en annule/remplace plutôt qu’en incrémental), architecture non optimisée (avez-vous partitionné vos tables de faits ? Si oui les alimentez-vous en utilisant le partition switching ?) Si toutefois, elles entravent toujours vos durées d’intégration, SSIS vous permet toujours de passer outre, il suffit de cocher la bonne case sur les composants d’insertion.

OLE_DST - Without CheckConstraints

 #4 – L’ordre d’exécution des packages SSIS doit suivre la logique du modèle (on charge d’abord les dimensions avant de s’occuper des tables de faits). Or sans contraintes d’intégrités rien n’empêche de faire une opération de nettoyage sur une dimension alors que les données de faits ont déjà été chargées. Petit rappel au passage; que vous implémentiez du Tabular ou du Multidim, un seul type de modélisation convient : l’étoile !

 #3 – Il n’est pas rare de se plugger directement sur les sources de données SQL des outils opérationnels (ERP et autres applications tierces), seulement le business n’attends pas de voir et d’avoir les impacts que des évolutions et/ou migrations peuvent avoir sur le décisionnel. Cela peut donc changer l’essence même des données et il est préférable de s’en rendre compte immédiatement par une erreur de votre chaine d’intégration plutôt que de s’en apercevoir 3 à 6 mois après et d’avoir ainsi un chantier beaucoup plus vaste (évolution de la chaîne d’intégration, correction des données dans l’entrepôt …)

 #2 – Dans un contexte de compétitivité sans cesse grandissement, la demande BI Self-Service va croitre rapidement. Or cette dernière n’est pas antinomique avec la BI Corporate. Les deux sont bel et bien complémentaires (je vous renvoie pour cela à l’excellent post de Chris Webb http://t.co/dcVphdzI ). Or la BI Self-Service va s’appuyer en grande partie sur l’entrepôt que vous aurez modélisé. Et là rien de tel que les clés étrangères pour faciliter et s’assurer de la bonne construction du modèle d’analyse (Tabular ou Multidim)

 #1 – Un entrepôt reste avant tout une base de données relationnelle, et s’administre de la même façon : plan de maintenance, de sauvegarde, d’indexation, de statistiques …. Et bien entendu des contrôles d’intégrités référentielles. Cette dernière étape est très peu utilisée sur un entrepôt et pourtant les bugs çà existent (règles de gestion omises ou non connues par le métier, cas particuliers non recettés, etc…)

 Bon j’espère vous avoir convaincu.

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