Comment la BI Microsoft est en train d’évoluer !

 

BI on-demand, Big Data, Cloud, Mobilité, Machine Learning, Social BI…. Décryptage !

 

Cet article a pour but d’expliquer l’évolution actuelle et à venir de la Business Intelligence Microsoft et de décrypter son écosystème pas si simple à appréhender. En effet l’émergence du cloud, de la mobilité, des «  big data » (réseau sociaux, e-commerce…) et de l’analyse qu’on peut en faire, le tout associé aux demandes avec des délais de plus en plus cours de la part des décideurs ont fait naître de nouvelles technologies de Business Intelligence plus modernes et qui pour certaines sont parfois en phase transitoire et en mode « preview ».

L’écosystème se découpe en 4 domaines et un ensemble d’outils associés comme le montre le schéma d’ensemble ci-dessous :

 

 

 

 

_MSBI

 

 

La BI On-Premise (traditionnel) :

Depuis plus de quinze ans Microsoft fait évoluer ses outils décisionnels « On-Premise » autour de SQL Server pour permettre aux sociétés de mettre en place eux même une solution décisionnelle d’entreprise centralisée, robuste et évolutive, en harmonie avec l’infrastructure locale existante et qui répond à des exigences à la fois de sécurité, de performance mais aussi de budget.

Traditionnellement, pour la conception d’un environnement décisionnel, une société peut faire appel à l’IT, on parle alors pour une solution « Corporate » d’entreprise. Mais un utilisateur final souvent appelé « Power User » peut également mettre en place une solution et devenir autonome dans la totalité ou une partie des phases d’un projet, de la conception jusqu’au déploiement.

Dans le dernier cas on parle souvent de solution BI « On-demand » ou « Self-service » et parfois personnelle si elle est conçu intégralement par l’utilisateur.

La suite d’outils BI traditionnels, et ceux-ci autour de la SQL Server, est composée :

  • Du moteur de base de données : SQL DB Engine
  • De l’ETL : SSIS
  • Du moteur d’analyse multidimensionnel : SSAS
  • Du serveur de rapports : SSRS

Pour la BI « On-Demand », Excel est l’outil de prédilection.

 

_BIOnprem

 

 

 

La BI « Corporate » :

Avec SQL Server et depuis la version 7.0, la plateforme BI Corporate de Microsoft est en perpétuelle évolution toujours dans un but d’être plus robuste, performante et avec de nouvelles fonctionnalités souvent inédites.

 

Aujourd’hui nous en sommes à la version SQL Server 2014 maintenant orientée « Hybrid Cloud » pour faciliter de manière transparente la gestion du stockage, des sauvegardes ou des transferts des données que cela soit sur site ou dans le cloud.

Microsoft a également conçu et développé depuis la version SQL Server 2012 un nouveau moteur d’analyse « In Memory » qui rend plus puissant le traitement des données, les calculs d’agrégats et des formules d’analyse en mémoire, ainsi que la restitution et l’analyse de données autant fines et « Tabulaire » qu’agrégées et en tableau croisé.

De plus, un nouveau type d’index « Column Store Index » permet d’améliorer considérablement les performances de chargement des entrepôts et son requêtage.

Enfin, SQL server permet de réindustrialiser dans SSAS les données provenant des cubes « On-demand », que nous verrons plus loin, conçus avec « Power Pivot », stockées en local dans Excel, de les exposer dans SharePoint pour donner la possibilité à l’utilisateur final de concevoir directement dans un site des analyses ad-hoc grâce au composant de « Data Vizualisation » « Power View » issu de SQL Server et intégré à un service SharePoint 2013.

 

En complément, pour permettre aux utilisateurs de mieux collaborer, la solution SharePoint 2013 permet, en plus de ce qu’on a vu au-dessus avec « Power Pivot » et « Power View »,  la mise en place de portail décisionnel, le partage des classeurs Excel d’analyse, l’intégration des rapports SSRS et l’utilisation des fonctionnalités intrinsèques à SharePoint comme par exemple la GED,  les intranets, les réseaux sociaux d’entreprise… Un petit plus dans SharePoint avec Power View : la possibilité d’exporter les « Dashboards » dans des slides Power Point en conservant l’interactivité et la possibilité de mettre à jour les données au sein d’une présentation.

Un service appelé « Performance Point » qui intègre entre autre l’outil d’analyse racheté à la société « Proclarity » n’est plus mis en avant.

 

_Collab

 

 

Dans la gamme des outils « On-Premise », un nouveau « petit » vient de naitre, il s’appelle « DataZen », il vient d’être racheté par Microsoft, il est fourni gratuitement à partir de la version SQL Server 2008 édition Entreprise avec la Software Assurance.

Comme « Reporting Services », « DataZen » est un outil de restitution, il comporte un « Publisher » pour permettre à l’IT de concevoir des rapports avant les déployer coté serveur.

Par contre, contrairement à « Reporting Services », « DataZen » est un outil orienté « Multi-device » et permet par exemple d’analyser des données sur tout type de tablettes ou smartphones (Windows, iOS et Android), puisque qu’il est compatible HTML5.

De plus, il a été conçu pour faire du « Dashboarding » plus que du « Reporting » opérationnel et fait partie de la gamme d’outil d’analyse visuelle de « Data Visualization ».

Enfin, il offre intrinsèquement la possibilité de faire de la BI collaborative en gérant des flux de commentaires entre les utilisateurs.

Petit bémol, il ne comporte pas comme « Reporting Services » les fonctionnalités de « push mail »  automatisé, l’intégration à SharePoint et le « Data Alert ». Par contre il permet contrairement à Power BI pour le moment de faire des analyses en mode déconnecté.

 

_Datazen

 

 

 

On voit bien que Microsoft a construit au fil du temps une véritable plateforme BI On-Premise solide et avec des évolutions permanentes. Aujourd’hui, IT ou l’utilisateur final bénéficie de toute l’expérience de l’éditeur, de la maturité et la robustesse de sa solution pour mettre en place ou utiliser un système d’information décisionnel d’entreprise accessible depuis partout (tablette, smartphone…) et en mode collaboratif.

 

 

La BI « On-Demand » :

Excel, l’outil idéal pour d’analyse des données « On-demand » a bien évolué. Si on regarde en arrière, avec la version Excel 2003, pour faire de l’analyse avec une connexion OLAP, on se restreignait qu’à un simple tableau croisé dynamique (« Pivotable »). Aujourd’hui avec Excel 2013, on a une véritable solution complète de Business intelligence personnelle avec ses outils permettant à l’utilisateur final de concevoir, comme avec l’IT et SQL Server, sa propre solution en libre-service composée :

  • D’un ETL : Power Query
  • D’un moteur d’analyse et son « Modeler » : Power Pivot
  • De deux outils de « Data Vizualisation » : Power View et Power Map pour l’aspect Géospatial

La philosophie de « Power View » et « Power Map » est de mettre en place comme Power Point un scénario, « Story Boarding » avec des slides, dans le but de faire une présentation issue de sa propre analyse et de faire en quelque sorte « parler les chiffres » ou faire du « Story Telling ».

Cette présentation peut être du « One-Shot » et être exposée qu’une seule fois par exemple à un Codir. D’où l’utilité en la mettant en œuvre rapidement avec Excel, outil très familier de l’utilisateur final, de ne pas passer par des cycles de conception et développement avec des délais qui peuvent parfois être annoncés comme importants par l’IT.

Par contre dans le cas où cette solution nécessite d’être stable, pérenne, sécurisée et partageable, il faut la réindustrialiser de Excel vers SQL Server grâce à des outils d’ « Import » qui existent.

 

_Collab2

 

 

 

La Business Intelligence dans le « Cloud » :

L’évolution des services dans le cloud, notamment autour de l’offre Azure de Microsoft, permet à l’IT maintenant d’une manière transparente de disposer de coûts d’administration mieux maîtrisés. L’infrastructure technique et applicative est plus robuste, performante et évolutive. Enfin les cycles de mise à jour sont plus courts et les opérations de maintenance et l’hébergement sont gérés directement par Microsoft.

 

Trois solutions vont être décrites dans la suite de cet article:

« IaaS »: Infrastructure as a Service

« PaaS »: Platform as a Service

« SaaS » : Software as a Service

 

La solution SaaS Power BI :

Elle permet de concevoir simplement une solution personnelle de Business Intelligence depuis Excel, avec les composants Power Query, Power Pivot, Power View, Power Map. Mais aussi depuis un concepteur dédié appelé « Power BI Designer » ou « On-Line » à partir du site powerbi.com avec un espace dédié et un compte associé.

Office 365 comporte également un site dédié Power BI où sont publiés et visualisés les rapports.

Les utilisateurs se connectent à des sources de données sur site ou dans le cloud pour mettre en œuvre plusieurs rapports et peuvent également les imbriquer simplement dans des « Dashboards ».

Plusieurs connecteurs sont nativement fournis comme par exemple Analysis Services, Salesforce, Univers Business Object, Dynamics CRM, Azure….

 

_CaptureDash

 

 

Une fois la solution publiée sur le site powerbi.com, les utilisateurs pourront depuis un mobile (iPhone et Windows Phone pour le moment) ou d’autres « Devices » se connecter à la solution pour visualiser leurs « Dashboards ».

Des fonctionnalités avancées existent également :

 

– « Q&A ». Une fonctionnalité de requêtage en langage naturelle (uniquement en anglais pour le moment) qui permet de poser des questions Ad-hoc sans connaissances techniques.

– «Data Refresh » permet de planifier la mise à jour des données au sein des rapports.

– « Data Management Gateway » permet de se connecter depuis le cloud à des sources « On-Premise » comme des cubes SSAS.

– « Data Catalog » permet d’exposer les données des rapports dans le cloud pour en faire des sources pour d’autres utilisateurs concevant des rapports.

 

 

powerbi4

 

 

Aujourd’hui Power BI existe en version Preview car il est en perpétuelle évolution, il peut être mis en œuvre et utilisé gratuitement, sauf si vous voulez utiliser les fonctionnalités avancées ci-dessus ou si vous dépassez la capacité limite de 1Go de stockage de données, d’un débit de 10k rows/ heures ou de cycle de rafraîchissement des données de plus de un jour. Dans ce cas le coût actuel est de 9,99 $ par utilisateur/mois.

L’intégration dans l’environnement Microsoft avec un « Designer » très convivial et ergonomique, la conception BI possible depuis Excel , des composants graphiques très riches, des « Dashboards » dynamiques et interactifs, des connecteurs très variés, la publication sur des mobile et tablettes, les cycles MS de mises à jour très réguliers font de Power BI LA solution personnelle et complète pour faire de la « Business Intelligence » « On-demand ».

Face aux solutions concurrentes Tableau Software et Qlik (ex QlikView), Power BI n’a rien à se reprocher.

 

 

La BI Mobile :

Avec les deux solutions « Power BI » et « Datazen » la mobilté cross-plateform est possible. Selon les usages, vous pouvez utiliser l’un des deux outils. « Power BI » est idéal pour le Cloud en mode connecté, gratuit selon certains critères ou avec un système d’abonnement et « Datazen » pour une infrastructure sur site et en mode connecté ou déconnecté, il est inclus dans les licences SQL Server avec la « Sofware assurance » Microsoft.

Une différence importante : pour concevoir un rapport avec Power BI, l’utilisateur n’a pas besoin d’être développeur, il peut le créer dans Excel ou le Power BI Designer. Pour « Datazen », il faut être développeur et être formé sur le « Datazen Publisher ».

 

 

mob

 

 

 

La Business Intelligence dans Azure (IaaS et PaaS) :

Autour des services de données PaaS et de la possibilité d’utiliser des VM dans des infrastructures  IasS ou des « Appliances » va simplifier le travail des administrateurs sur site et offrir des possibilité inédites pour faire par exemple des traitements de « Big Data » ou alors du Machine Learning sur des « Datacenters » mutualisés, ultra-puissants où la mise à jours des services pourra s’effectuer plus simplement.

L’offre IaaS :

Elle offre la possibilité de porter simplement son infrastructure local dans le cloud dans des VM et de pouvoir la superviser à distance et sans avoir à supporter les coûts de maintenance associés.

 

Les appliances APS :

APS ex PDW a été conçue conjointement par Microsoft et des architectes « Hardware » pour offrir la meilleur performance en MPP (Massive Parallel Processing) au sein d’une appliance. Elle offre également la possibilité d’intégrer sous le nom de HDInsigt le moteur NoSQL d’Hadoop pour des traitements « Big Data » et d’offrir au travers du moteur « Polybase » la possibilité de faire des requêtes en langage SQL pour ensuite exposer les données à des fins d’analyse.

 

L’offre PaaS :

Plusieurs  services de données ont été mis en œuvre dans Azure et sont accessibles via son portail. Ceux-ci sont mis à jour régulièrement et certains sont encore en mode « Preview ».

Pour le traitement des « Big Data », Microsoft a intégré dans sa plateforme Azure le moteur Hadoop de la société HortonWorks bien connu pour son architecture de données distribuées, HDFS, sous le nom de « HD Insight ».

Avec HD Insight », sont remodelés, la création de clusters pour la parallélisation des traitements de données, la mise en œuvre des process « Map/Reduce » pour tout type de transformation comme l’agrégation  ou le  nettoyage de données déstructurées comme par exemple le comptage de l’occurrence de mots parmi une multitude de messages comme des tweets, de sms ou flux RSS.

Pour ce faire le service ELT « Azure Data Factory » avec une interface très conviviale, permet d’ordonnancer tous ces traitements, il permet d’extraire des données sources, d’appeler des transformations en passant par des bibliothèques qui s’appuient sur les fonctions « Map/reduce » pour simplifier l’écriture du code. Par exemple « Hive » pour exécuter des traitements SQL DDL (CREATE…) ou DML (SELECT…) ou alors « Pig » pour appeler comme dans un ETL des fonctions de transformations (Agrégation, comptage, boucles,…) et enfin les charger dans des « Data Warehouses ».

Pour la gestion des entrepôts le service « Azure SQL Data Warehouse » peut être utilisé pour mieux exposer ses données à des outils d’analyse comme SSAS ou Power BI.

Dans le cas de besoins d’analyse ou monitoring temps réels et pour une prise de décision rapide, le service « Azure Stream Analytics » permet de traiter des flux de données très volumineux et avec un débit important comme des « Click Streams » provenant de site internet e-commerce par exemple, de les ingérer, les regrouper, agréger ou filtrer grâce à une bibliothèque de fonctions proche du SQL, pour ensuite les exposer à des fins d’analyse avec Power BI ou de Machine Learning. « Azure Stream Analytics » peut s’appuyer sur les services d’Azure de files d’attente « Event Hub » et « Services Bus ».

Enfin avec le service, « Azure Machine Learning », les « Data Scientists » peuvent faire de l’analyse prédictive dans le cloud et exposer leur résultats à des outils front ou des process via à un appel de « Web services ». Ce service permet de créer dans un concepteur dédié, appelé « ML Studio », des unités de traitement appelés expériences comportant une boite à outils pour créer des jeux de données sources, évaluer des modèles, les entrainer ou faire du « Scoring »,  effectuer tout type de transformations en mode graphique comme on le ferait avec SSIS mais d’utiliser aussi le langage R pour le faire et enfin produire les résultats statistiques et de probabilités.

 

 

_Azure3

 

 

 

Pour mettre en œuvre tous ces services il existe un système d’abonnements dont le coût varie selon un ensemble de paramètres comme la volumétrie, le temps, le débit….

 

Vous constatez  que maintenant, avec l’offre PaaS BI dans Azure, il existe un kit de services clef en main de plus en plus matures, robustes et performants pour traiter des données de  Business Intelligence en Back Office surtout pour des process associés au « Big Data » au temps réel et au « Machine Learning ».

 

 

 

A venir :

Comme vous avez pu le constater, le paysage autour de la Business Intelligence Microsoft c’est pas mal transformé depuis ces derniers temps et il continue encore. SQL Server 2016 est annoncé avec de nouvelles évolutions comme l’unification de « BIDS » et « SSDT », l’intégration de HDInsight et Polybase qui n’étaient actuellement que dans APS ou Azure, la possibilité d’intéragir avec Azure Data Factory depuis SSIS, des nouvelles fonctionnalités de « Data Vizualisation » dans SSRS et la possibilité de publier des .rdl dans le site Power BI. Du nouveau également dans SSAS Tabular puisque les relations « many to many » seront supportées et enfin des évolutions autour de SSAS multidimensionnel et MDS.

Concernant les outils « Front », une nouvelle version d’Excel dans Office 2016 est à venir où Power Query va maintenant être natif à Excel, la possibilité maintenant de créer un rapport Power View sur des cubes SSAS multidimensionnels et d’autres fonctionnalités qui seront probablement annoncées bientôt.

 

Ce qui reste à améliorer :

La BI MS a encore de beaux jours devant elle, par contre l’offre peut paraître encore « décousu » et pas forcément simple à comprendre tant au niveau des usages, que du mode de licensing.

Par exemple, si l’utilisateur demande une publication de son modèle « Power Pivot » dans SharePoint, on s’aperçoit que le mode de licencing est complexe et qu’il faut composer entre les licences d’Excel 2013, de SQL Server mais aussi de SharePoint.

De plus, il faut un travail important de pédagogie envers l’utilisateur qui ne comprend pas pourquoi il y a autant d’offres. Par exemple, il peut être perdu avec les outils de « Data Vizualisation »: SSRS, Datazen, Power View…. donc il faut lui expliquer les correspondances qu’il y a entre les usages qu’on peut en faire et les outils associés.

Enfin, dans un composant comme Power View qui est distribué à la fois dans Excel et SharePoint On-premise, il peut y avoir des petits plus qui sont dans l’un mais pas dans l’autre, par exemple l’export vers Power Point qui n’est que dans SharePoint. De plus les composants Power BI d’Excel 2013 et du Power BI Designer ont été découplés mais les fonctionnalités sont beaucoup plus avancées dans le dernier.

 

Pour conclure :

Comme on vient de le voir avec les outils de « Data Vizualisation », les enjeux à venir de Microsoft vont être de les faire converger pour en faire une offre homogène, multi-usage, en plus d’être muti-device et cross-platform.

De même, une convergence entre le cloud et le « On-Premise » doit continuer à s’installer autour de l’hybride : faire en sorte homogénéiser les outils, les échanges de données ou de process inter plateformes, où tout sera transparent pour les développeurs, administrateurs, DBA, Data Manager ou Stewardship, Business Analyst, Power User, et utilisateur final, et où ils ne s’apercevront plus de la différence entre concevoir ou consommer de la BI sur un PC ou un mobile, « On-Premise » ou dans le cloud.

 

 

Sébastien MADAR

MVP SQL Server

 

 

Tutorial Power Query

Tutorial Power Query

Image

Contexte

L’objectif de ce tutorial est de pouvoir analyser et croiser toutes les informations concernant:

  • La ponctualité des TGV
  • L’information Transilien (les équipements, les services qui sont proposés dans les gares desservies par le Transilien)
  • Le nombre de voyageurs moyen par gare

Maquette

A l’issu de cet atelier nous obtiendrons une maquette semblable à celle ci-dessous :

Image

Dans ce scenario, nous utiliserons plusieurs requêtes(QUERY) afin de charger des données issues de différentes sources (csv, dossier, web) et réaliserons quelques transformations.

NB : toutes les formules utilisées dans ce tutorial sont codées en langage M, spécifique à Power Query.

QUERY 1 (Gare) : Chargement des données à partir d’un fichier csv

Dans cette requête nous utiliserons les transformations suivantes : « Source csv », « Use First Row As Headers », « Custom Colunm », « Replace Values », « data type ».

Pour charger les données à partir d’un fichier csv, je vous invite à suivre les étapes suivantes :

Dans Power Query :

  • Sélectionnez « From File »  ->  « From CSV » dans « Get External Data »
  • Sélectionnez ensuite le fichier  « Liste des gares de voyageurs du RFN avec coordonnées et adresses postales.csv »

Image

  • Activez « Formula Bar »

Image

  • Modifiez la source afin d’avoir un bon format de données en 1252 et un split par « ; » de type = Csv.Document(File.Contents(« C:\Users\Administrator\Documents\Source SNCF\Liste des gares de voyageurs du RFN avec coordonnées et adresses postales.csv »), null, « ; » , null, 1252)

 

cliquez sur « Use First Row As Headers » afin d’utiliser la première ligne comme en-tête.

Image

Pour créer une colonne calculée réalisant une concaténation :

  • Faites clics  droits -> Insert Custom Colunm

Image

  • Saisissez la formule suivante : Text.Combine({« 87 »,[UIC]})  afin de faire une concaténation du « 87 » et le code UIC pour faciliter le merge avec d’autre table

Image

Pour modifier les types de données :

  • Sélectionnez la colonne « Code UIC »  -> « Data Type » et choisissez le type Number

Image

Sur la colonne « Nom de la gare », remplacez le «-» par un espace afin d’améliorer le mapping avec les fichiers de ponctualité. Pour cela :

  • Utilisez « Replace Values » sur la colonne Région, remplacez la valeur « -CA » par «  Cote d’Azur »

Image

  • Validez les transformations et renommez « Query » par « Gare ».

Résultat attendu :

Image

QUERY 2 (Ponctualité) : Chargement des données à partir d’un dossier

Dans cette requête nous utiliserons : « Source dossier », « Split Column », « Use First Row As Headers », « Custom Colunm », « Transform », « data type », « Calcul de Ratio ».

Parmi les avantages de Power Query, il y a la possibilité de charger un dossier sans avoir besoin d’utiliser une boucle :

Dans Power Query :

  • Sélectionnez « From File » -> « From Folder » dans « Get External Data »
  • Sélectionnez ensuite le Dossier  « Source Ponctualite» (A voir avec le formateur pour le chemin).

Image

  • Parcourez le dossier « Source Ponctualite».

Image

  • Cliquez sur les doubles flèches dans la case « Content » afin de regrouper toutes les données des différents fichiers.

Image

Image

Afin de Splitter les différentes colonnes :

  • Cliquez sur « Split Column » -> « By Delimiter »

Image

  • Sélectionnez le « ; » comme  séparateur de colonne

Image

Image

Pour utiliser la première ligne comme en-tête :

  • Cliquez sur « Use First Row As Headers »

Image

  • Faites un filtre sur la colonne « Départ » afin de retirer la valeur « Départ » qui représentait la ligne d’en-tête de chaque fichier.
  • Pour les colonnes « Nb de circulations » et « Nb trains en retard à l’arrivée », remplacez les blancs par 0 (comme dans la QUERY 1) puis changez le format en « Number »
  • Afin de matcher avec la table « Gare », utilisez la fonction « Transform » puis « Capitalize Each Word » sur les colonnes « Départ » et « Arrivée ».

Image

  • Remplacez les « – » par des espaces sur ces deux colonnes.

Pour calculer le ratio du nombre de retards par rapport au nombre de circulations :

  • Insérez une nouvelle colonne
  • Saisissez la formule suivante :

if [Nb de circulations] = 0 then 0 else [# »Nb trains en retard à l’arrivée »]/[Nb de circulations]

Image

  • Renommez cette colonne « Ratio retard » et changez son format en « Number »
  • Validez et renommez la Query « Ponctualité TGV ».

Résultat Attendu :

Image

QUERY 3 (Equipement des gares) : Chargement des données à partir d’une page web

Dans cette requête, nous allons mettre en pratique les composants suivants : « Source web », « Split Column », « Use First Row As Headers », « data type ».

Dans Power Query :

  • Sélectionnez « From Web»

Image

ImageImage

Afin de Splitter les différentes colonnes :

  • Cliquez sur « Split Column » -> « By Delimiter »

Pour utiliser la première ligne comme en-tête :

  • Cliquez sur « Use First Row As Headers »

Faites les transformations qui suivent afin d’améliorer la correspondance avec les autres fichiers

  • Filtrez sur la colonne « Nombre de bornes d’appel » en retirant la valeur « Nombre de bornes d’Appel ».
  • Changez le format de la colonne « Code UIC » et « Nombre de bornes d’appel » en « Number ».
  • Supprimez la colonne « Gare ».
  • Validez et renommez la query « borne ».

Résultat attendu :

Image

QUERY 4 : Chargement des données à partir d’une page web et calcul des ratios

Dans cette requête, nous utiliserons les mêmes composants que dans la précédente mais avec le « Calcul des Ratios » en plus.

Dans Power Query :

  • Sélectionnez « From Web»
  • Rentrez l’adresse suivante :

http://files.transilien.com/hackdays/voyageurs_montant_en_gare.csv

Afin de Splitter les différentes colonnes

  • Cliquez sur « Split Column » -> « By Delimiter »

Pour utiliser la première ligne comme en-tête :

  • Cliquez sur « Use First Row As Headers »
  • Supprimez la colonne « Gare ».

Calcul des ratios :

Pour calculer le ratio permettant d’avoir le nombre de voyageurs minimum avec le code :

  • Insérez une nouvelle colonne
  • Saisissez la formule suivante :

 if [Nombre de voyageurs montant] = « entre  5000 et 15000 » then 5000 else if [Nombre de voyageurs montant] = « entre  1000 et 5000 » then 1000 else  if [Nombre de voyageurs montant] = « entre  300 et 1000 » then 300 else  if [Nombre de voyageurs montant] = « moins de 300 » then 0 else  if [Nombre de voyageurs montant] = « plus de 15000 » then 15000 else  0

  • Faites la même chose  pour la colonne « Nb Voyageurs Max » :

if [Nombre de voyageurs montant] = « entre  5000 et 15000 » then 15000 else if [Nombre de voyageurs montant] = « entre  1000 et 5000 » then 5000 else  if [Nombre de voyageurs montant] = « entre  300 et 1000 » then 1000 else  if [Nombre de voyageurs montant] = « moins de 300 » then 300 else  if [Nombre de voyageurs montant] = « plus de 15000 » then 15000 else  0

  • Idem pour la colonne « Nb Voyageurs Moyen » :

([Nb Voyageurs Min]+[Nb Voyageurs Max])/2

  • Supprimez la colonne « Nombre de voyageurs montant », modifiez le format des colonnes restantes en « Number ».
  • Validez et renommez la Query « Voyageurs ».

Résultat attendu :

Image

QUERY 5 : Chargement des données à partir d’une page web

Dans cette requête nous utiliserons : « Source web », « Use First Row As Headers », « un split par « ; »  », « Filtrer », « Replace Values ».

Dans Power Query :

Modifiez la source afin d’avoir un bon format de données en 1252 et un split par « ; » de type

  • Modifiez le format de la source

=Csv.Document(Web.Contents(« http://files.transilien.com/gare/gare_20120319.csv »),null, »; »,null,1252)

  • Mettez la première ligne en en-tête avec « Use First Row As Headers ».
  • Pour chaque colonne correspondant à des mesures (exemple : nb d’abris à vélo) ainsi que le code UIC, remplacez les blancs par 0 puis modifiez le format en « Number ». Pour le « CAB Passage Elargi contrôlé », remplacez « Pas de CAB » par 0.
  • Il est possible de filtrer sur la colonne « gare non SNCF » et de ne garder que les 0, c’est-à-dire seulement les gares SNCF.
  • Supprimez alors toutes les colonnes mises à part le code UIC et les mesures.
  •  Validez et renommez la Query « Equipement ».

Résultat attendu :

Image

QUERY 6 : Merge de plusieurs sources

Dans cette requête nous allons faire une jointure entre deux fichiers différents pour cela nous allons utiliser le Merge qui est l’équivalent d’INNER JOIN.

Dans Power Query :

  • Appuyez sur « Merge »  

Image

  • Sélectionnez  la table « Equipement » et le champ « Code UIC complet ».
  • Faites la jointure avec la table « Borne » sur le code UIC en cochant la case « Only include matching rows ».

Une nouvelle Query est créée avec la table « Equipement » contenant une nouvelle colonne « NewColumn ».

  • Appuyez sur Expand
  • Cochez le nombre de bornes d’appel.

Image

  • Une fois la colonne ajoutée, validez.

Résultat attendu :

Image

QUERY 7 : Merge de Query voyageurs avec la Query

Dans cette requête, nous allons faire une jointure entre deux fichiers différents. Pour cela, nous allons utiliser le Merge qui est l’équivalent d’INNER JOIN.

Dans Power Query :

  • Faites une nouvelle jointure « Merge » en sélectionnant la table précédemment créée ainsi que la table « Voyageurs » (toujours sur le code UIC).
  •  Cochez de nouveau la case « Only include matching rows » avant validation.
  • Faites un Expand et cochez le « Nb Voyageurs Min », « Nb Voyageurs Max » et « Nb Voyageurs Moyen ».
  • Validez et renommez la query « Transilien ».

Résultat attendu :

Image

Les « QUERY » sont maintenant créées et peuvent être intégrées au Data Model.

  • Cliquez  sur « Load to Data Model » pour les intégrer  et être utilisées afin de créer des rapports sous PowerView.

Laila TABII

[JSS 2013] Session : Gestion des données, Power BI

Speaker : Jean Pierre Riehl

Introduction :

Power BI est une solution de Business Intelligence composée de plusieurs éléments.

3 grands usages de Power BI :

  1. Information worker – Excel
  2. Data Steward – Excel, PowerBI site
  3. IT admin – Data management Gateway, IT admin Portal

Power Query :

C’est un Add-in Excel gratuit.
C’est l’outil de BI Self-Service qui peut charger des données qui ne sont pas dans le format attendu depuis toutes les sources de données et qui peut connecter les informations.

Jean-Pierre Riehl nous montre 3 démos

Démo 1 : Façonner des données

On peut créer une requête dans Power Query, traiter les données et nettoyer les données pour arriver le jeu de données qui peut répondre aux questions.

Il y a trois fonctionnalités utiles dans Power Query

Json.Document : il nous permet de lire, transformer et exploiter ce format assez nativement.

Merge : L’opération Merge crée une nouvelle requête de multi-sources de données.

Un pivot : Il crée une colonne d’attributs pour la colonne sélectionnée et une colonne de la valeur pour chaque valeur de la cellule sélectionnée.

On peut aussi créer notre propre fonction avec le langage M.

Démo 2 : Partager et Rechercher

L’objectif : Collaborer avec l’équipe et avec l’ensemble de service et partager des travaux.

Share Query : qui permet de partager la requête sur Office 365 et mettre la disposition à l’autre.

(Partager uniquement les métadonnées pas EXCEL!)

Online Search : le moteur nous permet de rechercher partout avec le mot clé

  1. Chercher les données public (Wikipédia)
  2. Chercher les données dans mon organisation.

Démo 3 : Data Management Gateway

Il se charge de communiquer et mettre le lien entre On-Premise et Office 365 – Cloud

Il permet de mettre à jour les données sur Cloud et d’exposer en flux Odata les informations d’une base de données qui se trouvent sur site.

Conclusion :

Quand User est dans EXCEL, il peut communiquer directement avec Office 365. Ça sert à rechercher et partager les informations.

Data Management Gateway, il s’agit comme proxy et il permet de Data Refresh indexation des métadonnées.

Yuanfan – Consultant décisionnel MCNEXT

[JSS2013] Session : SSAS Tabular : Modélisation

Speaker : Aurélien Koppel et Benoit Girard
Level: 300

Petit rappel :

SSAS 2012 C’est 2 moteurs :

  • Moteur Multi dimensionnel => MOLAP et ROLAP
  • Moteur Tabular => X Velocity et Direct Query

Dans cette session nous allons analyser le moteur XVelocity

But de ce nouveau moteur :

  • Moteur plus performant
  • Prioriser les perf. Requetage : Perf requetage > Perf Process
  • Many to Many plus performant
  • Modèle plus Agile – ex. ajout nouvel attribut => Multi Dim : il faut tout reprocess!
    Tabular : reprocess de la table de l’attribut uniquement
  • Minimum de config – ex. pas d’agrégation

STOCKAGE

Mode stockage en colonne, en mémoire et compressé.

Compression en moyenne X10 vs. BI Traditionnel principalement grâce à l’encoding qui peut se faire de 2 façons :

  • Value encoding – pour valeur arithmétique dense
  • Hash encoding – pour les strings

Que stock le moteur en mémoire ?

  • Dictionnaire de donnée – ex. pour l’encoding
  • Colonne par segment – segment de 8M de lignes
  • Colonne calculée – mais non compressé !
  • Hiérarchie
  • Liaison entre les tables

Les DMV

Ils existent plusieurs DMV qui permettent entre autre de voir le type d’encoding utilisé et si celui-ci a déjà été modifié :

  • $SYSTEM.Discover_Storage_table
  • $SYSTEM.Discover_Storage_table_column
  • $SYSTEM.Discover_Storage_table_column_segment

INFRA

Le plus important :

  1. RAM – Stockage des données
  2. Processeur – Compréssion / decompression
  3. Disque – Seulement au process et restart

Pour la RAM besoin de 2 fois la taille du modèle car lors du process l’instance crée un 2eme modèle en parallèle et remplace le 1er à la fin du process (comme en multidim.)

MODELISATION

Supporte le modèle étoile et flocon mais mieux vaux utiliser le modèle en étoile

  • Meilleur Lisibilité
  • Profiter de l’auto-Exist
  • Meilleur perf sur les Cross Join complexe
  • Hiérarchie plus simple (dans la même table donc pas besoin de calcul complexe en DAX)

*Astuce dans Excel – on peut afficher les attributs des ‘dimensions’ vide.  Clique droit => Pivot table Options => Displays => Show Item with no data on rows

Hierarchie parent – enfant

Pas d’UI comme en Multidim mais possible de le faire dans les mesures en DAX

2 cas possibles :

  • Fonction PATH => recrée toute l’arborescence de la hiérarchie
  • Fonction PATHITEM => récupère 1 niveau de la hiérarchie au choix

Many to Many

Pas d’UI comme en Multidim mais possible de le faire dans les mesures en DAX, c’est performant par contre la logique est à implémenter pour chaque mesure donc contraignant.

PROCESSING STEPS

  1. Connections + exécution de la requête => Minimiser les connections, 1 connection pour plusieurs table si même source (attention à l’utilisation du wizard donc !)
  2. Encodage => utiliser les DMV pour voir le type d’encoding
  3. Compression => lit le 1er segment, puis lit le 2eme et compresse le 1er en parallèle, puis lit le 3eme et compresse le 2eme en parallèle, …
    Défaut setting de 10 s. pour compression de 1M de lignes
  4. Recalc => Recalcul les colonnes calculées, attention les colonnes calculées sont stockées en mémoire mais non compressé.

STRATEGIE DE PROCESSING

Process Défaut : seulement la 1ere fois pour que le modèle soit disponible

Process Full : Process Data + Process Recalc

Process Data : requête base de données, encoding et compression

Process Recalc : toute la base est recalculée! + Attention aux colonnes calculées, recalcule par ligne donc beaucoup plus lent

Process Add : ajout des nouvelles lignes. A la fin du process Add il y a un process Recal automatiquement

OUTILS TIERS

Ces outils gratuits permettent l’ajout de fonctionnalités non présentes dans SSDT ou Excel par défaut

BIHELPER

Permet l’ajout d’action :

  • Tabular Action Editor
  • Tabular Display folder
  • Tabular Hidemember
  • Tabular Sync description
  • Tabular Translation Editor

BISM Normalizor

Permet de comparer et fusionner des modèles, ex. Excel PowerPivot VS. Tabular modèle sur instance SSAS donc pratique après import d’un modèle PowerPivot sur server ou SharePoint

DAX Studio

Excel Add-in. Plus pratique pour écrire du DAX et permet de lancer les requêtes DAX puis voir le détail des perf. Permet également d’analyser les traces du profiler.

BISM ServerMemoryReport

Fichier PowerPivot qui requête les DMVs de la base SSAS pour visualiser la mémoire utilisée.

Ludovic – Consultant décisionnel MCNEXT

[PASS SUMMIT 2013] Advanced Analysis with Excel 2013

Speaker : Dejan Sarka, SolidQ

La session parle de data mining et de l’utilisation des outils Microsoft associés (Excel + PowerPivot)

Agenda :

  • Excel Data preparation and Data mining
  • Table analysis with Excel
  • Introducing PowerPivot
  • Combining data mining with PowerPivot

Introduction :

L’Addin Data Mining est à chercher avec la version SQL 2012

Il utilise en source une  connection à SSAS en multidim ou une Range Excel ou une Table Excel

Concrètement on peut utiliser 3 addins :

  • Le client Data mining pour Excel
  • Table Analysis Tool pour Excel
  • Les modèles de Data Mining pour Visio (pas encore pour excel 2013, pas montré par le speaker)

Le client Data mining pour Excel :

Tout se trouve sur la base (Ribbon)

La préparation des données se fait avec Explore Data tool + Clean Data Tool (nettoyage basique = re-nommage de colonne, suppression des valeurs aberrantes = outliers) + Sample Data tool (échantillonnage pour training du modèle)

Pour le modèle de données on peut utiliser :

  • Classify tool -> utilise Decision tree
  • Estimate tool -> utilize Regression tree
  • Cluster tool -> utilize Clustering
  • Associate tool -> utilize des règles d’association
  • Forecast tool -> utilize Times Series
  • Options avancées : créer une nouvelle mining structure ou pour ajoutter un modèle à une mining structure existante

Pour la précision et la validation des données on utilise dans la section correspondante du Ribbon :

  • Accuracy Chart
  • Classification Matrix
  • Profit Chart
  • Cross Validation tool

Les modèles que l’on veut valider doivent partager la même structure de mining

Le client de data mining sert aussi pour :

  • Model usage
  • Management
  • Connections

Démo :

  • analyser des données via Excel (connection a une bd sql 2012 adventureworksdw2012)
  • utiliser le Data mining client : explorer les données à partir d’une table de données d’Excel data et affichage de la distribution continue d’un champ (par ex du revenue)
  • Convertir la table en range puis classify (uses decision tree) pour analyser les achats de vélos : cela crée un  modèle et sa structure dans SSAS, on peut voir alors l’arbre de décision (par exemple depuis ssms ou excel : idem)
  • On peut aussi utiliser document model via Excel (option qui existe) et copier une des représentation graphique (image statique)

Table analysis tool :

Il apporte une vision orientée plus business et peut être utilisé pour :

  • Analyze key influencers (Native Bayes mining model)
  • Detect categories (Clustering mining model) and Fill From Example (Logistic Regression mining model)
  • Surligner les Exceptions categories (Clustering mining model)  et analyser le panier du consommateur (Règles d’association)
  • Outil de prévision (Forecast tool via Times Series model et Prediction calculator via Logistic Regression)
  • Goal seek (que modifier pour arriver à l’objectif) et What if (quell est l’impact d’une modification)

Démo :

  • Gool seek (target bike buyer) pas très utile car chiffre utilise en source pas top
  • What if (target bike buyer avec distance — > renvoie un niveau de confiance)
  • Hihlight Exceptions (lent c’est pour cela qu’on échantillonne) : marque les lignes sources et produit un rapport

Combining data mining with PowerPivot :

L’introduction sur PowerPivot décrit BISM puis explique différentes implémentations du modèle tabulaire de SSAS :

  • Personal BI  = Excel PowerPivot
  • Team BI = BI avec SharePoint
  • BI d’entreprise = SSAS en mode tabulaire

On peut utiliser PowerPivot pour du datamining. Il faut :

  1. Créer un rapport de table pivot à plat
  2. Supprimer les sous totaux et totaux
  3. Formater la zone de données en tant que table
  4. Utiliser l’outil Table Analysis pour explorer les données

On peut faire l’inverse, utiliser du datamining en tant que source de PowerPivot. Pas d’éditeur DMX dans PowerPivot mais voici la solution de contournement :

  1. Définir SSAS multidim database en source de données
  2. Ecrire dans un éditeur MDX (genre SSMS) la requête DMX manuellement (bonne chance)
  3. Importer les données
  4. Créer les relations

Démo :

Première démo

  • import de donnes dans PowerPivot via query sur source sql + measure total of bike buyer (sum) + count of bike byer + moyenne via sum/counta
  • création pivot table report
  • exclure subtotals et total pour avoir une table
  • convertir  en formules
  • analyser via tool d’excel

Deuxième démo

  • création dans Visual Studio d’un data mining model
  • Création  requête sql (en dmx avec prediction join) pour avoir des clusters par customerkey
  • Import du modèle dans PowerPivot via la requête préparée avant en dmx
  • Connexion dans le modelé de PowerPivot entre vTargetMail et la Query
  • Création d’un graphique (histogramme de prédiction des buyers by cluster)

Conclusion :

Bonne session si on aime le Data Mining.

Encore une utilisation de la self-service BI (on oublie un peu que le data mining existe ?).

Avantage Microsoft : la gratuité des outils que l’on a déjà pour du data mining (Excel + SSAS) quand on fait  de la BI.

Oui Microsoft n’est pas le standard pour le data mining …

Pascal Rouzé

[PASS SUMMIT 2013] Power BI Self-Service Information Management

Speakers : Matthew Roche & Ofer Ashkenazi – Microsoft Corporation – Power BI Team

Niveau de la session : 200

1. Power BI review :

  • 1 milliard d’utilisateurs Office,
  • 1/4 des entreprises clientes sont sur Office 365,

Démo SSIM dans Power BI:

– US Airports Weather Data et Arrival Delays,
– Sources : Windows Azure HDInsight, Excel,
– Share Query : il est important de rajouter la description de la requête afin de faciliter l’indexation de la Query et donc la retrouver en top list dans online search,
– Power MAP (nécessite la version 64 bits de l’os et d’Excel),

2. Power BI SSIM main modules :

  • Data Catalog : contient notamment les métadonnées des différentes sources internes et externes (les Query y sont publiées suite au Share),
  • Power Query in Excel,
  • Admin Center,
  • Data Management Portal,

3. Delivering tangible benefits for customers :

  • Data Steward : s’assure que les métiers ont les données dont ils ont besoin quand ils en ont besoin (création et annotation des tueries, identification des besoins en données, suivi des statistiques d’utilisation des données (usage analytics par date / utilisateur / requête)),
  • IT Admin : se charge de fournir les sources de données, de gérer les droits d’accès et du Data Management Gateway,

Conclusion :

Session assez intéressante pour découvrir et comprendre l’écosystème de Power BI en termes d’outils et d’acteurs.

Ismaïl Ben Lamine

[PASS SUMMIT 2013] Deep Dive into the Power Query Formula Language

Speakers : Faisal Mohamood – Lead Program Manager, Theresa Palmer-Borovski – Program Manager & Clement Szyperski

Niveau de la session : 400

Agenda :

  • Design Goals & Guidelines Principles
  • Lang Constructs & Type System
  • Demo, demo, Demo

La session démarre avec les objectifs de construction du langage (utlisateurs avancés d’Excel, Syntaxe simple, proximité avec DAX…)

1. Principes :

  • Dynamique
  • Fonctionnel
  • Hautement ordonné
  • Typage optionnel

2. Type System :

Ensemble de types restreint

Possibilité de définir des types personnalisés

3. Values :

  • Null
  • Logical
  • Number
  • Text
  • Date …
  • Duration

4. Complex Values :

  • List
  • Record
  • Table
  • Fonction
  • Type
  • Binary

5. Fonctions Unaires

Existence de forms syntaxiques spéciales (expressions conditionnelles, Let expression, Error Expression, Try expression)

Existence de types structures (List, Record, Table, Function)

 

Passé la partie théorique assez courte (il en faut bien un peu non), le reste de la présentation enchaine démo sur démo des fonctions du langage : c’est ce qu’il faut regarder de la session.

Conclusion

Une très bonne session avec un aperçu des possibilités offertes par le langage. Une alternative à SSIS dans certains cas ?

Pascal Rouzé