Le Blog de C-quad

Archive pour juillet 2011

SpagoBI – Premier tableaux de bords

Nous avons vu précédemment comment installer et configurer l’accès à une base de données, nous allons donc pouvoir nous attaquer à la création de nos premières restitutions : les tableaux de bords.

Pré-requis

Pour pouvoir créer des tableaux de bords ou tout autre objet sur SpagoBI, il est préférable d’utiliser le « Studio ».  Vous pouvez télécharger le Studio pour Linux ou le Studio pour Windows. Le studio va vous permettre de créer entre autre les fichiers « template ». Il est tout à fait possible de les écrire aussi à la main, mais il est tout de même plus aisé de le faire avec le studio.

Lancer « SpagoBI – Eclipse Plateform ». Nous allons créer un nouveau projet : Fichier -> Nouveau -> Autre -> SpagoBI -> SpagoBI Project.

Nous déclarons maintenant notre serveur. Déplions l’item « Resources » afin de faire apparaitre l’item « Server » sur lequel nous allons faire un clic droit afin de pouvoir créer un nouveau serveur.

Nous voila prêt à créer notre premier tableau de bord.

Création d’un tableau de bord

On va commencer par créer un nouveau « DataSet » sur lequel le tableau de bord se basera.

Je crée le dataset « Ventes_par_pays » via l’interface web qui contient la requête suivante :

select s.store_country
sum(f.store_sales) as store_sales, sum(f.store_cost) as store_cost, sum(f.unit_sales) as unit_sales
from sales_fact_1998 f
inner join store s on (s.store_id = f.store_id)
inner join sales_region r on (s.region_id = r.region_id)
group by   s.store_country ;

Nous pouvons maintenant aller dans le Studio et faire un clic droit sur « Sbi_Analysis ». Nous choisissons « DashBoard » :

Sélectionner le document créé et faire un clic droit « Deploy »

On défini ensuite le nom du document ainsi que le dataset sur lequel il est basé.

Direction l’interface graphique :

 Comme vous pouvez le constater, nous n’avons que très peu la main sur l’organisation du tableau. Cette fonctionnalité permet pour moi surtout d’afficher le résultat d’une requête « proprement ». Mais je suis peut être passé à coté de quelque chose au niveau du paramétrage.

Je vous fais un rapide aperçu des autres possibilités de restitutions avec la requête utilisée :

Les colonnes en trois dimensions :

La requête utilisée :

select  concat(fname,' ',lname) Customer_Name,    
        round(rand()*100,2) Effort_Index,
    round(rand()*100,2) Competitiveness,
    round(rand()*100,2) Cost_Optimization,
    round(rand()*100,2) Health
from customer
where customer_id < 5;

Et son rendu graphique en utilisant columns3d.lzx.swf:

L’aiguille de compteur :

La requête utilisée :

select concat(fname,' ',lname) as serie, round(rand()*100,2) as value
from customer
where customer_id =1;

Le rendu en utilisant rot.lzx.swf :

Sur le modèle du tableau de bord ci dessus, il est possible de faire le même type de restitutions avec plusieurs aiguilles.

Le compteur multi-aiguilles :

Malheureusement le compteur avec plusieurs aiguilles n’est pas disponible dans la liste de choix dans le Studio. Il vous faudra donc créer le template vous même. Voici le template que j’ai utilisé :

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<DASHBOARD movie='/dashboards/multirotation.lzx.swf' >
 <DIMENSION width='400' height='400' />
 <CONF>
  <PARAMETER name='minValue' value='0' />
  <PARAMETER name='maxValue' value='100' />
  <PARAMETER name='lowValue' value='33' />
  <PARAMETER name='highValue' value='66' />
  <PARAMETER name='numNeedles' value='4' />
  <PARAMETER name='xSerieAttributeName ' value='serie' />
  <PARAMETER name='value1' value='profits' />
  <PARAMETER name='value2' value='ventes' />
  <PARAMETER name='value3' value='SAV' />
  <PARAMETER name='value4' value='salaires' />
  <PARAMETER name='colorNeedle1' value='red' />
  <PARAMETER name='colorNeedle2' value='blue' />
  <PARAMETER name='colorNeedle3' value='aqua' />
  <PARAMETER name='colorNeedle3' value='yellow' />

  <PARAMETER name='multichart' value='false'/>
  <PARAMETER name='numCharts' value='1' />
    <PARAMETER name='orientation_multichart' value='vertical'/>
    <PARAMETER name='colorTitleSerie' value='purple' />
    <PARAMETER name='sizeTitleSerie' value='12' />
    <PARAMETER name='displayTitleBar' value='true' />
    <PARAMETER name='title' value='Exemple de graphique a 4 aiguilles' />
    <PARAMETER name='colorTitle' value='purple' />
    <PARAMETER name='sizeTitle' value='14' />
    <PARAMETER name='legend' value='true'/> 
 </CONF>

   <DRILL document="chtHistorySalesDirec">
    <!-- possible types for param: RELATIVE, ABSOUTE-->
      <PARAM name="param1" type="RELATIVE"  value="$F{serie}"/> <!-- parameter from db-->
      <PARAM name="paramStat" type="ABSOLUTE"  value="parametro statico"/> <!-- static parameter -->
  </DRILL>

  <DATA url='/servlet/AdapterHTTP?ACTION_NAME=GET_DATASET_RESULT'></DATA>

</DASHBOARD>

La requête utilisée :

SELECT 'Titre du graphique' as serie, 
       'profits' as value1, 'ventes' as value2, 'SAV' as value3, 'salaires' as value4, 
        20 as profits, 80 as ventes, 12 as SAV, 7 as salaires 
from dual;

Comme vous pouvez le constater la requête est structurante pour le rendu. Le texte retourné pour la colonne « serie » sert de Titre au graphique, et les libellés stockés dans value[1-4] servent dans la légende.

Voici le rendu en utilisant multirotation.lzx.swf :

Je pense qu’il est possible de créer des modèles de graphiques différent les fichiers « lzx.swf » sont en effet le fruit d’un logiciel libre : OpenLaszlo. Je n’ai par contre pas trouvé de tutoriel sur la possible création de nouveaux modèles pour SpagoBI.

 

SpagoBI 3.0 – Accéder à une base de données


Nous avons vu précédemment comment installer SpagoBI, nous allons maintenant le configurer afin qu’il puisse accéder à une base de données.

C’est mieux si un portail de restitution peux lire vos données 😉

Plutôt que de prendre le contenu d’une base de données qui ne vous est pas accessible, nous allons utiliser la base de données foodmart.

Créer la base de données Foodmart pour nos tests

Nous allons télécharger les données foodmart pour mysql.

Après décompressions de l’archive nous pouvons lancer l’import dans mysql :

$ mysql -u root -p < 'foodmart 20090211 1624.sql'

Nous donnons ensuite les droits à SpagoBI sur cette nouvelle database :

mysql> GRANT ALL PRIVILEGES ON `foodmart`.* TO 'SpagoBI'@'%';

Une petite vérification nous permet de constater que tout est ok :

$ mysql -u SpagoBI -p foodmart

Ajout de la connexion dans tomcat

Tel que nous l’avons fait pour la configuration de la base de données propre à SpagoBI, nous allons ajouter un connexion Jndi pour foodmart.
Pour cela nous éditons le fichier /etc/tomcat6/server.xml et ajoutons dans la section GlobalNamingResources :

<Resource name="jdbc/foodmart" auth="Container" type="javax.sql.DataSource"
          maxActive="100" maxIdle="30" maxWait="10000"
          username="SpagoBI" password="SpagoBI" driverClassName="com.mysql.jdbc.Driver"
          url="jdbc:mysql://localhost:3306/foodmart"/>

A noter : l’édition du fichier server.xml permet à ce que toutes les webapps de tomcat aient accès à cette connexion. Si vous voulez limiter la définition de la connexion à une seule application, il faut ajouter les lignes ci dessus au fichier context.xml du répertoire META-INF de votre application.

Création d’un DataSource dans SpagoBI

Se connecter avec le compte biadmin et aller dans Ressources -> Source de données :

Et renseigner les paramètres conformément à ce qui a été saisi dans le fichier server.xml :

On tombe alors sur une erreur :

it.eng.spago.error.EMFInternalError: severity [ERROR] description [DefaultScrollableDataResult::getDataRow: java.sql.SQLException: Column 'DS_ID' not found

Pour corriger cet erreur, il faut modifier le fichier statements.xml situé dans webapps/SpagoBI/WEB-INF/conf/config/ en y supprimant les alias.

exemple :

Select DS_ID as ID

doit être modifié en

Select DS_ID

Je n’ai par contre pas trouvé pourquoi cela ne fonctionne pas avec la première écriture.  A ma connaissance les alias de colonnes existent depuis la norme SQL92. A votre avis le problème vient du driver odbc mysql ? ou de l’utilisation qui en est faite par SpagoBI ? Je ne sais pas ou déposer mon rapport de bug …

edit : Après recherche approfondie, il n’est plus nécessaire de modifier le fichier statements.xml. Ce problème sur les alias est tracé dans le bug Mysql 35150 et 31499. Afin de corriger le problème il faut modifier la connexion mysql.

Pour cela éditer le fichier /etc/tomcat6/server.xml et ajouter « ?useOldAliasMetadataBehavior=true » :

<Resource name="jdbc/SpagoBI" auth="Container"
 type="javax.sql.DataSource"
 maxActive="100" maxIdle="30" maxWait="10000"
 username="SpagoBI" password="SpagoBI" drvierClassName="com.mysql.jdbc.Driver"
 url="jdbc:mysql://localhost:3306/SpagoBI?useOldAliasMetadataBehavior=true" />

Dans Ressources -> DataSet -> onglet Type on sélectionne Query. La Base de données que l’on vient de référencer « Foodmart » et on saisi une requête pour vérifier que la connexion est bien fonctionnelle :

Dans mon exemple j’ai pris :

select * from product;

Cliquer sur « Preview » et l’on constate que tout fonctionne 🙂