Sécurité dynamique dans les cubes SSAS avec SQL Server 2012

Avec l’ouverture des données Power BI Self-Service vers les utilisateurs (vu dans plusieurs de mes missions) les métiers veulent gérer eux même les droits ou périmètres sur de nombreux utilisateurs finaux (>1000).

En effet dans certains domaines, nous avons une forte contrainte de confidentialité des données contenues dans les cubes SSAS. Les juridictions qui doivent s’appliquer peuvent être changées avec des cadences variables allant de la journée, à des cadences inférieures (temps réel). Pour cela, l’ensemble de la sécurité mise en œuvre est stocké dans la source de données du cube, qui détermine qui peut voir quoi.

A travers cet article, nous allons voir comment rendre dynamique une sécurité de cube SSAS basée sur l’appel à une procédure stockée.

Lire la suite

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

 

 

Optimisation Many-to-Many SSAS

Agenda :

Tour d’horizon des axes d’optimisation des M2M

  • Partitionnement
  • Agrégation

Les matrices de compression en détails

  • Présentation du fonctionnement
  • Utilisation de BIDS Helper
  • Démonstrations

Rappel des M2M:

Exemple:  manymany

  • Des raisons d’achat et des achats
  • Des personnes et des comptes bancaires (une personne peut avoir plusieurs comptes bancaires, et un compte peut être associé à plusieurs personnes)

Gestions SSAS:

  • Ssas gère nativement le multidim, par contre ce n’est pas le cas pour le tabulaire

Un exemple d’un modèle de données :

Modele M2M

Ce qui nous intéresse c’est le rectangle en pointié.

C’est un forum où des utilisateurs sont inscrits et qui font des activités, l’activité de notre exemple est de faire des commentaires sur des articles, en fonction de leurs activités ils reçoivent des badges, par exemple poster un article sur sqlserver donne droit au badge sqlserver.

Où est la M2M ?: Comment on peut compter le nombre de commentaires en fonction des badges ?

Ce que l’on souhaite de faire :

Croiser badge et commentaire : Connaitre le score et nombre de commentaires par badge.

Gérer les relations : Faire attention à ne pas sommer n’importe comment.

Performance et optimisation : On souhaite que tout cela fonctionne avec un minimum de temps de réponse.

Les partitions:

Processing et requête plus rapides

  • Parallélisme
  • Scanne moins de données

Optimisation au niveau de SSAS:

Une partition est un découpage de beaucoup de groupes de mesures. Dans SSAS par défaut : un groupe de mesure = une partition.

Dans 90% des cas on découpe les partitions par année, pays ..

Le gain est au niveau de requêtage et processing, SSAS process les partitions en parallèle.

Le requêtage  est rapide car SSAS scanne moins de données.

Les partitions « Bonnes pratiques »:

Le fait de diviser les groupes de mesures cela nous donne plus de flexibilité.

Il faut pensez aux scripts XMLA pour créer les partitions, c’est pratique pour gagner du temps, en l’occurrence on peut générer le code xmla pour une partition et l’exécuter pour les autres en changeant les variables.

Définir une agrégation sur une partition :

Pour les très grandes volumétries, il vaut mieux une grande partition que plusieurs petites (le cas où les données d’une année représente une très grande volumétrie).

partitions

Les aggregations:

Un autre grand point sur lequel on peut faire des optimisations est les agrégations. En créant des pré-calculs sur les partitions, on demande à SSAS de préparer les résultats en avance, donc il commence à agréger les données en fonction de différents attributs.

Trois façons de faire :

La Méthode classique qui est l’assistant qui permet soit de sélectionner manuellement les attributs, soit semi-manuel c-à-d des fonctions qui appliquent des algorithmes sur les groupes de mesures et les différents axes pour choisir lui-même les meilleurs agrégations.

Et sinon on a l’optimisation parfaite qui est le « usage based optimization » qui est une optimisation automatique (après activation), ça historise toutes les requêtes jouées sur le cube et à partir de ça il crée des agrégation basé sur le comportement de ces requêtes (très performant mais demande beaucoup de ressources).

Les agrégations : Bonnes pratiques:

  • Méthode : faite du semi manuel Unrestricted fonctionne plutôt bien sur de faible volumétrie (avec l’option “gain de perf”).
  • SSDT : Gérer 20% à 30 % des agrégations possibles.
  • Pourquoi pas 100% ?: parce que si on demande à SSAS de faire 100% des agrégations, il va faire toutes les combinaison possible et donc le temps de process et l’espace disque vont exploser.

Cela marche plutôt bien sur des petites volumétries.

Récapitulatif:

recap

Quand on fait une requête sur un cube via excel par exemple, c’est du code mdx qui est balancé au niveau du cube, et là on a 3 niveaux d’interprétations:

Soit la requête a déjà été exécutée dans le cache donc le résultat est dans le cache, ça renvoi la réponse directement, sinon ça va chercher le résultat dans les agrégations, et si ces derniers ne suffisent pas il va taper dans les partitions.

Il existe une autre option que l’on appelle le cache warming, c’est préparer le cache avec les requêtes de type “prepare cache” .

Les Matrices de compression : la solution lorsque le partitionnement et les agrégations ne suffisent plus ?

aproche sql

Dans ce modèle :

  • Un utilisateur a un ou plusieurs badges, un badge est attaché à un ou plusieurs utilisateurs.
  • Un utilisateur poste un ou plusieurs commentaires, un commentaire appartient à un utilisateur.

Ce que l’on souhaite calculer :

  • Sommer les scores des commentaires qui ont un lien avec le badge.

Résultat :

  • Le score ici est 9 par badge.

Illustration :
Afin de calculer le nombre de commentaires par badge, on a utilisé les relations entre les 2 tables de faits «FactComment » et « FactBadge ». Cela a renvoyé 90 lignes.

On a ici un problème de volumétrie, par exemple pour un utilisateur qui a posté un commentaire et qui a 90 badges, la requête renvoi 90 lignes. Alors que la seule chose qui nous intéresse est que cet utilisateur a posté un commentaire et que ce commentaire à un score de 9.

Si on concatène les « BadgeId » et on les met en une seul ligne, on compresse 90 fois la table.

matrice de compression

L’idée est de revoir ce modèle pour passer par une autre table de Fait que l’on va créer « FactBadge – matrix » qui va être une table de fait modifiée avec d’une part la concaténation de l’ensemble de mes badges et d’autre part une clé qui sera une référence vers une autre table de Dimension que l’on va créer également « DimBadge – matrix ».

Un processus en 4 étapes :

  1. Estimer les gains
  2. Modification du DataWarehouse (Ajout de 2 tables, une fact et une dimension)
  3. Modification du Cube
  4. Implémentation dans l’ETL

estimation gain

Estimation des gains avec bids helper, on click sur le petit bouton (qui apparait en haut) , et il va scanner toutes les many-to-many, et puis nous donner la taille originale de la table de fait , la taille compressée et la taille de la Dim matrix qui va être à créer, et il nous calcule la réduction en pourcentage (ici 42%).

Ou bien avec du code !! Mais l’assistant est plus simple quand même 😉

modification du dataware

On a rajouté du temps d’alimentation:

  • Alimenter les 2 tables qu’on vient de créer
  • Faire un update sur la FactComment qui peut être couteux vu la volumétrie.

modification du dataware 2

Voilà un exemple d’illustration:

Dans la « FactBadge » on a des user qui sont attachés à des badges, le user 174601 a 3 badges, et le user 480508 aussi. On remarque que les 2 user ont le même ensemble de badge, donc ce qu’on va faire c’est concaténer l’ensemble de ces 3 badge et les mettre dans la table « DimBadgeMatrix »  avec comme clé ‘’BadgeMatrixId = 1’’.

On génère la 1ere table de la manière suivante :
À partir de cette table « DimBadgeMatrix »  on remplis la table de fait « FactBadgeMatrix », ça va nous permettre de garder le lien entre la table  « FactBadge » et « FactBadgeMatrix »

Enfin on met dans la table « FactComment » un lien directe vers la table de fait.

La taille de la colonne ‘’BadgeMatrixKey’’ va devenir un problème à gérer.

Il n’est pas envisageable de passer ‘’BadgeMatrixKey’’ en VARBINARY(MAX) or nous allons très vite dépasser les 8000 caractères maximum d’un VARCHAR(max).

Solution : Les fonctions de hachage sont là pour nous aider.

On nomme fonction de hachage une fonction particulière qui, à partir d’une donnée fournie en entrée, calcule une empreinte servant à identifier rapidement, bien qu’incomplètement, la donnée initiale. Les fonctions de hachage sont utilisées en informatique et en cryptographie.

A noter : il faut bien choisir sa fonction de hachage la plus adapté au cas afin d’éviter tout risque de tomber en collision.

modif cube

Dans SSAS, la nouvelle table de Fait « FactBadge-Matrix » est en Relation de type ‘’Regular’’ avec les dimensions « DimBadge » & « DimBadge Matrix ».

L’autre table de fait « Fact Comment » quant à elle est liée à la dimension « DimBadge » avec une Relation de type Many-to-Many en passant par la table de fait « FactBadge-Matrix ».

Modification ETL (4/4)

L’ETL doit :

  • Alimenter DimBadgeMatrix
  • Alimenter FactBadgeMatrix
  • Mettre à jour la table FactComment
  • Si votre liste d’identifiants dépasse 8000 caractères, l’ETL doit gérer le Hachage de la colonne.

Conclusion :

Conclusion

On remarque sur le graphique ci-dessus que la méthode ‘’M2M Matrix+Aggreg’’ est la plus rapide en termes de temps de requêtage. En revanche, le temps d’alimentation dans l’ETL est considérablement plus long par rapport à la méthode ‘’M2M+Aggreg’’. Le temps du process cube quant à lui est quasiment le même.

 

 

[JSS 2014] Session : BI et déploiement automatique avec TFS

Speaker : Romuald COUTAUD & Khirdine HADDAR
Level : 200

Objectif : Déploiement des solutions BI (SQL, SSIS, SSAS, SSRS) automatiquement avec TFS

Introduction :
Dans tous les projets BI, il y a différentes façons de gérer l’industrialisation de nos projets (SQL, SSIS, SSAS, SSRS). Cette session va permettre d’illustrer TFS (Team Foundation Server), l’un des moyens d’industrialisation de ces projets en automatisant la génération et le déploiement des livrables dans les différents environnements (Dev, Intégration, Prod).
Ce qui va être présenté ici, ce sont juste le versioning des solutions ainsi que la génération et le déploiement automatique des différents projets (TFS Build).

TFS :

  • Gestion des versions des projets
  • Packager les livrables
  • Build TFS
    • Extraction et copie automatique sur le serveur
    • Génération des projets (se fait à l’aide de MSBuild, un framework dotNet)
    • Déploiement

Le déploiement se fait à l’aide de WWF (Windows Workflow Foundation). Il est possible d’utiliser des scripts PowerShell pour compléter ces tâches.
Beaucoup de DLL (codeplex) ont été développées par la communauté et seront utilisées.

Outils :
SSIS : MSBuildSSIS2012 (génération & déploiement)
SSAS : SSASHelper (génération)
SSRS : SSRSMSBuildTasks

Démo : Déploiement SQL Server

  • définition du Build avec les différents arguments (deploy, environment, …)
    • fait appel à TFSBuild.exe

>> Toute la base a été déployée sur l’instance DB indiquée, avec les schémas associés.

Démo : Déploiement SSIS

  • le fichier .proj a été modifié pour prendre en compte des options non natifs
  • possibilité d’ajouter un fichier .xaml (WWF) pour avoir des options en plus aussi (BuildSSIS, …)
  • lancement du Build
    • compilation du projet SSIS + déploiement
    • le build peut se lancer aussi en ligne de commande. En changeant les paramètres, on peut facilement déployer les mêmes sources mais sur des instances différentes

Démo : Déploiement SSAS

  • mêmes procédés que précédemment à l’exception qu’un fichier de config.xml peut permettre de customiser ses différentes sources
  • comme indiqué dans la partie Outils, le codeplex pour SSAS ne permet pas de déployer les solutions SSAS. Pour la démo, un script PowerShell a été développé, permettant les déploiements

Démo : Déploiement SSRS

  • pas de surprise par rapport aux précédentes démos. La DLL, récupérée sur codeplex, met à disposition plusieurs méthodes permettant de checker l’existance d’un rapport, l’ajout/suppression d’un rapport, la modification de la source, etc.
  • les étapes de génération et déploiement restent les mêmes que précédemment

Pré-requis :

  • avoir un server TFS configuré
  • adapter tous les codeplex récupérés

Conclusion :
On entend beaucoup parler de TFS (surtout chez les dotNetiens) mais durant mes différentes missions, je n’ai pas eu l’occasion de voir cette méthode mise en place.
Très bonne session, on a pu voir qu’une fois que toutes les configurations, pour chaque type de solution, ont été mises en place, on peut facilement déployer nos solutions sur chacun de nos environnements et du coup, faciliter la tâche à nos chers collègues du support (et nous même).
Reste à voir ce que TFS peut donner avec les tests unitaires par exemple…

70-467 – Designing Business Intelligence Solutions with Microsoft SQL Server 2012 – Novembre 2013

La préparation

Comme pour l’examen 70-466, il n’existe pas de training kit officiel. Le programme couvre de l’infrastructure aux différentes couches BI ( SSAS, SSRS, SSIS, MDS/DQS, intégration à SharePoint,… ). Etant intéressé par l’infrastructure, j’ai pu pratiquer Always On et la réplication mais pas dans le cadre de production.  Je ne peux donc que conseiller d’étudier les aspects d’authentification et de délégation et les aspects d’architecture lié à la BI.

L’examen

A peine une pause et j’enchainais sur cet examen que je redoutais et je ne fût pas déçu. Cet examen comporte 2 études de cas avec 10 questions chacun et 30 autres questions.

Les « case study » décrivent l’architecture d’une société et les options envisagées pour la faire évoluer ( cas d’une migration à 2012) et les problèmes rencontrés. Les questions sont assez large, de la validation de choix d’architecture à la résolution de problème en passant par les bonne pratiques. Les questions sont assez difficiles.

Les 30 questions suivantes sont aussi difficiles et sont assez larges sur les sujets de cet examen.

Au final, le MCSE est acquis et le soulagement fût total au moment de valider mon examen.

70-466 – Implementing Data Models and Reports with Microsoft SQL Server 2012 – Novembre 2013

La préparation

J’ai passé les certifications liées au MCSA en me basant sur les excellent training kit, mon expérience et divers sites pouvant répondre à mes questions. Mais lorsque je me suis décidé à passer mon MCSE Business Intelligence, j’ai été surpris de ne pas trouver de training-kit officiel sur ce sujet.
En parcourant le programme de cet examen, trois parties se distinguent: l’administration multidimensionnel ( SSAS et SSRS), le tabulaire.
Pour le multidimensionnel, je me suis basé sur les le training kit 70-448. Concernant le tabulaire, j’ai regardé et pratiqué beaucoup de tutoriels ne pratiquant pas en mission ce nouveau mode.

L’examen

Ayant une forte expérience sur la partie multi-dimensionnel, j’abordais cet examen confiant. Lorsque l’on a un doute, il faut souvent se dire, quelle serait la réponse dans l’esprit Microsoft. En découvrant les 50 questions, l’examen est très complet avec comme d’habitude les questions à choix multiples sur 4 sujets différent, les drag& drop à mettre dans le bon sens.
L’examen s’étant bien déroulé et enchainant le 70-467, ma joie de réussite fut courte.

[JSS2013] Session : SSAS Tabular : DAX

Speaker : Marco RUSSO
Niveau : 300-400

Objectif de la session :

Analyser les plans d’exécution DAX avec SQL Profiler pour améliorer les perfs

Introduction :

Le modèle tabulaire se base sur 2 moteurs : Formula Engine et Storage Engine (VertiPaq/xVelocity)

Les différents types de Plan d’exécution :

On a trois types de plan d’exécution : logique (flux logique de la requête), physique () et VertiPaq Queries (les requêtes exécutées par le moteur xVelocity).

Chaque code DAX va générer un plan d’exec différent.

Voici les évènements à tracer :

  • Error
  • Query begin (requête DAX)
  • Query end
  • DAX Query Plan
  • VertiPaq Storage Engine (SE) Query Begin
  • VertiPaq SE Query Cache
  • VertiPaq SE Query End

Analyser les traces

Pour avoir une meilleure lisibilité des traces, on peut utiliser le codeplex DAX Studio. C’est un outil avec un requêteur et un profiler intégrés. Le résultat des traces est récupérable sur Excel.

Les opérations VertiPaq

  • VertiScan => Filtre les données sur un ou plusieurs colonnes
  • VertiCalc => pour des calculs simples les calculs sont faits par le moteur, pour des calculs complexes le moteur fait appel au formula engine pour effectuer les calculs et effectuer des allers retours

Exemple :

  • Le SUM est exécuté dans le moteur xVelocity et pas dans le Formula Engine. Le SUM est donc très performant.
  • Le SUMX est exécuté dans le Formula Engine car il évalue les calculs ligne à ligne. Tant que les calculs sont simples, ils se font dans le storage engine. Dès qu’on voit apparaître le CallbackDataID, c’est que l’expression devient assez complexe et le moteur rebascule en formula engine pour chaque ligne.

N.B : N’oubliez pas de vider le cache lors de vos tests.

Quelques bonnes pratiques

  • Filtrez les données au plus tôt pour réduire le plan d’exécution
  • Utilisez les relations au lieu du CALCULATE
  • Privilégiez ADDCOLUMNS à la place du SUMMARIZE
  • Délaissez les fonctions de type If…

Conclusion :

Très bonne session de Marco Russo qui nous explique bien la mécanique interne du moteur VertiPaq.

David – Consultant décisionnel MCNEXT