Business Geek

Aller au contenu | Aller au menu | Aller à la recherche

Business Intelligence

Fil des billets - Fil des commentaires

jeudi 28 mai 2009

Améliorer le tableau croisé dynamique de Excel

J'ai souvent eu des questions d'utilisateurs avancés d'Excel pour enrichir l'utilisation du tableau croisé dynamique.

Même si nativement, Excel est sans doute le meilleur client Analysis Services, il lui reste quelques lacunes, qui nécessitent souvent l'intervention des équipes informatiques. Un exemple est l'ajout de calculs simples (ratio, sommes, etc.).

Pour faire cela, on doit écrire un membre calculé en MDX qui prend en charge le calcul. Je vous avais montré comment en ajouter un dans Report Builder 2.0 dans un précédent post.

Maintenant, la même opération est possible directement dans Excel grâce à un add-in qui vient de sortir sur CodePlex : OlapPivotTableExtend

Vous pouvez trouver toutes les infos et le télécharger ici : http://www.codeplex.com/OlapPivotTableExtend.

Il y a évidemment plein d'autres fonctionnalités : recherche, bibliothèque de calcul, etc. L'une de mes préférées est sans aucun doute de pouvoir récupérer la requête MDX générée, chose qu'il fallait faire avec du code VBA ou bien un Profiler.

lundi 23 février 2009

L’après Performance Point

La rumeur a couvé dans les blogs dès le 23 janvier. Elle a vite été confirmée par un communiqué de Microsoft à ses partenaires et communautés le 26 janvier. 

"Microsoft arrête le développement du module planning de Performance Point."

Mais comment comprendre cette décision ? Comment interpréter cette annonce ? Que faire quand on a un projet en cours, ou à venir, basé sur ce produit ? C'est ce dont nous allons discuter dans cet article en apportant un éclairage sur la roadmap de Microsoft.

 

Tout d'abord, faisons taire les rumeurs les plus folles, Microsoft n'arrête pas la Business Intelligence ! Il y a beaucoup d’autres usages que l’élaboration budgétaire dans la BI. Et bien au contraire, avec l'annonce faite sur Performance Point, Microsoft ne fait que recentrer sa stratégie. En effet, rappelons qu’au cœur de la stratégie Business Intelligence de Microsoft se trouve SQL Server (avec Analysis Services, Reporting Services, Integration Services, le Data Mining, etc.) pour la partie plate-forme décisionnelle et Microsoft Office (notamment Excel et Sharepoint) pour les interfaces utilisateurs.

Evidemment, un éditeur qui arrête un produit, c’est toujours dommageable. Mais quand cet éditeur est Microsoft, avec toute sa puissance, ses lignes de produits, il faut y voir un rééquilibrage. Comme je l’expliquais dans une précédente tribune (http://blog.djeepy1.net/post/2008/12/25/Microsoft-Connect), Microsoft est très à l’écoute de ses clients et des communautés ce qui, j’en suis persuadé, va l’obliger à communiquer plus clairement la nouvelle ligne.

Ca a commencé d’ailleurs aux TechDays, avec une session spéciale proposée par Lionel Billon (Product Manager SQL Server) pour répondre aux questions sur le sujet.

Donc que deviennent les modules de Performance Point ?

Tout d’abord, la partie M&A (Monitoring & Analytics), avec les tableaux de bord et les scorecards, passe dans le giron de Sharepoint (MOSS plus exactement) et ce module s’appellera à terme PerformancePoint Services. C'est-à-dire que si vous avez déjà un portail MOSS (en licence Enterprise CAL avec Software Assurance :-(), vous n’aurez pas de coût supplémentaire pour mettre en place des tableaux de bord ou faire bénéficier à vos utilisateurs d’interfaces d’analyse sophistiquées. Sinon, c’est le moment d’en profiter et de creuser Excel Services, la GED, le travail collaboratif, etc. ;-).

Concernant la migration, le passage de relais et toutes les questions de mise en œuvre, vous pouvez partir sur le Dashboard Designer actuel qui permet de créer des tableaux de bord déjà sur Sharepoint. Microsoft gardera la compatibilité à terme (ou fournira un outil de migration).

Donc de ce côté, c’est plutôt une bonne nouvelle et je vous encourage à continuer dans vos projet de M&A.

Du côté de la planification et de l’élaboration budgétaire, Microsoft confirme l’arrêt du module tel qu’il est actuellement. Je reste convaincu qu’il ressortira à un moment ou à un autre dans un autre produit ou sous une autre forme mais rien n’a été confirmé. Les pistes sont un mode Open Source sur CodePlex, ou alors un module de Forecasting dans Dynamics. Moi, je parierai bien sur une intégration dans une future version d’Office car l’add-in de saisie et de spreading était pas mal.

Mais il faut se faire une raison, le produit tel qu’il est ne sera pas continué.

Mais rassurons les actuels clients : comme pour tous ses produits, Microsoft assurera un support sur Performance Point pendant 10 ans. Ce sont uniquement les nouveaux développements qui cesseront après la livraison du SP3.

 

Maintenant, une remarque plus personnelle sur ces annonces ; et cela n'engage que moi. Je suis complètement en phase avec ce recentrage. Mettre les tableaux de bord dans MOSS me semble un choix judicieux et cohérent avec le discours Sharepoint et BI global. En plus, cela va dans le sens des réductions de coûts pour le client, et c’est toujours bon à prendre.

Concernant Planning, je crois au bien fondé du produit. J’ai pu assister à une session de formation partenaires chez Microsoft et j’ai été très séduit. Cependant, un point a choqué mon esprit de technicien, c’est la facilité de créer des modèles (et donc des cubes) et la dangerosité engendrée, à savoir se retrouver avec pleins de cubes, qui n’auraient pas forcément les même données et qui pourraient entrainer une complexification de la gestion en terme de maintenance. Un des buts à la base était d’en finir avec le "Excel-Hell" mais si c’est pour se retrouver avec un "Model-Hell" (ou un Cube-Hell). Et puis Planning Server ne permet pas de résoudre la complexité d’intégration des données, à la charge de la DSI.

Donc  l’arrêt de Planning Server m’embête parce que les add-in de saisie Excel sont très bien (même s'il est déjà possible avec un peu de développement d'écrire dans un cube - fonctionnalité WriteBack - depuis 2000) et ce produit permet de vulgariser la modélisation de cube et de DataWarehouse pour un utilisateur métier. Et puis les routines de gestion des conversions de devises, de dé-doublonnage, de revenue sharing, etc. sont très intéressantes. Mais en même temps, si c’est pour mieux intégrer ces modules dans l’écosystème existant (Office, MOSS, SQL Server) moi je dis Banco.

 

J’espère avoir répondu aux questions sur l’avenir de Performance Point Server. S’il en restait, n’hésitez pas à me contacter. Je tâcherais d’y répondre ou de transmettre à qui de droit.

mardi 10 février 2009

Reporting Services - Obtenir le numéro de semaine

Comme j'ai une mauvaise mémoire et que je cherche cette expression à chaque fois. Je profite de ma tribune publique pour l'écrire et la partager.

Le but est de récupérer le numéro de la semaine d'une date dans une expression Reporting Services. En fait, je connais bien la fonction à utiliser : DatePart(). Le problème c'est que DatePart prend comme premier paramètre un code identifiant l'élément de la date à récupérer. Et le souci c'est que ce code n'est pas tout à fait le même qu'en T-SQL (qui contient une fonction DatePart aussi) et que la documentation est spartiate à cet égard.

Donc la bonne syntaxe est :

=Datepart("ww", Today())

Pour identifier la semaine, il faut utiliser le code ww (et non wk, w, week, etc.)

jeudi 5 février 2009

Reporting Services - Instruction Switch du moteur d'expressions

Un petit billet pour rappeler l'utilisation de l'instruction SWITCH du moteur d'expressions de Reporting Services (que l'on soit dans Visual Studio ou dans Report Builder 2.0).

Cette instruction correspond à une instruction CASE (C#, T-SQL) ou Select (VB.NET). Sauf que contrairement aux autres langages, il n'y a pas la gestion du cas par défaut. Je vais vous montrer comment le gérer.

La syntaxe est la suivante :

Switch (
<condition 1>, <valeur 1>,
<condition 2>, <valeur 2>,
<condition 3>, <valeur 3>
)

A chaque fois qu'une condition est satisfaite, la valeur correspondante est retournée, le code étant débranchant. Evidemment, il arrive souvent que vous ne puissiez écrire tous les cas possibles et imaginables. Dans ce cas, il faut écrire la condition par défaut et voici comment je fais personnellement :

Switch (
<condition 1>, <valeur 1>,
<condition 2>, <valeur 2>,
<condition 3>, <valeur 3>,
1=1, <valeur par défaut>
)

La dernière condition étant toujours vraie, elle simule une instruction ELSE (T-SQL) ou DEFAULT (C#, VB.NET).

mardi 27 janvier 2009

Report Builder 2.0 - Créer un membre calculé sans passer par SSAS

Une demande souvent récurrente en reporting ad hoc, c'est le besoin de faire des calculs, plus ou moins simples, qui n'ont pas été prévus dans le cube. Dans ce cas, on est souvent obligé de passer par le service informatique qui n'a pas forcément le temps ou les ressources pour traiter la demande rapidement.

Dans Analysis Services, les calculs se font avec des membres caclulés (Calculated member) qui se codent en MDX et se déploient directement dans le cube. Il est possible d'écrire un membre calculé pour une requête, valable le temps de la session. Report Builder 2.0 s'appuie sur cette possibilité pour proposer à l'utilisateur d'écrire ses propres membres calculés dans le concepteur de requêtes.

Attention, la requête s'écrit en MDX, ce qui limite l'usage pour des utilisateurs non développeurs mais soulignons toutefois la présence de cette fonctionnalité facile d'accès.

Il y a une utilisation efficace de cette fonctionnalité. Dans beaucoup de requêtes multidimensionnelles, on a besoin de ramener des propriétés d'une dimension dans une optique d'affichage, mais sans croiser les dimensions et complexifier la requête dans le cube OLAP. Par défaut, on glisse la propriété dans le designer de requête. Ceci a pour effet de générer un croisement de dimension en MDX ( {PropA * PropB} ). Quand on veut remonter une propriété pour affichage, on préfère la mettre dans les mesures, ce qui est possible avec un membre calculé.

On crée donc un membre calculé et on le fait pointer sur la propriété courrante :

[Product].[Color].CurrentMember.Name

On utilisera .Name ou .Value en fonction de ce que l'on veut remonter.

 

jeudi 8 janvier 2009

Report Builder 2.0 - Le reporting ad hoc pour l'utilisateur final

La nouvelle version de Report Builder qui accompagne SQL Server 2008 est l'outil parfait pour des utilisateurs non-informaticiens (Information Workers). Sans avoir besoin de Visual Studio, on peut créer des rapport riches, complexes et puissants en quelques minutes à partir d'un modèle de données existant (Report Model ou Cube OLAP) et les mettre à disposition de tous sur le serveur de rapports.

Voici un webcast qui présente cet outil puissant et intuitif dans la création d'un rapport vu et réalisé par l'utilisateur final (ie. moi dans un rôle de composition ;-)).

Report builder 2.0 - Présentation
Report builder 2.0 - Présentation

SSIS - Processing d'un cube dynamique

Dans un processus d'ETL qui alimente un Datawarehouse, on finit quasiment à chaque fois par un process du cube afin qu'il intègre les nouvelles données pour les utilisateurs.

La tâche Analysis Services Processing permet de faire cette action simplement ; en effet, juste un petit écran de configuration et tout fonctionne.

Cette tâche se connecte en fait à la base SSAS et envoie un batch de commande XMLA, le langage de "gestion" de Analysis Services.

Le problème est que l'assistant de configuration se base sur la base OLAP par défaut (Initial Catalog) de votre chaîne de connexion pour se configurer ; ce qui pose problème quand elle est différente entre la plate-forme de développement et celle de production, ou encore si on veut faire un package un peu générique.

Il faudra donc passer par une variable et une expression pour dynamiser cette étape :

  1. Configurer le composant pour faire le process de votre base sur votre plate-forme de développement 
  2. Ajouter une variable (avec le Package pour scope) de type String
  3. Aller dans les propriétés du composant et copier la valeur de ProcessingCommands
    <Batch><Process>
      <Object><DatabaseID>BIWise</DatabaseID></Object>
      <Type>ProcessFull</Type>
    </Process></Batch>
  4. Aller dans la propriété Expressions et en ajouter une. Elle doit ressembler à ceci (attention à l'échappement des ")
    <DatabaseID>" + @[User::OlapDatabaseID] +  "</DatabaseID>

N'oubliez pas de configurer la variable en post-déploiement ou en pré-exécution.

jeudi 25 décembre 2008

Sécurité de Report Builder 2.0 - Permissions minimums

Report Builder 2.0 est un outil fantastique pour l'utilisateur final (j'y reviendrai très prochainement avec un Webcast). Il nécessite néanmoins un peu de préparation de la part de l'administrateur Reporting Services afin de donner les bons droits aux utilisateurs sans ouvrir les vannes.

Je décris ici les permissions minimums à accorder aux utilisateurs de Report Builder et quelques bonnes pratiques afin d'éviter le syndrome "Je mets tout le monde Administrateur".

Quelques mots sur la sécurité

Sans faire tout un article sur la sécurité de Reporting Services, il faut savoir qu'elle est basée sur des rôles donnant accès aux différentes tâches élémentaires de Reporting (afficher un rapport, créer un répertoire, etc.). Pour autoriser des utilisateurs, on leur assigne un ou plusieurs rôles.

Deuxième point important à intégrer, la sécurité est héritée ; c'est à dire que l'affectation à un rôle au niveau racine se propage à tous les niveaux enfants. On peut bien évidemment casser cette sécurité pour une branche donnée de l'arborescence. D'ailleurs, une première bonne pratique est de sécuriser plus fortement le niveau racine et d'affecter des droits de plus en plus fins en descendant dans l'arborescence.

My Reports

Parmi les fonctionnalités de Reporting Services, il existe la notion de "My Reports" qui permet à chaque utilisateur d'avoir un répertoire privé dans lequel il peut créer et modifier des rapports, visibles que de lui (et de l'administrateur). Cette fonctionnalité, qui s'active depuis Management Studio (clic droit->Propriétés), semble idéale pour nos utilisateurs finaux. Le souci est qu'il faut donner des droits sur le répertoire racine pour atteindre ce répertoire spécial, rendant visible du coup les autres répertoires et éléments du niveau racine et des niveaux en héritant (cf. la bonne pratique évoquée ci-dessus). 

Le seul compromis possible est de créer un rôle spécifique pour le dossier racine permettant de ne voir que les dossiers et de casser l'héritage de sécurité pour tous les sous-niveaux.

Que l'on utilise ou pas la fonctionnalité "My Reports" (moi je préfère créer un répertoire de travail fixe pour tous les utilisateurs d'un même service/groupe), il va falloir associer nos utilisateurs aux bons rôles. A notre disposition, nous avons les rôles suivants :

  • My Reports : donne tous les droits nécessaire à la création/modification de rapports (attention, à ne mettre QUE sur le répertoire My Reports)
  • Report Builder : permet de voir et d'ouvrir des rapports existants (+ les abonnements mais ce n'est pas le sujet)

Pour moi, le premier est trop permissif et le second ne permet pas la modification. Rappelons que le but pour l'utilisateur final est de pouvoir créer ses propres rapports ou bien modifier un rapport existant (j'ai souvent des demandes de mises en page pour lesquelles je n'ai pas de valeur ajoutée en tant que consultant). En plus, il va falloir gérer des points importants comme les droits sur les modèles et sources de données ainsi que la prévisualisation...

Ma solution

Je vais vous livrer ma solution à la problématique de droits minimums pour l'utilisateur final.

  1. Je mets l'utilisateur dans le rôle "Report Builder" au niveau racine afin qu'il puisse naviguer et ouvrir les rapports existants. Je prends soin de casser l'héritage de sécurité pour des répertoires sensibles (souvent les RH).
  2. Je crée un répertoire de validation afin que les utilisateurs puissent enregistrer leur travail (création ou modification). Je mets l'utilisateur dans le rôle "Publisher" afin qu'il puisse sauvegarder.
    J'utilise cette notion de répertoire de validation afin d'éviter les erreurs de manipulation et qu'un utilisateur ne corrompe un rapport existant.
  3. Je donne les droits en lecture sur les sources de données. Le rôle "Report builder" permet d'accèder aux modèles de données mais pas aux sources de données, ce qui est gênant dans un cadre multidimensionnel. J'ajoute donc la tâche "View data sources" dans le rôle "Report Builder" (voire je crée un nouveau rôle juste pour cela).
    J'affecte ce rôle au répertoire contenant les sources de données pour une création de rôle dédié ou bien à toute l'arborescence, ce qui a déjà été fait dans l'étape 1.

  4. Il reste un droit à donner à l'utilisateur : ExecuteReportDefinition pour permettre la prévisualisation directement dans Report Builder. Ce droit fait partie du rôle système (au niveau serveur) System User. Comme je ne veux pas affecter ce rôle à mes utilisateurs finaux, je crée un rôle système spécifique, ne contenant que ce droit.

Voila, notre utilisateur final peut installer Report Builder 2.0 (et le Framework 3.5 SP1) et vous n'avez qu'à lui configurer le Report Server par défaut et préparer les modèles et sources de données partagées.

Il ne reste plus qu'à désigner un utilisateur référant pour déplacer les rapports du répertoire de validation vers leur emplacement définitif. Le rôle Publisher est adéquat pour ce travail même si je préfère en créer un plus limité (qui n'aura pas d'accès aux modèles et sources de données).

lundi 17 novembre 2008

Erreur Migration Reporting Services 2005 vers 2008

Une petite mésaventure qui est arrivé à un de mes collègues lors de la migration de SQL Server 2005 à SQL Server 2008. Tout a bien fonctionné, la migration se passe sans encombre, tous les services repartent, les applications fonctionnent très bien sauf 1 rapport qui affiche cette erreur :

Index was out of range. Must be non negative and less than the size of the collection. Parameter name index

Nous avons donc réouvert le rapport sous Visual Studio pour voir ce qui pouvait clochait et pas de problème apparent, le rapport n'affichant même pas d'erreur dans la prévisualisation (qui n'est tout à fait pas le même moteur de rendu).

Après quelques périgrinations sur le web, je trouve un problème similaire avec des largeurs de colonnes dans la CTP2 (https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=338777&wa=wsignin1.0). Le problème était (celui-ci étant corrigé dans la RTM) que dans certains cas, l'éditeur mettait des -1 comme valeur d'attribut, ce qui n'est pas interprété par le renderer qui lève une exception.

Nous avons donc cherché s'il n'y avait pas de -1 dans le fichier RDL résultant de la migration et nous en avons trouvé. La migration ajoute des valeurs par défaut mais le moteur de rendu HTML de Reporting Services 2008 ne sait pas les traiter. Il faut donc aller supprimer ces -1 à la main (clic droit->View Code dans l'explorateur de solution).

<ChartArea Name="Default">
 <ChartCategoryAxes>
  <ChartAxis Name="Primary">
   <ChartMinorGridLines>
    <Enabled>False</Enabled>
    <Interval>NaN</Interval>
    <IntervalOffset>-1</IntervalOffset>
   </ChartMinorGridLines>
  </ChartAxis>
 </ChartCategoryAxes>
</ChartArea Name="Default">

Ici, le problème se situait dans les lignes intermédiaires d'un graphique, pourtant désactivées.

 

PS : si vous aussi avez rencontré ce problème, merci de le signaler sur Connect pour une correction plus rapide de la part de Microsoft. Ca se passe ici : https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=382355

 

lundi 10 novembre 2008

Report Builder in-the-Cloud

Lors d'une démo à la PDC, j'ai vu la possibilité de faire des rapports Reporting Services avec des données récupérées depuis SQL Data Services (ie. "dans le cloud").

Cette fonctionnalité est possible grâce aux fonctionnalités d'extensibilité qui existent dans Reporting Services depuis la version 2005. En effet, il est possible grâce aux Custom Data Extensions (CDE) d'ajouter sa propre méthode de récupération de données (un System.Data.DataSet, un composant métier, un webservice, etc.).

Une partie des équipes de SQL Services, affectée sur des labs autour de la nouvelle technologie, nous propose donc une implémentation permettant de requêter des données dans le Cloud. En ajoutant simplement quelques Assemblies dans les répertoires de Report Builder, Visual Studio et Report Server, on dispose donc d'un nouveau type de connexion pour les Data Sources : SQL Server Data Services.

Pour obtenir les DLLs et le quide d'installation : http://sqlserviceslabs.net/Reporting.html

Une fois installé, il suffit de se connecter sur son Authority, de choisir son Container et d'écrire sa requête à la Linq Style :

from e in entities where e.Kind == "Contacts" select e

Note : attention, une erreur de jeunesse certainement, il ne faut pas de retour à la ligne dans la requête

 

Merci à Stella Chan, Lead Program Manager sur Reporting Services chez Microsoft, pour ses réponses. D'ailleurs, je reviendrai bientôt avec un post sur sa session à la PDC (http://channel9.msdn.com/pdc2008/BB26/)

- page 1 de 3