Blog Homsys

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 « Initiation au MDX dans le cadre d’un cube SSAS », le mercredi 18 janvier à partir de 18h30.

Cette formation a pour objectif de vous donner un aperçu du MDX et de son application dans un cube Analysis Services.

Vous y apprendrez notamment :

• Les notions les plus importantes du MDX
• Les fonctions MDX les plus utiles pour un cube SSAS
• La création d’indicateurs via script MDX dans un cube
• La création de jeu de données (SET) et de membres calculées
• L’utilisation de la « time intelligence »

Pré-requis : avoir déjà des connaissance sur SSAS (idéalement avoir suivi les deux premières FES SSAS).

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.

Dans  SSIS, lorsqu’on utilise un fichier XML issus de Infopath  comme source XML, on obtient l’erreur suivante :

«  Unable to infer the XSD from the XML file. The XML contains multiple namespaces »

Pour pouvoir utiliser ce fichier avec SSIS, il faut :

1. Supprimer les Namespaces
2. Ajouter une balise pour que SSIS interprète les champs du fichier XML comme des colonnes d’une même table et non comme des  tables distinctes.

Ceci peut se faire via un script de transformation XSLT .

Dans le control flow, il faut créer une tache  XML  de type XSLT.

Elle a besoin de 3 connections sur 3 fichiers différents :

Input : C’est une connexion sur le fichier XML source

Output : C’est le résultat de la transformation. Cela peut être une connexion sur un fichier (nécessaire pour générer une première fois le fichier XSD) , ou une variable, qui pourra ensuite être utilisée comme source XML.

Second Operand : C’est le script XSLT qui sera appliqué. Le SecondOperandType peut être une connexion si le script est stocké dans un fichier externe, ou bien DirectInput, si le code du script est saisi directement dans la valeur de SecondOperand.

Voici le contenu du script XSLT pour supprimer les Namespaces et générer la balise <myTable> :

<?xml version="1.0" encoding="utf-8" ?>
<xsl:stylesheet version="1.0"
xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:output method="xml" indent="no" /><xsl:template match="/|comment()|processing-instruction()">
<xsl:copy>
<myTable>
<xsl:apply-templates />
</myTable>
</xsl:copy>
</xsl:template>
<xsl:template match="*">
<xsl:element name="{local-name()}">
<xsl:apply-templates select="@*|node()" />
</xsl:element>
</xsl:template>
</xsl:stylesheet>

Le fichier résultat est maintenant utilisable par SSIS comme source XML.

La gestion des fichiers de configuration en XML de SSIS n’est pas chose aisée. Il n’est pas facile de les modifier à la main, et les re-générer à chaque modification est un processus assez lourd. Quand en plus on doit gérer plusieurs fichiers de configuration en fonction des différents environnements de développement, recette, production… cette tâche devient rapidement compliquée et source d’erreurs.

Je vous mets donc à disposition cet outil éprouvé dans plusieurs projets BI. Cet outil est une interface web développée en asp qui vous permet de gérer simplement les différents fichiers de configurations de SSIS et ce quel que soit le nombre d’environnements différents.

L’outil permet de déclarer des environnements, de créer de nouvelles variables et de modifier les valeurs associées à ces variables pour chacun des environnements. Vous pouvez directement comparer, pour une variable, sa valeur dans chacun des environnements.

Les modifications ne sont prises en compte que lors de leur validation et une sauvegarde des fichiers est effectuée avant leur ré-écriture.

Les variables déjà présentes dans les fichiers de configuration sont automatiquement intégrées. Vous n’avez donc pas à déclarer les variables que vous avez déjà définies.

Vous n’aurez donc plus à toucher les fichiers de configuration XML, l’outil vous offre une interface simple et efficace pour les manipuler.

Archive à télécharger :
SSISConfig.zip

Déclaration des environnements :

Liste des variables et affichage de leur valeur dans chacun des environnements :

Création d’une nouvelle variable :

Modification de la valeur d’une variable :

Validation des modifications :

Sauvegarde automatique des anciennes versions des fichiers :

Quand on doit gérer des relations en Many to Many en SQL, on se retrouve confronté au problème de

« Comment concaténer les valeurs d’une colonne de plusieurs lignes »
ou
« Comment simuler une fonction de concaténation comme agrégation d’un group by »?

Ex :

Une solution efficace et rapide est d’utiliser la clause FOR XML :

SELECT p1.CategoryId,
( SELECT ProductName + ‘,’
FROM Products p2
WHERE p2.CategoryId = p1.CategoryId
ORDER BY ProductName
FOR XML PATH( ») ) AS ProductList,COUNT(1) as ProductCount
FROM Products p1
GROUP BY CategoryId ;

Pour une étude détaillée des différentes solutions utlisables en Transac SQL, voici une excellente étude du problème :
http://www.simple-talk.com/content/article.aspx?article=539

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.

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…

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.

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…

Cet article est le premier d’une liste de billets que je vais consacrer à la nouvelle version de Microsoft PowerPivot CTP3 nom de code « Denali ». Le projet SQL Server Denali sera disponible dans une nouvelle version technique (sortie finale prévue fin 2011 ou début 2012).

Dans ce premier post nous allons essayer de découvrir ensemble ce que nous apporte cette nouvelle version comme lot de nouveautés.

Pour rappel Powerpivot c’est :

Un add-in Excel qui offre aux utilisateurs avancés la possibilité de créer des solutions BI in-Memory, sans intervention de l’IT (ou alors de manière ponctuelle et courte). Cet add-in est utilisé dans Excel 2010. Techniquement, cela s’appuie sur un moteur OLAP monté en mémoire.

2 composants sont disponibles gratuitement :

• PowerPivot for Excel : A installer avec Excel 2010. L’utilisation se fait donc dans Excel 2010
• PowerPivot for SharePoint: A installer avec SharePoint 2010. L’utilisation se fait donc dans Excel 2010 depuis SharePoint 2010

Pour plus d’informations : www.powerpivot.com

Liste de nouveautés :

• Diagramme View: qui semble particulièrement agréable et pratique, permettant de visualiser en un clin d’œil les relations entre les tables.
• Création de KPI: permet de définir des objectifs et des tendances aux mesures
• Mode avancé: Les fonctionnalités avancées sont maintenant disponibles dans un onglet séparé. Elles permettent de créer ou de modifier des perspectives, de synthétiser une colonne numérique par une fonction d’agrégation.
• Trie par colonne: possibilité de trier une colonne par une autre colonne ;  Par exemple, on peut  trier les libellés de mois par  colonne « numéro du mois » qui attribue à chaque mois son numéro.
• Table de temps: permet de définir une dimension comme « temporelle » et de bénéficier ainsi de fonctions dédiées.
• Perspectives: permet de définir des « vues » différentes sur le modèle. c’est équivalent aux perspectives d’Analysis Services
• Hiérarchies: permet d’organiser les membres d’une dimension de manière hiérarchique. Les hiérarchies parents / enfants sont notamment gérées.

Beaucoup de nouvelles fonctions DAX ont été ajoutées :

Des fonctions statistiques :

• DistinctCount
• Ecart type
• TopN
• Variance

Des fonctions de relations:

• LOOKUPVALUE: Fonction de recherche informationnelles pour rechercher une certaine valeur dans une table (LOOKUPVALUE()).
• Relation multiples : permet de créer plusieurs relations entre tables, contrairement à la première version.
• Relations parents-enfants: Fonction parent-enfant informationnelle (PATH(), PATHCONTAINS()).
• SWITCH: Fonction logique pour fournir une sélection à choix multiple.

Des fonctions de présentation de données :

• Changement des types de données: modifier le type de données pour une colonne qu’elle soit calculée ou non.
• Formats nombre de mesures: définir le type de format de nombre (comme les devises).
• Description: Ajouter des descriptions (tableaux, mesures, indicateurs de performance clés).

Voilà tout pour ce post, rendez-vous aux prochains billets où j’aurais l’occasion de tester les principales nouveautés.

Pour consulter les articles détaillés :

• Vue diagramme
  
• Trie par colonne

• SQL Server 2011 Denali CTP3 disponible en téléchargement http://t.co/Kgz8SNl, avec des exemples: http://t.co/mY1Bsgh #