Business Geek

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.

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).

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

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/)

Une petite fonctionnalité attendue depuis la version 2005 fait son apparition dans la version 2008 de Reporting Services ; c’est la gestion fine du contenu des textboxes.

En 2005, une textbox contient un texte statique OU une expression. Il fallait donc mettre des textboxes côte à côte pour faire un bloc de texte un peu plus évolué.

Maintenant, il est possible de créer des PlaceHolder dans une textbox et donc mélanger des expressions avec du texte statique.

De plus, les textboxes supportent maintenant du contenu enrichi. On peut donc formater le texte très finement voire utiliser des balises HTML pour un formatage plus dynamique. Chaque bloc de texte ou placeholder peut être formaté indépendemment.

Biensur, tout ceci disponible sous Visual Studio 2008 ou Report Builder 2.0

Je n’ai pas pour habitude de faire juste du relaying de news mais là, je ne peux m’en empêcher.

Report Builder 2.0 est disponible en release !!

Pour le téléchargement, c’est ici : http://www.microsoft.com/downloads/details.aspx?FamilyID=9f783224-9871-4eea-b1d5-f3140a253db6&displaylang=en

Pour ceux qui ne connaissent pas Report Builder, c’est l’outil de création de rapports pour Reporting Sevices à destination des utilisateurs finaux. La version 1.0 livrée avec SQL Server 2005 était très limitée ce qui dégradait fortement son intérêt. Cette nouvelle version corrige tous ces défauts et offre un vrai outil de reporting ad hoc.

Report Builder est maintenant iso-fonctionnel avec Visual Studio mais en version bien plus light et au design épuré. Personnellement, je m’en sers même à la place de VS. Pour l’utilisateur final, on créera un modèle d’entités tout prêt afin de masquer la complexité d’un schéma de base de données.

Je vous invite à consultez les Webcasts de la Bewise Developer Conference pour voir une démo du Report Builder en action : http://www.bewise.fr/fr-FR/business/DGD/Pages/BDC2008-Webcasts.aspx

Pour consulter le SSIS Package Store, il faut se connecter au service windows de Integration Services. Quand celui-ci est sur un serveur distant, il faut activer des droits pour autoriser cette connexion sinon, vous aurez l’erreur suivante :

Connect to SSIS Service on Machine "abcd" failed: Access is denied 

Pour ce faire, il faut aller dans la configuration des services de composants distribués (DCOM Config). Tout est expliqué sur ce lien :

http://msdn.microsoft.com/en-us/library/aa337083%28SQL.90%29.aspx

Cependant, il manque une étape dans cette manipulation. En plus d’aller configurer le composant MsDtsSrv, il faut aller dans les propriétés de “My Computer”, dans l’onglet “COM Security” et éditez les limites des permissions de lancement et d’activation en ajoutant les comptes autorisés à se connecter.

Encore un nouvel événement Microsoft me direz-vous… Et bien oui et on ne s’en lasse pas, même si c’est événement n’est autre que le Microsoft Technet Tour de la fin d’année.

Au menu, des sessions et des échanges avec les speakers Microsoft sur les dernières technologies Microsoft : SQL Server 2008, Hyper-V, Visual Studio 2008, le Framework .NET 3.5, etc.

Le session sur la Business Intelligence avec SQL Server 2008 et Performance Point sera animée par votre serviteur. Je vous invite aussi à aller voir la session de Patrick Guimonet de Microsoft sur SQL Server 2008 dont le lancement en version finale a eu lieu pendant les vacances.

L’événement est gratuit et a lieu ces lundi et mardi (oui, le post arrive au dernier moment). Il vous reste donc quelques heures pour valider votre inscription et venir découvrir ou poser des questions sur les solutions Microsoft.

Dans un projet décisionnel, il est nécessaire lors de la restitution des données de les formater. Par exemple, si vous présentez des données financières, il va falloir les afficher correctement.

Dans une architecture applicative classique, on laisse ce travail à la couche de présentation. Dans un contexte décisionnel, cette couche de présentation peut être Reporting Services qui gère le formatage mais ce peut-être aussi un client OLAP autonome comme Excel.

Excel sait gérer aussi le formatage mais c’est aux utilisateurs de le configurer via les options de la PivotTable :

Cette manipulation n’est pas à faire par les utilisateurs finaux, surtout pour des formats triviaux comme des devises.

Analysis Services permet d’ajouter des informations de formatage sur chaque cellule pour guider les clients OLAP dans leur réprésentation des données. Ainsi, quand on déclare un membre calculé par exemple, on ajoute la clause FORMAT_STRING décrivant le membre :

CREATE MEMBER CURRENTCUBE.[Measures].[Prix TTC]
AS [Measures].[Prix Vendu]*1.196,
FORMAT_STRING = "Currency"

Cette configuration se fait aussi via l’assistant :

Un client OLAP peut récupérer cette information en précisant une clause dans la requête MDX qu’il fait à Analysis Services.

SELECT {[Measures].[Prix TTC]} ON COLUMNS
FROM [CubSales]
CELL PROPERTIES VALUE, FORMAT_STRING

Excel précise bien cette clause dans ses échanges avec SSAS. Or il se peut qu’il n’arrive pas à représenter un membre calculé de type monétaire malgré l’information provenant du cube.

Une des solutions pour résoudre ce problème serait d’ajouter la clause FORMATTED_VALUE pour forcer le formatage des cellules par Analysis Services avant de renvoyer les données. On peut le vérifier avec une requête MDX dans Management Studio :

SELECT {[Measures].[Prix TTC]} ON COLUMNS
FROM [CubSales]
CELL PROPERTIES VALUE, FORMAT_STRING, FORMATTED_VALUE

Mais Excel ne permet pas de préciser ce paramètre donc il nous faut trouver une solution. Dans le cadre de données monétaires, il est important de connaître le pays pour déterminer la bonne devise. En effet, si mon cube contient des montants en euros, je ne veux pas voir des dollars si le client ou le serveur est en version US. En conséquence, il faut préciser la culture (Locale) en plus du formatage dans les méta-données des cellules. Ainsi, corrigeons notre membre calculé :

CREATE MEMBER CURRENTCUBE.[Measures].[Prix TTC]
AS [Measures].[Prix Vendu]*1.196,
FORMAT_STRING = "Currency", LANGUAGE = 1036 //fr-FR

Et là, plus de problème dans Excel pour afficher vos membres calculés en € de façon automatique. Attention, cette manipulation n’est pas possible avec le formulaire d’édition du membre calculé, il vous faudra passé par le script MDX de création.

Le propos de ce billet n’est pas de fournir un tutoriel complet sur la migration de cubes entre AS 2000 et 2005 mais juste de présenter les méthodes pour le faire. Je ne présenterai que les points d’entrée, je ne rentrerai pas dans le détails des incompatibilités entre les versions.

Méthode 1 : In-place upgrade

C’est la méthode qui paraît la plus simple mais qui pour moi s’avère la plus risquée. En effet, j’ai toujours l’impression de tout miser sur un coup de dé.

J’appelle cette méthode la Next->Next->Next, car c’est tout simplement le setup d’installation de SQL Server 2005 qui va effectuer la migration du cube en 2005 et faire la bascule en redémarrant le service. Le processus de migration est équivalent à la méthode présentée ci-dessous sauf que tout est automatisé. La coupure de service est donc assez réduite (hormis le fait qu’il faille refaire une process du cube).

Avantages : pas de modification des clients, courte coupure de service
Inconvénients : risque d’incompatibilités

PS : utilisez l’Upgrade Advisor de SQL Server 2005 pour avoir un aperçu des problèmes que vous pourriez rencontrer.

Méthode 2 : la migration

Contrairement à ce qu’on pourrait penser, la migration n’est pas une méthode compliquée. On ne doit pas refaire complètement ses cubes. Un assistant livré avec Analysis Services 2005 permet de monter un cube à partir des méta-données d’un cube sous 2000.

Cet assistant s’appelle le MigrationWizard.exe et se trouve dans %programfiles%\Microsoft SQL Server\90\Tools\Binn\VSShell\Common7\IDE.
On y accède aussi dans Management Studio avec un clic-droit sur l’instance Analysis Service 2005 comme présenté dans ce screenshot.

L’assistant est très intuitif et le résultat est très bien (mis à part les incompatibilités connues).
Il ne reste plus qu’à ouvrir notre nouveau cube dans Business Intelligence Development Studio (oui, je sais, Visual Studio) pour apporter si besoin des petits correctifs et le tour est joué.

Avantages : simplicité, on maîtrise le passage entre les versions
Inconvénients : changement de la chaîne de connexion des clients

Conclusion

Le choix de la méthode dépend de nombreux facteurs comme les utilisateurs et l’utilisation qu’ils font du cube, les données sources, la volumétrie, la conception initiale sous 2000, les incompatibilités remontées par l’Upgrade Advisor… Dans tous les cas, une migration se planifie et se prépare : audit, test, test, test, migration !