Archive

Archive for September, 2011

Nouveautés Microsoft Power Pivot- Hiérarchie

September 28th, 2011 No comments

Sur ce post, on va s’intéresser à la “hiérarchie” qui est prise en charge dans la nouvelle version de PowerPivot (nom de code: Denali).

La hiérarchie est créée via le diagramme view,  en sélectionnant la table sur laquelle on souhaite créer une hiérarchie.

La hiérarchie est créée en glissant les colonnes souhaitées.

Une fois la hiérarchie créée, on peut l’utiliser dans un tableau croisé Excel, ou la glisser dans les Slicers. Excel créera alors un slicer pour chaque niveau.

On pourra alors filtrer sur les composants en ajoutant la hiérarchie créée en filtre

Retour vers la synthèse des nouveautés de Powerpivot V2.

Nouveautés Power Pivot-Fonctions de relations parent-enfant

September 23rd, 2011 No comments

Dans ce post on se penchera sur certaines fonctions de gestion des relations.

Dans la première version de Power Pivot on ne pouvait pas créer de relations entre deux colonnes de la même table. Aujourd’hui, la nouvelle version permet la gestion des relations parent_enfant via les fonctions DAX.

Pour mieux comprendre, on utilisera un exemple «  fichier employé_responsable » renseignant pour chaque employé les responsables hiérarchiques, s’ils existent.

La première fonction qu’on testera est la fonction Path.

PATH([employe],[responsable]) :Retourne une chaine de caractères délimitée par la clé_employé de tous les nœuds parents de la clé employé courante, commençant du plus ancien au plus courant.

Par exemple si on s’intéresse à l’employée « Melanie_Furion » on peut voir qu’elle a pour responsable  « NADIA_FARSSI », et que « Nadia farssi » a pour responsable « Jhon Dior » ce qui correspond bien au path 111|8|18.

La seconde fonction à laquelle on s’intéresse est PATHLENGTH.

PATHLENGTH (PATH([employe],[responsable])) : Retourne la longueur du path, dans le cas Melani_furion la fonction retourne 3, donc 3 niveaux hiérarchiques.

Intéressons-nous maintenant à la fonction PATHITEM.

PATHITEM(PATH([employe],[responsable]),2)) :Retourne le deuxième niveau de la hiérarchie commençant par le niveau le plus haut. Par exemple pour l’employé Melanie_furion le responsable de niveau 2, correspond à l’id 8 (Nadia Farssi)

Il serait également intéressant de combiner les fonctions LOOKPVALUE, PATH, PATHITEM. Ceci nous permettrait de créer une sorte de hiérarchie associée à cette relation parent_enfant, de chaque niveau.

LOOKPVALUE ([nom-complet],[employe],PATHITEM(PATH([employe],[responsable]),2) :

Retourne le nom de l’employé associé au deuxième niveau de la hiérarchie

On peut ainsi dresser la hiérarchie de l’entreprise.

On peut rapidement voir les différents niveaux hiérarchiques, en déduire des responsables.

PowerPivot code Denali nous offre une fonction de relation parent-enfant intéressante, mais restant limitée comparée à la solution Microsoft SSAS. Notamment, on remarquera l’existence de niveaux « blancs ». Par exemple Jhon Dior étant le plus haut responsable, les niveaux 2 et 3 ne sont pas renseignés.

Là où SSAS nous permet d’utiliser la propriété HideMemberIf permettant de résoudre ce point, Powerpivot ne propose pas d’équivalent simple. Il existe malgré tout des solutions de contournement qui ont été publiées, parmi elles on peut citer la solution d’Alberto Ferrari.

Retour vers la synthèse des nouveautés de Powerpivot V2.

D’autres articles à venir sur les autres nouveautés…

Formation en soirée « SSAS avancé », le 19 octobre à Paris

September 20th, 2011 No comments


Venez rencontrer les équipes Homsys à l’occasion de cette Formation !
Au programme : échanges avec nos experts, ateliers et bonnes pratiques.

Homsys vous propose de participer à ses Formations en Soirée avec une session à Paris « SQL Server Analysis Services (SSAS) avancé », le mercredi 19 octobre à partir de 18h30.

Cette formation vous donnera un aperçu des fonctionnalités avancées d’un cube Analysis Services. Vous y apprendrez notamment :

  • Les relations évoluées (référencé, many to many)
  • Le paramétrage des agrégations et des partitions
  • La création d’indicateurs MDX
  • La création d’actions (notamment drillthrough)
  • La gestion des traductions
  • L’implémentation des perspectives
  • A savoir déployer et sécuriser les données dans Analysis Services

Pré-requis : vous disposez déjà d’une première expérience BI et connaissez les bases de SSAS (création de la Data source View, création d’une dimension, création d’un cube simple).

Rendez-vous dans nos locaux, au 38 rue de Ponthieu – 75008 Paris.

Pour en savoir plus : site Homsys

Pour vous inscrire* :
– Mail : klalandre[at]homsys.com
– En ligne : sur le site Homsys

*Participation gratuite après confirmation par Homsys.

Nouveautés Power Pivot-Tri par colonne

September 15th, 2011 No comments

Une autre nouveauté de PowerPivot est la fonction « sort by Column » (en français « tri par colonne ») .

Dans la première version de Power Pivot le tri des données se faisait par ordre alphabétique, ce qui était adéquat pour les noms, la description des produits mais pas pour des mois ou des jours de semaine. La solution de contournement dans la V1 consistait à associer un nombre devant le jour de semaine, comme vous pouvez le voir :

Grâce à la V2 cette fonction devient possible avec le « tri par colonne ».

Pour obtenir :

Retour vers la synthèse des nouveautés de Powerpivot V2.

D’autres articles à venir sur les autres nouveautés…

Nouveautés Power Pivot-Vue diagramme

September 13th, 2011 No comments

Le décisionnel Microsoft s’oriente vers le BISM (Business Intelligence Semantic Model) en fusionnant le modèle de PowerPivot et celui, plus classique, d’ Analyses Services (UDM). C’est dans ce contexte que l’on trouve une des grandes nouveautés de powerPivot : la vue diagramme ! bien plus pratique pour réaliser nos modèles d’analyse.

Retour vers la synthèse des nouveautés de Powerpivot V2.

D’autres articles à venir sur les autres nouveautés…

Optimisation procédure stockée paramétrée sous SQL Server (optimisation reporting Services)

September 12th, 2011 2 comments

Je vous propose ici un retour d’expérience sur l’optimisation de procédures stockées SQL server. Cette optimisation est issue de recherche effectuée pour analyser des problèmes de lenteur sur des rapports Reporting Services utilisant des procédures stockées avec passage de paramètres.

Le cas m’est apparu en constatant que les temps d’exécution d’une même requête SQL pouvaient varier de 8s à plus de 10mn selon que je l’exécutais directement dans l’éditeur management Studio ou incorporée à une procédure stockée.

Ce problème est appelé « parameter sniffing ou spoofing». En synthèse, le moteur SQL Server essaye d’optimiser la requête en définissant un plan d’exécution basé sur les statistiques. Ces dernières sont établies lors de la première exécution de la requête et sont donc dépendantes de la valeur du paramètre utilisée pour cette exécution.

Ce plan d’exécution pose problème dès que la distribution des données est biaisée : certains paramètres vont retourner un petit nombre de lignes tandis que d’autres vont au contraire en renvoyer un grand nombre.

Le plan d’exécution n’étant pas recalculé à chaque fois, les temps ne sont plus optimisés.

Ci-dessous un exemple (source http://blogs.msdn.com/b/queryoptteam/archive/2006/03/31/565991.aspx) afin d’expliquer avec plus de détails les points précédents :

Soit les objets suivants :

Une table t de 1002000 lignes avec 100000 lignes allant de 1 à 999999 et 2000 autres lignes avec la valeur 1000008.

Col1

1

1

2

2

1000000

999999

1000001

1000008

1000008

1002000

1000008

Soit la procédure stockée ci-joint interrogeant la table t.

CREATE procedure [dbo].[foo]

(@p int)

as

select * from t where col1 = @p

Une première analyse du plan d’exécution de la procédure stockée avec le paramètre ‘450’ nous donne les informations suivantes :

image

Il est estimé que le nombre de ligne est de 1 et le nombre de ligne renvoyé est aussi de 1.

Dans la deuxième exécution nous allons mettre le paramètre suivant : ‘1000008’ (qui représente 2000 lignes dans la base de données) et nous allons observer le plan d’exécution :

image

Le plan d’exécution estime que le nombre de lignes renvoyées est de 1 alors que le nombre de lignes retournées réellement est de 2000.

Ce plan d’exécution n’est pas optimisé pour le paramètre d’une valeur de ‘10000008’.

En effet le précédent paramètre fourni, d’une valeur de ‘450’, a été aspiré par l’optimiseur SQL et gardé en mémoire ; d’où la mauvaise estimation.

Pour remédier à ce problème et ainsi obtenir de meilleures performances, plusieurs solutions existent : la déclaration d’un paramètre en local (ainsi le paramètre ne peut être aspiré par l’optimiseur de requête SQL) ou l’ajout d’une option ‘‘recompile’’.

Exemple :

Déclaration d’une variable locale

alter procedure [dbo].[foo]

(@p int)

as

declare @p_local int

set @p_local=@p

select * from t where col1 = @p_local

@p=5 image

@p=1000008

image

Dans le premier cas en déclarant une variable locale, le nombre estimé passe à 1,11332 lignes, qui correspondent au calcul suivant : [1/1000001 (nombre de valeur distinct)]*1002000(nombre total de lignes).

Cette première solution utilise les statistiques de la table pour estimer le nombre de lignes. Elle présente cependant un inconvénient quand les données ne sont pas réparties équitablement.

Option (recompile)

alter procedure [dbo].[foo] (@p int)

as

select * from t where col1 = @p

option(recompile)

@p=5 image

@p=1000008

image

Dans le deuxième cas, en utilisant l’option recompile, nous forçons le moteur à recompiler la requête SQL, ce qui peut être contraignant en terme de temps processeur si celle-ci est complexe, mais le plan d’exécution obtenu est toujours optimisé pour le paramètre aspiré.

Dans notre cas l’estimation du nombre de lignes correspond au résultat attendu.

Bilan en termes de performances :

Défaut Variable locale Option recompile
@p=5 91 ms 89 ms 87 ms
@p=1000008 288 ms 255 ms 166 ms

Ci-dessus les temps écoulés lors de l’exécution de la procédure stockée. Dans le cas par défaut le paramètre @p=5 a été aspiré, ce qui induit un temps d’exécution de 288 ms pour le paramètre @p=1000008.

En comparaison, le fait de déclarer une variable locale ou de mettre en place une option recompile ne change pas les temps de traitement pour le paramètre @p=5, les nombres de lignes estimés étant proches, les temps sont quasi-identiques. Cependant, une différence est notable avec le paramètre @p=1000008 : avec l’option recompile, la procédure stockée possède un plan d’exécution optimisé, d’où une meilleur performance. Dans le cas de la déclaration en locale d’une variable avec @p=1000008, le plan étant optimisé à l’aide des statistiques (estimation de 1,1 lignes par résultat) le temps d’exécution reste important face à l’option recompile mais préférable à celui par défaut.

En conclusion, en fonction des cas de figure rencontrés (données biaisées, procédure stockée complexe …), il est préférable d’utiliser l’une des solutions proposées.

Ecrire une requête MDX personnalisée sous Excel 2007

September 12th, 2011 No comments

Il peut être parfois utile d’exécuter une requête MDX directement depuis Excel. Ainsi, on ne s’encombre pas de toutes les dimensions / mesures du cube, et ainsi, on exécute la requête beaucoup plus rapidement.
On peut ainsi utiliser directement un tableau croisé dynamique que l’on mettre à jour régulièrement, afin d’avoir une information toujours à jour.

La méthode est simple : utiliser un fichier datasource (.odc) personnalisé avec sa requête MDX

Pour cela :

1. Téléchargez ce fichier, et enregistrez-le dans le dossier ‘Mes Data Sources’ du dossier Mes documents.

2. Ouvrez-le avec un éditeur de texte classique, et modifiez les lignes suivantes :

  • ligne 8 <title>Detail_Forecast all regions</title> : remplacez par un titre approprié
  • ligne 12 <o:Name>Detail_Forecast all regions</o:Name> : remplacez par un titre approprié. Attention, c’est le nom qui apparaîtra sous Excel dans la liste des connexions.
  • ligne 18 <odc:ConnectionString>Provider=MSOLAP.3;Integrated Security=SSPI;Persist Security Info=True;Initial Catalog=<Nom_de_la_base_OLAP>;Data Source=<Serveur_OLAP>;MDX Compatibility=1;Safety Options=2;MDX Missing Member Mode=Error</odc:ConnectionString> : remplacez par la datasource voulue (que l’on trouve généralement dans un ficher de cube Excel), ou remplacez celle-ci par les valeurs appropriées.
  • ligne 20 <odc:CommandText>Insérer la requête MDX ici</odc:CommandText> : C’est ici que vous allez insérer la requête MDX. Attention de ne pas faire apparaître de paramètres (@Parameter, CONSTRAINED) car Excel ne les supporte pas.

3. Enregistrez le fichier.

4. Ouvrez Excel, et aller sur l’onglet ‘Données’.

5. Cliquez sur ‘Connexions existantes’ et sélectionnez votre fichier datasource précédemment enregistré

6. Une fois ouvert, le résultat de la requête s’affichera sous forme de table. Si vous souhaitez plutôt l’utiliser sous forme de Pivot, à l’étape 4, allez à l’onglet ‘Insertion’, puis sélectionnez ‘TblCroiséDynamique’, et choisissez le fichier datasource.

Categories: Trucs & astuces Tags: , , ,

IBM Techsoftware 2011

September 2nd, 2011 No comments

Hier et aujourd’hui se tient l’IBM TechSoftware à Bois Colombes. L’événement est l’occasion de 2 plénières et plus de 120 sessions techniques et workshops.

Je vous propose dans cet article un retour sur la plénière d’hier qui donne un avant goût des sujets phares pour les prochaines années.

3 thèmes se dégagent:

1) Le BAO:

Très intéressant pour nous puisqu’il s’agit tout simplement de notre quotidien. BAO pour Business Analytics and optimisation. Il ne s’agit pas moins de ce que l’on appelle chez Homsys la BI ++: aller plus loin que le reporting rétroviseur, maintenant, bien ancré dans les entreprises.

2 axes forts dans cette mouvance: l’analyse prédictive et la gestion des gros volumes de données, ce que l’on appelle le Big Data (plusieurs peta octets de données).

Watson, l’ordinateur d’IBM qui a battu les meilleurs humains au jeu Jeopardy en début d’année est l’illustration de cette capacité à dégager une décision à partir de gros volumes d’information.

Hasard du calendrier, IBM annonçait hier le rachat de l’éditeur I2 spécialisé dans l’intelligence économique et l’analyse.

2) Le cloud:

2ème thèmatique de cette plénière, le cloud est un sujet de plus en plus présent mais qui nécessite une transformation dans notre manière de penser, de fonctionner.

Basculer vers le cloud équivaut à Virtualiser, Automatiser et Standardiser.

Tout n’est pas “cloudifiable” mais lorsque cela est possible les gains sont significatifs.

IBM propose notamment un cloud public qui permet d’obtenir en quelques clics une machine virtuelle prête à l’emploi.

3) La mobilité:

3ème et dernière thématique de cette plénière, la mobilité est poussée en entreprise en raison des pratiques privées. Les tablettes et smartphones font partie de notre quotidien mais en dehors de la sphère professionnelle (hormis pour les e mails). Les dirigeants d’entreprise veulent maintenant pouvoir utiliser ces outils qu’ils trouvent si pratiques à la maison dans l’entreprise. Cela me rappelle juste la réunion de ce lundi matin où notre DG préféré est venu en réunion avec sa tablette,…, pour un résultat mitigé (comme quoi il reste encore un peu de travail pour que la mobilité soit parfaite)…

La mobilité arrive avec un certains nombre de défis à surmonter:

– Les OS sont beaucoup plus hétérogènes, 5 majeurs dans le monde mobile pour 1 dans le monde PC (Windows avoisine les 99% de part de marché).

– Les failles de sécurité (à priori plus nombreuses sur les mobiles) auxquelles s’ajoute le risque de perte / vol, sont un problème pour la confidentialité des données

– Le mobile pour être attractif doit tirer partie du contexte: géolocalisation, contexte social (réseaux sociaux)…

Read more…